Release Notes for Derby 10.2.2.0

Overview

These notes describe the difference between Derby release 10.2.2.0 and the preceding release, 10.2.1.6. 10.2.2.0 is a bug-fix release. It includes compiled versions of Derby's JDBC4 drivers, which appeared in the previous release only as source code. In addition, 10.2.2.0 includes a number of bug fixes not found in the previous release. No new features appear in 10.2.2.0.

Derby is a pure Java relational database engine using standard SQL and JDBC as its APIs.

Derby functionality includes:

SQL support:

Other features:

JDK/JDBC support:

New Features

Release 10.2.2.0 is a bug-fix release. No new features were added since 10.2.1.6.

Bug Fixes

The following bug fixes turn up in Derby 10.2.2.0 but not in the preceding 10.2.1.6 release.

Issue Id Description
DERBY-638 Network driver setTransactionIsolation() causes a commit, but does not complete it locally
DERBY-737 SYSCS_UTIL.SYSCS_COMPRESS_TABLE should create statistics if they do not exist
DERBY-912 OutOfMemory error on continuous execution of SQL statement
DERBY-1089 Derby fails inserting a join into a table with a generated column
DERBY-1204 CREATE TRIGGER with an INSERT action statement with multiple rows and a referenced column throws a StringIndexOutOfBoundsException
DERBY-1231 LIKE does not match empty strings when used with a prepared statement
DERBY-1495 Attempt to modify an identity column error after resetting identity column
DERBY-1645 ALTER TABLE ... SET INCREMENT BY X... Turns off the "Generated By Default" identity column constraint
DERBY-1693 Out of Memory Error with derby.language.logStatementText=true
DERBY-1716 Revoking select privilege from a user times out when that user still have a cursor open.
DERBY-1732 The language and store systems treat a JVM error such as OutOfMemoryError differently leading to the raw store protocol violation errors
DERBY-1847 SELECT statement asserts with XJ001 when attempted to select a newly added column in SQL authorization mode
DERBY-1856 Multiple communication failures when starting server with derby.drda.timeSlice
DERBY-1894 SQLSTATE 42X10 occurs when qualifying a column with a synonym in ORDER BY clause
DERBY-1940 Removed Ease of Development API
DERBY-1967 UNION (ALL) constraint violation problem
DERBY-1991 Misleading stack traces for exceptions raised by the JDBC 4.0 embedded driver
DERBY-2008 NullPointerException with LTRIM, RTRIM and 2-argument SUBSTR() call in GROUP BY clause.
DERBY-2014 NullPointerException with NULLIF in GROUP BY clause
DERBY-2015 NullPointerException in INSERT ... SELECT with self-joined table and IDENTITY column
DERBY-2030 'set schema sys' followed by 'show tables' does not show tables in sys schema
DERBY-2084 getTransactionIsolation() in network client should not activate a transaction
DERBY-2131 External DTD files are accessed without a privileged block when Derby parses XML values that reference such DTDs.

Issues

10.2.2.0 does not introduce any additional issues beyond the 10.2.1.6 issues. Those issues are:

DERBY-253

Problem

PreparedStatement.setUnicodeStream() and ResultSet.getUnicodeStream() throw SQLException when invoked after upgrading to Apache Derby 10.2.

Symptoms

Calling either of these methods will result in an exception with SQLSTATE 0A000 and message: "Feature not implemented: ..."

Cause

PreparedStatement.setUnicodeStream() and ResultSet.getUnicodeStream() have been deprecated since JDBC 2.0. Derby's implemetation of these methods was broken, and it was decided that the methods should throw a not-implemented exception instead of being fixed.

Solution

This was an intentional change. No Derby product solution is offered.

Workaround

Use setCharacterStream() and getCharacterStream() instead of setUnicodeStream() and getUnicodeStream().

DERBY-668

Problem

Sysinfo classpath information was insufficiently detailed.

Symptoms

Sometimes it was hard to tell where the Derby classes were actually being loaded from in the JVM.

Cause

The algorithm that sysinfo used for analyzing and reporting on the application classpath was not robust.

Solution

The sysinfo tool now prints additional information about the origin of the classes and jars that it examines. The origin of a class might be: an entry in the application classpath, an entry in a class loader location list, a jar fetched due to being listed in the manifest entry of another jar, a standard extension in the JRE's extensions directory, a jar installed into the application server, or any of various other possibilities.

Workaround

No workaround. The behavior is now correct.

DERBY-721

Problem

