Release Notes for Apache Derby 10.10.1.1

Summary of Change

A SQLWarning is now raised if you try to change the encryption state of an already booted database.

Symptoms Seen by Applications Affected by Change

A SQLWarning is raised if an application sets any of the following attributes while trying to connect to an already booted database:

• dataEncryption
• newBootPassword
• newEncryptionKey
• decryptDatabase

The connection attempt will succeed but a SQLWarning will appear on the warning chain returned by Connection.getWarnings(). The warning will have SQLState 01J17 and the following message text: You cannot encrypt, re-encrypt, or decrypt a database which is already booted. You must shutdown the database before attempting these operations.

Incompatibilities with Previous Release

In previous releases, Derby did not raise a SQLWarning when an application set the above attributes while connecting to an already booted database. This may cause an incompatibility now if an application always sets the dataEncryption attribute when connecting to an encrypted database. In previous releases, that attribute was simply ignored. Now the application sees an extra SQLWarning on the warnings chain after connecting to the encrypted database.

Rationale for Change

All of the affected attributes share a common meaning: change the encryption state of the database. When the database was already booted, the request to change its encryption state was ignored silently. The application could not tell whether the encryption state of the database had actually been changed. Now the application can check the warning chain to determine whether the database was encrypted, re-encrypted, or decrypted as requested.

Application Changes Required

Applications should check the warning chain after requesting a change to the encryption state of the database.

Applications which check the warning chain may need to be adjusted if they set the dataEncryption attribute whenever they connect to an encrypted database.

Summary of Change

Changed SQL states for errors while re-encrypting a database.

Symptoms Seen by Applications Affected by Change

When re-encryption of a database fails and throws SQLException, the SQL state returned by SQLException.getSQLState() may not be the same as it was in earlier releases.

The following SQL states and messages have changed in this version:

XBCXP

Cannot re-encrypt the database with a new boot password or an external encryption key when there is a global transaction in the prepared state.

XBCXR

Cannot re-encrypt a read-only database with a new boot password or an external encryption key .

XBCXT

Cannot re-encrypt a database with a new boot password or an external encryption key, when database is in the log archive mode.

XBCXV

Encryption of an encrypted database with a new key or a new password failed: <failureMessage>

The new SQL states and messages reported for the above failure scenarios, are:

XBCXO

Cannot encrypt, re-encrypt or decrypt the database when there is a global transaction in the prepared state.

XBCXQ

Cannot encrypt, re-encrypt or decrypt a read-only database.

XBCXS

Cannot encrypt, re-encrypt or decrypt a database when it is in the log archive mode.

XBCXU

Encryption, re-encryption or decryption of a database failed: <failureMessage>

Incompatibilities with Previous Release

The SQL states for some errors during re-encryption of a database have changed.

Rationale for Change

The consolidated messages simplify the error handling in the database encryption code.

Application Changes Required

Applications that look for the old SQL states should be changed to look for the new SQL states.

Summary of Change

Table function arguments may no longer refer to other tables in the same query block.

Symptoms Seen by Applications Affected by Change

In previous releases, table functions could be invoked with arguments which referred to other tables in the same query block. Now Derby will reject those queries when the statement is prepared. For instance, the following query, which used to work...

select *
from T, table( F( T.a ) ) S;

...now raises this error:

ERROR 42ZB7: Illegal reference to column 'T.A'. Table function parameters may not refer to other tables in the same query block.

Incompatibilities with Previous Release

Applications may fail if they join table functions to other tables inside the FROM list.

Rationale for Change

The old syntax violated the SQL Standard and did not have a well-defined meaning.

Application Changes Required

No change is required if table function arguments refer to tables in outer query blocks. For instance, the following query still works:

select *
from T
where exists ( select B from table( F( T.a ) ) S );

However, applications need to re-write any query which uses the FROM list to join a table function to another table in the same query block. Developers should reason carefully about what the old syntax meant to express and then re-write the query in a legal way. In many cases, the following transformations may result in a query which expresses what the developer originally intended:

• New column - Add a new column to the end of the ResultSet returned by the table function. The new column is the value of the original argument which joined to another table in the FROM list.
• New overload - Add a new overload for the table function. The new overload omits the original argument which joined to another table in the FROM list. The new overload returns results for all possible values of the omitted argument.
• Move the join into the WHERE clause - Now re-write the failing query, moving the join out of the FROM list and into the WHERE clause.

After applying these transformations, the failing query (given above) would look like this:

select *
from T, table( F() ) S
where T.A = S.A;

Summary of Change

A new system procedure helps correct misbehaving triggers and metadata queries.

