Re: BUG #16500: SQL Abend. select multi_key_columns_range_partition_table

> On Sun, Jul 05, 2020 at 04:45:40PM +0900, Etsuro Fujita wrote:
> 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.

Yes, I agree. Not generating any pruning steps if prefix has no clauses
was my first idea, but looking at attached patch I've apparently missed
one part in the implementation and was under the false impression it
wouldn't work.