Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id EE6D39F9F46
	for <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql-postgresql.org@postgresql.org>;
	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 <pgsql-sql@postgresql.org>; Mon, 11 Dec 2006 03:24:31 -0400 (AST)
Received: by nz-out-0102.google.com with SMTP id i11so720529nzh
	for <pgsql-sql@postgresql.org>; 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: <bf54be870612102324i50635183s8dab2f3af55f5cfc@mail.gmail.com>
Date: Mon, 11 Dec 2006 12:24:29 +0500
From: "Shoaib Mir" <shoaibmir@gmail.com>
To: "Michael Glaesemann" <grzm@seespotcode.net>
Subject: Re: subquery abnormal behavior
Cc: "Postgres SQL language list" <pgsql-sql@postgresql.org>
In-Reply-To: <EE83C543-2CCD-41CD-8AEA-BD337673FF7B@seespotcode.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_55919_31453860.1165821869768"
References: <bf54be870612102248j33f63ca1gd89e76d536a0262c@mail.gmail.com>
	<EE83C543-2CCD-41CD-8AEA-BD337673FF7B@seespotcode.net>
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 <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
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Oh that explains a lot...<br><br>Thank you,<br>-------------<br>Shoaib Mir<br>EnterpriseDB (<a href="http://www.enterprisedb.com">www.enterprisedb.com</a>)<br><br><br><div><span class="gmail_quote">On 12/11/06, <b class="gmail_sendername">
Michael Glaesemann</b> &lt;<a href="mailto:grzm@seespotcode.net">grzm@seespotcode.net</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>On Dec 11, 2006, at 15:48 , Shoaib Mir wrote:<br><br>&gt; create table myt1 (a numeric);<br>&gt; create table myt2 (b numeric);<br>&gt;<br>&gt; select a from myt1 where a in (select a from myt2);<br>&gt;<br>&gt; This should be giving an error that column 'a' does not exist in
<br>&gt; myt2 but it runs with any error...<br><br>The a in the IN clause is the same a in outer expression. This is in<br>effect:<br><br>select a from myt1 where a = a;<br><br>Now, if you were to say<br><br>select a from myt1 where a in (select 
myt2.a from myt2);<br>ERROR:&nbsp;&nbsp;column myt2.a does not exist<br>LINE 1: select a from myt1 where a in (select myt2.a from myt2);<br><br>And if you were to instead have<br>create table myt1 (a numeric);<br>CREATE TABLE<br>create table myt2 (b numeric);
<br>CREATE TABLE<br>insert into myt1(a) values (1), (2);<br>INSERT 0 2<br>insert into myt2 (b) values (3), (4), (2);<br>INSERT 0 3<br>create table myt3 (a numeric);<br>CREATE TABLE<br>insert into myt3 (a) values (2), (3),(4);
<br>INSERT 0 3<br>test=# select a from myt1 where a in (select a from myt3);<br>a<br>---<br>2<br>(1 row)<br><br>It looks like PostgreSQL treats it as a natural join like<br><br>select a from myt1 natural join myt3;<br><br>
Hope this helps.<br><br>Michael Glaesemann<br>grzm seespotcode net<br><br><br></blockquote></div><br>

------=_Part_55919_31453860.1165821869768--