Undefined results were returned to an application which opend an InputStream twice on the same column of a ResultSet.

Symptoms

The value siphoned out of the column was erratic.

Cause

Streams were being shared between the two readers.

Solution

Now we throw an exception if the application tries to open two streams on the same column in a ResultSet.

Workaround

Users must recode Applications which open multiple streams on the same column.

DERBY-781

Problem

When optimizing a query that has one or more non-flattenable subqueries in the FROM clause, Derby will now check to see if it is possible to perform a hash join with that subquery as the inner table. Prior to Derby 10.2, the optimizer would never consider a hash join with a subquery; it only did nested loop joins.

Symptoms

Execution performance of queries containing non-flattenable subqueries may change. The expectation is that the new (10.2) query plans will show improved performance over the old ones.

Another potential symptom is that the compilation time for such queries may increase. If this happens, the increase should only occur at compilation time; execution time should either improve or, at the very least, remain the same as in earlier versions of Derby.

Cause

If the optimizer chooses to do a hash join with a subquery, Derby only has to execute the subquery a single time per statement, after which Derby can just perform the desired join against the materialized result set. Depending on how many rows are in the outer table of the join, this once-per-statement execution of the subquery can lead to major performance improvements over the once-per-outer-row execution employed by earlier versions of Derby.

As for the extra compilation time, this is due to the simple fact that the optimizer is now doing more work--i.e. in addition to considering nested loop joins with subqueries, it is now _also_ considering hash joins with those subqueries, and that means that it could potentially take longer for the optimizer to finish its work. Note again that, if it occurs, the increased time should only occur at compilation time; execution time should either improve or, at the very least, remain the same as in earlier versions of Derby.

Solution

This was an intentional change to improve the execution plans chosen by the optimizer for queries having large and/or complex subqueries. The expectation is that the new behavior--and the subsequent query plans--will lead to improved performance over the old ones, so no further solution is required.

Workaround

There is no way to disable/workaround this new behavior since the symptom as described above is a good one for Derby.

That said, any user who notices a negative performance change after moving to Derby 10.2, and who believes that the difference in performance is related to this optimizer enhancement, is encouraged to visit the performance diagnosis page and to follow up with his/her findings on the Derby mailing lists.

DERBY-822

Problem

Queries may fail earlier and locks may be acquired earlier when executing queries. Location where errors occur in an embedded environment is different from the location where errors occur in a network environment.

Symptoms

Errors that happen as part of the normal execution path are moved earlier. For example, code to execute a query, with executeQuery() retrieve the result set metadata and then perform a next() might fail with a lock timeout on executeQuery() instead of next(). Locking changes are observed.

Cause

Pre-fetching moves execution of retrieval of data earlier for network client/server configurations.

Solution

This was an intentional behavior change to improve performance. No Derby product solution is offered.

Workaround

Application code needs to be changed to adjust error handling if needed.

DERBY-1130

Problem

Derby's client DataSources were using a wrong database name when getting a connection in the following case:

Symptoms

Instead of throwing an exception saying databaseName is a required Derby DataSource property and must be set, client driver was using "null" as database name and returning a connection to database named "null".

Cause

The database name was constructed wrongly in the client driver.

Solution

This was solved by setting the internal database name property in the client driver correctly. Also ensured that databaseName set as a connection attribute will not be used by Derby's client DataSources.. This fix will be available in Derby versions 10.2 and above.

Workaround

If using release prior to version 10.2, make sure database name is set only as a DataSource property when using Derby's client DataSources.

DERBY-1295

Problem

Result sets of type TYPE_SCROLL_INSENSITIVE used to implicitly close when positioned at the end in autocommit mode.

Symptoms

Calling the ResultSet.next() method when positioned on the last row of a result set of type SCROLL_INSENSITIVE in auto commit mode used to cause the result set to be closed.

Cause

The JDBC specification allows a JDBC driver to implicitly close a ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next method of ResultSet returns false. Derby also used to implicitly close result sets of type SCROLL_INSENSITIVE when the ResultSet.next() method returns false in auto commit mode.

Solution

The behavior of SCROLL_INSENSITIVE result sets in auto commit mode has been changed to comply with the JDBC4 specification. SCROLL_INSENSITIVE result sets are not implicitly closed when calling the ResultSet.next() method in auto commit mode while positioned on the last row.

Workaround

Fix applications which rely on the previous, non-standard behavior.

DERBY-1314

Problem

The behaviour of executeQuery() and executeUpdate() did not match the JDBC specification when invoking stored procedures.

