Re: BUG #10793: Empty result set instead of column does not exist error using CTE.

On Sat, Jun 28, 2014 at 3:41 AM, <kai(at)schwebke(dot)com> wrote:

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

Not a bug at all.

The problem here is that you are making a correlated subquery, and so "val"
on the subquery is referring to "t1.val" (from the outer query), not
"t1_cte.val" (from the inner query). So your code is more like:

(SELECT t1.val FROM t1_cte);

Rather than what you expected:

(SELECT t1_cte.val FROM t1_cte); -- would make the error you expect

I think it is a good practice to always use aliases and qualified column
names to avoid such cases.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres