Re: Oracle Bug? SQL not returning correct rows.

From: <zugzug21_at_gmail.com>
Date: 13 Feb 2007 07:38:53 -0800
Message-ID: <1171381133.439901.25570@j27g2000cwj.googlegroups.com>

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

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

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.
>
Received on Tue Feb 13 2007 - 09:38:53 CST