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

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 1 Jul 2011 06:16:09 -0700 (PDT)
Message-ID: <7acabfed-70dd-47a5-aceb-646f96ad32e3_at_x38g2000pri.googlegroups.com>

On Jun 30, 3: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.

This is expected behavior because of the SQL scope rules; You failed to alias your sub-query as specified in the SQL manual. The outer table columns are visible within the sub-query,

HTH -- Mark D Powell -- Received on Fri Jul 01 2011 - 08:16:09 CDT