Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table

On Wed, Jul 8, 2020 at 3:15 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> Maybe I'm missing something, but I
> suspect that hash partitioning would also have a similar issue,

Here is an example causing the same assertion failure:

create table hp_prefix_test (a int, b int, c int, d int) partition by
hash (a part_test_int4_ops, b part_test_int4_ops, c
part_test_int4_ops, d part_test_int4_ops);
create table hp_prefix_test_p1 partition of hp_prefix_test for values
with (modulus 2, remainder 0);
create table hp_prefix_test_p2 partition of hp_prefix_test for values
with (modulus 2, remainder 1);
explain (costs off) select * from hp_prefix_test where a = 1 and b is
null and c = 1 and d = 1;

where part_test_int4_ops is borrowed from insert.sql.  For hash
partitioning, I think prefix is allowed to contain no clauses for
all/any of earlier partition keys unlike range partitioning, so I
modified the assertion test to avoid the failure.

While working on it, I noticed there is yet another issue in
generating pruning steps.  This is the comment for
get_steps_using_prefix():

 * To generate steps, step_lastexpr and step_lastcmpfn are appended to
 * expressions and cmpfns, respectively, extracted from the clauses in
 * 'prefix'.  Actually, since 'prefix' may contain multiple clauses for the
 * same partition key column, we must generate steps for various combinations
 * of the clauses of different keys.

But part of that function assumes that prefix contains at most one
clause for each of middle partition keys, which causes the same
assertion failure when there are multiple clauses for the middle
partition keys in prefix.  Here is an example using range partitioning
causing the failure:

create table rp_prefix_test3 (a int, b int, c int, d int) partition by
range(a, b, c, d);
create table rp_prefix_test3_p1 partition of rp_prefix_test3 for
values from (1, 1, 1, 0) to (1, 1, 1, 10);
create table rp_prefix_test3_p2 partition of rp_prefix_test3 for
values from (2, 2, 2, 0) to (2, 2, 2, 10);
explain (costs off) select * from rp_prefix_test3 where a >= 1 and b
>= 1 and b >= 2 and c >= 2 and d >= 0;

To fix, I modified that function (precisely,
get_steps_using_prefix_recurse()) to allow the middle partition keys
also to have multiple clauses in prefix.

Attached is an updated version of the patch.

Best regards,
Etsuro Fujita