Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table

On Sat, Jul 4, 2020 at 2:35 AM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> I spent some time reviewing the patch, and noticed that the changes to
> gen_prune_steps_from_opexps() break the logic in
> get_matching_range_bounds(), causing another issue. Here is an
> example:
>
> postgres=# create table prefix_test (a int, b varchar) partition by
> range (a, b);
> postgres=# create table prefix_test1 partition of prefix_test for
> values from (1, 'a') to (1, 'b');
> postgres=# create table prefix_test2 partition of prefix_test for
> values from (2, 'a') to (2, 'b');
> postgres=# set enable_partition_pruning to on;
>
> postgres=# explain select * from prefix_test where a <= 2 and b = 'a';
> QUERY PLAN
> --------------------------------------------------------------------------
> Seq Scan on prefix_test2 prefix_test (cost=0.00..29.05 rows=2 width=36)
> Filter: ((a <= 2) AND ((b)::text = 'a'::text))
> (2 rows)
>
> Will do a bit more investigation about this.

I think get_matching_range_bounds() assumes that if opstrategy (ie,
the strategy of the last expression of the given lookup key) is the =
strategy, all the expressions of the lookup key have the = strategy.
I’m not sure we can extend that function to support cases where
preceding expressions have strategies other than the = strategy like a
<= 2 and b = ’a’ in the above (or a <= 1 and b = ’a’ in your patch),
but if so, doing that seems to me more like an improvement than a fix.
I think a simple fix for this issue would be to just give up on
generating pruning steps if prefix contains no clauses, like the
attached.

Best regards,
Etsuro Fujita