Release Notes for Apache Derby 10.11.0.0

These notes describe the difference between Apache Derby release 10.11.0.0 and the preceding release 10.10.2.0.

Overview

The most up to date information about Derby releases can be found on the Derby download page.

Apache Derby is a pure Java relational database engine using standard SQL and JDBC as its APIs. More information about Derby can be found on the Apache web site. Derby functionality includes:

Java and JDBC versions supported:

New Features

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

Bug Fixes

The following issues are addressed by Derby release 10.11.0.0. These issues are not addressed in the preceding 10.10.2.0 release.

Issue Id
Description
DERBY-6644Support standard syntax for altering column nullability
DERBY-6643ALTER TABLE columnAlteration syntax needs fixing
DERBY-6638Remove unnecessary use of reflection in SignatureChecker
DERBY-6633Remove DOM level 3 XPath requirement from description of XML operators
DERBY-6629Restrict privileged operation in CreateXMLFile
DERBY-6624Use javax.xml.xpath interfaces for XPath support
DERBY-6615Remove unused newInstance() method in BaseMonitor
DERBY-6611Broken link in API docs to derby.drda.keepAlive documentation
DERBY-6609Documentation for SQL features should reflect current standard
DERBY-6605"Derby support for SQL-92 features" topic in Reference Manual needs updating
DERBY-6602LuceneQueryVTI handles NULL key values inconsistently
DERBY-6601Clean up Java EE compliance section of Reference Manual
DERBY-6599Incorrect quoting of 42ZB4 message
DERBY-6598Document permissions recommendations for JAR procedures
DERBY-6597LUCENESUPPORT.LISTINDEXES() fails with FileNotFoundException
DERBY-6596LUCENESUPPORT routines should check for NULL arguments
DERBY-6595CheckToursDBTest failed while updating sequence value on disk
DERBY-6594Typos in "Listing indexes" topic of the tools guide
DERBY-6591Minor tweaks needed on new ij commands
DERBY-6587Foreign Key constraint not matched when using UUID in a composite foreign key when using SYSCS_UTIL.SYSCS_IMPORT_TABLE
DERBY-6585add HoldForConnection ij command to match NoHoldForConnection
DERBY-6581Document simple case syntax
DERBY-6580Document the new SYSCS_UTIL.SYSCS_PEEK_AT_IDENTITY function
DERBY-6577Quantified comparison returns wrong result in CASE, COALESCE, IN and BETWEEN
DERBY-6576A immediate Fk constraint blows up iff its referenced PK is deferred and we modify a duplicate key column
DERBY-6571Document deferrable constraints
DERBY-6567Incorrect nullability for CASE expression with parameter
DERBY-6566Simplify handling of untyped nulls in CASE and NULLIF expressions
DERBY-6564Document the experimental, optional LuceneSupport tool.
DERBY-6563NOT elimination for CASE expressions is broken
DERBY-6561Organization topics of some manuals need updating
DERBY-6560Reference manual says ELSE clause is required in CASE expressions
DERBY-6559A immediate Fk constraint blows up iff its referenced PK is deferred and we delete a duplicate
DERBY-6554Too much contention followed by assert failure when accessing sequence in transaction that created it
DERBY-6553Sequence generator makes CREATE TRIGGER fail with internal error
DERBY-6552The public api includes methods inherited from superclasses which aren't in the public api and so have no javadoc comments
DERBY-6545Should not be able to add a default to an identity column
DERBY-6543Syntax error when reference to transition variable has whitespace around it
DERBY-6542Improve the concurrency of identity columns by using SYS.SYSSEQUENCES
DERBY-6540Schema-qualified table names could be mistaken for transition tables
DERBY-6537StringUtil.fromHexString is used to convert encryptionKey to byte[]
DERBY-6535Remove storageFactory field from subclasses of InputStreamFile
DERBY-6534Remove StorageFile.getURL() and its implementations
DERBY-6527Fix errors in foreign keys documentation
DERBY-6526Document the MERGE statement
DERBY-6521Improve error handling when restricting file permissions
DERBY-6518JVMInfo should not use parseFloat() to parse java.specification.version
DERBY-6515ij doesn't list its command NoHoldForConnection in the help menu
DERBY-6511java.lang.NoSuchMethodError chaining a function and procedure
DERBY-6505Clean up dead code in FileUtil
DERBY-6504change AllocPage.ReadContainerInfo to catch ArrayIndexOutOfBoundsException and turn it into Derby error.
DERBY-6503Starting network server on a network drive fails with JDK 7 on Windows
DERBY-6496Optional tool registration may fail because the CompilerContext is not always available at execution time.
DERBY-6493Improve reporting of exceptions wrapped in InvocationTargetException
DERBY-6488Get rid of the EmbedSQLException class
DERBY-6484Include SQLState in client exception messages
DERBY-6480Oracle Java documentation URLs need updating
DERBY-6478Fix language about supported DataSources for Compact Profiles
DERBY-6469Change the documentation to reflect new RDBNAM limit of 1024 bytes
DERBY-6467Document context-aware table functions.
DERBY-6464Improve the encapsulation of various compiler classes
DERBY-6462Provide more information about database name and path syntax
DERBY-6459Remove Class.forName calls that load JDBC driver from Derby samples/demos
DERBY-6458The Reference Manual should state that the year, month, and day components of a timestamp must be positive integers.
DERBY-6454DROP TABLE documentation could clarify how triggers are handled
DERBY-6453Remove dead code in InsertResultSet and flag skipCheckConstraints
DERBY-6447Use StrictMath for more functions in SYSFUN
DERBY-6440Connections opened by ForeignTableVTI never get released
DERBY-6434Incorrect privileges may be required for INSERT and DELETE statements.
DERBY-6432INSERT/UPDATE incorrectly require user to have privilege to execute CHECK constraints on the target table.
DERBY-6431Update Developer's Guide topic to include generated columns
DERBY-6429Privilege checks for UPDATE statements are wrong.
DERBY-6424Document thenExpression
DERBY-6423The expression syntax in CASE's THEN clause doesn't accept boolean value expression
DERBY-6421Cast to UDT in CHECK constraint causes NPE or assert failure
DERBY-6420Clarify how DROP statements work on trigger dependencies
DERBY-6419Make BTree scan honor OPENMODE_LOCK_NOWAIT for row locks.
DERBY-6410ClassCastException when launching derby from windows subst drive
DERBY-6390Document the WHEN clause in the CREATE TRIGGER statement
DERBY-6386Errors in jdbc4.LobStreamTest if derbyclient.jar is first in the classpath
DERBY-6378OFFSET/FETCH NEXT ignored when query is enclosed in parentheses
DERBY-6370dblook doesn't schema-qualify identifiers in trigger actions
DERBY-6362CHECK constraint uses wrong schema for unqualified routine invocations
DERBY-6359Document rolling derby.log file feature
DERBY-6350Provide a rolling file implementation of derby.log
DERBY-6330Simplify StringBuffer use, as they are mutable
DERBY-6322Remove erreoneous warning in NetBeans: superfluous use of super to access inherited member variable
DERBY-6321NetBeans project file: add XML api to source classpath
DERBY-6318Simplify setting of possibly null parameters in XPLAIN descriptors
DERBY-6315Improve test coverage of org.apache.derby.impl.io.InputStreamFile
DERBY-6304Remove unused methods in Predicate
DERBY-6296Simplify PropertyUtil using Properties.stringPropertyNames()
DERBY-6292Use Arrays.copyOf() in FormatableArrayHolder.getArray()
DERBY-6291Improve code coverage of org.apache.derby.iapi.jdbc.BrokeredCallableStatement
DERBY-6287Don't use reflection to call Java 6 methods in FileUtil
DERBY-6285Use factory method to create thread pool for timed login
DERBY-6284Improve test coverage of org.apache.derby.iapi.db.ConnectionInfo
DERBY-6276Convert lang/DB2IsolationLevels.sql to JUnit
DERBY-6266Add ability to print a Derby execution ResultSet as xml.
DERBY-6262Simplify message-generating methods using varargs
DERBY-6259Collapse the level 2 optimizer into its parent module.
DERBY-6254Reduce number of factory methods in StandardException
DERBY-6253Collapse SQLException factories
DERBY-6248nightly regression test failure: testDerby966(org.apache.derbyTesting.functionTests.tests.jdbcapi.XATest)java.sql.SQLFeatureNotSupportedException: The DDM object 0x2408 is not supported. The connection has been terminated.
DERBY-6243Fold Java5ClassFactory into ReflectClassesJava2
DERBY-6242Merge ConcurrentXactFactory into XactFactory
DERBY-6241Remove SinglePool from trunk
DERBY-6240Remove Clock cache manager from trunk
DERBY-6236Remove references to old JVMs (pre-Java 6) from the user guides
DERBY-6234Remove references to BUILTIN authentication from the user guides
DERBY-6231Remove unnecessary checks for UnsupportedEncodingException in the client
DERBY-6230Use the JVM's cache of Number instances in ReuseFactory
DERBY-6227Distinct aggregates don't work well with territory-based collation
DERBY-6217Put all of the security documentation in a single, separate user guide
DERBY-6213Deprecate support for Java 5 and CDC
DERBY-6207Update policy files in java/drda/org/apache/derby/drda
DERBY-6206Cleanup suspect coding practices in misc Derby packages
DERBY-6202Cleanup suspect coding practices in the org.apache.derby.iapi.sql.dictionary package
DERBY-6201Cleanup suspect coding practices in the org.apache.derby.impl.sql.execute.rts package
DERBY-6200Cleanup suspect coding practices in the org.apache.derby.iapi.types package
DERBY-6199Cleanup suspect coding practices in the org.apache.derby.vti package
DERBY-6198Cleanup suspect coding practices in the org.apache.derby.tools package
DERBY-6197Cleanup suspect coding practices in the org.apache.derby.impl.tools.planexporter package
DERBY-6195Cleanup suspect coding practices in the org.apache.derby.impl.tools.ij package.
DERBY-6192Cleanup suspect coding practices in org.apache.derby.iapi.services.property package
DERBY-6188Cleanup suspect coding practices in org.apache.derby.iapi.services.io package
DERBY-6186SYSTRIGGERSRowFactory should use DataDescriptorGenerator to build descriptor
DERBY-6184Clean up warnings in XA transaction id classes
DERBY-6182Cleanup suspect coding practices in org.apache.derby.iapi.error package
DERBY-6177Cleanup suspect coding practices in org.apache.derby.catalog.types
DERBY-6169Reduce visibility of classes and methods under impl/sql
DERBY-6168Clean up registered format ids
DERBY-6163Reduce visibility of methods in subclasses of PageBasicOperation
DERBY-6161Simplify code that handles LOB files
DERBY-6138org.apache.derbyTesting.functionTests.tests.store.ClassLoaderBootTest fails with sealing violation: package org.apache.derby.iapi.services.sanity is sealed depending on classpath order
DERBY-6133simple array index typo
DERBY-6128Examine Derby classes to determine if we need to add serialVersionUID to any of them
DERBY-6125Code clean up in client driver.
DERBY-6096OutOfMemoryError with Clob or Blob hash join: DataTypeDescriptor.estimatedMemoryUsage() has no case for BLOB or CLOB so would underestimate memory usage for those types at zero
DERBY-6075Use modern collections in impl/sql/compile
DERBY-5840Clean up compiler warnings introduced by using Java 5 language features
DERBY-5615NPE in Store when running SELECT in a read-only database accessed via the classpath subprotocol when authentication, authorization, and Java security are turned on
DERBY-5317NullPointerException in org.apache.derby.client.net.Request.sendBytes() with client
DERBY-5313Assert failure with CASE expression in GROUP BY clause
DERBY-5196Correct the layout of log.ctrl as described on the Derby web site
DERBY-5111NullPointerException on unique constraint violation with unique index
DERBY-4805Increase the length of the RDBNAM field in the DRDA implementation
DERBY-4750add documentation to declare global temporary tables to explain expected behavior when used with XA transactions.
DERBY-4478Use AtomicLong for XactFactory.tranId
DERBY-4403Assert failure (sane) or NullPointerException (insane) when attempting to GROUP BY expression containing scalar subquery
DERBY-4381Connection to Derby database using jar subprotocol doesn't work if the path has round bracket in it
DERBY-3573Argument checking for ResultSet.setFetchSize(int) is incorrect
DERBY-3155Support for SQL:2003 MERGE statement
DERBY-2438Remove JDBC20Translation and JDBC30Translation classes
DERBY-2423Embedded and client differ on ResultSetMetaData.isCurrency() value for DECIMAL and NUMERIC columns
DERBY-2041Trigger should register a dependency on tables and columns used in its body
DERBY-2002Case expression allows NULL in all parts of <result>
DERBY-1997Misleading text in WwdEmbedded demo source file for Working With Derby
DERBY-1984Re-factor JDBC classes to remove support for JDBC 2
DERBY-1576Extend the CASE expression syntax for "simple case"
DERBY-1028Change constructors in NetConnection classes to use LogWriter instead of NetLogWriter
DERBY-673Get rid of the NodeFactory
DERBY-534Support use of the WHEN clause in CREATE TRIGGER statements
DERBY-532Support deferrable constraints

