Re: Oracle Bug? SQL not returning correct rows.

On Feb 13, 10:38 am, zugzu..._at_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_at_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_at_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_at_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_at_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_at_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_at_opis a
                            where a.ship_from_loc_key = 5
                            and a.ship_dt > SYSDATE - 11
                            and exists (select * from
 op_tag_history_at_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 Received on Tue Feb 13 2007 - 10:53:44 CST