Re: RANGE partition pruning can still exclude the default partition

On Thu, 6 Aug 2026 at 22:47, Ewan Young <kdbase(dot)hack(at)gmail(dot)com> wrote:
> 0001 tests the first key of the bound instead of the last key of the lookup
> value; the code change is two lines. I did not delete the blocks the way
> 709dfd27f14 did, because when the first key really is unbounded there is
> nothing beyond it and removing them would scan the default partition for no
> reason. Tests cover a trailing MAXVALUE, the MINVALUE mirror, a genuinely
> unbounded first key, and the no-default case; the last two are unchanged by
> the patch, on purpose.
>
> 0001 alone gives up one pruning opportunity: "WHERE a = 19 AND b >= 5" now
> also scans the default partition. That pruning was unsound where it
> happened - "WHERE a >= 19 AND b >= 5" makes the identical call (strategy
> >=, values (19, 5)), and there the rows above the bound do qualify; master
> answers it with 2 rows where 3 is correct. The function cannot tell the
> two apart.
>
> 0002 recovers it where the knowledge exists. In the equality-prefix path
> the code already skips the offset below the smallest matching bound when
> that bound is (prefix, MINVALUE); the mirror was missing, so a greatest
> matching bound of (prefix, MAXVALUE) still pulled in the offset above it.
> With 0002, "WHERE a = 19 AND b >= 5" scans t1 only, while "a >= 19 AND
> b >= 5" and plain "a = 19" still scan the default partition, the latter
> because a NULL in a later key is routed there. 0002 is a pruning
> improvement rather than a correctness fix, hence the split.

I spent some time looking at these patches and I believe both the
changes together make sense. I feel the commentary around how all this
works is a bit lacking in general, however.

The reason to switch to only checking for MINVALUE / MAXVALUE on the
first partition key is because you've made it so the
BTEqualStrategyNumber code handles doing this for subsequent keys, and
since the step generation always generates steps with leading subsets
of the given values, the code you added to BTEqualStrategyNumber will
trim off the default when the final partition found by the
BTEqualStrategyNumber code for the leading prefix has a MAXVALUE
clause. For example, if you have a query doing "WHERE a = 19 AND b >=
5", the pruning code will generate 2 pruning steps, one for "a = 19"
and the 2nd with the full "a = 19 AND b >= 5". If there had been 3
clauses, then steps would be generated for each intermediate prefix of
the quals matching each partition key (i.e 3 pruning steps). When we
get the pruning results for the "a = 19" step, that hits the
BTEqualStrategyNumber code and because we don't have quals there for
all partition key columns, we must find all partitions that match "a =
19". There's a while loop doing that in the BTEqualStrategyNumber
case.

Because we generate steps for each combination of leading prefix of
clauses matching the partition key, the leading "a = 19" step going
through the BTEqualStrategyNumber code will detect if the final
partition we land on that matches "a = 19" has a MAXVALUE, and not
include the DEFAULT. Since the result of this step and the step
containing the quals for both keys are intersected, you only need to
prune the default partition in one of the steps for it not to appear
in the intersected result of all pruning steps.

I did modify the patch slightly so that the MINVALUE / MAXVALUE code
for the 0th partition key only executes when nvalues == 1. In the
example above, this means it only executes in the "a = 19" step and
not the "a = 19 and b >= 5" step. This doesn't make any difference to
the final result, but I think it makes more sense to do this as it
means we only apply that optimisation once rather than doing it
several times redundantly. I also tried to explain what's going on in
a paragraph of comments.

I've attached an AI-written and hand-modified fuzzing tool that I used
to check your patch doesn't prune any partitions that should not be
pruned. This works by creating random permutations of RANGE
partitioned tables and then inserting a small number of records to
each partition. It then runs a few queries on that partitioned table
with various WHERE clauses and checks the number of records returned
with enable_partition_pruning both on and off, then checks the number
of records is the same in each instance. This would find cases where
partitions are pruned by mistake. I hand-modified the tool to write
the EXPLAIN output to pp_fuzz.debug_output and then I compared the
output from your patched version with master and checked to ensure the
results all made sense.

Please have a look at v3 and confirm you're happy with the adjustments
I've made.

David