Symptoms Seen by Applications Affected by Change

Applications did not have a way to recompile metadata queries and triggers if a Derby bug made the queries and triggers behave incorrectly. If you suspect that your metadata queries or triggers are misbehaving, you may be able to fix their behavior by calling the following new system procedure:

CALL SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS()

Rationale for Change

The new SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS procedure was introduced as a workaround when Derby bugs break metadata queries and triggers.

Summary of Change

ResultSet#updateBigDecimal on a REAL or DOUBLE column now does underflow checking.

Symptoms Seen by Applications Affected by Change

Using ResultSet#updateBigDecimal will throw SQL error 22003 in applications that didn't use to throw.

Incompatibilities with Previous Release

If the application performs ResultSet#updateBigDecimal on a REAL or a DOUBLE column with a value that is too small to be represented in Derby and didn't include code to handle SQLDataException 22003, it would now need to handle this (and possibly update the value with 0.0 itself if that is the desired behavior).

Rationale for Change

To make Derby behavior more consistent and to reliably detect underflow behavior.

Application Changes Required

If the application would sometimes try to perform ResultSet#updateBigDecimal with a number that can't be represented by REAL or DOUBLE, as the case may be, an exception handler may have to be added.

Summary of Change

Derby will create readme files in the database directories.

Symptoms Seen by Applications Affected by Change

A database created with Derby 10.10, or upgraded to 10.10, will contain readme files named README_DO_NOT_TOUCH_FILES.txt in the top-level database directory and in the subdirectories seg0 and log.

Rationale for Change

Unintentional editing of the database files by the users can leave the database in a non-recoverable state. To caution users against such an action, a database created with 10.10 or upgraded to Derby 10.10 will have three readme files which will warn users against touching any files in the various database directories. These readme files are in the top-level database directory and in the subdirectories seg0 and log.

Summary of Change

Legacy DB2 compatibility limits on REAL and DOUBLE values have been lifted.

Symptoms Seen by Applications Affected by Change

SQLDataException 22003 may not be raised for applications which use float and double Java values outside DB2's limits for SQL REAL and DOUBLE.

In soft upgrade mode, the checking of the limits is still in place, but is deferred from ResultSet#updateXXX to ResultSet#updateRow.

Incompatibilities with Previous Release

The application cannot rely on Derby to detect when values outside the legacy DB2 limits are used. These limits are:

    float DB2_SMALLEST_REAL = -3.402E+38f;
    float DB2_LARGEST_REAL  = +3.402E+38f;
    float DB2_SMALLEST_POSITIVE_REAL = +1.175E-37f;
    float DB2_LARGEST_NEGATIVE_REAL  = -1.175E-37f;

    double DB2_SMALLEST_DOUBLE = -1.79769E+308d;
    double DB2_LARGEST_DOUBLE  = +1.79769E+308d;
    double DB2_SMALLEST_POSITIVE_DOUBLE = +2.225E-307d;
    double DB2_LARGEST_NEGATIVE_DOUBLE  = -2.225E-307d;

In soft upgrade mode, the place where the limits are checked has changed, cf. above.

Rationale for Change

We do this to allow applications to store a wider range of the Java floating point number types in Derby. It lets the application use Derby for all Java float and double values with the exception of NaN (not a number), -0.0 (normalized to +0.0) and +/- infinity as defined in the IEEE-754 1985 floating-point standard.

Application Changes Required

If the application relied on Derby to detect when use of numbers outside the DB2 limits were used, the application would now need to perform such checks itself.

Summary of Change

Error codes match severity levels on the network client.

Symptoms Seen by Applications Affected by Change

In previous releases, an SQLException raised by the network client would in nearly every case return -1 from its getErrorCode() method.

Starting with this release, if both the network server and the network client are running Derby version 10.10 or higher, getErrorCode() returns a value that represents the severity of the error, and that is different from -1.

Special note for mixed version environments: If either the server or the client is at version 10.10 or higher, and the other party is at version 10.9 or lower, getErrorCode() may return a value that is neither -1 nor the severity of the error.

Incompatibilities with Previous Release

Applications that check the value returned by SQLException.getErrorCode() will see a different value in the new release.

Rationale for Change

The change makes getErrorCode() behave consistently in the network client driver and the embedded driver.

Application Changes Required

If the application is coded in a way that depends on SQLException.getErrorCode() returning -1, it will have to be recoded to ignore the error code or to accept a different value.

In environments where the server version and the client driver version differ, and the return value from SQLException.getErrorCode() has significance for the application, it is recommended to upgrade both the client driver and the server to version 10.10 or higher.