Re: New 8.1.6 bug of the day

dejaisbogus_at_my-deja.com wrote:
>
> Why does the following 8.1.6 normal join return bad data?
>
>
> SELECT TABLE1.F1, TABLE1.F2, TABLE2.F1, TABLE2.F2, TABLE2.F3
> FROM TABLE3, TABLE2, TABLE1
> WHERE TABLE1.F1='test1'
> AND TABLE1.F1=TABLE2.F3
> AND TABLE1.F3=TABLE3.F1;
>
> F1 F2 F1 F2 F3
> ---------- ---------- ---------- ---------- ----------
> test1 test2 999 test2 test1
>
> and the 999 is WRONG (should be 1).

Definitely different behavior from 7.3.4. I just tested the sql against that version and I came up with 1. Moved to 8.1.5 and I got the 999. Definitely something different.

I did notice one thing though. On 8.1.5 if an ANALYZE TABLE is not done then it doesn't make any difference whether the SELECT is performed with rule or choose. However, if statistics are gathered then the behavior changes with RULE giving the wrong answer and CHOOSE giving the correct answer.

I ran a couple different scenarios but it always seemed to come back to the result changing based on whether RULE or CHOOSE is used. Without statistics it doesn't make any difference - the answer is always wrong. With statistics the answer is correct for CHOOSE but wrong for RULE.

I also noticed that if the ALTER SESSION is set for RULE but a hint is provided in the SELECT statement (I tried several different hints, but not all of them) then the answer comes out correct - but only if statistics are present.

I agree with the assessment of others - this is something that Oracle needs to look into. I can see where it could definitely cause problems with joins. Received on Mon May 15 2000 - 00:00:00 CDT