Re: BUG #16501: Incorrect result. select multi_key_columns_range_partition_table

On Thu, 18 Jun 2020 at 21:49, PG Bug reporting form
<noreply@postgresql.org> wrote:
> I am not good at English, so I will send a reproduction script.

Many thanks for the report. This is certainly a bug in the partition
pruning code for range partitioned tables.

A more simple case is:

create table rp (a int, b int) partition by range (a,b);
create table rp_2020 partition of rp for values from (2020, 1) to (2020, 7);
insert into rp values(2020,1);
explain select * from rp where a <= 2020 and b = 1;

Which gives:
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)


# set enable_partition_pruning=off;
SET
# select * from rp where a <= 2020 and b = 1;
  a   | b
------+---
 2020 | 1
(1 row)

This seems to be caused by the following code, which assumes it's ok
to use the prefix for <= / = / >= btree operators.  Initially, I
imagined that there's no reason to allow anything apart from = there,
but I suppose we could consider sub-ranges of partitions that are <=
2020, but then I don't really understand why the same thing can't be
done with < 2020.

/*
* We can't consider subsequent partition keys if the
* clause for the current key contains a non-inclusive
* operator.
*/
if (pc->op_strategy == BTLessStrategyNumber ||
pc->op_strategy == BTGreaterStrategyNumber)
consider_next_key = false;
break;

Perhaps Amit can explain the reason for that?

David