RE: Misbehaving query
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Misbehaving query
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: Misbehaving query
Bellow, Bambi wrote: 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 SELECT a.* , b.* FROM (SELECT dummy x FROM dual WHERE 1 = 2) a , (SELECT dummy x FROM dual) b WHERE b.x = a.x(+) / SELECT a.* , b.* FROM (SELECT dummy x FROM dual WHERE 1 = 2) a , (SELECT dummy x FROM dual) b / You join empty result set of table a, with some rows from table b. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Misbehaving query
Thank you Carol!
Bambi.
-Original Message-
Sent: Friday, December 12, 2003 2:44 PM
To: Multiple recipients of list ORACLE-L
Junk minus junk2 results in no rows, and when you do the implied
cartesian join between view a and view b, joining no rows with one row
gives no rows.
Makes sense to me.
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Carol Bristow
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Bellow, Bambi
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Misbehaving query
That's what I was thinking, too. I tried it on 8.1.7.4 and it works the
same way, as I expected. You would need an outer join to get something
back.
Scott Canaan ([EMAIL PROTECTED])
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.
-Original Message-
Carol Bristow
Sent: Friday, December 12, 2003 3:44 PM
To: Multiple recipients of list ORACLE-L
Junk minus junk2 results in no rows, and when you do the implied
cartesian join between view a and view b, joining no rows with one row
gives no rows.
Makes sense to me.
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Carol Bristow
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Scott Canaan
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Misbehaving query
Junk minus junk2 results in no rows, and when you do the implied
cartesian join between view a and view b, joining no rows with one row
gives no rows.
Makes sense to me.
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415
-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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Carol Bristow
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