Issues

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


Note for DERBY-6566

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

Note for DERBY-6545

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;


Note for DERBY-6542

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' );


Note for DERBY-6447

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

Note for DERBY-6434

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.


Note for DERBY-6429

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...

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.


Note for DERBY-6213

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:

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

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:

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.


Note for DERBY-6128

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.

Note for DERBY-6096

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.


Note for DERBY-2041

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.


Note for DERBY-2002

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).

Build Environment

Derby release 10.11.0.0 was built using the following environment:

Verifying Releases

It is essential that you verify the integrity of the downloaded files using the PGP and MD5 signatures. MD5 verification ensures the file was not corrupted during the download process. PGP verification ensures that the file came from a certain person.

The PGP signatures can be verified using PGP or GPG. First download the Apache Derby KEYS as well as the asc signature file for the particular distribution. It is important that you get these files from the ultimate trusted source - the main ASF distribution site, rather than from a mirror. Then verify the signatures using ...

% pgpk -a KEYS
% pgpv db-derby-X.Y.tar.gz.asc

or

% pgp -ka KEYS
% pgp db-derby-X.Y.tar.gz.asc

or

% gpg --import KEYS
% gpg --verify db-derby-X.Y.tar.gz.asc

To verify the MD5 signature on the files, you need to use a program called md5 or md5sum, which is included in many unix distributions. It is also available as part of GNU Textutils. Windows users can get binary md5 programs from here, here, or here.

We strongly recommend that you verify your downloads with both PGP and MD5.