Re: [jira] Commented: (DERBY-504) SELECT DISTINCT returns duplicates when selecting from subselects

[Satheesh Bandaram]

I will commit this change tomorrow. Many thanks for reviewing it and running Derby all. Satheesh Rick Hillegas (JIRA) wrote: [ http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12320834 ] Rick Hillegas commented on DERBY-504: ------------------------------------- Looks great. Derball passes. Ready for a committer to check in. SELECT DISTINCT returns duplicates when selecting from subselects ----------------------------------------------------------------- Key: DERBY-504 URL: http://issues.apache.org/jira/browse/DERBY-504 Project: Derby Type: Bug Components: SQL Versions: 10.2.0.0 Environment: Latest development sources (SVN revision 232227), Sun JDK 1.5, Solaris/x86 Reporter: Knut Anders Hatlen Assignee: Knut Anders Hatlen Priority: Minor Attachments: DERBY-504.diff, DERBY-504.stat, DERBY-504_b.diff, DERBY-504_b.stat, DERBY-504_c-CRLF.diff, DERBY-504_c-CRLF.diff, DERBY-504_c.diff, DERBY-504_c.stat 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