db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Commented: (DERBY-504) SELECT DISTINCT returns duplicates when selecting from subselects
Date Fri, 02 Sep 2005 05:30:42 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I will commit this change tomorrow. Many thanks for reviewing it and
running Derby all.<br>
<br>
Satheesh<br>
<br>
Rick Hillegas (JIRA) wrote:<br>
<blockquote
 cite="mid1525402018.1125625512723.JavaMail.jira@ajax.apache.org"
 type="cite">
  <pre wrap="">    [ <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12320834">http://issues.apache.org/jira/browse/DERBY-504?page=comments#action_12320834</a>
] 

Rick Hillegas commented on DERBY-504:
-------------------------------------

Looks great. Derball passes. Ready for a committer to check in.

  </pre>
  <blockquote type="cite">
    <pre wrap="">SELECT DISTINCT returns duplicates when selecting from subselects
-----------------------------------------------------------------

         Key: DERBY-504
         URL: <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-504">http://issues.apache.org/jira/browse/DERBY-504</a>
     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&gt; CREATE TABLE names (id INT PRIMARY KEY, name VARCHAR(10));
0 rows inserted/updated/deleted
ij&gt; INSERT INTO names (id, name) VALUES
       (1, 'Anna'), (2, 'Ben'), (3, 'Carl'),
       (4, 'Carl'), (5, 'Ben'), (6, 'Anna');
6 rows inserted/updated/deleted
ij&gt; 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&gt; SELECT DISTINCT(name) FROM (SELECT name, id FROM names) AS n ORDER BY name;
NAME      
----------
Anna      
Ben       
Carl      
3 rows selected
ij&gt; SELECT DISTINCT(name) FROM (SELECT name FROM names) AS n;
NAME      
----------
Anna      
Ben       
Carl      
3 rows selected
    </pre>
  </blockquote>
  <pre wrap=""><!---->
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message