pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2025-01-17 bf826ea062 45004f527a 0671a71e02 94c02bd334 724ebebb11 5f725648fa Fix setrefs.c's failure to do expression processing on prune steps.
* BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
@ 2025-01-16 20:00 PG Bug reporting form <noreply@postgresql.org>
2025-01-16 23:35 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2025-01-16 20:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
The following bug has been logged on the website:
Bug reference: 18778
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 17.2
Operating system: Ubuntu 24.04
Description:
The following script:
CREATE TABLE t (id int, PRIMARY KEY (id)) PARTITION BY RANGE (id);
CREATE TABLE t0 PARTITION OF t FOR VALUES FROM (0) TO (1);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (2);
SELECT 1 FROM (SELECT EXISTS (SELECT 1 FROM t0 WHERE id = t00.id) AS b FROM
t0 t00) r, t
WHERE t.id > CASE WHEN jsonb_build_object(b) IS NULL THEN 1 ELSE 1 END;
fails with:
ERROR: XX000: unrecognized node type: 24
LOCATION: ExecInitExprRec, execExpr.c:2657
The first bad commit for this anomaly (discovered with SQLsmith) is
41efb8340.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
2025-01-16 20:00 BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type PG Bug reporting form <noreply@postgresql.org>
@ 2025-01-16 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-18 15:52 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Zhang Mingli <zmlpostgres@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2025-01-16 23:35 UTC (permalink / raw)
To: exclusion@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> The following script:
> CREATE TABLE t (id int, PRIMARY KEY (id)) PARTITION BY RANGE (id);
> CREATE TABLE t0 PARTITION OF t FOR VALUES FROM (0) TO (1);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (2);
> SELECT 1 FROM (SELECT EXISTS (SELECT 1 FROM t0 WHERE id = t00.id) AS b FROM
> t0 t00) r, t
> WHERE t.id > CASE WHEN jsonb_build_object(b) IS NULL THEN 1 ELSE 1 END;
> fails with:
> ERROR: XX000: unrecognized node type: 24
Thanks for the report! setrefs.c is supposed to remove
AlternativeSubPlan nodes from the plan, but it's failing to do so
here. Digging, the un-cleaned-up AlternativeSubPlan is inside the
exec_pruning_steps of an Append node's part_prune_info, and I see
that setrefs is totally unaware that those expressions might need
processing. I think it ought to be applying fix_scan_expr to them,
as per the attached. There are a bunch of tidying-up things that
fix_scan_expr does, so I suspect that there may be more bug symptoms
reachable from this oversight. Some of the missed processing may be
redundant --- for example it's likely that
record_plan_function_dependency is duplicative because functions used
here would also be used elsewhere in the query. But it's hard to
believe it all is.
regards, tom lane
Attachments:
apply-setrefs-fixups-to-pruning-expressions.patch (text/x-diff, 999B) download
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index fff2655595..1e7b7bc6ff 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -1795,6 +1795,12 @@ set_append_references(PlannerInfo *root,
PartitionedRelPruneInfo *pinfo = lfirst(l2);
pinfo->rtindex += rtoffset;
+ pinfo->initial_pruning_steps =
+ fix_scan_list(root, pinfo->initial_pruning_steps,
+ rtoffset, 1);
+ pinfo->exec_pruning_steps =
+ fix_scan_list(root, pinfo->exec_pruning_steps,
+ rtoffset, 1);
}
}
}
@@ -1871,6 +1877,12 @@ set_mergeappend_references(PlannerInfo *root,
PartitionedRelPruneInfo *pinfo = lfirst(l2);
pinfo->rtindex += rtoffset;
+ pinfo->initial_pruning_steps =
+ fix_scan_list(root, pinfo->initial_pruning_steps,
+ rtoffset, 1);
+ pinfo->exec_pruning_steps =
+ fix_scan_list(root, pinfo->exec_pruning_steps,
+ rtoffset, 1);
}
}
}
* Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
2025-01-16 20:00 BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type PG Bug reporting form <noreply@postgresql.org>
2025-01-16 23:35 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-01-18 15:52 ` Zhang Mingli <zmlpostgres@gmail.com>
2025-01-18 16:51 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Zhang Mingli @ 2025-01-18 15:52 UTC (permalink / raw)
To: exclusion@gmail.com, Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Zhang Mingli
www.hashdata.xyz
On Jan 17, 2025 at 07:35 +0800, Tom Lane <tgl@sss.pgh.pa.us>, wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > The following script:
> > CREATE TABLE t (id int, PRIMARY KEY (id)) PARTITION BY RANGE (id);
> > CREATE TABLE t0 PARTITION OF t FOR VALUES FROM (0) TO (1);
> > CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (2);
>
> > SELECT 1 FROM (SELECT EXISTS (SELECT 1 FROM t0 WHERE id = t00.id) AS b FROM
> > t0 t00) r, t
> > WHERE t.id > CASE WHEN jsonb_build_object(b) IS NULL THEN 1 ELSE 1 END;
>
> > fails with:
> > ERROR: XX000: unrecognized node type: 24
>
> Thanks for the report! setrefs.c is supposed to remove
> AlternativeSubPlan nodes from the plan, but it's failing to do so
> here. Digging, the un-cleaned-up AlternativeSubPlan is inside the
> exec_pruning_steps of an Append node's part_prune_info, and I see
> that setrefs is totally unaware that those expressions might need
> processing. I think it ought to be applying fix_scan_expr to them,
> as per the attached. There are a bunch of tidying-up things that
> fix_scan_expr does, so I suspect that there may be more bug symptoms
> reachable from this oversight. Some of the missed processing may be
> redundant --- for example it's likely that
> record_plan_function_dependency is duplicative because functions used
> here would also be used elsewhere in the query. But it's hard to
> believe it all is.
>
Hi, I have tested the patch, and confirm that it works well. Additionally, I believe it might be worth creating a test case for it.
And I have a question that may not be directly related to the issue: why is there a Subplan 2, and it appears to be unused?
explain(costs off, verbose) SELECT 1 FROM (SELECT EXISTS (SELECT 1 FROM t0 WHERE id = t00.id) AS b FROM
t0 t00) r, t WHERE t.id > CASE WHEN jsonb_build_object(b) IS NULL THEN 1 ELSE 1 END;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Nested Loop
Output: 1
-> Seq Scan on public.t0 t00
Output: t00.id
-> Append
-> Index Only Scan using t0_pkey on public.t0 t_1
Output: t_1.id
Index Cond: (t_1.id > CASE WHEN (jsonb_build_object(EXISTS(SubPlan 1)) IS NULL) THEN 1 ELSE 1 END)
SubPlan 2
-> Seq Scan on public.t0 t0_1
Output: t0_1.id
-> Index Only Scan using t1_pkey on public.t1 t_2
Output: t_2.id
Index Cond: (t_2.id > CASE WHEN (jsonb_build_object(EXISTS(SubPlan 1)) IS NULL) THEN 1 ELSE 1 END)
SubPlan 1
-> Index Only Scan using t0_pkey on public.t0
Index Cond: (t0.id = t00.id)
(17 rows)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
2025-01-16 20:00 BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type PG Bug reporting form <noreply@postgresql.org>
2025-01-16 23:35 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Tom Lane <tgl@sss.pgh.pa.us>
2025-01-18 15:52 ` Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type Zhang Mingli <zmlpostgres@gmail.com>
@ 2025-01-18 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2025-01-18 16:51 UTC (permalink / raw)
To: Zhang Mingli <zmlpostgres@gmail.com>; +Cc: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Zhang Mingli <zmlpostgres@gmail.com> writes:
> And I have a question that may not be directly related to the issue: why is there a Subplan 2, and it appears to be unused?
Yeah, that's because the AlternativeSubPlan originally had two child
subplans: a parameterized one intended for a few calls, and a hashed
one to be used for a lot of calls. We build both because at the time
when this needs to happen, we don't have any clue how many calls
there will be. We wait till the very end of planning to decide which
one is more likely to win based on context, and then discard the
AlternativeSubPlan, replacing it with a direct reference to the chosen
SubPlan. But the other one stays in the tree. Getting rid of it
seemed complicated, and it won't be referenced at runtime so there's
not much penalty from leaving it there. It is confusing, I admit.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-01-18 16:51 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-01-16 20:00 BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type PG Bug reporting form <noreply@postgresql.org>
2025-01-16 23:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-01-18 15:52 ` Zhang Mingli <zmlpostgres@gmail.com>
2025-01-18 16:51 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox