Thursday, June 30, 2011

inconsistent/unpredictable results make me grumpy

Here is a strange one. Why does one query get an ORA-00904 but the same query when used as part of an IN apparently get ignored ( or not noticed as an error )?

This query returns an error:

select table_name from dba_users;
Error at line 1
ORA-00904: "TABLE_NAME": invalid identifier


This query executes ( not very well but ... ):
select * from dba_tables where table_name in
( select table_name from dba_users )
;

This reproduces in 11.1.0.7.6 and 10.2.0.4.

Is this a well known bug already or ( for some reason ) expected behavior?

I am just starting to research it now ... unfortunately a developer has some bad code running in production with this type of code.

2 comments:

  1. The simple answer is query transformation - the query submitted by the developer is not the query that is actually executed by Oracle Database. You can see the transformation with the help of a 10053 trace:
    alter session set tracefile_identifier = 'Find Me';
    alter session set events '10053 trace name context forever, level 1';

    select TABLE_NAME from dba_tables where table_name in ( select TABLE_NAME from dba_users );

    alter session set events '10053 trace name context off';

    On 11.2.0.2 you will probably end up with something like this showing for the final query after transformation:
    (long query snipped due to length)
    If you look closely at the above, you will see that the IN list subquery was transformed like this, which shows that the TABLE_NAME column was replaced by the number 0 and the IN subquery was transformed into an EXISTS subquery:
    EXISTS (SELECT 0 FROM "SYS"."USER$" "U","SYS"."RESOURCE_GROUP_MAPPING$" "CGM","SYS"."TS$" "DTS","SYS"."TS$" "TTS","SYS"."PROFNAME$" "P","SYS"."USER_ASTATUS_MAP" "M","SYS"."PROFILE$" "PR","SYS"."PROFILE$" "DP")

    ReplyDelete
  2. Expected behaviour

    select * from dba_tables where table_name in
    ( select table_name from dba_users );

    is equivalent to

    select * from dba_tables t where t.table_name in ( select t.table_name from dba_users u );

    while not a useful query, there's nothing that is syntactically invalid about it. A subquery can include columns from a table in the higher level query, just like :


    select * from dba_tables t where t.table_name in ( select t.table_name from dba_users u
    where t.owner = u.username);

    ReplyDelete