Re: BUG #4902: Subquery in VALUES referencing a CTE

The following bug has been logged online:

Bug reference: 4902
Logged by: Marko Tiikkaja
Email address: marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi
PostgreSQL version: 8.4.0
Operating system: Linux
Description: Subquery in VALUES referencing a CTE
Details:

While playing around with common table expressions, I found this:

=> with cte(foo) as ( values(0) ) values((select foo from cte));
ERROR: XX000: SubPlan found with no parent plan
LOCATION: ExecInitExpr, execQual.c:4343

In src/backend/executor/nodeValuesscan.c the comment near line 116 says that
subqueries inside VALUES should be initplans, but in this case we get a
SubPlan. Passing node instead of NULL to ExecInitExpr() below gives the
expected output.

"Marko Tiikkaja" <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> While playing around with common table expressions, I found this:

> => with cte(foo) as ( values(0) ) values((select foo from cte));
> ERROR: XX000: SubPlan found with no parent plan

Oh, interesting, thanks for the bug report!

> In src/backend/executor/nodeValuesscan.c the comment near line 116 says that
> subqueries inside VALUES should be initplans, but in this case we get a
> SubPlan. Passing node instead of NULL to ExecInitExpr() below gives the
> expected output.

I don't think that's a safe fix because of the reason cited in that same
comment: it's going to result in dangling pointers and probable crashes
anytime the ValuesScan node contains multiple rows or is executed more
than once. The right fix is to make sure that the subquery really is an
initplan, which takes a bit of fooling around in the planner:
http://archives.postgresql.org/pgsql-committers/2009-07/msg00041.php

regards, tom lane