Friday, October 09, 2009

A SQL with a hidden error

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?

3 comments:

Jurek said...

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.

yds said...

@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

yds said...

Another guy got caught by this:

http://www.freelists.org/post/oracle-l/Weird-sql-response-could-not-explain