db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-504) SELECT DISTINCT returns duplicates when selecting from subselects
Date Thu, 08 Sep 2005 07:01:30 GMT
    [ http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12322909 ] 

Knut Anders Hatlen commented on DERBY-504:

About the 10.1 patch:

1) Yes, it includes the cleanup patch.
2) No, it is not generated from an svn merge command, but it could be done. I incorrectly
assumed that it wouldn't work since applying the trunk patch directly failed. Seems like subversion
is smarter than me... ;)

Applying this command will merge the committed changes into 10.1:

     svn merge -r 267238:267239 mytrunkdir my10.1dir

This doesn't include the cleanup patch, but it will fix the bug. The cleanup patch could be
applied directly, but it is not important in the 10.1 backport.

There is one difference between my 10.1 patch and the svn merge. One of the j9_22 canons (java/testing/org/apache/derbyTesting/functionTests/master/j9_22/distinct.out)
is different. Since I haven't tested the j9_{13,22} files, that canon must be checked by someone
else anyway.

> 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:
>  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-10.1-unix.diff, DERBY-504-10.1-windows.diff, DERBY-504-10.1.stat,
DERBY-504-cleanup.diff, DERBY-504-cleanup.stat, 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:
For more information on JIRA, see:

View raw message