Symptoms

Cause

The methods executeQuery() and executeUpdate() were not implemented in compliance with the JDBC specification.

Solution

In Derby 10.2, the behaviour of the methods executeQuery() and executeUpdate() has been changed to match the JDBC specification.

Workaround

Use execute() instead of executeUpdate()/executeQuery() to invoke a stored procedure which does not return exactly 0 or 1 ResultSet objects.

WORKAROUND None.

DERBY-1323

Problem

For a JDBC ResultSet with type TYPE_FORWARD_ONLY, the methods rowUpdated, rowDeleted and rowInserted could previously be called while not on a row, i.e. before positioning in the result set, while on insertRow, after updateRow before new positioning, after deleteRow before new positioning and when after last row. This is now disallowed.

Symptoms

Calls to any of these methods while not on a row will now throw SQLException with SQLState 24000.

Cause

Derby now disallows these calls when not on a row.

Solution

Change the application to not call these methods unless on a row. Note that using them at all is rather meaningless for a ResultSet of type TYPE_FORWARD_ONLY since the returned result will always be 'false'. This is because once you modify a row, it can no longer be accessed, you need to move to the next row, if there is one, to get a new current row. Presently in Derby, these methods are only really meaningfully used for result sets of type TYPE_SCROLL_INSENSITIVE and of concurrency CONCUR_UPDATABLE in which case updated and deleted rows can be detected.

Workaround

Fix applications which rely on this non-standard behavior.

DERBY-1357

Problem

The optimizer will now abandon sub-optimal join orders as soon as it realizes that they cost more than the best join order so far.

This fix also ensures that, in the case of short-circuited join orders, Derby will still generate (and execute) an overall plan that matches the "best path" decisions made by the optimizer--which was not always the case prior to these changes.

Symptoms

Execution performance of large queries (esp. those with nested subqueries and/or with large FROM clauses) may change. The expectation is that the new (10.2) query plans will show improved performance over the old ones.

Cause

Since the optimizer is now spending less time evaluating sub-optimal join orders, it is possible that it will be able to try out more join orders before optimizer "timeout" occurs. As a result the optimizer can sometimes find better plans than it did in earlier versions of Derby.

Solution

This was an intentional change to fix behavior that was not working correctly in earlier versions of Derby. The expectation is that the new behavior--and the subsequent query plans--will lead to improved performance over the old ones, so no further solution is required.

Workaround

There is no way to disable/workaround this new behavior since the symptom as described above is a good one for Derby.

That said, any user who notices a negative performance change after moving to Derby 10.2, and who believes that the difference in performance is related to this optimizer change, is encouraged to visit the performance diagnosis page and to follow up with his/her findings on the Derby mailing lists.

DERBY-1384

Problem

Default BLOB/CLOB length should be the maximum length supported by Derby (2G-1)

Symptoms

An application that used BLOB will current reject values greater than 1M, changing the default means the application will now silently accept those values.

Cause

The allowable size of Derby LOBs has been increased.

Solution

This was an intentional change to make Derby conform to its own documentation.

Workaround

Fix applications which rely on Derby rejecting LOBs that are bigger than 1M.

DERBY-1621

Problem

Trigger action statement is not recompile when there is a change that would affect it.

Symptoms

(1) Trigger action such as an INSERT statement does not get recompiled when the underlying table is affected by a CREATE or DROP INDEX statement. e.g.: 

         create table t (i int);
         create table t2 (i int);
         create trigger tt after insert on t for each statement mode db2sql insert into t2 values 1;
         insert into t values 1;
         select * from t2;
         create unique index tu on t2(i);
         insert into t values 1;
         select * from t2;
         insert into t values 1;
         1 row inserted/updated/deleted

The above example creates an unique index on table t2. when the trigger is fired, it did not raise an unique constraint error.

(2) When the trigger action statement underlying view gets dropped, the trigger statement did not get recompiled. e.g.: 

         create table t11 (c111 int not null primary key, c112 int);
         insert into t11 values(1,1);
         insert into t11 values(2,2);
         create view v21 as select * from user1.t11;
         create table t31 (c311 int);
         create table t32 (c321 int);
         create trigger tr31t31 after insert on t31 for each statement mode db2sql insert into t32 values (select c111 from user1.v21 where c112=1);
         insert into t31 values(1);
         select * from t31;
         select * from t32;
         drop view v21;
         insert into t31 values(1);

In the above example, a view which the trigger action references is dropped; however, the last SQL INSERT statement did not throw an error.

