From: Tom Lane <tgl@sss.pgh.pa.us>
To: otter117@yahoo.de
cc: pgsql-docs@postgresql.org
Subject: Re: undocumented feature or bug in subquery : psql (PostgreSQL) 9.4.6
 on SLES12 SP1
In-reply-to: <20160825162900.15675.78433@wrigleys.postgresql.org>
References: <20160825162900.15675.78433@wrigleys.postgresql.org>
Comments: In-reply-to otter117@yahoo.de
	message dated "Thu, 25 Aug 2016 16:29:00 -0000"
Date: Fri, 26 Aug 2016 09:07:14 -0400
Message-ID: <26546.1472216834@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

otter117@yahoo.de writes:
> 2 tables a and b
>                Table &quot;public.a&quot;
>      Column      |           Type           |   Modifiers
> -----------------+--------------------------+---------------
>  foo             | integer                  | not null
>  bar             | integer                  | not null

>                Table &quot;public.b&quot;
>      Column      |           Type           |   Modifiers
> -----------------+--------------------------+---------------
>  bar             | integer                  | not null

> No error when executing this statement even though table b does not contain
> column foo
> SELECT foo,bar FROM a WHERE foo=1 AND bar NOT IN (SELECT bar FROM b WHERE
> bar > 2 AND foo=2)

No, but table a does, and that's a legal outer reference per the SQL
standard.  This is neither a bug nor undocumented.

> Do I need to qualify all columns in the subquery or is this a bug is psql
> 9.4.6?

It's often wise to qualify column references in multi-table queries
(subqueries or not) to ensure they are referencing what you think
they're referencing.

			regards, tom lane


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs