Re: A SQL bug/feature? Non-existent column in select of in-list subquery returns rows instead of ORA-
Date: Wed, 30 Mar 2011 07:33:51 +0200
Message-id: <4D92C0BF.7080108_at_inter.net.il>
Since we are dealing with this kind of queries I would like to add that a where clause like:
where field in (select another_field from another_table where something) works also in the query parameter of exp. This allows you to export rows from a table based on values in another table.
I used it a few days ago to copy a referential integrity subset from production to development, since they are on different sub nets and there is no direct connection between them.
Yechiel Adar
Israel
On 29/03/2011 20:28, Kenneth Naim wrote:
>
> 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_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
>
> ------------------------------------------------------------------------
>
>
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11
>
> ------------------------------------------------------------------------
>
>
> Checked by AVG - www.avg.com <http://www.avg.com>
> Version: 10.0.1204 / Virus Database: 1498/3537 - Release Date: 03/29/11
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 30 2011 - 00:33:51 CDT