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--