Summary of Change

Queries which use the LEFT OUTER JOIN ... ON syntax for joining multiple tables may have previously returned incorrect results.

Symptoms Seen by Applications Affected by Change

Applications which use the explicit LEFT OUTER JOIN ... ON syntax to perform joins between three or more FROM tables may have seen incorrect results prior to this release. In particular, if a query a) has several JOIN ... ON clauses, one or more of which is specified as a LEFT OUTER JOIN, and b) has a WHERE predicate which references one or more of the left-outer-joined columns, then the query may have returned incorrect results. More specifically, the query result may have been missing rows.

As an example, take the following query:

select distinct * from
  t1 left outer join t2 on t1.a = t2.d
  inner join t3 on t1.a= t3.j
  inner join t4 on t2.c= t4.x
where t1.a =-2;

In Derby 10.3.2.1 and earlier a bug caused the compiler to perform incorrect transitive closure on the predicates, which led to the addition of a new predicate, T4.X = -2, to the WHERE list. As a result of the extra (invalid) predicate, the query result could end up missing rows.

This problem has been fixed in Derby 10.4.

Incompatibilities with Previous Release

None.

Rationale for Change

Due to an error in column reference mappings, it was possible for earlier versions of Derby to confuse ON predicate column references with each other, thereby leading to incorrect computation of transitive closure. This in turn could lead to the addition of invalid predicates to the query, which caused wrong results in certain cases. By fixing this bug we ensure that the affected queries will now return correct results.

Application Changes Required

None.