[ http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12320737 ]
Rick Hillegas commented on DERBY-504: ------------------------------------- This is a clean, compact bug fix. It could be made a little more compact: SelectNode and ProjectRestrictNode have almost identical while loops for extracting the base column reference. Please don't miss the opportunity to abstract out a common method here. Once this change is made, I will apply the patch on my machine and run derbyall against jdk1.4. > 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 > > 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
