Environment:
ps_rsc_ar820_tbl = Table exists in the Oracle 9i DB I'm logged onto
@opis = DB Link to an 8i DB

Problem:
Why don't the results from Query 1 match the results from Query 3?

Query 1 is returning more than the desired number of rows. When I run
Query 2
independently it returns the correct number of rows and values. Query
3 is what
I expected to receive from Query 1. Query 4 works properly and it is
just
Query 1 written without using an IN clause.

-- Query 1
select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN (select a.shipper_nbr
from op_shipper@opis a
where a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from
op_tag_history@opis b
where b.shipper_key =
a.shipper_key
and b.event_key = 85)
)

-- Query 1 Returns (NOT CORRECT):
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353677 17243
M353677 17243
M353677 17243
M353677 17243

-- Query 2: Running the SQL located in the IN Sub-Query
select a.shipper_nbr
from op_shipper@opis a
where a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from op_tag_history@opis b
where b.shipper_key = a.shipper_key
and b.event_key = 85)

-- Query 2: Returns
M353655
M353677
M353744
M353771
M353934
M353946
M353953
M354038
M354132
M354154
M354187


-- Query 3
select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN
('M353655','M353677','M353744','M353771',
'M353934','M353946','M353953','M354038',
'M354132','M354154','M354187'
)

-- Query 3 Returns (This is correct)
M353655 21085
M353677 17243


-- Query 4
select c.bill_of_lading, c.qty
from ps_rsc_ar820_tbl c, op_shipper a
where c.bill_of_lading = a.shipper_nbr
and a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from op_tag_history b
where b.shipper_key = a.shipper_key
and b.event_key = 85)

--Query 4 Returns (This is correct)
M353655 21085
M353677 17243

Any clarification on why this is happening would be appreciated!

-Ryan

On Feb 12, 12:25 pm, zugzu...@gmail.com wrote:
> Environment:
> ps_rsc_ar820_tbl = Table exists in the Oracle 9i DB I'm logged onto
> @opis = DB Link to an 8i DB
>
> Problem:
> Why don't the results from Query 1 match the results from Query 3?
>
> Query 1 is returning more than the desired number of rows. When I run
> Query 2
> independently it returns the correct number of rows and values. Query
> 3 is what
> I expected to receive from Query 1. Query 4 works properly and it is
> just
> Query 1 written without using an IN clause.
>
> -- Query 1
> select bill_of_lading, qty
> from ps_rsc_ar820_tbl
> where bill_of_lading IN (select a.shipper_nbr
> from op_shipper@opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from
> op_tag_history@opis b
> where b.shipper_key =
> a.shipper_key
> and b.event_key = 85)
> )
>
> -- Query 1 Returns (NOT CORRECT):
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353677 17243
> M353677 17243
> M353677 17243
> M353677 17243
>
> -- Query 2: Running the SQL located in the IN Sub-Query
> select a.shipper_nbr
> from op_shipper@opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from op_tag_history@opis b
> where b.shipper_key = a.shipper_key
> and b.event_key = 85)
>
> -- Query 2: Returns
> M353655
> M353677
> M353744
> M353771
> M353934
> M353946
> M353953
> M354038
> M354132
> M354154
> M354187
>
> -- Query 3
> select bill_of_lading, qty
> from ps_rsc_ar820_tbl
> where bill_of_lading IN
> ('M353655','M353677','M353744','M353771',
> 'M353934','M353946','M353953','M354038',
> 'M354132','M354154','M354187'
> )
>
> -- Query 3 Returns (This is correct)
> M353655 21085
> M353677 17243
>
> -- Query 4
> select c.bill_of_lading, c.qty
> from ps_rsc_ar820_tbl c, op_shipper a
> where c.bill_of_lading = a.shipper_nbr
> and a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from op_tag_history b
> where b.shipper_key = a.shipper_key
> and b.event_key = 85)
>
> --Query 4 Returns (This is correct)
> M353655 21085
> M353677 17243
>
> Any clarification on why this is happening would be appreciated!
>
> -Ryan

try query 1 and queery 3 with this select phrase:
select bill_of_lading, qty, *
...

and see if that doesn't give a hint at what's happening.

Good idea but it didn't help.

If I just try to use bill_of_lading, qty, * it gives me: "ORA-00936:
missing expression".

If I try to give the table an identifier (select bill_of_lading, qty,
z.* from ps_rsc_ar820_tbl z...) then it just returns an identical Z
row for each of the dups. I also tried to include the identifiers in
the sub-query to make sure something weird wasn't going on there and
as expected Oracle complained that I was referencing an invalid
identifier.

Thanks,
Ryan

>
> try query 1 and queery 3 with this select phrase:
> select bill_of_lading, qty, *
> ...
>
> and see if that doesn't give a hint at what's happening.- Hide quoted text -
>

