On Thu, 6 Aug 2026 at 22:47, Ewan Young <[email protected]> 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
partprune_fuzz.sql
Description: Binary data
v3-0001-Fix-pruning-of-DEFAULT-partition-in-RANGE-partiti.patch
Description: Binary data
