SELECT DISTINCT returns duplicates when selecting from subselects

• Key: DERBY-504
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Component/s: SQL
• Affects Version/s: 10.1.2.1
• Fix Version/s: 10.1.2.1, 10.2.1.6

Description

When one performs a select distinct on a table generated by a subselect, there sometimes are duplicates in the result. The following example shows the problem:

ij> CREATE TABLE names (id INT PRIMARY KEY, name VARCHAR(10));
0 rows inserted/updated/deleted
ij> INSERT INTO names (id, name) VALUES
       (1, 'Anna'), (2, 'Ben'), (3, 'Carl'),
       (4, 'Carl'), (5, 'Ben'), (6, 'Anna');
6 rows inserted/updated/deleted
ij> SELECT DISTINCT(name) FROM (SELECT name, id FROM names) AS n;
NAME
----------
Anna
Ben
Carl
Carl
Ben
Anna

Six names are returned, although only three names should have been returned.

When the result is explicitly sorted (using ORDER BY) or the id column is removed from the subselect, the query returns three names as expected:

ij> SELECT DISTINCT(name) FROM (SELECT name, id FROM names) AS n ORDER BY name;
NAME
----------
Anna
Ben
Carl

3 rows selected
ij> SELECT DISTINCT(name) FROM (SELECT name FROM names) AS n;
NAME
----------
Anna
Ben
Carl

3 rows selected