Horton

Thread

Commits

  1. Repair failure with SubPlans in multi-row VALUES lists.

  2. Arrange for ValuesScan to keep per-sublist expression eval state in a

  1. BUG #14924: Subquery in VALUES inside recursive CTE

    christianmduta@gmail.com — 2017-11-24T12:08:36Z

    The following bug has been logged on the website:

Bug reference:      14924
Logged by:          Christian Duta
Email address:      christianmduta@gmail.com
PostgreSQL version: 10.1
Operating system:   macOS High Sierra 10.13.1 
Description:        

When working with recursive CTEs, I had the following happen:

Evaluating the following

-- List numbers from 1 to 10.
WITH RECURSIVE
steps(i) AS (
  SELECT 1
    UNION
  SELECT val.x
  FROM steps AS s,
  LATERAL (
    VALUES
    ((SELECT s.i + 1)), 
    (s.i + 1)
  ) AS val(x)
  WHERE s.i < 10
)
SELECT *
FROM steps;

results in

ERROR:  XX000: SubPlan found with no parent plan
LOCATION:  ExecInitExprRec, execExpr.c:1013

  2. Re: BUG #14924: Subquery in VALUES inside recursive CTE

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-24T17:06:26Z

    christianmduta@gmail.com writes:
> When working with recursive CTEs, I had the following happen:

Thanks for the report!  Seems not to be specific to CTEs:

regression=# SELECT val.x
  FROM generate_series(1,10) AS s(i),
  LATERAL (
    VALUES
    ((SELECT s.i + 1)), 
    (s.i + 1)
  ) AS val(x)
  WHERE s.i < 10;
ERROR:  SubPlan found with no parent plan

However, simplifying further, this works:

regression=# SELECT val.x
  FROM generate_series(1,10) AS s(i),
  LATERAL (
    VALUES
    ((SELECT 1 + 1)), 
    (s.i + 1)
  ) AS val(x)
  WHERE s.i < 10;

so it looks like it's the LATERAL reference inside the sub-select
that is needed to trigger the bug.

Same results back to 9.3 :-(

			regards, tom lane

  3. Re: BUG #14924: Subquery in VALUES inside recursive CTE

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-11-25T02:48:54Z

    I wrote:
> christianmduta@gmail.com writes:
>> When working with recursive CTEs, I had the following happen:

> Thanks for the report!  Seems not to be specific to CTEs:

I traced this problem to this old hack in nodeValuesscan.c:

        /*
         * Pass NULL, not my plan node, because we don't want anything in this
         * transient state linking into permanent state.  The only possibility
         * is a SubPlan, and there shouldn't be any (any subselects in the
         * VALUES list should be InitPlans).
         */
        exprstatelist = ExecInitExprList(exprlist, NULL);

That was okay when written (in commit 0dfb595d7), but with the
introduction of LATERAL, it's possible for a VALUES list to contain
a sub-select that converts to a SubPlan rather than an InitPlan.

I don't particularly want to abandon the hack of discarding expression
eval state after each VALUES row; without that, a very large VALUES
is going to eat a lot of memory.  The next best answer seems to be
to go ahead and pass the Values node as parent to the expressions,
but to prevent the expressions from actually hooking into its subPlan
list, as per the attached patch.

The main problem with this is that in future, child expressions might
possibly try to attach to fields of the parent plan node other than
the subPlan list.  We could extend this hack to deal with that, but
I can't think of any forcing function to ensure that we'd notice the
need to.  Still, that's a pretty hypothetical problem, and we do have
a live bug here.

			regards, tom lane