Re: inconsistent results make me grumpy ( where is the 904? )
Date: Fri, 1 Jul 2011 12:55:31 -0700 (PDT)
Message-ID: <32112280-b80b-4162-81ce-4cf969d17863_at_c41g2000yqm.googlegroups.com>
On Jun 30, 12:17 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> A repost here from my grumpy old dba blog:
>
> 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.
It is not a bug; Oracle is taking table_name from dba_tables and trying to select that from dba_users since you didn't qualify the column name. If you try this:
SQL> select * from dba_tables where table_name in 2 ( select u.table_name from dba_users u);
( select u.table_name from dba_users u)
*
ERROR at line 2:
ORA-00904: "U"."TABLE_NAME": invalid identifier
SQL> it fails, as expected as table_name is now associated with dba_users, not dba_tables as it was before. Since table_name was uniquely identified in the query construct (it appears only in dba_tables) Oracle didn't complain; it's as though you wrote
select * from dba_tables where table_name in ( select 'DBA_USERS' from dba_users);
except that Oracle substituted the current table_name into the subquery for each record it returned, treating it as though it were a constant.
David Fitzjarrell Received on Fri Jul 01 2011 - 14:55:31 CDT