[DERBY-504] SELECT DISTINCT returns duplicates when selecting from subselects

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 10.1.2.1
• Fix Version/s: 10.1.2.1, 10.2.1.6
• Component/s: SQL
• Environment: Latest development sources (SVN revision 232227), Sun JDK 1.5, Solaris/x86
• Assignee: Knut Anders Hatlen
• Reporter: Knut Anders Hatlen
• Created: 12/Aug/05 18:07
• Updated: 02/May/13 02:28
• Resolved: 11/Jan/07 09:49

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

Issue Links

• is depended upon by: DERBY-519 GROUP BY test depends on incorrect behaviour