这是一个发生在生产环境中的真实案例，由于数据库中的数据一直在增长，我们写了一个关于主要业务数据的清理存储过程，第二天发现程序还没有跑完，查看log发现它在删其中的一个大表的所有记录，好在我对于大表的删除有条数和时间间隔限制，所以一晚上只删了一部分，但是真的把我吓了一跳，我首先立马停止了job，然后利用logminer读出已经被删除的数据，然后还原，接下来就是分析脚本看看是哪里出了问题。

原来是我有个delete的操作，where条件里有个子查询，但是子查询的栏位本身不存在，但是delete的表有这个栏位，所以就默认删除了表里的所有数据，而不是报栏位不存在的错误，这一点让我十分困惑，后来发邮件请教了崔大师，首先重现问题如下：

create table t1(a varchar(5));

insert into t1 values('cc');

create table t2(id varchar(10));

insert into t2 values('aa');

insert into t2 values('bb');

SQL> select id from t1;
select id from t1
       *
ERROR at line 1:
ORA-00904: "ID": invalid identifier

  SQL> select id from t2;
ID
  ----------
  aa
bb

我们可以看到t1表里没有栏位id ，而t2里有栏位id

接下来查询下面的语句，居然没有报错，而且返回了所有记录

SQL> select * from t2 where id in (select id from t1);

ID
----------
aa
bb

(no errors)!!! why

SQL> truncate table t1;

Table truncated.

SQL> select * from t2 where id in (select id from t1); ——然后把t1表里的记录清空，也没有报错，但是查询结果是没有记录

no rows selected

后来查看执行计划

SQL> select * from t2 where id in (select id from t1);

ID

--------------------

aa

bb

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_ID dad8vbwaytup8, child number 0

-------------------------------------

select * from t2 where id in (select id from t1)

Plan hash value: 549228466

----------------------------------------------------------------------------

----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 36 (100)| |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL | T2 | 2 | 14 | 18 (0)| 00:00:01 |

|* 3 | FILTER | | | | | |

| 4 | TABLE ACCESS FULL| T1 | 1 | | 18 (0)| 00:00:01 |

----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$1

2 - SEL$1 / T2@SEL$1

3 - SEL$2

4 - SEL$2 / T1@SEL$2

Outline Data

-------------

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

DB_VERSION('11.2.0.3')

ALL_ROWS

OUTLINE_LEAF(@"SEL$2")

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T2"@"SEL$1")

FULL(@"SEL$2" "T1"@"SEL$2")

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( IS NOT NULL)

3 - filter(:B1=:B2)

Column Projection Information (identified by operation id):

-----------------------------------------------------------

1 - "ID"[VARCHAR2,10]

2 - "ID"[VARCHAR2,10]

Note

-----

- dynamic sampling used for this statement (level=2)

56 rows selected.

上述执行计划中有如下两处内容：

(1)、 3 - filter(:B1=:B2)

(2)、 1 - "ID"[VARCHAR2,10]

2 - "ID"[VARCHAR2,10]

这说明 Oracle把子查询(select id from t1)中的关键字“id“当作了一个关联变量，这个变量其实就是表T2的ID列的值，也就是说这里的过滤条件是ID=ID，这意味着只要表t1中有数据，则上述SQL（select * from t2 where id in (select id from t1)）的查询结果一定是表T2中的全部数据，但如果表t1中没有数据了，则上述SQL就相当于是 select * from t2 where id in (null)，这时候当然就没有结果了。

你想让上述SQL报错ora-00904还是很容易的：

把这个SQL改一下就会报ora-00904：
select * from t2 where id in (select t1.id from t1)

崔大师给出的解释是：

where的条件使用了相同的列名，然后oracle就把（select id from t1）中的id关联到t2表的id，从t2表中查到的id都会赋给（select id from t1）中的id，这样只要t1表里有记录，（select id from t1）这个查询永远都是常量，相当于exist永远为真，但是oracle这样处理机制很不安全。

这样我感觉也算是oracle的一个bug，因为如果不小心栏位写错了，他不报错而是全部查询出来了，对于DML操作都是一种风险，我发生问题的环境是oracle9.2.0.8 ，当数据库升级到11.2.0.3，发表这篇博客时我特的重现了一下，貌似还是有这样的问题，大家有兴趣可以做个简单的测试。

ITPub博客

当子查询栏位不存在时，没有报错，反而会出现误操作

最新文章