31 May, 2008

Ever heard of "_simple_view_merging" ?

We've all (or most of us, I guess) have heard of and some of us have even realised being hit by complex_view_merging issues in 9i and 10g.

A couple of days ago, I came across Bug#6653652 "Wrong results from simple view merging". {I "came across" the Bug only when going through recent notes in the "Knowledge Base" section and do NOT know if any of my databases / applications has or does suffer this bug !).

Ostensibly it is a 10g bug but I ran the test script in 9.2.0.6 and saw the same results -- except (uh oh !) that I could not run an "alter session set "_simple_view_merging"=FALSE;" in 9.2.0.6 !

I've taken the very simple test case from Note# 6653652.8 and have expanded it with a few more "verification" queries :
The tests were in 9.2.0.6 and 10.2.0.3 I plan to upgrade one of my test environments to 10.2.0.4 this weekend.

UPDATE 01-Jun : Testing on a 10.2.0.4 environment, the first query DID return the correct results (so also did the one where I used a DECODE to translate a NULL on organizer, I did get the correct value "1024") -- the bug IS fixed in 10.2.0.4


SQL>
SQL> REM Bug 6653652. Note 6653652.8
SQL> REM title : "Wrong results from simple view merging"
SQL> REM description :
SQL> REM "Wrong results are possible from queries that undergo
SQL> rem simple view merging if the query contains a scalar
SQL> rem subquery in an OR branch."
SQL>
SQL> REM Fixed In 10.2.0.4, 11.1.0.7, 11.2.x.x (future release)
SQL>
SQL>
SQL>
SQL>
SQL> drop table test1;
drop table test1
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table test2;
drop table test2
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> drop table test3;
drop table test3
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> -- using exactly the same structures and data as in the Oracle test case
SQL> -- just change the string 'organizer' to 'is org record' to reduce confusion
SQL> create table test1 (id varchar2(128), creator number);

Table created.

SQL> create table test2 (id varchar2(128), key varchar2(64),value number);

Table created.

SQL> create table test3 (id number, value number);

Table created.

SQL> insert into test1 values('a',2);

1 row created.

SQL> rem insert into test2 values('a','organizer',1024);
SQL> insert into test2 values('a','is org record',1024);

1 row created.

SQL> insert into test3 values(1024,2);

1 row created.

SQL>
SQL>
SQL>
SQL> -- using exactly the same query as in the Oracle test case
SQL> -- this query (with the scalar subquery in the OR) returns incorrect results
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2

SQL>
SQL>
SQL> -- let's verify tables test1 and test2
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 )
6 -- or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2 1024

SQL>
SQL>
SQL> -- let's verify table test3
SQL> select id from test3 group by id;

ID
----------
1024

SQL>
SQL> -- test3's group by returns the value
SQL> -- therefore the subquery in the test case should return TRUE and succeed !
SQL>
SQL>
SQL> -- let's explicitly define how the OR is in the WHERE
SQL> -- handle a null for organizer with a decode
SQL> select creator, decode(organizer,NULL,9999,organizer)
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where
6 (
7 (creator=2)
8 or organizer in (select id from test3 group by id)
9 );

CREATOR DECODE(ORGANIZER,NULL,9999,ORGANIZER)
---------- -------------------------------------
2 9999

SQL>
SQL>
SQL> --- retry original query with _simple_view_merging=FALSE
SQL>
SQL> alter session set "_simple_view_merging"=FALSE;

Session altered.

SQL>
SQL> select creator, organizer
2 from (select creator,
3 (select value from test2 where id=test1.id) as organizer
4 from test1) view1
5 where (creator=2 or organizer in (select id from test3 group by id));

CREATOR ORGANIZER
---------- ----------
2 1024

SQL>
SQL>
SQL>
Note : alter session set "_simple_view_merging"=FALSE; was not possible in 9.2.0.6

4 comments:

Mumbai Guy said...

Hemant, your posts are very informative and educational. Keep blogging. thanks.

Mike said...

Hi Hennant, I'm faced with the bug 4730372 related to the same parameter "_simple_view_merging".
The app worked fine on oracle 9 but after migrating to oracle 10.2.0.4 we found this bug:

http://myotragusbalearicus.wordpress.com/2010/12/13/ora-0600-after-migrating-to-oracle-10-2-0-4-bug-4730372/

Hemant K Chitale said...

Mike,
In my tests, the bug manifested in 9.2 (9.2.0.6).

Hemant

Luiz Fernando Altran said...

7 years after you saved me :)
My Oracle 11g R1 suddenly start to give me ORA 7445, and after using your trick I solved the problem.
Thank you