zugzug21@gmail.com wrote:
> Environment:
> ps_rsc_ar820_tbl = Table exists in the Oracle 9i DB I'm logged onto
> @opis = DB Link to an 8i DB
>
> Problem:
> Why don't the results from Query 1 match the results from Query 3?
>
> Query 1 is returning more than the desired number of rows. When I run
> Query 2
> independently it returns the correct number of rows and values. Query
> 3 is what
> I expected to receive from Query 1. Query 4 works properly and it is
> just
> Query 1 written without using an IN clause.
>
> -- Query 1
> select bill_of_lading, qty
> from ps_rsc_ar820_tbl
> where bill_of_lading IN (select a.shipper_nbr
> from op_shipper@opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from
> op_tag_history@opis b
> where b.shipper_key =
> a.shipper_key
> and b.event_key = 85)
> )
>
> -- Query 1 Returns (NOT CORRECT):
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353677 17243
> M353677 17243
> M353677 17243
> M353677 17243
>
> -- Query 2: Running the SQL located in the IN Sub-Query
> select a.shipper_nbr
> from op_shipper@opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from op_tag_history@opis b
> where b.shipper_key = a.shipper_key
> and b.event_key = 85)
>
> -- Query 2: Returns
> M353655
> M353677
> M353744
> M353771
> M353934
> M353946
> M353953
> M354038
> M354132
> M354154
> M354187
>
>
> -- Query 3
> select bill_of_lading, qty
> from ps_rsc_ar820_tbl
> where bill_of_lading IN
> ('M353655','M353677','M353744','M353771',
> 'M353934','M353946','M353953','M354038',
> 'M354132','M354154','M354187'
> )
>
> -- Query 3 Returns (This is correct)
> M353655 21085
> M353677 17243
>
>
> -- Query 4
> select c.bill_of_lading, c.qty
> from ps_rsc_ar820_tbl c, op_shipper a
> where c.bill_of_lading = a.shipper_nbr
> and a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from op_tag_history b
> where b.shipper_key = a.shipper_key
> and b.event_key = 85)
>
> --Query 4 Returns (This is correct)
> M353655 21085
> M353677 17243
>
> Any clarification on why this is happening would be appreciated!
>
> -Ryan

I wish I had a dollar for everytime someone that makes a mistake
writing a query scream "BUG!"

A bug in some new or obscure functionality ... yeah I'll buy it
but this is just a Cartesian product produced by Oracle doing
precisely what you are telling it to do.

That's my story and I'm sticking with it unless you can put together
DDL and DML that proves otherwise. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

> Why don't the results from Query 1 match the results from Query 3?

As both Query 1 and Query 3 look exactly like this, only differing in
the IN clause

select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN ( ... )

it should be impossible that Query 1 returns five records for
bill_of_lading = M353655 and Query 3 returns just one. There are only
two possible reasons for it as far as I can see it: 1. A bug in
Oracle. 2. Your actual query differs from what you posted. The second
reason is much more likely than the first one, however. Maybe you
tried to simplify your query for posting it here and accidentally
removed the critical part?

It's definately not number 2. I've had others that I work with look
at the code and run it themselves. The only way we can get the query
to return the correct results is to put a distinct on bill_of_lading,
qty...but that doesn't really explain what is happening...it just
masks the issue.

Thanks for the post.

-Ryan

> it should be impossible that Query 1 returns five records for
> bill_of_lading = M353655 and Query 3 returns just one. There are only
> two possible reasons for it as far as I can see it: 1. A bug in
> Oracle. 2. Your actual query differs from what you posted. The second
> reason is much more likely than the first one, however. Maybe you
> tried to simplify your query for posting it here and accidentally
> removed the critical part?

Okay Mr. Intelligence...explain why it's happening! Instead of
offering an explanation to my question you have decided to try to
insult my intelligence. Why even post a response?

Great advertisement for your organization...it gives prospective
clients a taste of what they can expect if they signed up for a
seminar.

> I wish I had a dollar for everytime someone that makes a mistake
> writing a query scream "BUG!"
>
> A bug in some new or obscure functionality ... yeah I'll buy it
> but this is just a Cartesian product produced by Oracle doing
> precisely what you are telling it to do.
>
> That's my story and I'm sticking with it unless you can put together
> DDL and DML that proves otherwise. ;-)
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

On Feb 13, 9:05 am, zugzu...@gmail.com wrote:
> It's definately not number 2. I've had others that I work with look
> at the code and run it themselves. The only way we can get the query
> to return the correct results is to put a distinct on bill_of_lading,
> qty...but that doesn't really explain what is happening...it just
> masks the issue.
>
> Thanks for the post.
>
> -Ryan
>
> > it should be impossible that Query 1 returns five records for
> > bill_of_lading = M353655 and Query 3 returns just one. There are only
> > two possible reasons for it as far as I can see it: 1. A bug in
> > Oracle. 2. Your actual query differs from what you posted. The second
> > reason is much more likely than the first one, however. Maybe you
> > tried to simplify your query for posting it here and accidentally
> > removed the critical part?


Can you run your four SQLs in sqlplus and cut paste the results
here. In your original post, you refer to op_shipper as
a distributed table in Query 1 while you refer to it as
a local table in Query 4.

Apart from that can you post explain plans on the two queries
(The IN vs the join)?

Have the tables in question recently suffered from any kind
of block corruption?
Also, what version of 9i do you have? Are you on the
latest patch (9.2.0.8)?

Anurag

THIS IS THE SAME QUERY AS YESTERDAY WITH THE SYSDATE CHANGED TO
ACCOUNT FOR
THE EXTRA DAY. WE FOUND EVIDENCE THAT SEEMS TO SHOW THIS PROBLEM IS
DUE
TO 9I TO 8I DB CROSS JOIN BECAUSE IF WE RUN QUERY 1 FROM THE 8I DB
(OF COURSE CHANGING THE DB LINKS APPROPRIATELY) WE DO NOT SEE THE
PROBLEM.

ANY THOUGHTS?

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 13 10:27:25 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> select bill_of_lading, qty
2 from ps_rsc_ar820_tbl
3 where bill_of_lading IN (select a.shipper_nbr
4 from op_shipper@opis a
5 where a.ship_from_loc_key = 5
6 and a.ship_dt > SYSDATE - 11
7 and exists (select * from
op_tag_history@opis b
8 where b.shipper_key =
a.shipper_key
9 and b.event_key = 85)
10 ) ;

BILL_OF_LADING QTY
------------------------------ ----------
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353677 17243
M353677 17243
M353677 17243
M353677 17243

10 rows selected.

SQL> select a.shipper_nbr
2 from op_shipper@opis a
3 where a.ship_from_loc_key = 5
4 and a.ship_dt > SYSDATE - 11
5 and exists (select * from op_tag_history@opis b
6 where b.shipper_key = a.shipper_key
7 and b.event_key = 85);

SHIPPER_NBR
------------------------------
M353655
M353677
M353744
M353771
M353934
M353946
M353953
M354038
M354132
M354154
M354187
M354243
M354245
M354386
M354388

15 rows selected.

SQL> select bill_of_lading, qty
2 from ps_rsc_ar820_tbl
3 where bill_of_lading IN
4 ('M353655','M353677','M353744','M353771','M353934' ,'M353946',
5 'M353953','M354038','M354132','M354154','M354187', 'M354243',
6 'M354245','M354386','M354388'
7 );

BILL_OF_LADING QTY
------------------------------ ----------
M353655 21085
M353677 17243

SQL> select c.bill_of_lading, c.qty
2 from ps_rsc_ar820_tbl c, op_shipper a
3 where c.bill_of_lading = a.shipper_nbr
4 and a.ship_from_loc_key = 5
5 and a.ship_dt > SYSDATE - 11
6 and exists (select * from op_tag_history b
7 where b.shipper_key = a.shipper_key
8 and b.event_key = 85);

BILL_OF_LADING QTY
------------------------------ ----------
M353655 21085
M353677 17243

SQL>

++++++++++++++++++++++++++++++

THIS IS THE SAME QUERY BEING RAN FROM THE ORACLE8 DB USING
A DB LINK TO THE ORACLE9 DB.

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 13 10:31:16 2007

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.6.1.0 - 64bit Production

SQL> select bill_of_lading, qty
2 from ps_rsc_ar820_tbl@finance
3 where bill_of_lading IN (select a.shipper_nbr
4 from op_shipper a
5 where a.ship_from_loc_key = 5
6 and a.ship_dt > SYSDATE - 11
7 and exists (select * from
op_tag_history b
8 where b.shipper_key =
a.shipper_key
9 and b.event_key = 85)
10 );

BILL_OF_LADING QTY
------------------------------ ----------
M353655 21085
M353677 17243

SQL>
>
> Can you run your four SQLs in sqlplus and cut paste the results
> here. In your original post, you refer to op_shipper as
> a distributed table in Query 1 while you refer to it as
> a local table in Query 4.
>

