Re: Oracle Bug? SQL not returning correct rows.

On Feb 13, 9:05 am, zugzu..._at_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 Received on Tue Feb 13 2007 - 08:56:11 CST