Release Notes for Derby 10.4.1.0

These notes describe the difference between Derby release 10.4.1.0 and the preceding release 10.3.2.1.

Overview

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

Derby functionality includes:

New Features

This is a feature release. The following new features were added:

Bug Fixes

The following issues are addressed by Derby release 10.4.1.0. These issues are not addressed in the preceding 10.3.2.1 release.

Issue IdDescription
DERBY-3538NullPointerException during execution for query with LEFT OUTER JOIN whose inner table selects all constants.
DERBY-3458dblook fails on TERRITORY_BASED databases
DERBY-3442Reference Manual doesn't state limit on number of identity columns
DERBY-3430Inconsistency in JDBC autogen APIs between Connection.prepareStatement(...) and Statement.execute(...)
DERBY-3379"No Current connection" on PooledConnection.getConnection() if pooled connection is reused during connectionClosed processing
DERBY-3373SQL "distinct" and "order by" needed together
DERBY-3366Various formatting erros in L10N property files
DERBY-3352truncateTable crashed, Caused by: java.lang.NullPointerException
DERBY-3350SQL CAST always marks its type as nullable even if the expression to be cast is not nullable
DERBY-3343Subsequent calls to PreparedStatement cause SQLIntegrityConstraintViolationException on column that is "Generated always"
DERBY-3322Server guide refers to phantom property in template policy file for the Network Server
DERBY-3321NullPointerException for 'NOT EXISTS' with nested subquery
DERBY-3316Leak in client if ResultSet not closed
DERBY-3308Broken synchronization for event handling in ClientPooledConnection40
DERBY-3303ArrayIndexOutOfBoundsException at MergeSort.compare
DERBY-3302NullPointerException during recovery of database with territory-based collation
DERBY-3301Incorrect result from query with nested EXIST
DERBY-3299Uniqueness violation error (23505) occurs after dropping a PK constraint if there exists a foreign key on the same columns.
DERBY-3298Getting Started manual needs clearer introduction to connection URL
DERBY-3288wrong query result in presence of a unique index
DERBY-3279Derby 10.3.X ignores ORDER BY DESC when target column has an index and is used in an OR clause or an IN list.
DERBY-3278Developer's Guide topic cdevdvlp51654 has duplicate information on connection URL
DERBY-3274Developer's Guide has duplicate information on database connections
DERBY-3262Documentation is missing cross-references for connection URL attributes
DERBY-3260NullPointerException caused by race condition in GenericActivationHolder
DERBY-3257SELECT with HAVING clause containing OR conditional incorrectly return 1 row - should return 2 rows - works correctly with 10.2 DB
DERBY-3253NullPointer Exception (NPE) from query with IN predicate containing two values and joining a view with a large table. ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while evaluating an expression.
DERBY-3247Activation for a dynamic ResultSet created from an Prepared/CallableStatement will not be closed until garbage collection indicates it is unused to the LCC and the LCC closes it
DERBY-3244NullPointerException in ....B2IRowLocking3.searchLeftAndLockPreviousKey
DERBY-3243(jdbc net client) exception during normal iteration through "ResultSet" of "select * from t"
DERBY-3238When table contains large LOB values (> ~32K) trigger execution fails for that row with ERROR XCL30: An IOException was thrown when reading a 'BLOB'
DERBY-3234Published APIs indicate J2SE 1.3 support
DERBY-3232Top-level index.html in releases incorrectly indicates support for JDBC 2.1 in 10.3 releases
DERBY-3231Sorting on COUNT with OR and GROUP BY delivers wrong results.
DERBY-3230Selecting data from a Table raises Error XN008: Query processing has been terminated due to an error on the server
DERBY-3229testSysinfoLocale fails if derbyTools.jar is first in the classpath
DERBY-3226BlobLocatorInputStream.read() and ClobLocatorInputStream.read() don't mask out sign bits
DERBY-3221"java.sql.SQLException: The conglomerate (-5) requested does not exist." from Derby 10.3.1.4 embedded within Eclipse 3.3 and RAD 7.0
DERBY-3216do row level lock space reclamation in btree of indiv rows.
DERBY-3215Potential NullPointerException in CachedPage class
DERBY-3214Optimizer can see negative cost estimates when pulling Optimizables from the join order.
DERBY-3198Using setQueryTimeout will leak sections
DERBY-3194LOCALIZEDDISPLAY of CURRENT_TIMESTAMP returns only the TIME
DERBY-3175NullPointerException on INSERT after ALTER TABLE ... DROP COLUMN
DERBY-3172ConnectionEventListener.connectionErrorOccurred not being executed when SQLState 08006 Error is thrown
DERBY-3168Reference Manual lacks topics on trace-related connection URL attributes
DERBY-3160SYSCS_GET_USER_ACCESS incorrectly treats the passed in user name as a SQL identifier and thus can reports the wrong user information
DERBY-3159SYSCS_SET_USER_ACCESS fails if the user name contains characters not allowed in regular identifiers.
DERBY-3158Repeat calls to SYSCS_UTIL.SYSCS_SET_USER_ACCESS() for the same user and permission fail with repeated user in list exception.
DERBY-3142sysinfo ignores derby.ui.locale
DERBY-3123Style sheet (CSS) references in demo HTML files are incorrect
DERBY-3119Derby does not resolve functions to methods with Integer return type.
DERBY-3110server hangs after trace on command fails
DERBY-3109Nonexistent property derby.database.defaultAccessMode shown in Developers Guide: User Authentication examples
DERBY-3107On client, Clob.free() does not release locators.
DERBY-3099Possible bug in interaction with buffer manager causing pages not to be freed on rollback to savepoint
DERBY-3098LOB locks are not released after free().
DERBY-3096SSL handshake throws "bad_certificate" when server tries to authenticate client
DERBY-3094Grouping of expressions causes NullPointerException
DERBY-3093Intermittent transaction failure caused by internal duplicate savepoint name for triggers
DERBY-3085Fails to handle BLOB fields with a PreparedStatement with size >32750 bytes
DERBY-3084CREATE SCHEMA in refman does not contain material on restrictions in sqlAuthorization mode
DERBY-3081cannot create instance of generated class because of java.lang.VerifyError in 4 tests on IBM iseries
DERBY-3080With TERRITORY_BASED collation, when multiple characters map to a single collation element '_' matches a single collation element rather than a single character
DERBY-3079Database name is printed twice in derby.log on rollbacks when logStatementText is enabled
DERBY-3077Trying to reconnect with derby client after bringing server down throws SQL Exception 58009 rather than 08XXX exception
DERBY-3075Client error: 08001 Error connecting to server.. throws SQLTransientConnectionException instead of SQLNonTransientConnectionException
DERBY-3074Database shutdown exception 08006 throws SQLTransientConnectionException instead of SQLNonTransientConnectionException
DERBY-3061Wrong results from query with two conjuncts
DERBY-3060Network Server incorrectly assumes that all SQLExceptions with error code 08004 are caused by an authentication failure.
DERBY-3046setObj(int,Object,int) causes NPE if no parameter markers are present; should generate SQLException as for setObject(int,Object)
DERBY-3044Typos in documentation
DERBY-3038SYSCS_IMPORT_TABLE FAILS with No current connection after shutdown/reconnect to encrypted database : 10.3.1.4 regression
DERBY-3033select query results in nullpointer exception in skipScan()
DERBY-3032java.lang.ClassCastException returning null from a case statement in subquery
DERBY-3025NPE when connecting to database with securityMechanism=8
DERBY-3023Different result rows depending on the sequence of INNER JOIN and OUTER JOIN
DERBY-3001SYSTABLES documentation for TABLETYPE should include 'A' (Synonym)
DERBY-3000getTables() call with 10.3 causes java.sql.SQLException: The parameter position '8' is out of range. The number of parameters for this prepared statement is '7'.
DERBY-2989Insert into table with constraints fails if the table name is "indicator" (or any other SQL keyword)
DERBY-2986Query involving CASE statement significantly slower in 10.3.1.4 than in 10.2.2.0
DERBY-2983The ResultSet returned by DatabaseMetaData.getFunctions() does not contain a required column named FUNCTION_TYPE.
DERBY-2963AccessControlException: Access denied java.net.SocketPermission <client ip> accept,resolve
DERBY-2959create table ... as select ... from systemtable with no data fails even when there is no character string type involved. This happens in a territory based database
DERBY-2939Log file is flushed every time a log buffer gets full
DERBY-2935DDMReader.readLengthAndCodePoint() decodes long integer incorrectly
DERBY-2932Connection.createClob() and Connection.createBlob() throw a procedure not found exception and do not switch to non-locator code upon soft upgrade
DERBY-2923Incorrect reading of nullable Blob/Clob columns during soft upgrade with client/server
DERBY-2910SimpleStringOperatorNode in it's bindExpression method generates a character string CAST if required but does not set the correct collation.
DERBY-2906derbynet.jar & derbytools.jar incorrectly have a META-INF/services/java.sql.Driver entry
DERBY-2902AS IDENTITY (START WITH -9223372036854775808) fails
DERBY-2896DatabaseMetaData.getTables() fails in TERRORITY_BASED collation database with SQLState 42818: Comparisions between CHAR and CHAR not allowed.
DERBY-2892Closing a resultset after retrieving a large > 32665 bytes value with Network Server does not release locks
DERBY-2891Clob.getCharacterStream(long,long) ignores position parameter for large (>32k) CLOBs
DERBY-2885Wrong connection object used in EmbedConnection.clearLOBMapping()
DERBY-2882Remove references to JDK 1.2 and 1.3 in the documentation
DERBY-2879CREATE TABLE AS <subquery> does not maintain the collation for character types.
DERBY-2874NetworkServer not accepting connections with default security manager on Ipv6 machines
DERBY-2868BUILDING.txt doesn't mention junit.jar in list of jars installed in tools/java
DERBY-2863Collation doc corrections/improvements
DERBY-2860Documentation problems for IMPORT/EXPORT LOBs feature
DERBY-2858Export exceptions swallow useful information
DERBY-2831NullPointerException creating a function in a schema that doesn't exist when the return type is a character type
DERBY-2815ij doesn't start with J2ME / JSR169 / weme6.1 because attempting to find java.sql.Driver if ij.protocol property is specified
DERBY-2809Expressions with a parameter can be assigned the incorrect type
DERBY-2803SSL certificate authentication succeeds unexpectedly
DERBY-2796Obscure error messages when using SSL in various combinations
DERBY-2777Parameters should take their collation from the context in which they are getting used rather than the current compilation schema
DERBY-2776Internally generated CAST nodes should not use the collation of the current compilation schema. Instead they should use collation of target type passed to it.
DERBY-2733ij rolls through NullPointerException (NPE) with J2ME/JSR169/WEME 6.1.
DERBY-2725If all the operands involved in collation operation have a collation derivation of NONE, then Derby should throw an exception for that as per the SQL standards.
DERBY-2689Deadlock with GenericPreparedStatement
DERBY-2683tools and utilities guide: ij.URLCheck's list of checked attributes is not correct
DERBY-2680Reference manual section "Setting attributes.." lacks upgrade=true attribute
DERBY-2619 A Derby source release must include the documentation source files
DERBY-2607DatabaseMetaData is not consistent about throwing SqlException when tablename=null
DERBY-2592Wrong description of IndexName field in public JavaDoc for LockTable
DERBY-2585Mistaken description in page of CLOB
DERBY-2564ContextService.notifyAllActiveThreads() and network server code need a privileged block around the calls to Thread.interrupt()
DERBY-2559recreating a datasource using javax.naming.Reference from a ClientDataSource40 fails
DERBY-2437SYSCS_EXPORT_TABLE can be used to overwrite derby files
DERBY-2352Assertion Failure with order by and group by expression
DERBY-2351ORDER BY with expression with distinct in the select list returns incorrect result
DERBY-2297In the Dev Guide example derby.database.readOnlyAccessUsers is incorrectly mentioned as derby.database.readAccessUsers
DERBY-2270'18 ' (18 followed by four blanks) needs three more blanks
DERBY-2142NullPointerException while using XAConnection/PooledConnection in a heavily contended multithreaded scenario
DERBY-2128The word 'class' appears twice for the message SIF01.V
DERBY-2073Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
DERBY-2016ArrayIndexOutOfBoundsException for COALESCE with aggregate functions
DERBY-1823Derby Developer's Guide - Issues w/ User authentication and authorization extended examples section/paragraph
DERBY-1790DatabaseMetaData.getTables() with passed in table type of SYNONYM returns system tables and not SYNONYMS
DERBY-1585derbylang/procedureInTrigger: not able to create trigger due to an open ResultSet
DERBY-1573Unsafe synchronization in NetworkServerControlImpl
DERBY-974ClientDriver can lose some connection properties
DERBY-857LDAP user authentication fails under a security manager

