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

"John Hurley" <hurleyjohnb_at_yahoo.com> wrote in message news:1ece6ae1-9bb6-48a3-a2ed-e2adb42cc345_at_g12g2000yqd.googlegroups.com...
>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's expected behaviour. Think correlated subqueries.

If a column referenced inside a subquery does not belong to any of the tables in the subquery then Oracle assumes that it is a correlating column that comes from the next layer out.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com