DISTINCT Elimination Based on a Uniqueness Condition

A DISTINCT (and the corresponding sort) can be eliminated from a query if a uniqueness condition exists that ensures that no duplicate values will be returned. If no duplicate values are returned, the DISTINCT node is superfluous, and Derby transforms the statement internally into one without the DISTINCT keyword.

The requirements are:

• No GROUP BY list.
• SELECT list contains at least one simple column reference.
• Every simple column reference is from the same table.
• Every table in the FROM list is a base table.
• Primary table

  There is at least one unique index on one table in the FROM list for which all the columns appear in one of the following:

  • equality predicates with expressions that do not include any column references
  • simple column references in the SELECT list
• Secondary table

  All the other tables in the FROM list also have at least one unique index for which all the columns appear in one of the following:

  • equality predicates with expressions that do not include columns from the same table
  • simple column references in the SELECT list

For example:

CREATE TABLE tab1 (c1 INT NOT NULL, 
    c2 INT NOT NULL, 
    c3 INT NOT NULL, 
    c4 CHAR(2), 
    PRIMARY KEY (c1, c2, c3))
CREATE TABLE tab2 (c1 INT NOT NULL, 
    c2 INT NOT NULL, 
    PRIMARY KEY (c1, c2))
INSERT INTO tab1 VALUES (1, 2, 3, 'WA'), 
    (1, 2, 5, 'WA'), 
    (1, 2, 4, 'CA'), 
    (1, 3, 5, 'CA'), 
    (2, 3, 1, 'CA')
INSERT INTO tab2 VALUES (1, 2), 
    (1, 3), 
    (2, 2), 
    (2, 3)
-- all the columns in the index on the only table appear
-- in the way required for the Primary table
SELECT DISTINCT c1, c2, c3, c4
FROM tab1
-- all the columns in the index on the only table appear
-- in the way required for the Primary table
SELECT DISTINCT c3, c4
FROM tab1
WHERE c1 = 1
AND c2 = 2
AND c4 = 'WA'
-- all the columns in the index on tab1 appear
-- in the way required for the Primary table,
-- and all the columns in the
-- other tables appear in the way required
-- for a Secondary table
SELECT DISTINCT tab1.c1, tab1.c3, tab1.c4
FROM tab1, tab2
WHERE tab1.c2 = 2
AND tab2.c2 = tab1.c2
AND tab2.c1 = tab1.c1