Обсуждение: BUG #10793: Empty result set instead of column does not exist error using CTE.

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.

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