[ http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12320917 ]
Satheesh Bandaram commented on DERBY-504: ----------------------------------------- I have submitted this patch to trunk. I wonder if the change in SelectNode.java may be doing redundant work by getting BaseColumnColumnNode and checking for simpleColumns. Since we have already matched (resultColumns.countNumberOfSimpleColumnReferences() == resultColumns.size()), doesn't that guarantee resultColumns to be only simpleColumns? Sending java\engine\org\apache\derby\impl\sql\compile\FromBaseTable.java Sending java\engine\org\apache\derby\impl\sql\compile\ProjectRestrictNode.java Sending java\engine\org\apache\derby\impl\sql\compile\ResultColumn.java Sending java\engine\org\apache\derby\impl\sql\compile\ResultSetNode.java Sending java\engine\org\apache\derby\impl\sql\compile\SelectNode.java Sending java\testing\org\apache\derbyTesting\functionTests\master\distinct.out Sending java\testing\org\apache\derbyTesting\functionTests\master\groupBy.out Sending java\testing\org\apache\derbyTesting\functionTests\master\j9_13\distinct.out Sending java\testing\org\apache\derbyTesting\functionTests\master\j9_13\groupBy.out Sending java\testing\org\apache\derbyTesting\functionTests\master\j9_22\distinct.out Sending java\testing\org\apache\derbyTesting\functionTests\master\j9_22\groupBy.out Sending java\testing\org\apache\derbyTesting\functionTests\tests\lang\distinct.sql Transmitting file data ............ Committed revision 267239. > 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 -- 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
