[SQL] 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)
Re: [SQL] subquery abnormal behavior
On 12/11/06, Shoaib Mir [EMAIL PROTECTED] 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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] subquery abnormal behavior
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 [EMAIL PROTECTED] 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)
Re: [SQL] subquery abnormal behavior
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] subquery abnormal behavior
Oh that explains a lot... Thank you, - Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/11/06, Michael Glaesemann [EMAIL PROTECTED] 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
