Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Bug? SQL not returning correct rows.
Anurag,
When I ran the query using the hint I got the proper result set. What triggered you to think of adding this hint? I understand what the HASH_SJ hint is doing but a hint should not affect the result set...right?!?
Here is the explain plans for both:
Also, I wasn't using CAPS to look like I was yelling (I know...that drives me nuts too). I wanted to separate my comments from the spooled output and since I can't use bold, italics, or underline that was the only thing I could think of.
Thanks for taking the time to respond to my post. I've had several coworkers looking into this also. The query was for a one-time report and the database I was running it from was not the system I support. I'll forward a message to the DBAs to have them check if they can upgrade to fix this solution.
-Ryan
> 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- Hide quoted text -
>
> - Show quoted text -
Received on Tue Feb 13 2007 - 13:37:06 CST