Re: inconsistent results make me grumpy ( where is the 904? )

From: ddf <>
Date: Fri, 1 Jul 2011 12:55:31 -0700 (PDT)
Message-ID: <>

On Jun 30, 12:17 pm, John Hurley <> 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 and
> 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

Original text of this message