Oracle的又一巨型bug

關於exist中條件為true時的執行計劃問題，同事發現的，還真沒留意到：
SQL> with tmp as(
  2  select 1 a from dual union all
  3  select 2 a from dual union all
  4  select 3 a from dual union all
  5  select 4 a from dual),
  6  notext as(
  7  select 2 a from dual union all
  8  select 3 a from dual)
  9  select * from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a))
10  /

         A
----------
         1
         2
         3
         4
         1
         2
         3
         4

8 rows selected
執行計劃（紅色部分明顯錯了）：
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |    24 |    13   (8)| 00:00:01 |
|   1 | MERGE JOIN CARTESIAN|      |     4 |    24 |    13   (8)| 00:00:01 |
|*  2 |   VIEW               |      |     2 |     6 |     4   (0)| 00:00:01 |
|   3 |    HASH UNIQUE       |      |     2 |       |     4  (50)| 00:00:01 |
|   4 |     UNION-ALL        |      |       |       |            |          |
|   5 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |   BUFFER SORT        |      |     4 |    12 |    13   (8)| 00:00:01 |
|   8 |    VIEW              |      |     4 |    12 |     8   (0)| 00:00:01 |
|   9 |     UNION-ALL        |      |       |       |            |          |
|  10 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  11 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|  13 |      FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("B"."A"="B"."A")

25 rows selected

正確的結果（加了紅色部分）：
with tmp as(
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual union all
select 4 a from dual),
notext as(
select 2 a from dual union all
select 3 a from dual)
select * from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a) and rownum <= 1e100)

執行計劃（透過rownum約束了謂詞推進，這次對了）：
-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     3 |    12   (0)| 00:00:01 |
|*  1 |  FILTER          |      |       |       |            |          |
|   2 |   VIEW           |      |     4 |    12 |     8   (0)| 00:00:01 |
|   3 |    UNION-ALL     |      |       |       |            |          |
|   4 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL    |      |     1 |       |     2   (0)| 00:00:01 |
|*  8 |   COUNT STOPKEY  |      |       |       |            |          |
|   9 |    VIEW          |      |     2 |     6 |     4   (0)| 00:00:01 |
|  10 |     UNION-ALL    |      |       |       |            |          |
|  11 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
|  12 |      FAST DUAL   |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM  ( (SELECT /*+ */ 2 "A"
              FROM "SYS"."DUAL" "DUAL") UNION ALL  (SELECT /*+ */ 3 "A" FROM
              "SYS"."DUAL" "DUAL")) "B" WHERE ROWNUM<=1e100))
   8 - filter(ROWNUM<=1e100)

bell6248給的另一個解決辦法：
with tmp as(
select 1 a from dual union all
select 2 a from dual union all
select 3 a from dual union all
select 4 a from dual),
notext as(
select 2 a from dual union all
select 3 a from dual)
select rownum from tmp a where exists(select 1 from notext b where b.a = decode(1,1,b.a,a.a))

需要說明的是，以證明在11g裡面該bug已經修復。10201bug太多了，什麼時候發個心把版本升了。