Thread: subquery abnormal behavior

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)

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

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