From derby-dev-return-8084-apmail-db-derby-dev-archive=db.apache.org@db.apache.org Fri Sep 02 05:34:33 2005 Return-Path: Delivered-To: apmail-db-derby-dev-archive@www.apache.org Received: (qmail 32079 invoked from network); 2 Sep 2005 05:34:33 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 2 Sep 2005 05:34:33 -0000 Received: (qmail 89361 invoked by uid 500); 2 Sep 2005 05:34:32 -0000 Delivered-To: apmail-db-derby-dev-archive@db.apache.org Received: (qmail 89331 invoked by uid 500); 2 Sep 2005 05:34:31 -0000 Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Development" Delivered-To: mailing list derby-dev@db.apache.org Received: (qmail 89318 invoked by uid 99); 2 Sep 2005 05:34:31 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 01 Sep 2005 22:34:31 -0700 X-ASF-Spam-Status: No, hits=1.7 required=10.0 tests=HTML_30_40,HTML_MESSAGE,HTML_TITLE_EMPTY,MIME_HTML_ONLY,SPF_HELO_FAIL X-Spam-Check-By: apache.org Received-SPF: neutral (asf.osuosl.org: local policy) Received: from [32.97.110.130] (HELO e32.co.us.ibm.com) (32.97.110.130) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 01 Sep 2005 22:34:46 -0700 Received: from westrelay02.boulder.ibm.com (westrelay02.boulder.ibm.com [9.17.195.11]) by e32.co.us.ibm.com (8.12.10/8.12.9) with ESMTP id j825YTig345778 for ; Fri, 2 Sep 2005 01:34:29 -0400 Received: from d03av04.boulder.ibm.com (d03av04.boulder.ibm.com [9.17.195.170]) by westrelay02.boulder.ibm.com (8.12.10/NCO/VERS6.7) with ESMTP id j825XDvp175390 for ; Thu, 1 Sep 2005 23:33:13 -0600 Received: from d03av04.boulder.ibm.com (loopback [127.0.0.1]) by d03av04.boulder.ibm.com (8.12.11/8.13.3) with ESMTP id j825XC1d018607 for ; Thu, 1 Sep 2005 23:33:12 -0600 Received: from [127.0.0.1] (sig-9-48-111-150.mts.ibm.com [9.48.111.150]) by d03av04.boulder.ibm.com (8.12.11/8.12.11) with ESMTP id j825WiVq018149 for ; Thu, 1 Sep 2005 23:33:12 -0600 Message-ID: <4317E382.2050001@Sourcery.Org> Date: Thu, 01 Sep 2005 22:30:42 -0700 From: Satheesh Bandaram User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Derby Development Subject: Re: [jira] Commented: (DERBY-504) SELECT DISTINCT returns duplicates when selecting from subselects References: <1525402018.1125625512723.JavaMail.jira@ajax.apache.org> In-Reply-To: <1525402018.1125625512723.JavaMail.jira@ajax.apache.org> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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