Release Notes for Apache Derby 10.11.1.1

Summary of Change

More type mismatches are detected in THEN and ELSE clauses of CASE expressions.

Symptoms Seen by Applications Affected by Change

If a CASE expression has a THEN clause or an ELSE clause that consists of an explicitly typed NULL, and the type is not compatible with all the other THEN and ELSE clauses of the CASE expression, an exception will be thrown.

For example, the following CASE expression

CASE
  WHEN a=b THEN 1
  ELSE CAST(NULL AS CHAR(10))
END

will cause the following error

ERROR 42X89: Types 'CHAR' and 'INTEGER' are not type compatible.
Neither type is assignable to the other type.

In Derby versions from 10.3 to 10.10, the same expression would have succeeded, and it would have evaluated either to 1 or to NULL with type INTEGER.

Rationale for Change

The old behaviour was unintended and could hide bugs in SQL statements.

Application Changes Required

Applications that cast NULL to an incorrect type in a THEN or ELSE clause, should rewrite that clause to use either an implicitly typed NULL or an explicitly typed null of a type compatible with the other THEN or ELSE clauses.

For example, the failing expression mentioned above could be rewritten to the following:

CASE
  WHEN a=b THEN 1
  ELSE NULL
END

The NULL in the ELSE clause will get its type inferred from the type of the THEN clause. That is, INTEGER.

If an explicitly typed NULL is preferred, the expression could also be rewritten to the following:

CASE
  WHEN a=b THEN 1
  ELSE CAST(NULL AS INTEGER)
END

Summary of Change

You can no longer add a default to an identity column.

Symptoms Seen by Applications Affected by Change

You can no longer change an identity column as follows:

alter table MyTable alter column MyIdentityColumn default 99;

Incompatibilities with Previous Release

Previously, that statement would have added a default to MyTable.MyIdentityColumn and the column would have ceased to be an identity column.

Rationale for Change

The previous behavior violated the SQL Standard.

Application Changes Required

Applications which need to change an identity column into a non-identity column with a default should be re-coded to do something like this:

alter table MyTable add column dummy int default 99;
update MyTable set dummy = MyIdentityColumn;

alter table MyTable drop column MyIdentityColumn;
rename column MyTable.dummy to MyIdentityColumn;

Summary of Change

Identity columns are now backed by internal sequence generators.

Symptoms Seen by Applications Affected by Change

In previous releases, identity values were managed in the heavily used SYS.SYSCOLUMNS table. This caused lock contention among insert statements.

Incompatibilities with Previous Release

After hard-upgrading to 10.11, identity columns will now be backed by internal sequence generators. This should reduce lock contention among insert statements. It also means that identity columns now pre-allocate ranges of upcoming values, just as sequences do. Applications should take extra care to shutdown databases gracefully before exiting. If an application crashes or does not close its databases gracefully, then the unused, pre-allocated identity values will leak; the user will see a gap between the last identity value inserted before the crash and the first identity value inserted after restarting the application.

In addition, after hard-upgrading to 10.11, users will no longer be able to query the SYS.SYSCOLUMNS table in order to discover the next value which will be inserted into an identity column. Instead, users should use the new SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY() system function. Users should never directly query SYS.SYSCOLUMNS or SYS.SYSSEQUENCES. Directly querying these catalogs will acquire read locks which may throttle application throughput.

Rationale for Change

This change was made in order to improve the throughput/performance of inserts into tables which have identity columns.

Application Changes Required

After hard-upgrading to 10.11, be sure that your application closes its databases gracefully so that you do not leak unused, pre-allocated identity values. Individual databases may be closed via the shutdown=true attribute:

DriverManager.getConnection( "jdbc:derby:myDatabase;shutdown=true" );

Alternatively, all open databases may be closed by shutting down the engine:

DriverManager.getConnection( "jdbc:derby:;shutdown=true" );

If your application is prone to ungraceful crashes and you cannot tolerate leaking unused, pre-allocated identity values, then you can adjust the maximum number of unused values per identity column. You can do this be setting the derby.language.sequence.preallocator database property. The default setting for this property is 100:

call syscs_util.syscs_set_database_property( 'derby.language.sequence.preallocator', '10' );

In addition, after hard-upgrading to 10.11, applications should be adjusted so that they call SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY() in order to discover the next value which will be inserted into an identity column. Applications should no longer directly query SYS.SYSCOLUMNS for this information:

values SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY( 'APP', 'MYTABLE' );

Summary of Change

Implementation of LOG10, COSH, SINH and TANH changed.

Symptoms Seen by Applications Affected by Change

Apache Derby has built-in logarithmic and hyperbolic functions that live in the SYSFUN schema. Most, but not all, of these functions are implemented as calls to the corresponding methods in the java.lang.StrictMath class. In this release, more functions than before use the methods in the java.lang.StrictMath class.

