Date: Fri, 6 Oct 2023 17:02:43 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Sergei Glukhov <s.glukhov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Problem, partition pruning for prepared statement with IS NULL
 clause.
Message-ID: <ZSB1840wfTVsbuFD@momjian.us>
References: <d05b26fa-af54-27e1-f693-6c31590802fa@postgrespro.ru>
 <ZSB1hnquhK0ss4F4@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="+0k6zITfR7jNxDrn"
Content-Disposition: inline
In-Reply-To: <ZSB1hnquhK0ss4F4@momjian.us>
Archived-At: 
 <https://www.postgresql.org/message-id/ZSB1840wfTVsbuFD%40momjian.us>
Precedence: bulk


--+0k6zITfR7jNxDrn
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Fri, Oct  6, 2023 at 05:00:54PM -0400, Bruce Momjian wrote:
> On Fri, Oct  6, 2023 at 06:09:45PM +0400, Sergei Glukhov wrote:
> > Test case:
> > ------
> > set plan_cache_mode to force_generic_plan;
> > prepare stmt AS select * from hp where a is null and b = $1;
> > explain execute stmt('xxx');
> > ------
> 
> I can confirm the crash in git master.

There were some UTF8 non-space whitespace characters in the email so
attached is a clean reproducable SQL crash file.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--+0k6zITfR7jNxDrn
Content-Type: application/sql
Content-Disposition: attachment; filename="crash.sql"
Content-Transfer-Encoding: quoted-printable

create function part_hashint4_noop(value int4, seed int8)=0Areturns int8 as=
 $$=0Aselect value + seed;=0A$$ language sql strict immutable parallel safe=
;=0A=0Acreate operator class part_test_int4_ops for type int4 using hash as=
=0Aoperator 1 =3D,=0Afunction 2 part_hashint4_noop(int4, int8);=0A=0Acreate=
 function part_hashtext_length(value text, seed int8)=0Areturns int8 as $$=
=0Aselect length(coalesce(value, ''))::int8=0A$$ language sql strict immuta=
ble parallel safe;=0A=0Acreate operator class part_test_text_ops for type t=
ext using hash as=0Aoperator 1 =3D,=0Afunction 2 part_hashtext_length(text,=
 int8);=0A=0A=0Acreate table hp (a int, b text, c int)=0Apartition by hash =
(a part_test_int4_ops, b part_test_text_ops);=0Acreate table hp0 partition =
of hp for values with (modulus 4, remainder 0);=0Acreate table hp3 partitio=
n of hp for values with (modulus 4, remainder 3);=0Acreate table hp1 partit=
ion of hp for values with (modulus 4, remainder 1);=0Acreate table hp2 part=
ition of hp for values with (modulus 4, remainder 2);=0A=0Ainsert into hp v=
alues (null, null, 0);=0Ainsert into hp values (1, null, 1);=0Ainsert into =
hp values (1, 'xxx', 2);=0Ainsert into hp values (null, 'xxx', 3);=0Ainsert=
 into hp values (2, 'xxx', 4);=0Ainsert into hp values (1, 'abcde', 5);=0A=
=0Aset plan_cache_mode to force_generic_plan;=0Aprepare stmt AS select * fr=
om hp where a is null and b =3D $1;=0Aexplain execute stmt('xxx');=0A=0A
--+0k6zITfR7jNxDrn--