Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <27988449.1182034106400.JavaMail.jira@brutus>
Date: Sat, 16 Jun 2007 15:48:26 -0700 (PDT)
From: "Myrna van Lunteren (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Updated: (DERBY-1852) Wrong results: duplicate rows returned
 for nested UNIONs when they should be eliminated.
In-Reply-To: <6007236.1158261562269.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ https://issues.apache.org/jira/browse/DERBY-1852?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Myrna van Lunteren updated DERBY-1852:
--------------------------------------

    Attachment: releaseNote.html

scrubbed release note.

> Wrong results: duplicate rows returned for nested UNIONs when they should be eliminated.
> ----------------------------------------------------------------------------------------
>
>                 Key: DERBY-1852
>                 URL: https://issues.apache.org/jira/browse/DERBY-1852
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1
>            Reporter: A B
>            Assignee: A B
>             Fix For: 10.3.0.0
>
>         Attachments: d1852_engine_v1.patch, d1852_quick_doNOTcommit.diff, d1852_tests_v1.patch, d1852_v1.stat, releaseNote.html, releaseNote.html, releaseNote.html, releaseNote.html
>
>
> Regarding UNIONs, the Derby documentation says that if the "ALL" keyword is not specified, the default behavior is to remove duplicate rows from the result.   And for simple cases of UNIONs, that is indeed the case.  However, there are certain queries with nested UNIONs where Derby is supposed to remove duplicates from the result set, but does not.
> This is *NOT* a regression.  I have been able to reproduce the behavior in early versions of 10.1.2 and also back to 10.0.2.2, which is the oldest client I have.
> To show the problem:
> create table t1 (i int, j int);
> create table t2 (i int, j int);
> insert into t1 values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10);
> insert into t2 values (1, 2), (2, -4), (3, 6), (4, -8), (5, 10);
> insert into t2 values (3, 6), (4, 8), (3, -6), (4, -8);
> -- Following query correctly returns 13 rows; the first union
> -- does not specify "ALL" and should therefore remove
> -- duplicates.
> select * from t1 union select * from t2 union all select * from t1;
> -- But if we do a join with the same query, we end up with
> -- 19 rows instead of 13.  This is because the duplicate entries
> -- are not correctly removed.
> select * from t1 left outer join
>   (select * from t1 union select * from t2 union all select * from t1) x2
> on t1.i = x2.i;
> Just for sanity, I also ran these statements against DB2 and the result was of the last query was indeed 13 rows, not 19.  So I think this is a case where Derby is returning incorrect results.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.