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

If you do:
select a.* from dba_users a where a.username in (select b.username from dba_tables b)

you'll get an error, rather than a result you didn't expect.

I think, if you don't have the table aliases, it *has* to be allowed. If you think about it, the inner query *has* to be able to resolve columns in the outer table, otherwise writing correlated subqueries would be impossible, no?

And, given the query:
select * from dba_users where username in (select username from dba_tables)

and given the fact that the inner query can resolve columns from the outer table, the above query *has* to work. There's no way for the parser to be sure, in the general case, whether the query is semantically correct.

Hope that helps,

-Mark

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Strickland Sent: Tuesday, March 29, 2011 2:08 PM
To: Harel Safra
Cc: oracle-l_at_freelists.org
Subject: Re: A SQL bug/feature? Non-existent column in select of in-list subquery returns rows instead of ORA-

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<mailto: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<mailto: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