From: Zhang Mingli <zmlpostgres@gmail.com>
To: exclusion@gmail.com, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
Date: Sat, 18 Jan 2025 23:52:01 +0800
Message-ID: <dc467c76-e5e5-4eaf-950b-32097f32d4f2@Spark> (raw)
In-Reply-To: <1919554.1737070534@sss.pgh.pa.us>
References: <18778-24cd399df6c806af@postgresql.org>
<1919554.1737070534@sss.pgh.pa.us>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: zmlpostgres@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type
In-Reply-To: <dc467c76-e5e5-4eaf-950b-32097f32d4f2@Spark>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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