等于NULL的查询条件导致查询结果不正确
今天有人找我确认一个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。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-660632/ ，如需转载，请注明出处，否则将追究法律责任。
