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