Alert: Remember to specify the table name alias for column of the subquery (不存在列名)

前几日同事那遇到的灾难性的SQL, 一个简单的子查询，不知是一开始就写错了子查询中的列名，还是子查询的表后期有改列名，导致主查询表所有数据被更改，在未指定列所属表名时，列的查询顺序先检查子查询再检查主查询，这是oracle的预期行为，并不会报列不存在，只是在后期书写时子查询记的增加列上的所属别名。

-DEMO

SQL> @st anbob.tp
        ID NAME
---------- ----------
         1 a
         2 a
         3 a
         4 a
         5 a

SQL> @st anbob.tc
       CID        PID NAME
---------- ---------- ----------
         1          5 a

SQL> @desc anbob.tc
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      CID                                      NUMBER(38)
    2      PID                                      NUMBER(38)
    3      NAME                                     VARCHAR2(10)

SQL> select * from anbob.tp where id in (select id from anbob.tc);

        ID NAME
---------- ----------
         1 a
         2 a
         3 a
         4 a
         5 a

Note:
TC表中并不存在ID列，但是也未报错，而是直接返回了主查询的所有数据。

# enable opertimizer query transformation trace

SQL> oradebug setmypid
Statement processed.
SQL> @53on
alter session set events '10053 trace name context forever, level 1';
alter session set "_optimizer_trace"=all;

SQL> select * from anbob.tp where id in (select id from anbob.tc);
        ID NAME
---------- ----------
         1 a
         2 a
         3 a
         4 a
         5 a

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

# trace file

=====================================
SPD: BEGIN context at statement level
=====================================
Stmt: ******* UNPARSED QUERY IS *******
SELECT "TP"."ID" "ID","TP"."NAME" "NAME" 
   FROM "ANBOB"."TP" "TP" 
   WHERE "TP"."ID"=ANY (SELECT "TP"."ID" "ID" FROM "ANBOB"."TC" "TC")


*************************
Common Subexpression elimination (CSE)
*************************
CSE:     CSE not performed on query block SEL$2 (#2).
CSE:     CSE not performed on query block SEL$1 (#1).
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "TP"."ID" "ID","TP"."NAME" "NAME" 
FROM "ANBOB"."TP" "TP" 
WHERE  EXISTS (SELECT 0 FROM "ANBOB"."TC" "TC" WHERE "TP"."ID"="TP"."ID")
*************************

Note
在CBO查询转换后因为子查询不存在ID列，优化认为是主查询的ID列，而条件成了”TP”.”ID”=”TP”.”ID”。 Always Be Ture!!!

SQL> delete anbob.tp where id in (select id from anbob.tc);

5 rows deleted.

SQL> @st anbob.tp
no rows selected

--全部被删除

SQL> rollback;

SQL> alter table anbob.tc rename column cid to id;
Table altered.

SQL> select * from anbob.tp where id in (select id from anbob.tc);

        ID NAME
---------- ----------
         1 a

在MOS Doc ID 124014.1 记录这是一种预期行为，不是oracle的BUG, 测试环境19c.

转自https://www.anbob.com/archives/5798.html