记一次 诡异的 Oracle 查询转换

说明： 今天技术群里有人问了一个查询语句的问题，这个语句看上去执行肯定会报错，因为查询的列名是不存在的，但是实际执行并没有报错，当然返回的结果也是错误的。使用 10053 看了下 SQL 执行过程中进行了比较诡异的改写，改变了 SQL 语义，先记录下来。

环境：

DB:Oracle 11.2.3.0

OS:Redhat 7.5

问题：

已知执行如下SQL 会报错 ORA-00904 ，报错原因是因为 dba_objects 表没有 table_name 字段。

SQL> select table_name from dba_objects;

那么如下 SQL 会报错吗？

把上面报错的语句作为子查询。

select count (*) from dba_tables where TABLE_NAME in ( select TABLE_NAME from dba_objects );

结果是没有报错， 可以返回数据：

select count (*) from dba_tables where TABLE_NAME in ( select TABLE_NAME from dba_objects );

问题分析

显然是优化器对原 SQL 进行了改写，通过 10053 可以查看到改写后的 SQL 语句。

SELECT COUNT(*) "COUNT(*)"
  FROM "SYS"."USER$"  "U",
       "SYS"."TS$"    "TS",
       "SYS"."SEG$"   "S",
       "SYS"."OBJ$"   "CO",
       "SYS"."TAB$"   "T",
       "SYS"."OBJ$"   "O",
       "SYS"."OBJ$"   "CX",
       "SYS"."USER$"  "CU",
       SYS."X$KSPPCV" "KSPPCV",
       SYS."X$KSPPI"  "KSPPI"
 WHERE EXISTS ......;

由于dba_tables 和 dba_objects 属于 sys 用户下的视图，引用了很多底层表， 10053 查看转换后的 SQL 比较复杂，很难马上理清各个表关联关系。

可以先排除复杂视图的干扰，将 dba_tables和dba_objects数据插入到两张堆表ttt1和ttt2中，在通过10053查看ttt1和ttt2查询改写后的SQL，结果一目了然。

过程如下：

创建ttt1和ttt2表

create table ttt1 as select * from dba_tables ;

create table ttt2 as select * from dba_objects ;

同理ttt2表也没有table_name列

select TABLE_NAME from ttt2 ;

查询同样没有报错

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

同样加到子查询里可以正常执行

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

COUNT (*)

----------

2884

理论上这个SQL 会被优化器改写，猜想可能会进行子查询展开，例如改写成如下 SQL ：

SELECT COUNT(*) "COUNT(*)"
  FROM "CJC"."TTT2" "TTT2", "CJC"."TTT1" "TTT1"
 WHERE "TTT1"."TABLE_NAME" = "TTT2"."TABLE_NAME";

查看执行计划，优化器并没有执行子查询展开 ( Subquery Unnesting ) ，而是将 in 条件改写成 exists

SQL> set autotrace on

SQL> select count(*) from ttt1 where TABLE_NAME in (select TABLE_NAME from ttt2);

生成10053trace 日志

SQL > alter session set tracefile_identifier = '10053C' ;

SQL > alter session set events '10053 trace name context forever ,level 1' ;

SQL > select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

COUNT (*)

----------

2884

SQL > alter session set events '10053 trace name context off' ;

查看 10053trace 日志

[oracle@cjcos02 trace]$ pwd

/u01/app/oracle/diag/rdbms/cjcdb01/cjcdb01/trace

[oracle@cjcos02 trace]$ vim cjcdb01_ora_13682_10053C.trc

搜索 关键字：Final query after transformations

---原SQL

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

---查询转换后的SQL

SELECT COUNT(*) "COUNT(*)"
  FROM "CJC"."TTT1" "TTT1"
 WHERE EXISTS (SELECT 0
          FROM "CJC"."TTT2" "TTT2"
         WHERE "TTT1"."TABLE_NAME" = "TTT1"."TABLE_NAME");

exists 表示子查询语句返回结果不为空， where 条件成立，就会执行主sql 语句，显然 "TTT1"."TABLE_NAME" = "TTT1"."TABLE_NAME" 条件无异于where 1=1 ，最终导致对tt1 表进行全表统计，即 select count(*) from ttt1;

显然是不合理的，本例是一个查询语句，得出了错误的数据，试想下如果是 delete 语句，岂不是误删除了所有数据？

为什么转换后的谓词条件变成了： "TTT1"."TABLE_NAME" = "TTT1"."TABLE_NAME" ？

难道不应该是 "TTT1"."TABLE_NAME" = "TTT 2 "."TABLE_NAME" ，然后因为 TTT 2 表没有 TABLE_NAME 列而报错吗？

如果条件是"TTT1"."TABLE_NAME" = "TTT2"."TABLE_NAME" 肯定会报错：

SELECT COUNT (*) "COUNT(*)"

FROM "CJC"."TTT1" "TTT1"

WHERE EXISTS ( SELECT 0

FROM "CJC"."TTT2" "TTT2"

WHERE "TTT1"."TABLE_NAME" = "TTT2"."TABLE_NAME" );

如何避免这个问题呢？

可以在子查询列上加上表名，这样就不会和外层表的列搞混了，例如：

select count (*) from ttt1 where TABLE_NAME in ( select ttt2.TABLE_NAME from ttt2 );

这个问题究竟算不算 BUG 呢？在 MOS 上可以查询很多关于子查询的 BUG ，但是没有找到完全匹配的，先记录这些，后期有发现在更新吧。

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

记一次诡异的Oracle查询转换