Specifically, the implementation of the LOG10, COSH, SINH and TANH functions have changed, and for some input values the values returned by those methods have changed.

For example, the function call LOG10(1000) would return 2.9999999999999996 in the previous versions. In this version, it will return 3.0.

The function call TANH(1000) would fail with

ERROR 22003: The resulting value is outside the range for the data type DOUBLE.

in previous versions. In this version, it will succeed and return 1.0.

Rationale for Change

Using the java.lang.StrictMath class instead of custom implementations makes the functions return more accurate results. It also fixes issues where the custom implementations experienced overflow in intermediate results and failed instead of returning a result.

Application Changes Required

The new implementations are used automatically after upgrade without any changes to the application. If your application uses any of the affected functions, you should check that it doesn't depend on these functions returning the exact same results before and after the upgrade.

If one of the affected functions is used in the generation expression of a generated column, the value of the generated column will not be recalculated automatically on upgrade. It will be recalculated when a column referenced in the generation expression is updated, or if the generated column is updated to its DEFAULT value. To force the generated values to be recalculated sooner after upgrade, you can issue an UPDATE statement such as:

UPDATE t SET generated_column = DEFAULT

Summary of Change

Privileges required for INSERT and DELETE statements have changed.

Symptoms Seen by Applications Affected by Change

Fewer privileges are now required to execute INSERT and DELETE statements.

Incompatibilities with Previous Release

In previous versions, INSERT and DELETE statements demanded that the user enjoy EXECUTE privilege on functions and USAGE privilege on types mentioned by the target table's check constraints, generated columns, and triggers. Those privileges are no longer required by INSERT and DELETE statements. INSERT and DELETE statements which previously failed due to insufficient privileges may succeed now.

Rationale for Change

This change makes Derby conform better to the SQL Standard.

Application Changes Required

Security may now be tightened for applications which run with SQL authorization enabled. Those applications may revoke EXECUTE and USAGE privileges which are no longer necessary in order to run INSERT and DELETE statements.

Summary of Change

Privileges required for UPDATE statements have changed.

Symptoms Seen by Applications Affected by Change

In previous versions, UPDATE statements demanded that the user enjoy UPDATE privilege on all columns from the target table which were mentioned in the WHERE clause. Now Derby requires SELECT privilege on those columns, instead.

Incompatibilities with Previous Release

In previous versions, UPDATE statements demanded more privileges than the SQL Standard required. In particular, UPDATE statements required...

• ...UPDATE privilege on columns from the target table which were mentioned in the WHERE clause.
• ...EXECUTE privilege on functions and USAGE privilege on types mentioned by the table's generation clauses, CHECK constraints, and UPDATE triggers.

Now Derby no longer demands these overbroad privileges. However, Derby does require SELECT privilege instead of UPDATE privilege on columns from the target table which are mentioned in the WHERE clause.

Rationale for Change

This change makes Derby conform better to the SQL Standard.

Application Changes Required

In applications which run with SQL authorization enabled, an UPDATE statement may now fail because the application has not granted the user SELECT privilege on all target table columns mentioned in the statement's WHERE clause. Those applications should grant users the appropriate SELECT privileges.

In addition, applications may now tighten their security by revoking UPDATE, EXECUTE, and USAGE privileges which are no longer necessary in order to run UPDATE statements.

Summary of Change

Derby no longer runs on Java 5 and CDC.

Symptoms Seen by Applications Affected by Change

Previous releases of Derby ran on Java 5 and on the small device CDC platform. The 10.11 release family only runs on Java 6 and higher JVMs.

Incompatibilities with Previous Release

Applications currently running on Java 5 or CDC will not be able to use Derby 10.11. Customers must upgrade their Java platform before installing Derby 10.11.

Previously, Derby's public javadoc included two branches: one for applications which ran on Java 5 and CDC, and another for applications which ran on Java 6 and higher. Now there is one set of public javadoc intended for use on all supported JVMs. Applications are encouraged to use the following Derby DataSources when running on a full Java SE/EE JVM:

• ClientConnectionPoolDataSource
• ClientDataSource
• ClientXADataSource
• EmbeddedConnectionPoolDataSource
• EmbeddedDataSource
• EmbeddedXADataSource

...and the following DataSources when running on Java 8's small-device compact profile 2:

• BasicClientConnectionPoolDataSource40
• BasicClientDataSource40
• BasicClientXADataSource40
• BasicEmbeddedConnectionPoolDataSource40
• BasicEmbeddedDataSource40
• BasicEmbeddedXADataSource40

For backward compatibility reasons, Derby continues to include the following DataSources. However, they are vacuous extensions of their superclasses now and may be removed in the future. Applications are encouraged to migrate away from these DataSources and to use the DataSources listed above instead:

• ClientConnectionPoolDataSource40
• ClientDataSource40
• ClientXADataSource40
• EmbeddedConnectionPoolDataSource40
• EmbeddedDataSource40
• EmbeddedXADataSource40

