RE: Misbehaving query

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 12 Dec 2003 12:49:49 -0800
Message-ID: <F001.005D9B48.20031212124949@fatcity.com>

Bambi,

I don't think there's a bug here.
Junk contains A,B,C.
Junk2 contains A,B,C,X.
Junk2 minus junk will yield X.
Junk minus junk2 will yield 'no rows'.

So, if you select from the cartesian join of the two inlines, and one of the inlines has no rows, then the output will have no rows. In other words, a cartesian join of two tables is semantically different from a union of the same two tables. For a simpler understanding, try this: create table junk3 as select * from junk where 1=0; Now, junk3 will have no rows. Now, do:
select * from junk,junk3;
What do you get?

Hope that helps,

-Mark

-----Original Message-----

Sent: Friday, December 12, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

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_at_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_at_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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bobak, Mark
INET: Mark.Bobak_at_il.proquest.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