On Feb 13, 10:38 am, zugzu...@gmail.com wrote:
> THIS IS THE SAME QUERY AS YESTERDAY WITH THE SYSDATE CHANGED TO
> ACCOUNT FOR
> THE EXTRA DAY. WE FOUND EVIDENCE THAT SEEMS TO SHOW THIS PROBLEM IS
> DUE
> TO 9I TO 8I DB CROSS JOIN BECAUSE IF WE RUN QUERY 1 FROM THE 8I DB
> (OF COURSE CHANGING THE DB LINKS APPROPRIATELY) WE DO NOT SEE THE
> PROBLEM.
>
> ANY THOUGHTS?
>
> SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 13 10:27:25 2007
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.7.0 - Production
>
> SQL> select bill_of_lading, qty
> 2 from ps_rsc_ar820_tbl
> 3 where bill_of_lading IN (select a.shipper_nbr
> 4 from op_shipper@opis a
> 5 where a.ship_from_loc_key = 5
> 6 and a.ship_dt > SYSDATE - 11
> 7 and exists (select * from
> op_tag_history@opis b
> 8 where b.shipper_key =
> a.shipper_key
> 9 and b.event_key = 85)
> 10 ) ;
>
> BILL_OF_LADING QTY
> ------------------------------ ----------
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353655 21085
> M353677 17243
> M353677 17243
> M353677 17243
> M353677 17243
>
> 10 rows selected.
>
> SQL> select a.shipper_nbr
> 2 from op_shipper@opis a
> 3 where a.ship_from_loc_key = 5
> 4 and a.ship_dt > SYSDATE - 11
> 5 and exists (select * from op_tag_history@opis b
> 6 where b.shipper_key = a.shipper_key
> 7 and b.event_key = 85);
>
> SHIPPER_NBR
> ------------------------------
> M353655
> M353677
> M353744
> M353771
> M353934
> M353946
> M353953
> M354038
> M354132
> M354154
> M354187
> M354243
> M354245
> M354386
> M354388
>
> 15 rows selected.
>
> SQL> select bill_of_lading, qty
> 2 from ps_rsc_ar820_tbl
> 3 where bill_of_lading IN
> 4 ('M353655','M353677','M353744','M353771','M353934' ,'M353946',
> 5 'M353953','M354038','M354132','M354154','M354187', 'M354243',
> 6 'M354245','M354386','M354388'
> 7 );
>
> BILL_OF_LADING QTY
> ------------------------------ ----------
> M353655 21085
> M353677 17243
>
> SQL> select c.bill_of_lading, c.qty
> 2 from ps_rsc_ar820_tbl c, op_shipper a
> 3 where c.bill_of_lading = a.shipper_nbr
> 4 and a.ship_from_loc_key = 5
> 5 and a.ship_dt > SYSDATE - 11
> 6 and exists (select * from op_tag_history b
> 7 where b.shipper_key = a.shipper_key
> 8 and b.event_key = 85);
>
> BILL_OF_LADING QTY
> ------------------------------ ----------
> M353655 21085
> M353677 17243
>
> SQL>
>
> ++++++++++++++++++++++++++++++
>
> THIS IS THE SAME QUERY BEING RAN FROM THE ORACLE8 DB USING
> A DB LINK TO THE ORACLE9 DB.
>
> SQL*Plus: Release 8.1.7.0.0 - Production on Tue Feb 13 10:31:16 2007
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production
> With the Partitioning option
> JServer Release 8.1.6.1.0 - 64bit Production
>
> SQL> select bill_of_lading, qty
> 2 from ps_rsc_ar820_tbl@finance
> 3 where bill_of_lading IN (select a.shipper_nbr
> 4 from op_shipper a
> 5 where a.ship_from_loc_key = 5
> 6 and a.ship_dt > SYSDATE - 11
> 7 and exists (select * from
> op_tag_history b
> 8 where b.shipper_key =
> a.shipper_key
> 9 and b.event_key = 85)
> 10 );
>
> BILL_OF_LADING QTY
> ------------------------------ ----------
> M353655 21085
> M353677 17243
>
> SQL>
>
>
>
> > Can you run your four SQLs in sqlplus and cut paste the results
> > here. In your original post, you refer to op_shipper as
> > a distributed table in Query 1 while you refer to it as
> > a local table in Query 4.


ok .. You might be hitting a bug. It appears that
instead of doing a semi - join, the IN clause is
doing a regular join.
What is the explain plan of Query 1 (the one with wrong
results)?

Try infuencing the query using following hint:
select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN (select /*+ HASH_SJ */ a.shipper_nbr
from op_shipper@opis a
where a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 11
and exists (select * from
op_tag_history@opis b
where b.shipper_key =
a.shipper_key
and b.event_key = 85)
) ;

Note the hash_sj hint I gave.

You might want to patch to 9.2.0.8 if you believe you
are hitting this bug. Its quite likely it might be
fixed in 9.2.0.8

... and please don't use CAPS. We are just trying to help ..
CAPS=shouting .. doesn't give us much joy to help you.

Anurag