Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tZB7Q-002r3M-Da for pgsql-bugs@arkaria.postgresql.org; Sat, 18 Jan 2025 15:52:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tZB7O-00109J-6q for pgsql-bugs@arkaria.postgresql.org; Sat, 18 Jan 2025 15:52:14 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tZB7N-00109B-Nc for pgsql-bugs@lists.postgresql.org; Sat, 18 Jan 2025 15:52:14 +0000 Received: from mail-pj1-x1044.google.com ([2607:f8b0:4864:20::1044]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tZB7L-000Dd1-0P for pgsql-bugs@lists.postgresql.org; Sat, 18 Jan 2025 15:52:12 +0000 Received: by mail-pj1-x1044.google.com with SMTP id 98e67ed59e1d1-2ee51f8c47dso4335238a91.1 for ; Sat, 18 Jan 2025 07:52:11 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1737215531; x=1737820331; darn=lists.postgresql.org; h=mime-version:subject:references:in-reply-to:message-id:cc:to:from :date:from:to:cc:subject:date:message-id:reply-to; bh=5dfIQ1trYggkecsWblvl1TxEdGHyTplvJ9Obi7MSqpY=; b=ZeWrdsjDpqjtjgZ8gMGFcW+sSXQcBxkrXSfBSt7JHEj9GAy/j8tG7SWw1fZwwnAHcp 1GW/F2bY2EzVRW53cfZieyJET3VbIQUPIwLmCzRh9n4kZo4XTjHwhS0cASukm3IwTy/g xU11R/LoJNlqWhxGlKMTKru8yjI4KamS8SIr6hb3MzNWdfT22adxZTwKeWOQZYFl3yhj pH7nbLcjQxY3uPhgJd7rlh3+HFOHHHULy6/FcZxxmJxk36G8tjh07g/lCTerauZg1fgp 5fGwGRz1RwlCSr4ie/3P+Lul0Ca/HlBoiKVBTCggJaumLLBkQINTRp1rSzUANX1tiuTO PpGw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1737215531; x=1737820331; h=mime-version:subject:references:in-reply-to:message-id:cc:to:from :date:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=5dfIQ1trYggkecsWblvl1TxEdGHyTplvJ9Obi7MSqpY=; b=I92exhgbWY0PqQSDyjnMv0TRp8u9gZA+HuL+Jauk4EjFp+OvX2C8aCNYmFO2s83YAo 9YS/mQagp8UB7MYe/dgyvYcoBFgf53Acmm+cr+j0OyAd0nR/QAKE8ABKFx/g6UX4VD1h yjmTUvXHCKCd2zv4iiDzvUOCc1oaqHuNnFPjtsZHuUqthFIGMTGtr7qP2CSomfgab2Bb eV7+LfdDQDTuSbxL41myqD03IWbLJyPs2NznWpY9zoKwMcvz6bjVhZPGi3mvs0A5OUPX pi5K99jrPjHFxppKjVYCW1qSeO9MvLqoxiCr1EdYX8tlpadvvJftFM0pQwt7nimbrJKe r/cg== X-Gm-Message-State: AOJu0Yz/hHK8RXLQCC1HdeOWq6UjX2d/FxnDRizZHSXIyBu63F96RL0C G6Wc4xpJRbr4P5l4xiMn4ZGzNLdnZV45ito+VqzCCNIyZ8GoCEPVg77JefVy7UFvlA== X-Gm-Gg: ASbGncsvqDA26Tl4R7PFa++okrehS0eWh/booKPCe/gsob2ZhGmDWXfGy7BcpEhk/WN tGve4fnbYkrAXLFr1F1MjS139ziFLuN4lwegZ57OZIUX6MoU+7LlmHSa+usLJOoaPQpL5u/pxb9 HsjtHpaII9YCLDstDM6AUS1526olpEElvbI9s6HRNgs7ZEI9z7sY0HXJSuK6VQkgtgow/CWAFJB MRLlvMadGltOGQJzRDAFmscYiWsshP6DFe7RLjwHCJuzo0/Ydx7zwSjVuzSpaacbRC2IklVpQ== X-Google-Smtp-Source: AGHT+IE8qGQnSFTxfYAqvOtozz0ozLn8qfrClLh9GdXPdfIwgTPvXaBQ6khujKW/BHru9LbwUaqbpw== X-Received: by 2002:aa7:8887:0:b0:72d:3b2e:fef9 with SMTP id d2e1a72fcca58-72dafb9e7c6mr11074019b3a.20.1737215530551; Sat, 18 Jan 2025 07:52:10 -0800 (PST) Received: from [127.0.0.1] ([2406:da18:81d:bd02:84dd:595a:b7ac:f977]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-72dab817732sm4035395b3a.64.2025.01.18.07.52.08 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sat, 18 Jan 2025 07:52:10 -0800 (PST) Date: Sat, 18 Jan 2025 23:52:01 +0800 From: Zhang Mingli To: exclusion@gmail.com, Tom Lane Cc: pgsql-bugs@lists.postgresql.org Message-ID: In-Reply-To: <1919554.1737070534@sss.pgh.pa.us> References: <18778-24cd399df6c806af@postgresql.org> <1919554.1737070534@sss.pgh.pa.us> Subject: Re: BUG #18778: Query planning fails in ExecInitExprRec with unrecognized node type X-Readdle-Message-ID: dc467c76-e5e5-4eaf-950b-32097f32d4f2@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="678bce26_3c348f39_d682" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --678bce26_3c348f39_d682 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Zhang Mingli www.hashdata.xyz On Jan 17, 2025 at 07:35 +0800, Tom Lane , wrote: > PG Bug reporting form writes: > > The following script: > > CREATE TABLE t (id int, PRIMARY KEY (id)) PARTITION BY RANGE (id); > > CREATE TABLE t0 PARTITION O=46 t =46OR VALUES =46ROM (0) TO (1); > > CREATE TABLE t1 PARTITION O=46 t =46OR VALUES =46ROM (1) TO (2); > > > SELECT 1 =46ROM (SELECT EXISTS (SELECT 1 =46ROM t0 WHERE id =3D t00.i= d) AS b =46ROM > > t0 t00) r, t > > WHERE t.id > CASE WHEN jsonb=5Fbuild=5Fobject(b) IS NULL THEN 1 ELSE = 1 END; > > > fails with: > > ERROR: XX000: unrecognized node type: 24 > > Thanks for the report=21 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=5Fpruning=5Fsteps of an Append node's part=5Fprune=5Finfo, and I s= ee > that setrefs is totally unaware that those expressions might need > processing. I think it ought to be applying fix=5Fscan=5Fexpr to them, > as per the attached. There are a bunch of tidying-up things that > fix=5Fscan=5Fexpr does, so I suspect that there may be more bug symptom= s > reachable from this oversight. Some of the missed processing may be > redundant --- for example it's likely that > record=5Fplan=5Ffunction=5Fdependency 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=3F explain(costs off, verbose) SELECT 1 =46ROM (SELECT EXISTS (SELECT 1 =46R= OM t0 WHERE id =3D t00.id) AS b =46ROM t0 t00) r, t WHERE t.id > CASE WHEN jsonb=5Fbuild=5Fobject(b) IS NULL THE= N 1 ELSE 1 END; =C2=A0=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN -------------------------------------------------------------------------= ----------------------------------------- =C2=A0Nested Loop =C2=A0 =C2=A0Output: 1 =C2=A0 =C2=A0->=C2=A0=C2=A0Seq Scan on public.t0 t00 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: t00.id =C2=A0 =C2=A0->=C2=A0=C2=A0Append =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0=C2=A0Index Only Scan using t0=5F= pkey on public.t0 t=5F1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: t=5F1.id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (t=5F1= .id > CASE WHEN (jsonb=5Fbuild=5Fobject(EXISTS(SubPlan 1)) IS NULL) THEN = 1 ELSE 1 END) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SubPlan 2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0=C2= =A0Seq Scan on public.t0 t0=5F1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Output: t0=5F1.id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0=C2=A0Index Only Scan using t1=5F= pkey on public.t1 t=5F2 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: t=5F2.id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (t=5F2= .id > CASE WHEN (jsonb=5Fbuild=5Fobject(EXISTS(SubPlan 1)) IS NULL) THEN = 1 ELSE 1 END) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0SubPlan 1 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0->=C2=A0=C2=A0Index Only Scan us= ing t0=5Fpkey on public.t0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond:= (t0.id =3D t00.id) (17 rows) --678bce26_3c348f39_d682 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline


Zhang Mingli
www.hashdata.xyz
On Jan 17, 2025 at 07:35 +0800, Tom Lane <tgl=40= sss.pgh.pa.us>, wrote:
PG Bug reporting form <noreply=40postgresql.org> writes:
The following script:
CREATE TABLE t (id int, PRIMARY KEY (id)) PARTITION BY RANGE (id);
CREATE TABLE t0 PARTITION O=46 t =46OR VALUES =46ROM (0) TO (1);
CREATE TABLE t1 PARTITION O=46 t =46OR VALUES =46ROM (1) TO (2);
SELECT 1 =46ROM (SELECT EXISTS (SELECT 1 =46ROM t0 WHERE id =3D t00.id) = AS b =46ROM
t0 t00) r, t
WHERE t.id > CASE WHEN jsonb=5Fbuild=5Fobject(b) IS NULL THEN 1 ELSE 1= END;

fails with:
ERROR: XX000: unrecognized node type: 24

Thanks for the report=21 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=5Fpruning=5Fsteps of an Append node's part=5Fprune=5Finfo, and I see=
that setrefs is totally unaware that those expressions might need
processing. I think it ought to be applying fix=5Fscan=5Fexpr to them, as per the attached. There are a bunch of tidying-up things that
fix=5Fscan=5Fexpr does, so I suspect that there may be more bug symptoms<= br /> reachable from this oversight. Some of the missed processing may be
= redundant --- for example it's likely that
record=5Fplan=5Ffunction=5Fdependency is duplicative because functions us= ed
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 wo= rks 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=3F

explain(costs off, verbose) SELECT 1 =46ROM (SELECT EXISTS (SELECT 1 =46R= OM t0 WHERE id =3D t00.id) AS b =46ROM
t0 t00) r, t WHERE t.id > CASE WHEN jsonb=5Fbuild=5Fobject(b) IS NULL = THEN 1 ELSE 1 END;
&=23160;&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23= 160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=231= 60; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=2316= 0; &=23160; &=23160;QUERY PLAN
-------------------------------------------------------------------------= -----------------------------------------
&=23160;Nested Loop
&=23160; &=23160;Output: 1
&=23160; &=23160;->&=23160;&=23160;Seq Scan on public.t0 t00
&=23160; &=23160; &=23160; &=23160; &=23160;Output: t00.id
&=23160; &=23160;->&=23160;&=23160;Append
&=23160; &=23160; &=23160; &=23160; &=23160;->&=23160;&=23160;Index On= ly Scan using t0=5Fpkey on public.t0 t=5F1
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;Ou= tput: t=5F1.id
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;In= dex Cond: (t=5F1.id > CASE WHEN (jsonb=5Fbuild=5Fobject(EXISTS(SubPlan= 1)) IS NULL) THEN 1 ELSE 1 END)
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;Su= bPlan 2
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &= =23160;->&=23160;&=23160;Seq Scan on public.t0 t0=5F1
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &= =23160; &=23160; &=23160; &=23160;Output: t0=5F1.id
&=23160; &=23160; &=23160; &=23160; &=23160;->&=23160;&=23160;Index On= ly Scan using t1=5Fpkey on public.t1 t=5F2
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;Ou= tput: t=5F2.id
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160;In= dex Cond: (t=5F2.id > CASE WHEN (jsonb=5Fbuild=5Fobject(EXISTS(SubPlan= 1)) IS NULL) THEN 1 ELSE 1 END)
&=23160; &=23160; &=23160; &=23160; &=23160;SubPlan 1
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160;->&=23160;&=23160= ;Index Only Scan using t0=5Fpkey on public.t0
&=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &=23160; &= =23160;Index Cond: (t0.id =3D t00.id)
(17 rows)

&=23160;

--678bce26_3c348f39_d682--