pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubquery abnormal behavior
6+ messages / 4 participants
[nested] [flat]
* subquery abnormal behavior
@ 2006-12-11 06:48 Shoaib Mir <shoaibmir@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Shoaib Mir @ 2006-12-11 06:48 UTC (permalink / raw)
To: pgsql-sql
I just noticed an abnormal behavior for the subquery:
create table myt1 (a numeric);
create table myt2 (b numeric);
select a from myt1 where a in (select a from myt2);
This should be giving an error that column 'a' does not exist in myt2 but it
runs with any error...
I had been trying it on 8.2!
Can someone please comment?
Thank you,
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subquery abnormal behavior
@ 2006-12-11 06:51 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-11 06:51 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: pgsql-sql
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
> I just noticed an abnormal behavior for the subquery:
>
> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in myt2 but
> it runs with any error...
>
> I had been trying it on 8.2!
Even in 8.1.5 it does not complain. (its not 8.2 specific at least)
Regds
mallah.
>
> Can someone please comment?
>
> Thank you,
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subquery abnormal behavior
@ 2006-12-11 06:57 Shoaib Mir <shoaibmir@gmail.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Shoaib Mir @ 2006-12-11 06:57 UTC (permalink / raw)
To: pgsql-sql
I just noticed the same behavior in Oracle and SQL Server as well :)
Regards,
---------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
>
> I just noticed an abnormal behavior for the subquery:
>
> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in myt2 but
> it runs with any error...
>
> I had been trying it on 8.2!
>
> Can someone please comment?
>
> Thank you,
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subquery abnormal behavior
@ 2006-12-11 07:03 Michael Glaesemann <grzm@seespotcode.net>
parent: Shoaib Mir <shoaibmir@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Michael Glaesemann @ 2006-12-11 07:03 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: pgsql-sql
On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:
> create table myt1 (a numeric);
> create table myt2 (b numeric);
>
> select a from myt1 where a in (select a from myt2);
>
> This should be giving an error that column 'a' does not exist in
> myt2 but it runs with any error...
The a in the IN clause is the same a in outer expression. This is in
effect:
select a from myt1 where a = a;
Now, if you were to say
select a from myt1 where a in (select myt2.a from myt2);
ERROR: column myt2.a does not exist
LINE 1: select a from myt1 where a in (select myt2.a from myt2);
And if you were to instead have
create table myt1 (a numeric);
CREATE TABLE
create table myt2 (b numeric);
CREATE TABLE
insert into myt1(a) values (1), (2);
INSERT 0 2
insert into myt2 (b) values (3), (4), (2);
INSERT 0 3
create table myt3 (a numeric);
CREATE TABLE
insert into myt3 (a) values (2), (3),(4);
INSERT 0 3
test=# select a from myt1 where a in (select a from myt3);
a
---
2
(1 row)
It looks like PostgreSQL treats it as a natural join like
select a from myt1 natural join myt3;
Hope this helps.
Michael Glaesemann
grzm seespotcode net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subquery abnormal behavior
@ 2006-12-11 07:24 Shoaib Mir <shoaibmir@gmail.com>
parent: Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 6+ messages in thread
From: Shoaib Mir @ 2006-12-11 07:24 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: pgsql-sql
Oh that explains a lot...
Thank you,
-------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 12/11/06, Michael Glaesemann <grzm@seespotcode.net> wrote:
>
>
> On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:
>
> > create table myt1 (a numeric);
> > create table myt2 (b numeric);
> >
> > select a from myt1 where a in (select a from myt2);
> >
> > This should be giving an error that column 'a' does not exist in
> > myt2 but it runs with any error...
>
> The a in the IN clause is the same a in outer expression. This is in
> effect:
>
> select a from myt1 where a = a;
>
> Now, if you were to say
>
> select a from myt1 where a in (select myt2.a from myt2);
> ERROR: column myt2.a does not exist
> LINE 1: select a from myt1 where a in (select myt2.a from myt2);
>
> And if you were to instead have
> create table myt1 (a numeric);
> CREATE TABLE
> create table myt2 (b numeric);
> CREATE TABLE
> insert into myt1(a) values (1), (2);
> INSERT 0 2
> insert into myt2 (b) values (3), (4), (2);
> INSERT 0 3
> create table myt3 (a numeric);
> CREATE TABLE
> insert into myt3 (a) values (2), (3),(4);
> INSERT 0 3
> test=# select a from myt1 where a in (select a from myt3);
> a
> ---
> 2
> (1 row)
>
> It looks like PostgreSQL treats it as a natural join like
>
> select a from myt1 natural join myt3;
>
> Hope this helps.
>
> Michael Glaesemann
> grzm seespotcode net
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: subquery abnormal behavior
@ 2006-12-11 15:42 Aaron Bono <postgresql@aranya.com>
parent: Shoaib Mir <shoaibmir@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Aaron Bono @ 2006-12-11 15:42 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; pgsql-sql
On 12/11/06, Shoaib Mir <shoaibmir@gmail.com> wrote:
>
> Oh that explains a lot...
>
> Thank you,
> -------------
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
>
>
> On 12/11/06, Michael Glaesemann <grzm@seespotcode.net> wrote:
> >
> >
> > On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:
> >
> > > create table myt1 (a numeric);
> > > create table myt2 (b numeric);
> > >
> > > select a from myt1 where a in (select a from myt2);
> > >
> > > This should be giving an error that column 'a' does not exist in
> > > myt2 but it runs with any error...
> >
> > The a in the IN clause is the same a in outer expression. This is in
> > effect:
> >
> > select a from myt1 where a = a;
> >
> > Now, if you were to say
> >
> > select a from myt1 where a in (select myt2.a from myt2);
> > ERROR: column myt2.a does not exist
> > LINE 1: select a from myt1 where a in (select myt2.a from myt2);
> >
> > And if you were to instead have
> > create table myt1 (a numeric);
> > CREATE TABLE
> > create table myt2 (b numeric);
> > CREATE TABLE
> > insert into myt1(a) values (1), (2);
> > INSERT 0 2
> > insert into myt2 (b) values (3), (4), (2);
> > INSERT 0 3
> > create table myt3 (a numeric);
> > CREATE TABLE
> > insert into myt3 (a) values (2), (3),(4);
> > INSERT 0 3
> > test=# select a from myt1 where a in (select a from myt3);
> > a
> > ---
> > 2
> > (1 row)
> >
> > It looks like PostgreSQL treats it as a natural join like
> >
> > select a from myt1 natural join myt3;
> >
> > Hope this helps.
> >
> > Michael Glaesemann
> > grzm seespotcode net
> >
> >
> >
>
If you want to know more about this, check into how Correlated Subqueries
work. I would never recommend using Correlated Subqueries but knowledge of
them and how/why they work helps you understand what is going on here much
better.
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2006-12-11 15:42 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-11 06:48 subquery abnormal behavior Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 06:51 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-11 06:57 ` Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 07:03 ` Michael Glaesemann <grzm@seespotcode.net>
2006-12-11 07:24 ` Shoaib Mir <shoaibmir@gmail.com>
2006-12-11 15:42 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox