PostgreSQL subquery with syntax error gives a valid result

Question

What is happening here?

I got two tables, test1 and test2:

create table test1 (id1 int4 primary key);
create table test2 (id2 int4 primary key);

As expected, this query:

select id1 from test2;

produces a syntax error:

ERROR:  column "id1" does not exist
LINE 1: select id1 from test2;

However, when I try to execute this query:

select * from test1 where id1 in (select id1 from test2);

PostgreSQL doesn't complain, executes the query and gives me:

 id1
-----
(0 rows)

Is there any logic in this? Or should I file a bug report?

Aside: I'd use the the generally superior WHERE EXISTS () instead of WHERE col IN (). — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 16, 2013 at 15:44

Ihor Romanchenko · Accepted Answer · 2013-10-16 15:12:56Z

4

Columns from outer select are visible in sub-select.

Your query is equivalent to:

select * 
from test1 
where test1.id1 in (select test1.id1 from test2);

answered Oct 16, 2013 at 15:12

Ihor Romanchenko

29k9 gold badges55 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ok, so if table test1 contains a single value 42, the subquery is interpreted as "select 42 from test2", which is valid sql?

Tnx :) Just got a real life example of this behavior and it confused the hell out of me :)