Re: A SQL bug/feature? Non-existent column in select of in-list subquery returns rows instead of ORA-

From: Harel Safra <harel.safra_at_gmail.com>
Date: Tue, 29 Mar 2011 20:28:31 +0200
Message-ID: <AANLkTikz2LVVSNr3Zv38wBps=M48UgS7Q4ujiND9zM=c_at_mail.gmail.com>

It follows the same scoping rules as other programming languages. If the variable isn't defined in the current scope it's searched for in encompassing scopes.

Harel Safra
Sent from my phone.
On Mar 29, 2011 8:07 PM, "Mark Strickland" <strickland.mark_at_gmail.com> wrote:
> Yeah, I understand that but it doesn't seem correct to me. The inner query
> queries dba_tables, not dba_users.
>
> Note that the same behavior exists in SQL Server.
>
>
>
> On Tue, Mar 29, 2011 at 10:41 AM, Harel Safra <harel.safra_at_gmail.com>
wrote:
>
>> Username in the inner query referenced dba_users since it can't find such
a
>> column in dba_tables.
>>
>> Harel Safra
>> Sent from my phone.
>> On Mar 29, 2011 6:50 PM, "Mark Strickland" <strickland.mark_at_gmail.com>
>> wrote:
>> > 11.2.0.1 and 11.2.0.2 on OEL.
>> >
>> > Try "select * from dba_users where username in (select username from
>> > dba_tables)". It returns all the rows from dba_users instead of
returning
>> > an error. Perhaps this is consistent with SQL 92 but I don't see how.
Did
>> > Dr. Codd really intend this? If it is expected behavior, I'm willing to
>> > except the shunning and ridicule (even from Mladen). One of our
>> developers
>> > discovered this yesterday with application tables and I confirmed it
with
>> an
>> > equivalent query of data dictionary tables and opened an SR. Can
someone
>> > confirm this in 10g?
>> >
>> > Mark Strickland
>> > Seattle, WA
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 29 2011 - 13:28:31 CDT