Tuesday, August 12, 2008

wrong results for with cursor_sharing='SIMILAR'

If you running Oracle using cursor_sharing='SIMILAR'
you probably already aware about a bug that causes database
to produce wrong results for some type of sql statements.

If you have not run into it yet, try to execute the following
statement with cursor_sharing set to SIMILAR (either on system
or session level)

select t1.d1, t2.n1
(select 1 as d1 from dual d1, dual d2) t1,
(select 1 as d1, 2 as n1 from dual) t2
where t2.d1(+) = t1.d1
group by t1.d1, t2.n1;

If you got 1 and null in output - this is it, you hit the one.

Further change of cursor_sharing to 'EXACT' will return
1 and 2 and if after that you want to get bad results again
(changing to 'SIMILAR' back) nothing happens - from now on
you will see good results (for this particular cursor) until
you bounce a database.

The bug 7277101 is also applicable for and
unfortunately has not fixed yet.

Have a good day!

No comments: