From shoaibmir@gmail.com Mon Dec 11 06:48:06 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id DB1659FA02C for ; Mon, 11 Dec 2006 02:48:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 00483-02 for ; Mon, 11 Dec 2006 02:48:06 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.234]) by postgresql.org (Postfix) with ESMTP id 5B5A99F9F08 for ; Mon, 11 Dec 2006 02:48:06 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so717671nzh for ; Sun, 10 Dec 2006 22:48:03 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=IM4qCiitgUtMVDvxDpnIEA27roks4v6ae1j0ctIqT0dgQzWH/DrEX82ewV/WrDzku8st9ZWqzAZX7DnZT9ieH+XWFkg44SqV27LKr33ceq6EWWm5yH0+mbIcalKhDQZ3ErFeafPTykqLThdD2lhzYaF7h2C6GSXZvIvvb4w9CB8= Received: by 10.65.250.11 with SMTP id c11mr10252011qbs.1165819683363; Sun, 10 Dec 2006 22:48:03 -0800 (PST) Received: by 10.65.206.18 with HTTP; Sun, 10 Dec 2006 22:48:03 -0800 (PST) Message-ID: Date: Mon, 11 Dec 2006 11:48:03 +0500 From: "Shoaib Mir" To: "Postgres SQL language list" Subject: subquery abnormal behavior MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_55579_20757900.1165819683328" X-Archive-Number: 200612/84 X-Sequence-Number: 27040 ------=_Part_55579_20757900.1165819683328 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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) ------=_Part_55579_20757900.1165819683328 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)
------=_Part_55579_20757900.1165819683328-- From mallah.rajesh@gmail.com Mon Dec 11 06:52:00 2006 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A92859FA02C for ; Mon, 11 Dec 2006 02:52:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 78861-05 for ; Mon, 11 Dec 2006 02:51:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.230]) by postgresql.org (Postfix) with ESMTP id 0DA5B9F9F08 for ; Mon, 11 Dec 2006 02:51:59 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so717962nzh for ; Sun, 10 Dec 2006 22:51:59 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=NpPA8PJUwkgFvhGKQ08OpRj7ub7TY0If8qVeyFDrwsEgvfPAhkgB/B5GUdFZubWIJfPkNFuzc7BAbxpgxY5Cp6ufguUHfdyKqofJoeLcIAjir1jJnEmv6UA1L6eKEFKP+kBeLcD93fdg7lBH7q9PVCGfgvEFIUTonGvTWHQUu3g= Received: by 10.35.75.1 with SMTP id c1mr11774316pyl.1165819919095; Sun, 10 Dec 2006 22:51:59 -0800 (PST) Received: by 10.35.80.10 with HTTP; Sun, 10 Dec 2006 22:51:59 -0800 (PST) Message-ID: Date: Mon, 11 Dec 2006 12:21:59 +0530 From: "Rajesh Kumar Mallah" To: "Shoaib Mir" Subject: Re: subquery abnormal behavior Cc: "Postgres SQL language list" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Archive-Number: 200612/85 X-Sequence-Number: 27041 On 12/11/06, Shoaib Mir 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) > From shoaibmir@gmail.com Mon Dec 11 06:57:15 2006 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 10C239FA02C for ; Mon, 11 Dec 2006 02:57:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 08498-04 for ; Mon, 11 Dec 2006 02:57:13 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.238]) by postgresql.org (Postfix) with ESMTP id 546C59F9F08 for ; Mon, 11 Dec 2006 02:57:14 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so718400nzh for ; Sun, 10 Dec 2006 22:57:11 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=o8533BkMeyiSeU7bxEe2LfeSmKVuenMuNEAZv06SnGjw3xNQmlpUDvG1YkahxP38acALil3Yh0Cd8cjrs3gcAH4zsq2WDZvhegtz12/3svNZ66jJI4gYDzd4rymekSEI1YVnQQQXRG4db9OLzS943evVDO/vQlG7ct1Vqzm1rEs= Received: by 10.65.15.17 with SMTP id s17mr4607714qbi.1165820231511; Sun, 10 Dec 2006 22:57:11 -0800 (PST) Received: by 10.65.206.18 with HTTP; Sun, 10 Dec 2006 22:57:11 -0800 (PST) Message-ID: Date: Mon, 11 Dec 2006 11:57:11 +0500 From: "Shoaib Mir" To: "Postgres SQL language list" Subject: Re: subquery abnormal behavior In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_55699_18609565.1165820231455" References: X-Archive-Number: 200612/86 X-Sequence-Number: 27042 ------=_Part_55699_18609565.1165820231455 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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) > ------=_Part_55699_18609565.1165820231455 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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)

------=_Part_55699_18609565.1165820231455-- From grzm@seespotcode.net Mon Dec 11 07:03:32 2006 Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id EEB529F9F46 for ; Mon, 11 Dec 2006 03:03:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 02757-07 for ; Mon, 11 Dec 2006 03:03:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from proton.planetargon.com (unknown [198.145.37.35]) by postgresql.org (Postfix) with ESMTP id 81B559F9C35 for ; Mon, 11 Dec 2006 03:03:32 -0400 (AST) Received: from [172.16.1.106] (unknown [61.197.227.146]) by proton.planetargon.com (Postfix) with ESMTP id 070248045EA; Sun, 10 Dec 2006 23:03:30 -0800 (PST) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: "Postgres SQL language list" Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: subquery abnormal behavior Date: Mon, 11 Dec 2006 16:03:27 +0900 To: "Shoaib Mir" X-Mailer: Apple Mail (2.752.2) X-Archive-Number: 200612/87 X-Sequence-Number: 27043 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 From shoaibmir@gmail.com Mon Dec 11 07:24:31 2006 Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id EE6D39F9F46 for ; Mon, 11 Dec 2006 03:24:31 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 84052-08 for ; Mon, 11 Dec 2006 03:24:29 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0102.google.com (nz-out-0506.google.com [64.233.162.235]) by postgresql.org (Postfix) with ESMTP id 402DE9F9C35 for ; Mon, 11 Dec 2006 03:24:31 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so720529nzh for ; Sun, 10 Dec 2006 23:24:30 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ugT7lyR6znn+O2ThhzxLrA98I0Cq1xKisB9uwdPJoRqpRpSprzuhJZKjdJ3EfBER36jXI/wWX9Gb+erzpIyKPYrVgPvDKy5wONcB8RT4xQWv8OpTBsYTfeH1N8jMVx8sGn6/xmcxLmUHARqb8MJ9gtXwl7KUse7GXwuNJOQS7u8= Received: by 10.64.199.2 with SMTP id w2mr10367514qbf.1165821869812; Sun, 10 Dec 2006 23:24:29 -0800 (PST) Received: by 10.65.206.18 with HTTP; Sun, 10 Dec 2006 23:24:29 -0800 (PST) Message-ID: Date: Mon, 11 Dec 2006 12:24:29 +0500 From: "Shoaib Mir" To: "Michael Glaesemann" Subject: Re: subquery abnormal behavior Cc: "Postgres SQL language list" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_55919_31453860.1165821869768" References: X-Archive-Number: 200612/88 X-Sequence-Number: 27044 ------=_Part_55919_31453860.1165821869768 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Oh that explains a lot... Thank you, ------------- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/11/06, Michael Glaesemann 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 > > > ------=_Part_55919_31453860.1165821869768 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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



------=_Part_55919_31453860.1165821869768-- From postgresql@aranya.com Mon Dec 11 15:49:33 2006 Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 747899F931E for ; Mon, 11 Dec 2006 11:49:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 70539-01-2 for ; Mon, 11 Dec 2006 11:49:26 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 4D5B29F9312 for ; Mon, 11 Dec 2006 11:49:31 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1399151ugf for ; Mon, 11 Dec 2006 07:49:29 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=BaWzUIgiSvpQb5ID5v92MjwY7lDpsf/DaPlLgi0QVUjOjfKw48ob9N6PLleSLveXNjWYdRQCLlMDBQ+3oZ9zDdGrvQZiGdW1IyJGXdB5YpEkmYjPxGJzoi5gtQYLzHjlt/9nIensg0lLAC6x5f17mtaZ9v9rn5CHetJlzMDEUP0= Received: by 10.78.139.1 with SMTP id m1mr1798364hud.1165851762861; Mon, 11 Dec 2006 07:42:42 -0800 (PST) Received: by 10.78.131.12 with HTTP; Mon, 11 Dec 2006 07:42:42 -0800 (PST) Message-ID: Date: Mon, 11 Dec 2006 09:42:42 -0600 From: "Aaron Bono" To: "Shoaib Mir" Subject: Re: subquery abnormal behavior Cc: "Michael Glaesemann" , "Postgres SQL language list" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_87523_10673750.1165851762749" References: X-Google-Sender-Auth: 8faf14db34b830b8 X-Archive-Number: 200612/89 X-Sequence-Number: 27045 ------=_Part_87523_10673750.1165851762749 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/11/06, Shoaib Mir wrote: > > Oh that explains a lot... > > Thank you, > ------------- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > > On 12/11/06, Michael Glaesemann 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 ================================================================== ------=_Part_87523_10673750.1165851762749 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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
================================================================== ------=_Part_87523_10673750.1165851762749--