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"
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