Issues

Compared with the previous release (10.3.2.1), Derby release 10.4.1.0 introduces the following new features and incompatibilities. These merit your special attention.


Note for DERBY-3301

Summary of Change

Queries with nested EXIST, ANY or IN clauses now return correct results.

Symptoms Seen by Applications Affected by Change

In the previous release, applications that executed SQL statements containing nested EXISTS, ANY or IN clauses could see fewer rows than those satisfying the query. In particular, rows that had the same value for one of the selected columns as another row might not have been returned.

Incompatibilities with Previous Release

None.

Rationale for Change

The previous behavior violated the ANSI SQL standard. The new behavior is correct.

Application Changes Required

Typically none, but applications must handle that the correct results are now returned.


Note for DERBY-3026

Summary of Change

The frameworks directory (and its contents) has been removed.

Symptoms Seen by Applications Affected by Change

Users and applications that rely on any of the scripts or HTML files in the frameworks directory (which has been deprecated since the 10.2.1.6 release) or any of its subdirectories will no longer be able to find those files in the same location.

Incompatibilities with Previous Release

Applications or commands referencing files in the frameworks directory will fail.

Rationale for Change

In the 10.2.1.6 release, new and improved scripts were added in a new bin directory, intended to replace the scripts in the frameworks directory. The new scripts follow Apache conventions, and all scripts are located in a single directory, making them easier to find. Removing the old and deprecated scripts and the frameworks directory itself will eliminate a potential source of confusion and annoyance among users.

