Re: BUG #16501: Incorrect result. select multi_key_columns_range_partition_table

On Fri, Jun 19, 2020 at 9:00 AM David Rowley <dgrowleyml@gmail.com> wrote:
> 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.

Thanks, Kobayashi-san!

> 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)

Thanks for the simple test case, David!

> This seems to be caused by the following code, which assumes it's ok
> to use the prefix for <= / = / >= btree operators.

I think the root cause for this is the same as that for bug #16500.
See the commit message in the patch in [1].

> 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;

Me either.

Best regards,
Etsuro Fujita

[1] https://www.postgresql.org/message-id/CAPmGK16pXA_5-Sct%3DnWJh4SSPTVv7YAjXYjyz8iRt7WHBKdpjA%40mail.gmail.com