RE: A SQL bug/feature? Non-existent column in select of in-list subquery returns rows instead of ORA-
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <strickland.mark@xxxxxxxxx>, "'Harel Safra'" <harel.safra@xxxxxxxxx>
- Date: Tue, 29 Mar 2011 14:28:46 -0400
This has to do with correlated queries, where the inner query can access the
outer query. I've never liked it as it seems out of scope, but I've learned
to deal with it and on very rare occasions have need them. These types of
queries were more popular prior to 8i where a lot of the sql restriction on
inline views were lifted. As was previously mentioned using table aliases is
a good/best practice prevents this bug from happening.
Ken
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> 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>
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
_____
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11
_____
Checked by AVG - www.avg.com
Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11
Other related posts:
