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@schwebke.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=3D1)
> 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,
--=20
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br n=C3=ADvel F!
www.dextra.com.br/postgres