Fix incorrect multi-column RANGE partition pruning When performing partition pruning with a RANGE partitioned table where the pruning quals are only present for a leading prefix of the partition key, it was possible that partition pruning would accidentally prune away some partitions which shouldn't be pruned and include some partitions that were not needed.
This happened due to an incorrectly coded loop bound which was terminating the loop when the bound reached the first or last element in the partition bound array. This resulted in those end elements not being checked in cases where they should be checked. It appears that it might have been coded this way to avoid stepping off the array, but that was done incorrectly as it failed to take into account the direction of travel through the array (the loop can go forwards or backwards). I.e., it's valid to loop when 'off' is the last element if we're going backwards through the array, and valid to loop if 'off' is 0 and we're looping forward through the array, but the code as it was didn't allow that. Here we fix this by moving the loop condition check to after we've calculated the array element to process, and break from the loop if that element is beyond either end of the array. Example of accidentally pruned partition: p: partition by range (a, b); p1: for values from (1, 4) to (1, 7); p2: for values from (1, 7) to (3, 8); p3: for values from (4, 8) to (6, 9); def: default; select * from p where a <= 1; Here p2 was pruned by mistake. Example of accidentally not pruning a partition: p: partition by range (a, b); p1: for values from (7, 2) to (7, 7); def: default; select * from p where a > 7; No partitions would be pruned in this case, despite it being impossible for matching rows to exist in p1. Author: David Rowley <[email protected]> Reviewed-by: Ayush Tiwari <[email protected]> Reviewed-by: Tender Wang <[email protected]> Discussion: https://postgr.es/m/caaphdvp5ne9awah-tg1lke-uslz3nwwlwtudp5nt7ypktcf...@mail.gmail.com Backpatch-through: 14 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7c503d683ca06d14052614ff39ec8648d1d722d0 Modified Files -------------- src/backend/partitioning/partprune.c | 14 +++++++++--- src/test/regress/expected/partition_prune.out | 33 +++++++++++++++++++++++++++ src/test/regress/sql/partition_prune.sql | 20 ++++++++++++++++ 3 files changed, 64 insertions(+), 3 deletions(-)
