Vishal desai’s Oracle Blog

December 9, 2011

Oracle Query Trasformation bug

Filed under: Bug — vishaldesai @ 12:16 am

Are you aware of any bugs in decode vs case behavior? One of my developer posed this open ended question to me other day. I asked him to send me sql code or test case.

Test Case

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production 

SQL> create table table_1 as (select 'a' col1, 1 col2 from dual union select 'b' , 2  from dual );

Table created.

SQL> create table table_2 as (select 'a' col1, 1 col2 from dual union select 'b' , null  from dual);

Table created.

SQL>
SQL>
SQL> select * from table_1;

C       COL2
- ---------- 
a          1
b          2                                                                                                                                                                                            

SQL> select * from table_2;

C       COL2
- ---------- 
a          1
b                                                                                                                                                                                                       

SQL>
SQL> SELECT a.col1 acol1,
  2    a.col2 acol2,
  3    b.col1 bcol1,
  4    b.col2 bcol2 ,
  5    CASE
  6      WHEN 1 = 1
  7      THEN 'case breaks it'
  8    END case_breaks_it
  9  FROM
 10    (SELECT * FROM table_1) a,
 11    (SELECT col1, NVL(col2,0) col2 FROM table_2 ) b
 12  WHERE a.col1 = b.col1(+)
 13  AND a.col2   = b.col2(+);

A      ACOL2 B      BCOL2 CASE_BREAKS_IT
- ---------- - ---------- -------------- 
a          1 a          1 case breaks it
b          2            0 case breaks it                                                                                                                                                                

SQL>
SQL>
SQL>
SQL> SELECT a.col1 acol1,
  2    a.col2 acol2,
  3    b.col1 bcol1,
  4    b.col2 bcol2 ,
  5    DECODE(1,1,'decode works','this is strange') decode_doesnt
  6  FROM
  7    (SELECT * FROM table_1) a,
  8    (SELECT col1, NVL(col2,0) col2 FROM table_2) b
  9  WHERE a.col1 = b.col1(+)
 10  AND a.col2   = b.col2(+);

A      ACOL2 B      BCOL2 DECODE_DOESN
- ---------- - ---------- ------------ 
a          1 a          1 decode works
b          2              decode works

.

As you can see from above, BCOL2 column shows 0 when using case statement instead of null. Lets quickly look at run time query plan.

Query Plan

SQL> alter session set statistics_level=all;

Session altered.

SQL>
SQL> SELECT a.col1 acol1,
  2    a.col2 acol2,
  3    b.col1 bcol1,
  4    b.col2 bcol2 ,
  5    CASE
  6      WHEN 1 = 1
  7      THEN 'case breaks it'
  8    END case_breaks_it
  9  FROM
 10    (SELECT * FROM table_1) a,
 11    (SELECT col1, NVL(col2,0) col2 FROM table_2 ) b
 12  WHERE a.col1 = b.col1(+)
 13  AND a.col2   = b.col2(+);

A      ACOL2 B      BCOL2 CASE_BREAKS_IT
- ---------- - ---------- -------------- 
a          1 a          1 case breaks it
b          2            0 case breaks it                                                                                                                                                                

SQL> @xall

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  66rq1pmg8zyyy, child number 0
------------------------------------- 
SELECT a.col1 acol1,   a.col2 acol2,   b.col1 bcol1,   b.col2 bcol2 ,
CASE     WHEN 1 = 1     THEN 'case breaks it'   END case_breaks_it FROM
  (SELECT * FROM table_1) a,   (SELECT col1, NVL(col2,0) col2 FROM
table_2 ) b WHERE a.col1 = b.col1(+) AND a.col2   = b.col2(+)                                                                                                                                           

Plan hash value: 1294080186                                                                                                                                                                             

--------------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT   |         |      1 |        |       |    17 (100)|          |      2 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN OUTER   |         |      1 |      2 |    88 |    17   (6)| 00:00:01 |      2 |00:00:00.01 |       7 |  1156K|  1156K|  535K (0)|
|   2 |   TABLE ACCESS FULL| TABLE_1 |      1 |      2 |    32 |     8   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| TABLE_2 |      1 |      2 |    56 |     8   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------- 

Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------- 

   1 - SEL$5428C7F1                                                                                                                                                                                     

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - SEL$5428C7F1 / TABLE_1@SEL$2
   3 - SEL$5428C7F1 / TABLE_2@SEL$3                                                                                                                                                                     

