Summary of Change

Queries with an IN predicate and an ORDER BY predicate on the same column may have previously returned rows in the wrong order.

Symptoms Seen by Applications Affected by Change

Applications having queries which include an IN predicate and an ORDER BY clause may have seen results that were not correctly ordered in the Derby 10.3.1 and Derby 10.3.2 releases. In particular, if a query has a) an IN predicate for some column C, where the IN list contains only literals and/or parameter markers, and b) an ORDER BY clause that specifies a DESCENDING ordering on column C, then Derby may have ignored the ORDER BY C DESC clause, causing the query results to be returned in an incorrect order.

As an example, take the following query:

SELECT * FROM CHEESE
WHERE CHEESE_CODE IN ('00000', '54321') AND CHEESE_NAME='EDAM'
ORDER BY CHEESE_CODE DESC, CHEESE_NAME DESC, CHEESE_COST DESC;

The results for such a query are incorrectly returned in ascending order by CHEESE_CODE in the Derby 10.3.1 and 10.3.2 releases.

This problem has been fixed in Derby 10.4.

Incompatibilities with Previous Release

None.

Rationale for Change

Due to an error in the execution logic for IN lists it was possible for Derby to return query results in an incorrect order. By fixing this bug we ensure that the affected queries will now return results in the correct order.

Application Changes Required

None.