Right. Which is why you should ALWAYS use table aliases. 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mark Strickland Sent: Tuesday, March 29, 2011 2:08 PM To: Harel Safra Cc: oracle-l@xxxxxxxxxxxxx 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@xxxxxxxxx<mailto:harel.safra@xxxxxxxxx>> 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@xxxxxxxxx<mailto:strickland.mark@xxxxxxxxx>> 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