The frameworks directory has been deprecated since the 10.2.1.6 release, and has not been maintained since then. The 10.2.1.6 release notes announced the deprecation of the scripts in the frameworks directory, and an additional file (frameworks.DEPRECATED.txt) was added in the top-level directory of the 10.3.1.4 release, with the purpose of alerting users about this change. A warning message was also added to the scripts in the frameworks directory at the same time.

Application Changes Required

All references to the frameworks directory or its contents must be updated. The scripts in the bin directory may be used instead of the old scripts.


Note for DERBY-3013

Summary of Change

The column default value can now also be specified as CURRENT_USER or SESSION_USER.

Symptoms Seen by Applications Affected by Change

None

Incompatibilities with Previous Release

None

Rationale for Change

Extend Derby's support for standard SQL constructions.

Application Changes Required

None.


Note for DERBY-2967

Summary of Change

Databases with territory based collation might see the behavior of LIKE predicate in WHERE clause has changed.

Symptoms Seen by Applications Affected by Change

In territory based databases, the LIKE predicate will now comply with SQL specification(Derby already follows SQL specification for UCS_BASIC databases). SQL specification for LIKE predicate requires that database should do one character comparison at a time. Prior to the fix that went in as part of DERBY-2967, Derby did string comparison rather than character comparison for LIKE. This causes a problem with territory based database because the collation element(s) for a string of characters may not be same as the collation element(s) for inidivdual characters that make up that string. In addition, the metacharacter _ in the pattern in a LIKE predicate will now consume exactly one character rather than working on number of collation elements. To show the impact on application, consider following eg. Say we are working with a territory where character 'z' has same collation elements as 'xy'. For such a territory consider following LIKE predicate 'zcb' LIKE 'xy_b' Prior to DERBY-2967, Derby will return TRUE because collation elements for 'z' matched 'xy' and 'c' was consumed by '_' and 'b' matched 'b'. But this does not follow SQL specification. SQL specification requires one character comparison at a time in LIKE. With the fix for DERBY-2967, the LIKE predicate in this example will return FALSE because the collation element(s) for 'z' does not match collation element(s) for 'x'. In addition, the metacharacter _ in pattern for LIKE will consume *one* character in the string value. So for an eg clause 'xycb' LIKE '_cb' will return FALSE because metacharacter _ will consume 'x' and since 'c' does not match 'y', Derby will return FALSE.

