BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

Поиск
Список
Период
Сортировка
Искать

BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      19802
Logged by:          Gowtham Reddy
Email address:      gowthamdoma18@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 22.04 (WSL2), x86_64
Description:        

MERGE into a partitioned table fails with an internal error when the source
is a
subquery that is not pulled up, an UPDATE SET clause contains a scalar
subquery
that references the source, and the statement has both WHEN MATCHED UPDATE
and
WHEN NOT MATCHED INSERT:

CREATE TABLE mp (a int, b text) PARTITION BY RANGE (a);
CREATE TABLE mp1 PARTITION OF mp FOR VALUES FROM (0) TO (10);
MERGE INTO mp t
USING (SELECT true AS c OFFSET 0) s ON t.a IS NULL
WHEN MATCHED THEN UPDATE SET b = (SELECT NULL WHERE s.c)
WHEN NOT MATCHED THEN INSERT VALUES (1, 'x');

ERROR:  SubPlan found with no parent plan

Reproduced on 17.11, 18.6 and master (4848fd675, 2026-10-28).

Each of these makes it succeed: a non-partitioned target table; dropping
either
the WHEN MATCHED UPDATE or the WHEN NOT MATCHED INSERT action (or using DO
NOTHING
instead of the INSERT); a join condition that references the source (ON true
also works); a source that is pulled up (no OFFSET/LIMIT); the equivalent
UPDATE ... FROM.

The same message was fixed for a SELECT case on a partitioned table in
eacee6b61
("Don't use SubPlan-containing expressions for startup-time pruning"); that
commit is in the tested master, so this MERGE case seems to take a different
path.

Found by a grammar-based SQL fuzzer (university course project); the script
above is the hand-reduced test case.




Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Gowtham Reddy <gowthamdoma18@gmail.com>
Дата:
Hi,

Thanks for the quick analysis and the patch.

I applied the one-line change you described (passing &mtstate->ps to
ExecBuildUpdateProjection() for the partition MERGE UPDATE action) on
master at 4848fd675. The reproducer from my report now succeeds, and a
MERGE that takes the UPDATE path with the subquery in SET updates the
row correctly.

Regards,
Gowtham

On Thu, 29 Oct 2026 at 06:09, Tender Wang  wrote:
>
> PG Bug reporting form  于2026年10月29日周四 00:25写道:
> > MERGE into a partitioned table fails with an internal error when the source
> > is a
> > subquery that is not pulled up, an UPDATE SET clause contains a scalar
> > subquery
> > that references the source, and the statement has both WHEN MATCHED UPDATE
> > and
> > WHEN NOT MATCHED INSERT:
> >
> > CREATE TABLE mp (a int, b text) PARTITION BY RANGE (a);
> > CREATE TABLE mp1 PARTITION OF mp FOR VALUES FROM (0) TO (10);
> > MERGE INTO mp t
> > USING (SELECT true AS c OFFSET 0) s ON t.a IS NULL
> > WHEN MATCHED THEN UPDATE SET b = (SELECT NULL WHERE s.c)
> > WHEN NOT MATCHED THEN INSERT VALUES (1, 'x');
> >
> > ERROR:  SubPlan found with no parent plan
> >
> Hi,
>
> Thanks for the report and the nicely reduced test case.
>
> The problem is in ExecInitPartitionInfo().  When tuple routing creates a
> ResultRelInfo for a partition during MERGE, it also builds
> partition-specific state for the MERGE actions.  For an UPDATE action, it
> calls ExecBuildUpdateProjection() with a NULL parent PlanState.
>
> If the UPDATE target list contains a SubPlan, ExecInitSubPlanExpr() needs
> the parent PlanState to initialize the SubPlan and add the resulting
> SubPlanState to the parent's subPlan list.  Because no parent was supplied,
> executor initialization fails with:
>
>     ERROR:  SubPlan found with no parent plan
>
> The corresponding code in ExecInitMerge() passes &mtstate->ps when
> building the UPDATE projection.  There is also another call to
> ExecBuildUpdateProjection() earlier in ExecInitPartitionInfo(), for
> ON CONFLICT DO UPDATE, that already passes &mtstate->ps.  Only the
> partition-specific MERGE UPDATE path passes NULL.
>
> Passing the same parent in that path fixes the problem:
>
>     -                                 NULL);
>     +                                 &mtstate->ps);
>
> Attached is a patch with the fix.  I have run the regression tests successfully.
>
>
> --
> Thanks,
> Tender Wang


Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Tender Wang <tndrwang@gmail.com>
Дата:
Gowtham Reddy  于2026年10月29日周四 09:27写道:
>
> Hi,
>
> Thanks for the quick analysis and the patch.
>
> I applied the one-line change you described (passing &mtstate->ps to
> ExecBuildUpdateProjection() for the partition MERGE UPDATE action) on
> master at 4848fd675. The reproducer from my report now succeeds, and a
> MERGE that takes the UPDATE path with the subquery in SET updates the
> row correctly.