Rationale for Change

The older Java platforms are no longer being actively developed and they may contain well-known security vulnerabilities. The Java community is encouraged to migrate to modern, more secure JVMs which are being actively developed. Users interested in running Derby on small devices are encouraged to use Java 8's compact profile 2.

Application Changes Required

Customers who use Java 5 or CDC will need to upgrade their Java platform if they want to use features introduced by Derby 10.11. Applications are encouraged to migrate to the supported DataSources listed above.

Summary of Change

Due to a bug introduced in Derby 10.8, the serialized version number of the class EmbeddedConnectionPoolDataSource40 has changed in Derby 10.8 and later.

Symptoms Seen by Applications Affected by Change

Serialized objects for the class EmbeddedConnectionPoolDataSource40 produced by a Derby version 10.7 or older would not be readable with this version of Derby.

Incompatibilities with Previous Release

Derby releases newer than 10.8 can't read serialized data source objects of the class EmbeddedConnectionPoolDataSource40 if those objects were produced by Derby version 10.7 or older.

Rationale for Change

Accidental change.

Application Changes Required

N/A.

Summary of Change

Estimates have changed for the memory needed when hash-joining LOB-bearing tables.

Symptoms Seen by Applications Affected by Change

In previous releases, BLOBs and CLOBs held in memory were estimated to take zero bytes. This would mean that hash joins with many objects of type BLOB or CLOB could use a large amount of memory. That might improve performance. However, it could cause OutOfMemory errors. After the change for DERBY-6096, hash joins may spill to disk earlier and thus run slower.

Incompatibilities with Previous Release

BLOBs and CLOBs did not have a maximum memory limit for hash joins. Now they have the default limit of 1048576 (1MB). This limit can be overridden by setting the derby.language.maxMemoryPerTable property.

Rationale for Change

Hash joins of LOB-bearing tables were raising OutOfMemory errors and crashing the engine.

Application Changes Required

To allow BLOB/CLOB (and all) hash joins to use more memory, set the Derby property derby.language.maxMemoryPerTable to be the number of bytes you would like to allow for each hash join.

Summary of Change

Dropping objects mentioned by triggers now fails.

Symptoms Seen by Applications Affected by Change

When DROP TABLE/VIEW/PROCEDURE/FUNCTION/SYNONYM is invoked on an object which is used by a trigger in a triggered SQL statement, the DROP operation now fails and the object is not dropped. In previous releases, those operations would have succeeded, and an exception would have been thrown the next time the dependent trigger fired.

The message text of the new SQLException looks like this:

ERROR X0Y25: Operation 'DROP TABLE' cannot be performed on object 'T' because TRIGGER 'TR' is dependent on that object.

The new exception is thrown only if the trigger was created with version 10.11 or higher. If the dependent trigger was created with an older version, the DROP operation will succeed, and an exception will be thrown the next time the trigger fires.

Rationale for Change

The previous behavior dropped objects and made other objects invalid. That caused subsequent errors. The new behavior helps prevent such problems.

Also, the new behavior makes DROP TABLE/VIEW/PROCEDURE/FUNCTION/SYNONYM consistent with DROP TYPE/SEQUENCE/DERBY AGGREGATE, ALTER TABLE ... DROP COLUMN and REVOKE. Those statements already failed when there was a dependent trigger.

Application Changes Required

Applications that drop objects used in triggered SQL statements, must drop the dependent trigger before dropping the dependency.

Summary of Change

CASE expressions require at least one result expression with a known type.

Symptoms Seen by Applications Affected by Change

Earlier versions allowed CASE expressions where all the result expressions (then THEN and ELSE clauses) were untyped NULLs or a mix of untyped NULLs and untyped parameters. Now the following error will be raised when an application evaluates such an expression:

ERROR 42X87: At least one result expression (THEN or ELSE) of the CASE expression must have a known type.

Incompatibilities with Previous Release

Applications that use a CASE expression with unknown return type now fail.

Rationale for Change

The SQL standard requires that at least one of the result expressions is not an untyped NULL.

The previous behavior was inconsistent, as it accepted CASE statements where all result expressions were untyped if they were all NULLs or if they were a mix of NULLs and parameters, but it failed if they all were parameters.

Also, it arbitrarily chose the type CHAR(1) if it could not determine the type of the CASE expression. That type may or may not be the type the application wants. It is safer to fail when the type cannot be determined, and let the application specify explicitly which type it wants.

Application Changes Required

If an application has a CASE expression that fails because of this change, it should change the CASE expression so that at least one of the THEN or ELSE expressions has a known type.

For example, the following expression

CASE
  WHEN a = b THEN ?
  ELSE NULL
END

could be changed to

CASE
  WHEN a = b THEN CAST(? AS CHAR(1))
  ELSE NULL
END

to make it clear to the compiler that it actually wants the expression to return a value of type CHAR(1).