Transitive Closure on Search Clauses

Derby applies transitive closure on search clauses after transitive closure on join clauses. For each sargable predicate where a simple column reference is compared with a constant (or the IS NULL and IS NOT NULL operators), Derby looks for an equijoin predicate between the simple column reference and a simple column reference from another table in the same query block. For each such equijoin predicate, Derby then searches for a similar comparison (the same operator) between the column from the other table and the same constant. Derby adds a new predicate if no such predicate is found.

Derby performs all other possible transformations on the predicates (described in Predicate transformations) before applying transitive closure on search clauses.

For example, given the following statement:

SELECT * FROM Flights, FlightAvailability
WHERE Flights.flight_id = FlightAvailability.flight_id
AND Flights.flight_id between 'AA1100' and 'AA1250'
AND Flights.flight_id <> 'AA1219'
AND FlightAvailability.flight_id <> 'AA1271'

Derby first performs any other transformations:

the BETWEEN transformation on the second predicate:

AND Flights.flight_id >= 'AA1100' 
AND Flights.flight_id <=  'AA1250'

Derby then performs the transitive closure:

SELECT * FROM Flights, FlightAvailability
WHERE Flights.flight_id = FlightAvailability.flight_id
AND Flights.flight_id >= 'AA1100' 
AND Flights.flight_id <=  'AA1250'
AND Flights.flight_id <> 'AA1219'
AND Flights.flight_id <> 'AA1271'
AND FlightAvailability.flight_id >= 'AA1100' 
AND FlightAvailability.flight_id <=  'AA1250'
AND FlightAvailability.flight_id <> 'AA1271'
AND FlightAvailability.flight_id <> 'AA1219'

When a sargable predicate uses the = operator, Derby can remove all equijoin predicates comparing that column reference to another simple column reference from the same query block as part of applying transitive closure, because the equijoin predicate is now redundant, whether or not a new predicate was added. For example:

SELECT * FROM Flights, Flightavailability
WHERE Flights.flight_id = Flightavailability.flight_id
AND Flightavailability.flight_id = 'AA1122'

becomes (and is equivalent to)

SELECT * FROM Flights, Flightavailability
WHERE Flights.flight_id = 'AA1122'
AND Flightavailability.flight_id = 'AA1122'

The elimination of redundant predicates gives the optimizer more accurate selectivity information and improves performance at execution time.