Rationale for Change

The previous behavior violated the SQL specification. The new behavior is correct.


Note for DERBY-2925

Summary of Change

Prevent export from overwriting existing files

Symptoms Seen by Applications Affected by Change

Due to security concerns, and to avoid accidental file damage, Export processing will not overwrite existing files. As a result, applications may observe some behavioral changes with respect to Export operations.

Incompatibilities with Previous Release

  1. SYSCS_UTIL.SYSCS_EXPORT_TABLE: Exports of all the data from a table to an existing file is no longer possible and the user/application must remove the existing file, otherwise ERROR XIE0S will be returned.
  2. SYSCS_UTIL.SYSCS_EXPORT_QUERY: Exports of all the data returned from the SELECT statement to an existing file is no longer possible and the user/application must remove the existing file, otherwise ERROR XIE0S will be returned.
  3. SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE: Export of the result of a SELECT statement to a main data output file, and the LOB data into a large object auxiliary file to an existing file is no longer possible and the user/application must remove the existing data file and/or large object auxiliary file, otherwise ERROR XIE0S will be returned if the data file exists or ERROR XIE0T will be returned if the large object auxiliary file exists.

Rationale for Change

Due to security concerns and to avoid accidental file damage, Export processing will no longer overwrite an existing file.

Application Changes Required

The application needs to specify a different filename which does not exists or delete the existing file before performing the Export operation.


Note for DERBY-2892

Summary of Change

