Thread: 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.

* 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

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

* 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=3D# select * from test_table where foo in (select foo from
> > test_table2);
> >  foo | bar
> > -----+-----
> >  one |   1
> >  two |   2
>=20
> That'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 subje=
ct, 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 =3D f=
dt.c1 + 10)

    Qualifying c1 as fdt.c1 is only necessary if c1 is also the name of a=20
    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 nois=
e.

--=20
Christian

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