Message-ID: <F001.005D9B4F.20031212125951@fatcity.com>
Date: Fri, 12 Dec 2003 12:59:51 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: ml-errors@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Mladen Gogala <mladen@wangtrading.com>
Subject: Re: Misbehaving query
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

No problem. You are trying to create a cartesian product of the two queries.
Now, what is a Cartesian product of the two sets, A and B? It is a set of
all ordered pairs (a,b) where a is element of A and b is element of B. 
The result of your firs query is an ampty set:

SQL> select * from junk minus select * from junk2 ;
 
no rows selected

Cartesian product of an empty set with anything is an empty set itself.
A little bit set theory saves the day. If whe insert 'Y' into JUNK, everything
works as advertised:

  1  select a.*, b.*
  2  from
  3  ( select * from junk minus select * from junk2 ) a,
  4* ( select * from junk2 minus select * from junk ) b
SQL> /
 
T T
- -
Y X


On 12/12/2003 03:04:33 PM, "Bellow, Bambi" wrote:
> Hi!
> 
> I have a query that I think is behaving oddly; and, it may just be that I'm
> blind and am doing something silly (*there's* a first!), or it may be
> environment specific, but, I'm thinkin it may just be a bug.  I have filed a
> TAR with Oracle, and they keep sending workarounds, when I told them from
> the start that I've got one, this is only a question of why this query isn't
> working.  So, two parts, really... is this query really not working, and if
> so, does anyone have a clue as to why?
> 
> We're on 9.2.0.2 on SunOS.  The overall issue is easy.  I have two tables.
> I want to find the difference in the contents.  This is easily accomplished
> by doing
> 
> A MINUS B
> UNION
> B MINUS A
> 
> *That's* not the issue.  The issue is that if I do this through inlines, the
> query fails.  I'll put everything out there so you can just cut and paste
> iffen you wanna...
> 
> SQL> create table junk (test char(1));
> 
> Table created.
> 
> SQL> insert into junk values ('A');
> 
> 1 row created.
> 
> SQL> insert into junk values ('B');
> 
> 1 row created.
> 
> SQL> insert into junk values ('C');
> 
> 1 row created.
> 
> SQL> create table junk2 as select * from junk;
> 
> Table created.
> 
> SQL> insert into junk2 values ('X');
> 
> 1 row created.
> 
> SQL> select * from junk;
> 
> T
> -
> A
> B
> C
> 
> SQL> select * from junk2;
> 
> T
> -
> A
> B
> C
> X
> 
> SQL> select * from junk minus select * from junk2
>   2  union
>   3  select * from junk2 minus select * from junk;
> 
> T
> -
> X
> 
> SQL> select a.* 
>   2  from
>   3  ( select * from junk2 minus select * from junk ) a;
> 
> T
> -
> X
> 
> SQL> select a.*, b.*
>   2  from
>   3  ( select * from junk2 minus select * from junk ) a,
>   4  ( select * from junk2 minus select * from junk ) b;
> 
> T T
> - -
> X X
> 
> SQL> select a.*, b.*
>   2  from
>   3  ( select * from junk minus select * from junk2 ) a,
>   4  ( select * from junk2 minus select * from junk ) b;
> 
> no rows selected
> 
> 
> Anyone?
> Bambi.
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Bellow, Bambi
>   INET: bbellow@chi.navtech.com
> 
> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
> San Diego, California        -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen@wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).