BUG #13336: Unexpected result from invalid query
The following bug has been logged on the website:
Bug reference: 13336
Logged by: Christian Ullrich
Email address: chris@chrullrich.net
PostgreSQL version: 9.4.2
Operating system: Windows 8.1
Description:
An invalid column name in a subquery will be silently resolved against the
main query's table:
postgres=# create table test_table (foo varchar(10), bar integer);
CREATE TABLE
postgres=# insert into test_table values ('one', 1);
INSERT 0 1
postgres=# insert into test_table values ('two', 2);
INSERT 0 1
postgres=# create table test_table2 (baz varchar(10));
CREATE TABLE
postgres=# insert into test_table2 values ('gromp');
INSERT 0 1
-- Note: No column "foo" in test_table2
postgres=# select * from test_table where foo in (select foo from
test_table2);
foo | bar
-----+-----
one | 1
two | 2
I actually noticed this when querying "select * from
pg_available_extension_versions where name in (select name from
pg_extension)", which returns the whole contents of the
pg_available_extension_versions view although I got the column name in
pg_extension wrong.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
* I wrote:
PostgreSQL version: 9.4.2
An invalid column name in a subquery will be silently resolved against the
main query's table:
This behavior exists in 9.4.1 as well.
--
Christian Ullrich
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Hi,
On 2015-05-23 00:05:27 +0000, chris@chrullrich.net wrote:
-- Note: No column "foo" in test_table2
postgres=# select * from test_table where foo in (select foo from
test_table2);
foo | bar
-----+-----
one | 1
two | 2
That's not a bug. In a good number of subqueries you need access fields
from the surrounding query.
Greetings,
Andres Freund
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
* From: Andres Freund [mailto:andres@anarazel.de]
On 2015-05-23 00:05:27 +0000, chris@chrullrich.net wrote:
-- Note: No column "foo" in test_table2
postgres=# select * from test_table where foo in (select foo from
test_table2);
foo | bar
-----+-----
one | 1
two | 2That's not a bug. In a good number of subqueries you need access fields
from the surrounding query.
Hm. That makes some sense, I guess. But is that true even if the reference in the subquery is not explicitly qualified? "SELECT foo FROM test_table2" looks to me like it refers to that table pretty unequivocally.
On the other hand I can find one sentence in the documentation on the subject, and it appears to support the conclusion that this is not a bug:
http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html:
Example: SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
Qualifying c1 as fdt.c1 is only necessary if c1 is also the name of a
column in the derived input table of the subquery.
That can be read as confirming that resolving the unqualified name against the outer query is intended.
Still seems weird to me. But if that' the way it is, I'm sorry for the noise.
--
Christian
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Christian Ullrich <chris@chrullrich.net> writes:
Hm. That makes some sense, I guess. But is that true even if the reference in the subquery is not explicitly qualified? "SELECT foo FROM test_table2" looks to me like it refers to that table pretty unequivocally.
This behavior is required by the SQL standard ...
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs