Re: RANGE partition pruning can still exclude the default partition

Thanks for the review and for tightening this up.

v3 looks good to me. The nvalues == 1 restriction on the first-key
MINVALUE/MAXVALUE trim is a clear improvement: the leading single-key
step already trims the default there, and the BTEqualStrategyNumber
block handles the multi-key prefixes, so the intersection removes the
default without repeating the first-key check in every step. Your
comment rewrite explains the interaction much better than my original;
agreed it was too thin.

Happy with the adjustments -- thanks for picking this up.

On Fri, Sep 18, 2026 at 4:55 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> 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

--
Regards,
Ewan Young