Thread: undocumented feature or bug in subquery : psql (PostgreSQL) 9.4.6 on SLES12 SP1
undocumented feature or bug in subquery : psql (PostgreSQL) 9.4.6 on SLES12 SP1
From
otter117@yahoo.de
Date:
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/9.4/static/functions-subquery.html Description: psql (PostgreSQL) 9.4.6 on SLES12 SP1 2 tables a and b Table "public.a" Column | Type | Modifiers -----------------+--------------------------+--------------- foo | integer | not null bar | integer | not null Table "public.b" 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) Get an error on this one ERROR: column b.foo does not exist SELECT foo,bar FROM a WHERE foo=1 AND bar NOT IN (SELECT bar FROM b WHERE b.bar > 2 AND b.foo=2) It works if I remove foo from the subquery. SELECT foo,bar FROM a WHERE foo=1 AND bar NOT IN (SELECT bar FROM b WHERE b.bar > 2) Do I need to qualify all columns in the subquery or is this a bug is psql 9.4.6?
Re: undocumented feature or bug in subquery : psql (PostgreSQL) 9.4.6 on SLES12 SP1
From
Tom Lane
Date:
otter117@yahoo.de writes: > 2 tables a and b > Table "public.a" > Column | Type | Modifiers > -----------------+--------------------------+--------------- > foo | integer | not null > bar | integer | not null > Table "public.b" > 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