Return-Path: 
 <derby-dev-return-30410-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 64275 invoked from network); 21 Sep 2006 07:47:08 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 21 Sep 2006 07:47:08 -0000
Received: (qmail 90962 invoked by uid 500); 21 Sep 2006 07:47:07 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 90925 invoked by uid 500); 21 Sep 2006 07:47:07 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 90916 invoked by uid 99); 21 Sep 2006 07:47:07 -0000
Received: from idunn.apache.osuosl.org (HELO idunn.apache.osuosl.org)
 (140.211.166.84)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 21 Sep 2006 00:47:07 -0700
Authentication-Results: idunn.apache.osuosl.org smtp.mail=yipng168@gmail.com;
 spf=pass
Authentication-Results: idunn.apache.osuosl.org
 header.from=yipng168@gmail.com; domainkeys=good
X-ASF-Spam-Status: No,
 hits=2.8 required=5.0 tests=DNS_FROM_RFC_ABUSE,HTML_MESSAGE,MAILTO_TO_SPAM_ADDR
Received-SPF: pass (idunn.apache.osuosl.org: domain gmail.com designates
 64.233.182.188 as permitted sender)
DomainKey-Status: good
X-DomainKeys: Ecelerity dk_validate implementing
 draft-delany-domainkeys-base-01
Received: from [64.233.182.188] ([64.233.182.188:52775]
 helo=nf-out-0910.google.com)
	by idunn.apache.osuosl.org (ecelerity 2.1.1.8 r(12930)) with ESMTP
	id 82/52-04092-77342154 for <derby-dev@db.apache.org>;
 Thu, 21 Sep 2006 00:47:04 -0700
Received: by nf-out-0910.google.com with SMTP id n29so892120nfc
        for <derby-dev@db.apache.org>; Thu, 21 Sep 2006 00:46:57 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
        b=CYYykbmAQEhMqnw1Ry1kbpa03+dNXLKhUJ2P4GrLsVR640Z7R0r4lR7NG0j6GLrPF0sa5O1WrwPo26Sr64Dfvfj47cMW2tbDmwKOkrY82y38D4C2SqWnzpMAEFxkEyHvz6o5hJWxupBT1aKioUim0rLLJ2Y703rWO71v0UnebOg=
Received: by 10.49.29.3 with SMTP id g3mr289253nfj;
        Thu, 21 Sep 2006 00:46:56 -0700 (PDT)
Received: by 10.49.7.11 with HTTP; Thu, 21 Sep 2006 00:46:56 -0700 (PDT)
Message-ID: <bcbfb06b0609210046h2742351jbf30fd8d8fd17469@mail.gmail.com>
Date: Thu, 21 Sep 2006 00:46:56 -0700
From: "Yip Ng" <yipng168@gmail.com>
To: derby-dev@db.apache.org
Subject: Re: Question on EXCEPT ALL and INTERSECT ALL
In-Reply-To: <4511DE5F.2030303@gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1922_23441150.1158824816836"
References: <4511DE5F.2030303@gmail.com>
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N

------=_Part_1922_23441150.1158824816836
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 9/20/06, Rajesh Kartha <kartha02@gmail.com> wrote:<snip>

> ij> --
> --  I expected 6,6,8,9 only
> --
> (select i from t1 union all select j from t2) except all (select k from
> t3);
> 1
> -----------
> 1
> 1
> 2
> 3
> 6
> 6
> 8
> 9
>
> 8 rows selected
> </snip>


 Hi Rajesh:

  The query result above returned from the system is correct.  Let's take
the query apart so we can visualize better:

Q1:  (select i from t1 union all select j from t2)
Q2:  select k from t3

Q1 will retain all rows from both table t1 and t2, so the result is:
1,2,3,4,9,1,3,2,1,6,8,6
Q2 result is:  5,2,3,4,1

Q1 EXCEPT ALL Q2, will have the result:  1, 1, 2, 3, 6, 6, 8, 9 because Q1
have three rows containing the value of 1, Q2 has only one, so the result
should retain (3-1 or 2) rows containing the value of 1.  Q1 have two rows
containing the value of 2, Q2 has only one, so
the result should retain (2-1 or 1) row containing the value of 2 and so
forth.  Rows of Q1 that does not appear in Q2 will be retained in the final
result as well.

Q1 EXCEPT Q2, will have the result:  6,8,9 because duplicates are eliminated
from both
Q1 and Q2 before applying the EXCEPT operator:

Q1-nodups:  1,2,3,4,6,8,9
Q2-nodups:  1,2,3,4,5

So the result here is 6,8,9, which is also correct in your output.  Now,
let's try the INTERSECT testcase you listed, the output is also expected and
correct:
<snip>
ij> --
-- Are these Wrong results, I expected 1,1,1,2,2,3,3,4
--
(select i from t1 union all select j from t2) intersect all ( select k from
t3);

1
-----------
1
2
3
4