Outline Data
------------- 

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5428C7F1")
      MERGE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      FULL(@"SEL$5428C7F1" "TABLE_1"@"SEL$2")
      FULL(@"SEL$5428C7F1" "TABLE_2"@"SEL$3")
      LEADING(@"SEL$5428C7F1" "TABLE_1"@"SEL$2" "TABLE_2"@"SEL$3")
      USE_HASH(@"SEL$5428C7F1" "TABLE_2"@"SEL$3")
      END_OUTLINE_DATA
  */                                                                                                                                                                                                    

Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - access("TABLE_1"."COL1"="COL1" AND "TABLE_1"."COL2"=CASE  WHEN (ROWID IS NOT NULL) THEN NVL("COL2",0) ELSE NULL END )                                                                            

Column Projection Information (identified by operation id):
----------------------------------------------------------- 
   1 - (#keys=2) "TABLE_1"."COL1"[CHARACTER,1], "COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22], "COL2"[NUMBER,22]
   2 - "TABLE_1"."COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22]
   3 - ROWID[ROWID,10], "COL1"[CHARACTER,1], "COL2"[NUMBER,22]                                                                                                                                          

Note
----- 
   - dynamic sampling used for this statement (level=2)                                                                                                                                                 

63 rows selected.

SQL> SELECT a.col1 acol1,
  2    a.col2 acol2,
  3    b.col1 bcol1,
  4    b.col2 bcol2 ,
  5    DECODE(1,1,'decode works','this is strange') decode_doesnt
  6  FROM
  7    (SELECT * FROM table_1) a,
  8    (SELECT col1, NVL(col2,0) col2 FROM table_2) b
  9  WHERE a.col1 = b.col1(+)
 10  AND a.col2   = b.col2(+);

A      ACOL2 B      BCOL2 DECODE_DOESN
- ---------- - ---------- ------------ 
a          1 a          1 decode works
b          2              decode works                                                                                                                                                                  

SQL> @xall.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  373cus15943ut, child number 0
------------------------------------- 
SELECT a.col1 acol1,   a.col2 acol2,   b.col1 bcol1,   b.col2 bcol2 ,
DECODE(1,1,'decode works','this is strange') decode_doesnt FROM
(SELECT * FROM table_1) a,   (SELECT col1, NVL(col2,0) col2 FROM
table_2) b WHERE a.col1 = b.col1(+) AND a.col2   = b.col2(+)                                                                                                                                            

Plan hash value: 1294080186                                                                                                                                                                             

--------------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation          | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
|   0 | SELECT STATEMENT   |         |      1 |        |       |    17 (100)|          |      2 |00:00:00.01 |       7 |       |       |          |
|*  1 |  HASH JOIN OUTER   |         |      1 |      2 |    88 |    17   (6)| 00:00:01 |      2 |00:00:00.01 |       7 |  1156K|  1156K|  510K (0)|
|   2 |   TABLE ACCESS FULL| TABLE_1 |      1 |      2 |    32 |     8   (0)| 00:00:01 |      2 |00:00:00.01 |       3 |       |       |          |
|   3 |   TABLE ACCESS FULL| TABLE_2 |      1 |      2 |    56 |     8   (0)| 00:00:01 |      2 |00:00:00.01 |       4 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------- 

Query Block Name / Object Alias (identified by operation id):
------------------------------------------------------------- 

   1 - SEL$5428C7F1                                                                                                                                                                                     

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   2 - SEL$5428C7F1 / TABLE_1@SEL$2
   3 - SEL$5428C7F1 / TABLE_2@SEL$3                                                                                                                                                                     

Outline Data
------------- 

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5428C7F1")
      MERGE(@"SEL$2")
      MERGE(@"SEL$3")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      FULL(@"SEL$5428C7F1" "TABLE_1"@"SEL$2")
      FULL(@"SEL$5428C7F1" "TABLE_2"@"SEL$3")
      LEADING(@"SEL$5428C7F1" "TABLE_1"@"SEL$2" "TABLE_2"@"SEL$3")
      USE_HASH(@"SEL$5428C7F1" "TABLE_2"@"SEL$3")
      END_OUTLINE_DATA
  */                                                                                                                                                                                                    

Predicate Information (identified by operation id):
--------------------------------------------------- 

   1 - access("TABLE_1"."COL1"="COL1" AND "TABLE_1"."COL2"=CASE  WHEN (ROWID IS NOT NULL) THEN NVL("COL2",0) ELSE NULL END )                                                                            

Column Projection Information (identified by operation id):
----------------------------------------------------------- 
   1 - (#keys=2) "TABLE_1"."COL1"[CHARACTER,1], "COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22], ROWID[ROWID,10], "COL2"[NUMBER,22]
   2 - "TABLE_1"."COL1"[CHARACTER,1], "TABLE_1"."COL2"[NUMBER,22]
   3 - ROWID[ROWID,10], "COL1"[CHARACTER,1], "COL2"[NUMBER,22]                                                                                                                                          

Note
----- 
   - dynamic sampling used for this statement (level=2)

.

Query plans, Outline data and predicate information for both the plans are same but the column projection for query using decode with operation id 1 has additional column ROWID[ROWID,10]. But its not clear at this stage why Oracle needs ROWID from TABLE_2. Lets run 10053 trace to check query transformations.

10053

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TABLE_1"."COL1" "ACOL1","TABLE_1"."COL2" "ACOL2","TABLE_2"."COL1" "BCOL1",NVL("TABLE_2"."COL2",0) "BCOL2",'case breaks it' "CASE_BREAKS_IT" FROM "VDESAI"."TABLE_1" "TABLE_1","VDESAI"."TABLE_2" "TABLE_2" WHERE "TABLE_1"."COL1"="TABLE_2"."COL1"(+) AND "TABLE_1"."COL2"=CASE  WHEN ("TABLE_2".ROWID(+) IS NOT NULL) THEN NVL("TABLE_2"."COL2"(+),0) ELSE NULL END 

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TABLE_1"."COL1" "ACOL1","TABLE_1"."COL2" "ACOL2","TABLE_2"."COL1" "BCOL1",CASE  WHEN "TABLE_2".ROWID IS NOT NULL THEN NVL("TABLE_2"."COL2",0) ELSE NULL END  "BCOL2",'decode works' "DECODE_DOESNT" FROM "VDESAI"."TABLE_1" "TABLE_1","VDESAI"."TABLE_2" "TABLE_2" WHERE "TABLE_1"."COL1"="TABLE_2"."COL1"(+) AND "TABLE_1"."COL2"=CASE  WHEN ("TABLE_2".ROWID(+) IS NOT NULL) THEN NVL("TABLE_2"."COL2"(+),0) ELSE NULL END

.

From above its clear that Oracle optimizer is rewriting both queries little differently using query transformation. You cannot run this queries directly as there are some other variables applied to it internally during runtime. So running transformed query directly from sqlplus may not produce same result as the original query does.

To prevent query transformation, I tried to run query with case statement with NO_MERGE hint and it produced expected output. If you cannot change code set “_simple_view_merging” to false and you will get correct output.

NO_MERGE

VDESAI@csprod2 > select /*+ NO_MERGE(@"SEL$2") NO_MERGE(@"SEL$3") */ a.col1 acol1, a.col2 acol2, b.col1 bcol1, b.col2 bcol2
  2       ,case when 1 = 1 then 'case breaks it' end case_breaks_it
  3        from (select * from table_1) a,
  4             (select col1, nvl(col2,0) col2 from table_2) b
  5        where a.col1 = b.col1(+)
  6          and a.col2 = b.col2(+);

A      ACOL2 B      BCOL2 CASE_BREAKS_IT
- ---------- - ---------- --------------
a          1 a          1 case breaks it
b          2              case breaks it

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "A"."COL1" "ACOL1","A"."COL2" "ACOL2","B"."COL1" "BCOL1","B"."COL2" "BCOL2",'case breaks it' "CASE_BREAKS_IT" FROM  (SELECT /*+ NO_MERGE */ "TABLE_1"."
COL1" "COL1","TABLE_1"."COL2" "COL2" FROM "VDESAI"."TABLE_1" "TABLE_1") "A", (SELECT /*+ NO_MERGE */ "TABLE_2"."COL1" "COL1",NVL("TABLE_2"."COL2",0) "COL2" FR
OM "VDESAI"."TABLE_2" "TABLE_2") "B" WHERE "A"."COL1"="B"."COL1"(+) AND "A"."COL2"="B"."COL2"(+)

---------------------------------------+-----------------------------------+
| Id  | Operation            | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT     |         |       |       |    17 |           |
| 1   |  HASH JOIN OUTER     |         |     2 |    64 |    17 |  00:00:01 |
| 2   |   VIEW               |         |     2 |    32 |     8 |  00:00:01 |
| 3   |    TABLE ACCESS FULL | TABLE_1 |     2 |    32 |     8 |  00:00:01 |
| 4   |   VIEW               |         |     2 |    32 |     8 |  00:00:01 |
| 5   |    TABLE ACCESS FULL | TABLE_2 |     2 |    32 |     8 |  00:00:01 |
---------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("A"."COL1"="B"."COL1" AND "A"."COL2"="B"."COL2")

  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      OPT_PARAM('_unnest_subquery' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "A"@"SEL$1")
      NO_ACCESS(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      FULL(@"SEL$2" "TABLE_1"@"SEL$2")
      FULL(@"SEL$3" "TABLE_2"@"SEL$3")
    END_OUTLINE_DATA
  */

. 

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: