今天有人找我確認一個bug，查詢包含等於NULL的條件，導致外連線的結果不正確。

之所以懷疑是BUG，是由於在10.2.0.1中存在這個問題，但是在10.2.0.3中，同樣的查詢沒有問題。

由於提交給我SQL十分複雜，下面做了一個簡單的例子：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE TABLE T (ID NUMBER);

表已建立。

SQL> INSERT INTO T
2 SELECT ROWNUM
3 FROM TAB;

已建立23行。

SQL> COMMIT;

提交完成。

SQL> CREATE TABLE T1 (ID NUMBER);

表已建立。

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID IS NULL) T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
23

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID = '') T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
0

可以看到，查詢T1的時候如果指定ID IS NULL，就可以得到正確的結果，而如果查詢時指定ID = ‘’，則會導致查詢結果錯誤。

看看Oracle的執行計劃：

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID IS NULL) T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
23

執行計劃
----------------------------------------------------------
Plan hash value: 720849874

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    26 |     6 (17)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |    26 |            |          |
|* 2 |   HASH JOIN OUTER   |      |    23 |   598 |     6 (17)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |    23 |   299 |     3   (0)| 00:00:01 |
|* 4 |    TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - access("T"."ID"="ID"(+))
4 - filter("ID"(+) IS NULL)

Note
-----
- dynamic sampling used for this statement

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID = '') T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
0

執行計劃
----------------------------------------------------------
Plan hash value: 1485723496

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    26 |     0   (0)|          |
|   1 | SORT AGGREGATE      |      |     1 |    26 |            |          |
|* 2 |   FILTER             |      |       |       |            |          |
|* 3 |    HASH JOIN OUTER   |      |    23 |   598 |     6 (17)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |    23 |   299 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - filter(NULL IS NOT NULL)
3 - access("T"."ID"="ID"(+))

Note
-----
- dynamic sampling used for this statement

顯然，Oracle在HASH JOIN OUTER上面新增了一個恆為假的條件，NULL IS NOT NULL，從而將整個查詢過濾。而這種方法對於存在外連線的情況是不正確的。

下面看看10.2.0.3的情況：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> DESC T
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NUMBER

SQL> DESC T1
名稱是否為空? 型別
----------------------------------------- -------- ----------------------------
ID NUMBER

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
0

SQL> INSERT INTO T VALUES (1);

已建立 1 行。

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID IS NULL) T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
1

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID = '') T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
1

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID = '') T1
4 WHERE T.ID = T1.ID(+);

COUNT(*)
----------
1

執行計劃
----------------------------------------------------------
Plan hash value: 1102683273

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    16 |     0   (0)|          |
|   1 | SORT AGGREGATE     |      |     1 |    16 |            |          |
|* 2 |   HASH JOIN OUTER   |      |     1 |    16 |     8 (13)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|* 4 |    TABLE ACCESS FULL| T1   |     1 |     3 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - access("T"."ID"="ID"(+))
4 - filter("ID"(+)=TO_NUMBER(NULL))

SQL> SELECT COUNT(*)
2 FROM T,
3 (SELECT ID FROM T1 WHERE ID = '') T1
4 WHERE T.ID = T1.ID;

COUNT(*)
----------
0

執行計劃
----------------------------------------------------------
Plan hash value: 3380581376

-----------------------------------------------------------------------------
| Id | Operation            | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    16 |     0   (0)|          |
|   1 | SORT AGGREGATE      |      |     1 |    16 |            |          |
|* 2 |   FILTER             |      |       |       |            |          |
|* 3 |    HASH JOIN         |      |     1 |    16 |     8 (13)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T1   |     1 |     3 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

2 - filter(NULL IS NOT NULL AND NULL IS NOT NULL)
3 - access("T"."ID"="ID")

在10.2.0.3中，利用FILTER直接過濾查詢的最佳化方式仍然存在，但是當存在外連線的時候，Oracle就不會選擇這種方法了。

在metalink中，這個錯誤對應的bug號是5089814，這個錯誤影響的版本是10.2.0.1和10.2.0.2。

等於NULL的查詢條件導致查詢結果不正確

相關文章