4 rows selected
</snip>

Again, using the results from above:

Q1:  1,2,3,4,9,1,3,2,1,6,8,6
Q2 : 5,2,3,4,1

Q1 INTERSECT ALL Q2 means "return all rows from Q1 that are also found in
Q2".  The result will be 1,2,3,4 because there are three rows containing the
value of 1 in Q1 but Q2 only has one, so the result will only have one row
containing the value of 1.  There are two rows containing the value of 2 in
Q1 but Q2 only has one, so the result will only  have one row containing the
value of 2 and so forth.  (If Q2 happens to have 2 rows containing the value
of 2, the final result will have 2 rows containing the value of 2). Rows
that only appear in one of the two queries will not make it into the final
result.

For Q1 INTERSECT Q2, Q1 and Q2 will first have their duplicates removed
before
applying the INTERSECT operator:

Q1-nodups:  1,2,3,4,6,8,9
Q2-nodups:  1,2,3,4,5

So, the result is again 1,2,3,4.  Hope this helps.

Yip

------=_Part_1922_23441150.1158824816836
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 9/20/06, <b class="gmail_sendername">Rajesh Kartha</b> &lt;<a href="mailto:kartha02@gmail.com">kartha02@gmail.com</a>&gt; wrote:<div><span class="gmail_quote"></span><div>&lt;snip&gt; <br></div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
ij&gt; --<br>--&nbsp;&nbsp;I expected 6,6,8,9 only<br>--<br>(select i from t1 union all select j from t2) except all (select k from t3);<br>1<br>-----------<br>1<br>1<br>2<br>3<br>6<br>6<br>8<br>9<br><br>8 rows selected<br>&lt;/snip&gt;
</blockquote><div><br>&nbsp;Hi Rajesh:<br><br>&nbsp; The query result above returned from the system is correct.&nbsp; Let's take the query apart so we can visualize better:<br><br>Q1:&nbsp; (select i from t1 union all select j from t2)<br>Q2:&nbsp; select k from t3
<br><br>Q1 will retain all rows from both table t1 and t2, so the result is:&nbsp;&nbsp; 1,2,3,4,9,1,3,2,1,6,8,6<br>Q2 result is:&nbsp; 5,2,3,4,1<br><br>Q1 EXCEPT ALL Q2, will have the result:&nbsp; 1, 1, 2, 3, 6, 6, 8, 9 because Q1 have three rows containing the value of 1, Q2 has only one, so the result should retain (3-1 or 2) rows containing the value of 1.&nbsp; Q1 have two rows containing the value of 2, Q2 has only one, so
<br>the result should retain (2-1 or 1) row containing the value of 2 and so forth.&nbsp; Rows of Q1 that does not appear in Q2 will be retained in the final result as well.<br><br>Q1 EXCEPT Q2, will have the result:&nbsp; 6,8,9 because duplicates are eliminated from both
<br>Q1 and Q2 before applying the EXCEPT operator:<br><br>Q1-nodups:&nbsp; 1,2,3,4,6,8,9<br>Q2-nodups:&nbsp; 1,2,3,4,5<br><br>So the result here is 6,8,9, which is also correct in your output.&nbsp; Now, let's try the INTERSECT testcase you listed, the output is also expected and correct:
<br>&lt;snip&gt;<br>ij&gt; --<br>-- Are these Wrong results, I expected 1,1,1,2,2,3,3,4<br>--<br>(select i from t1 union all select j from t2) intersect all ( select k from t3);<br><br>1<br>-----------<br>1<br>2<br>3<br>4
<br><br>4 rows selected<br>&lt;/snip&gt;<br><br>Again, using the results from above:<br><br>Q1:&nbsp; 1,2,3,4,9,1,3,2,1,6,8,6<br>
Q2 : 5,2,3,4,1<br><br>Q1 INTERSECT ALL Q2 means &quot;return all rows from Q1 that are also found in Q2&quot;.&nbsp; The result will be 1,2,3,4 because there are three rows containing the value of 1 in Q1 but Q2 only has one, so the result will only have one row containing the value of 1.&nbsp; There are two rows containing the value of 2 in Q1 but Q2 only has one, so the result will only&nbsp; have one row containing the value of 2 and so forth.&nbsp; (If Q2 happens to have 2 rows containing the value of 2, the final result will have 2 rows containing the value of 2). Rows that only appear in one of the two queries will not make it into the final result.&nbsp; 
<br><br>For Q1 INTERSECT Q2, Q1 and Q2 will first have their duplicates removed before<br>applying the INTERSECT operator:<br><br>Q1-nodups:&nbsp; 1,2,3,4,6,8,9<br>
Q2-nodups:&nbsp; 1,2,3,4,5<br><br>So, the result is again 1,2,3,4.&nbsp; Hope this helps.<br><br>Yip<br><br><br></div></div>

------=_Part_1922_23441150.1158824816836--