Combining ORDER BY and UNION

Without a transformation, a statement that contains both ORDER BY and UNION would require two separate sorting steps--one to satisfy ORDER BY and one to satisfy UNION. (Currently Derby uses sorting to eliminate duplicates from a UNION.)

In some situations, Derby can transform the statement internally into one that contains only one of these keywords (the ORDER BY is thrown out). The requirements are:

• The columns in the ORDER BY list must be a subset of the columns in the select list of the left side of the union.
• All the columns in the ORDER BY list must be sorted in ascending order and they must be an in-order prefix of the columns in the target list of the left side of the UNION.

Derby will be able to transform the following statements:

SELECT miles, meal
FROM Flights
UNION VALUES (1000, 'D')
ORDER BY 1

Derby cannot avoid two sorting nodes in the following statement, because of the order of the columns in the ORDER BY clause:

SELECT flight_id, segment_number FROM Flights
UNION
SELECT flight_id, segment_number FROM FlightAvailability
ORDER BY segment_number , flight_id