(3) Conglomerate does not exist occurs in a specific case after dropping a table referenced by a trigger. The trigger action is not being recompiled and raises SQLSTATE XSAI2 even though the table being dropped was recreated again. e.g.: 

         create table t1 (id int, name varchar(20));
         create table t2 (id int);
         create trigger test_trigger after insert on t2 for each row mode db2sql insert into t1 values(100, 'hundred');
         insert into t2 values(1);
         insert into t2 values(1);
         select * from t1;
         drop table t1;
         insert into t2 values(1);
         create table t1 (id int, name varchar(20));
         insert into t2 values(1);

In the above example, a table which the trigger action references is dropped. The last INSERT statement should execute successfully but it raises SQLSTATE XSAI2: The conglomerate (896) requested does not exist.

Cause

Derby did not perform invalidation of the trigger action when object(s) that the trigger references are modified or dropped; hence, resulting in the stated problem above. The affected versions are Derby 10.0 and 10.1.

Solution

A fix to resolve the above Derby symptoms is available in 10.2.

Workaround

None.

DERBY-1652

Problem

In some cases, an after update trigger does not get fired upon itself when its trigger action contains an update statement on the trigger's subject table.

Symptoms

(1) When defining a trigger for the first time for a table, e.g.: 

  
        CREATE TABLE "TEST" ("TESTID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
                             "INFO" INTEGER NOT NULL,
                             "TIMESTAMP" TIMESTAMP NOT NULL DEFAULT '1980-01-01-00.00.00.000000');
    
        CREATE TRIGGER UPDATE_TEST
        AFTER UPDATE ON TEST
        REFERENCING OLD AS OLD
        FOR EACH ROW MODE DB2SQL
            UPDATE TEST SET TIMESTAMP = CURRENT_TIMESTAMP WHERE TESTID = OLD.TESTID;
  
        INSERT INTO TEST (INFO) VALUES (1), (2), (3);

        UPDATE TEST SET INFO = 1 WHERE TESTID = 2;

The above update statement executes successfully which it is incorrect. The system should have issued SQLSTATE 54038 since it self-triggers to its maximum depth of 16.

(2) With the above example, when an user upgrades to a higher version and issues the same update statement: 

        UPDATE TEST SET INFO = 1 WHERE TESTID = 2;
        ERROR 54038: Maximum depth of nested triggers was exceeded.

The SQLSTATE 54038 is issued in this case because after database upgrade, the trigger action will be invalidated by the system and will force a recompilation of the trigger when it is fired. The system generates the correct execution plan this time and since the trigger behavior have changed, this might cause applications to break unexpectedly.

Cause

Derby's did not generate the correct execution plan for self-trigger invocation when such a trigger is declared for the first time on the subject table; hence, resulting in the stated problem above. The affected version is Derby 10.0 and 10.1.

Solution

A fix to resolve the above Derby symptom is available in 10.1 and 10.2. thrown now.

Workaround

If self-trigger invocation was not intended by the application, the application can select which column(s) on the update statement can cause the trigger to fire in the CREATE TRIGGER statement. i.e.: 

   CREATE TRIGGER update_test
   AFTER UPDATE OF INFO ON test
   REFERENCING OLD AS old
   FOR EACH ROW MODE DB2SQL
       UPDATE test SET timestamp=current_timestamp WHERE testid=old.testid;

In the above statement, the trigger will only fire when an update is made to the "info" column instead of any column(s).

DERBY-1867

Problem

With IBM 1.4.1 JVM, trying to connect to the server using the derby client with security mechanism 8 (USRSSSBPWD) will result in error.

Symptoms

Connecting using the client driver with security mechanism 8 will throw the following error ERROR XJ112: Security exception encountered, see next exception for details. The stack trace will show that the problem is caused by java.security.NoSuchAlgorithmException: SHA1PRNG SecureRandom not available

Cause

Current USRSSBPWD implementation uses SHA1PRNG algorithm to generate random number(seed) that gets exchanged between client and the server. The SHA1PRNG algorithm is not available with the JCE provider that comes with IBM JVM version 1.4.1.

Solution

You must use another JCE provider.

Workaround

If you need to use the security mechanism 8, then make sure that support for SHA1PRNG is available in the JCE provider that is available with a particular JVM. For e.g. Use IBM 1.4.2 JVM that has support for SHA1PRNG or the Sun JVMs.

Build Environment

Derby release 10.2.2.0 was built using the following environment: