Re: Problem, partition pruning for prepared statement with IS NULL clause.

On Tue, 10 Oct 2023 at 21:31, Sergei Glukhov <s.glukhov@postgrespro.ru> wrote:
> create table hp (a int, b text, c int, d int)
>    partition by hash (a part_test_int4_ops, b part_test_text_ops, c
> part_test_int4_ops);
> create table hp0 partition of hp for values with (modulus 4, remainder 0);
> create table hp3 partition of hp for values with (modulus 4, remainder 3);
> create table hp1 partition of hp for values with (modulus 4, remainder 1);
> create table hp2 partition of hp for values with (modulus 4, remainder 2);
>
>
> Another crash in the different place even with the fix:
> explain select * from hp where a = 1 and b is null and c = 1;

Ouch.  It looks like 13838740f tried to fix things in this area before
and even added a regression test for it. Namely:

-- Test that get_steps_using_prefix() handles non-NULL step_nullkeys
explain (costs off) select * from hp_prefix_test where a = 1 and b is
null and c = 1 and d = 1;

I guess that one does not crash because of the "d = 1" clause is in
the "start" ListCell in get_steps_using_prefix_recurse(), whereas,
with your case start is NULL which is an issue for cur_keyno =
((PartClauseInfo *) lfirst(start))->keyno;.

It might have been better if PartClauseInfo could also describe IS
NULL quals, but I feel if we do that now then it would require lots of
careful surgery in partprune.c to account for that.  Probably the fix
should be localised to get_steps_using_prefix_recurse() to have it do
something like pass the keyno to try and work on rather than trying to
get that from the "prefix" list. That way if there's no item in that
list for that keyno, we can check in step_nullkeys for the keyno.

I'll continue looking.

David