This test case is built from a real life DBA task.
SQL> create table t as select rownum id, object_name from dba_objects where rownum <=10;
Table created.
SQL> create table t2 as select rownum id1, object_name from dba_objects where rownum <=5;
Table created.
Our intention was to do the following SQL with a subqeury:
SQL> select * from t where id in ( select id1 from t2);
ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1
Instead, we executed the following SQL, which has a typo . However, it got executed without error:
SQL> select * from t where id in ( select id from t2);
ID OBJECT_NAME
---------- ------------------------------
1 TAB$
2 I_IND1
3 I_COBJ#
4 USER$
5 I_OBJ1
6 I_PROXY_ROLE_DATA$_2
7 C_FILE#_BLOCK#
8 C_OBJ#
9 BOOTSTRAP$
10 I_ICOL1
10 rows selected.
Isn't it better Oracle can throw error for this SQL?
Friday, October 09, 2009
Subscribe to:
Post Comments (Atom)
3 comments:
Denis - it is a correlated query, means the "id" of the second select still refers to the first table and the subquery is run for each row of the first table. If you want an error, just explicitly prefix both tables with an alias:
select * from t where t.id in ( select t2.id from t2);
Hope it helps.
@Jurek - Thanks for your explaination. Today someone mentioned similar thing in this Oracle-L thread:
http://www.freelists.org/post/oracle-l/Strange-Behavior-with-SQL-using-IN
Another guy got caught by this:
http://www.freelists.org/post/oracle-l/Weird-sql-response-could-not-explain
Post a Comment