Re: Oracle Bug? SQL not returning correct rows.

zugzug21_at_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_at_opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from
> op_tag_history_at_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_at_opis a
> where a.ship_from_loc_key = 5
> and a.ship_dt > SYSDATE - 10
> and exists (select * from op_tag_history_at_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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org