Re: Yet another issue with step generation in partition pruning

Fujita-san,

Thanks a lot for your time on fixing these multi-column range
partition pruning issues. I'm sorry that I failed to notice the
previous two reports on -bugs for which you committed a fix last week.

On Tue, Aug 4, 2020 at 9:46 PM Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com> wrote:
> Commit 13838740f fixed some issues with step generation in partition
> pruning, but as I mentioned in [1], I noticed that there is yet
> another issue: get_steps_using_prefix() assumes that clauses in the
> passed-in prefix list are sorted in ascending order of their partition
> key numbers, but the caller (i.e., gen_prune_steps_from_opexps())
> doesn’t ensure that in the case of range partitioning, leading to an
> assertion failure. Here is an example causing such a failure, which
> would happen with/without that commit:
>
> create table rp_prefix_test2 (a int, b int, c int) partition by range (a, b, c);
> create table rp_prefix_test2_p1 partition of rp_prefix_test2 for
> values from (1, 1, 0) to (1, 1, 10);
> create table rp_prefix_test2_p2 partition of rp_prefix_test2 for
> values from (2, 2, 0) to (2, 2, 10);
> select * from rp_prefix_test2 where a <= 1 and b <= 1 and b = 1 and c <= 0;
>
> I don't think we write queries like this, but for this query, the
> caller would create the prefix list for the last partition key “c”
> {b=1, a<=1, b<=1} (the clauses are not sorted properly!), then calling
> get_steps_using_prefix(), which leads to an assertion failure.

That analysis is spot on.

> Attached is a patch for fixing this issue.

I have looked at the patch and played around with it using the
regression tests you've added recently. I was not able to find any
results that looked surprising.

Thanks again.

--
Amit Langote
EnterpriseDB: http://www.enterprisedb.com