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

       这是一个发生在生产环境中的真实案例，由于数据库中的数据一直在增长，我们写了一个关于主要业务数据的清理存储过程，第二天发现程序还没有跑完，查看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                                                      

                                                                                

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |    

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

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

转载于:http://blog.itpub.net/21560888/viewspace-1717153/