BUG #10793: Empty result set instead of column does not exist error using CTE. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      10793
Logged by:          Kai Schwebke
Email address:      kai@schwebke.com
PostgreSQL version: 9.3.4
Operating system:   Linux
Description:

To reproduce the issue create a table:

CREATE TABLE t1 (id INTEGER, val INTEGER);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 VALUES (2, 2);


This query returns an empty result set:

WITH t1_cte AS
   (SELECT id FROM t1 WHERE val=1)
SELECT id FROM t1
   WHERE id NOT IN
      (SELECT val FROM t1_cte);

-->

 id
----
(0 rows)


Instead the query should be rejected with
'ERROR:  column "val" does not exist',
because val is not in the CTE t1_cte.

Note that this query does return a non-empty result set:
WITH t1_cte AS
   (SELECT id, val FROM t1 WHERE val=1)
SELECT id FROM t1
   WHERE id NOT IN
      (SELECT val FROM t1_cte);

-->

 id
----
  2
(1 row)


So the issue is not just that the columns which are not selected
in the CTE are still present and just the error message is missing.
Instead the error message is missing and a wrong result set is returned.