等于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博客 ” ，链接：http://blog.itpub.net/4227/viewspace-660632/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-660632/