Thanks for testing the patch and confirming that it fixes the problem.

I am CC'ing Dean, who has worked on related MERGE executor code, in case
he has any comments on the proposed fix.

-- 
Thanks,
Tender Wang


Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Thu, Oct 29, 2026 at 6:09 AM Tender Wang  wrote:
>
> PG Bug reporting form  于2026年10月29日周四 00:25写道:
> > MERGE into a partitioned table fails with an internal error when the source
> > is a
> > subquery that is not pulled up, an UPDATE SET clause contains a scalar
> > subquery
> > that references the source, and the statement has both WHEN MATCHED UPDATE
> > and
> > WHEN NOT MATCHED INSERT:
> >
> > CREATE TABLE mp (a int, b text) PARTITION BY RANGE (a);
> > CREATE TABLE mp1 PARTITION OF mp FOR VALUES FROM (0) TO (10);
> > MERGE INTO mp t
> > USING (SELECT true AS c OFFSET 0) s ON t.a IS NULL
> > WHEN MATCHED THEN UPDATE SET b = (SELECT NULL WHERE s.c)
> > WHEN NOT MATCHED THEN INSERT VALUES (1, 'x');
> >
> > ERROR:  SubPlan found with no parent plan
> >
> Hi,
>
> Thanks for the report and the nicely reduced test case.
>
> The problem is in ExecInitPartitionInfo().  When tuple routing creates a
> ResultRelInfo for a partition during MERGE, it also builds
> partition-specific state for the MERGE actions.  For an UPDATE action, it
> calls ExecBuildUpdateProjection() with a NULL parent PlanState.
>
> If the UPDATE target list contains a SubPlan, ExecInitSubPlanExpr() needs
> the parent PlanState to initialize the SubPlan and add the resulting
> SubPlanState to the parent's subPlan list.  Because no parent was supplied,
> executor initialization fails with:
>
>     ERROR:  SubPlan found with no parent plan
>
> The corresponding code in ExecInitMerge() passes &mtstate->ps when
> building the UPDATE projection.  There is also another call to
> ExecBuildUpdateProjection() earlier in ExecInitPartitionInfo(), for
> ON CONFLICT DO UPDATE, that already passes &mtstate->ps.  Only the
> partition-specific MERGE UPDATE path passes NULL.

Yeah, that seems to be wrong

>
> Passing the same parent in that path fixes the problem:
>
>     -                                 NULL);
>     +                                 &mtstate->ps);

> Attached is a patch with the fix.  I have run the regression tests successfully.

I think your analysis and fix LGTM. Can you also add a test case so
that we do not break this in the future?  Thanks.


-- 
Regards,
Dilip Kumar
Google


Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Tender Wang <tndrwang@gmail.com>
Дата:
Вложения:
Hi,

Dilip Kumar  于2026年11月4日周三 18:49写道:
>
> I think your analysis and fix LGTM. Can you also add a test case so
> that we do not break this in the future?  Thanks.
>

Thanks for the review.
I added the regression test in the attached v2 patch.

-- 
Thanks,
Tender Wang

Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Dilip Kumar <dilipbalaut@gmail.com>
Дата:
On Wed, Nov 4, 2026 at 4:55 PM Tender Wang  wrote:
>
> Hi,
>
> Dilip Kumar  于2026年11月4日周三 18:49写道:
> >
> > I think your analysis and fix LGTM. Can you also add a test case so
> > that we do not break this in the future?  Thanks.
> >
>
> Thanks for the review.
> I added the regression test in the attached v2 patch.
>

Thanks this LGTM.


-- 
Regards,
Dilip Kumar
Google


Re: BUG #19802: MERGE into a partitioned table fails with "SubPlan found with no parent plan"

От:
Dean Rasheed <dean.a.rasheed@gmail.com>
Дата:
On Wed, 4 Nov 2026 at 12:28, Dilip Kumar  wrote:
>
> On Wed, Nov 4, 2026 at 4:55 PM Tender Wang  wrote:
> >
> > Thanks for the review.
> > I added the regression test in the attached v2 patch.
>
> Thanks this LGTM.

Thanks everyone.

Pushed, and back-patched to v15.

Regards,
Dean


FAQ