DERBY-2892 fixes the bug: "Closing a resultset after retrieving a large > 32665 bytes value with Network Server does not release locks." The fix changes the JDBC retrieval of BLOB/CLOB columns release locks and the lob locator after calls to ResultSet.getString(), ResultSet.getCharacterStream(), ResultSet.getBytes() or ResultSet.getBinaryStream(). Previously locks would remain held after these calls. Note: Locks will still be held for calls to ResultSet.getClob() or ResultSet.getBlob(). The user must call free() or commit or rollback the transation to release these locks.

Symptoms Seen by Applications Affected by Change

Users will no longer see locks being held for calls to ResultSet.getString(), ResultSet.getCharacterStream(), ResultSet.getBytes() or ResultSet.getBinaryStream().

Incompatibilities with Previous Release

As part of the implementation of this change, BLOB/CLOB columns can be retrieved only once through JDBC, for each row. For example, if the user calls ResultSet.getString() on a BLOB or CLOB column, they cannot make a subsequent call to ResultSet.getCharacterStream() for the same column on the same row. An Exception will be thrown if this is attempted.

Rationale for Change

In order to ensure the proper locking behavior, the change to throw an exception when a BLOB/CLOB column is retrieved more than once for a row was required.

Application Changes Required

Users should retrieve the field only once and save the value for reuse, rather than making multiple calls to ResultSet.getXXXX methods.


Note for DERBY-2351

Summary of Change

An ORDER BY clause of a DISTINCT query which specifies to order by a column which was not in the DISTINCT list is now rejected, because the intent of the query is ambiguous. Previously, Derby instead produced non-distinct results. Also, an ORDER BY clause which specifies a table-name-qualified column alias is now rejected as invalid, where previously it was accepted.

Symptoms Seen by Applications Affected by Change

New rules for DISTINCT and ORDER BY

Applications which specify certain combinations of SELECT DISTINCT with ORDER BY will now receive an error message, whereas formerly such applications received non-distinct results.

As an example, take the following:

create table person (name varchar(10), age int);
insert into person values ('John', 10);
insert into person values ('John', 30);
insert into person values ('Mary', 20);

SELECT DISTINCT name FROM person ORDER BY age;

The query above is now rejected, with the error message:

ERROR 42879: The ORDER BY clause may not contain column 'AGE', since the query specifies DISTINCT and that column does not appear in the query result.

If the AGE column is included in the DISTINCT list in the above query, there is no ambiguity

New column alias rules

Applications which specify a column alias for a column in the SELECT statement, and which specify an ORDER BY clause which specifies that column alias qualified by the table name, will now receive an error indicating that the ORDER BY clause is invalid.

As an example, take the following:

create table t1 (i int, j int);
select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;

This query is now rejected, as there is no column named 'idcolumn1' in table 't1'. The error message is:

ERROR 42X04: Column 'T1.IDCOLUMN1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.IDCOLUMN1' is not a column in the target table.

Valid forms of the query above are:

select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by idcolumn1, idcolumn2;

or

select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.id, t1.id;

Rationale for Change

When the query ambiguously specifies both DISTINCT and ORDER BY, Derby was unsure whether to return the rows properly ordered, but non-distinct, or to return a distinct set of rows, but in an unknown order. Since no clear resolution of the ambiguity could be found, we chose instead to reject the query.

The rules for resolving column references in ORDER BY clauses have been enhanced to consider column aliases and column names more fully. Derby now uses different resolution rules depending on whether the ORDER BY column reference is table.column, or just column:

Application Changes Required

A query which specifies ordering by a non-distinct column should instead include the ORDER BY column in the DISTINCT list, to resolve the ambiguity about which values of that column should be used to distinctly identify the resulting rows.

A query which specifies table-name.alias-name should be rewritten to specify either simply alias-name, or table-name.column-name.


Note for DERBY-2065

Summary of Change

Error code changed for embedded connection when a connection with an open transaction is attempted closed.

Symptoms Seen by Applications Affected by Change

In the previous release, calling Connection.close() on a connection with an open transaction raised an error with error code 25001 with the client driver, whereas the embedded driver raised error code 25000. The embedded driver has now been changed to raise the same error code as the client driver, i.e. 25001, as specified by the SQL standard.

Incompatibilities with Previous Release

Embedded applications that are dependent on the error code ever being "25000" could start failing. Embedded applications that are dependent on the error code never being "25001" could start failing.

Rationale for Change

Harmonize error codes raised by the client and embedded drivers, thereby also making the embedded driver compatible with the SQL standard.

Application Changes Required

Applications that are dependent on the error code must be changed to expect the new code.

Build Environment

Derby release 10.4.1.0 was built using the following environment: