Re: RANGE partition pruning can still exclude the default partition

Hi Radim,

Thanks for testing this -- that's exactly the case this thread is
about, and it's good to have independent confirmation from your
harness.

Just to untangle which fix is which: 6e5d5680b555 (David Rowley,
"Fix incorrect multi-column RANGE partition pruning") is a separate
bug in the loop bounds of get_matching_range_bounds(). It doesn't
touch the MINVALUE/MAXVALUE trailing-key blocks at the end of that
function, which is where your case comes from -- so your query is
still wrong on master even with it applied, as you found. The
mc2ap/mc2bp cases are Rowley's, and they're orthogonal to this one.

The fix for your exact query is the patch already on this thread (the
two-line change to test the *first* key of the bound instead of the
last key of the lookup value, plus Tender's comment tweaks in v2-0001).
I re-verified on current master (a9eebaf3735, i.e. with 6e5d5680b555
already in): your query returns 0 unpatched and 1 with the patch, and
Rowley's mc2ap cases still prune correctly. The patch's code hunk
applies cleanly over 6e5d5680b555; only the regression-test hunks
conflict because both add to partition_prune.sql, so I'll post a
rebased v3 that resolves that.

I'd gladly take you up on the offer to run a broader set of partitioned
queries against the rebased patch.

On Tue, Sep 1, 2026 at 8:10 PM Radim Marek <radim(at)boringsql(dot)com> wrote:
>
> Hey,
>
> I'm not sure the patch is a full fix, even with 6e5d5680b555 applied.
>
> CREATE TABLE t (a int, b int) PARTITION BY RANGE (a, b);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (13, 0) TO (19, MAXVALUE);
> CREATE TABLE td PARTITION OF t DEFAULT;
> INSERT INTO t VALUES (32, 5);
>
> SELECT count(*) FROM t WHERE a = 32; -- 1
> SELECT count(*) FROM t WHERE a = 32 AND b >= -7; -- 0, expected 1
>
> SET enable_partition_pruning = off;
> SELECT count(*) FROM t WHERE a = 32 AND b >= -7; -- 1
>
> The two cases added by the commit (mc2ap, mc2bp) behave as described, so this looks like a case it doesn't cover rather than a problem with it.
>
> I only tested master, so I do not know how far back this might go. Found while testing my planner-regression harness against recent commits.
>
> Happy to test a patch against a broader set of partitioned queries if that would help.
>
> Regards,
>
> Radim
>
>
>
> On Mon, 10 Aug 2026 at 07:34, Tender Wang <tndrwang(at)gmail(dot)com> wrote:
>>
>> Hi Ewan,
>>
>> Ewan Young <kdbase(dot)hack(at)gmail(dot)com> 于2026年8月6日周四 18:47写道：
>> >
>> > Hi,
>> >
>> > Issue still happens on master as of 9b917a93116, i.e. with 709dfd27f14
>> > already applied. It is a different instance of the same defect, in another
>> > branch of the same function, and it silently returns wrong results.
>> >
>> > CREATE TABLE t (a int, b int) PARTITION BY RANGE (a, b);
>> > CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (13, 0) TO (19, MAXVALUE);
>> > CREATE TABLE td PARTITION OF t DEFAULT;
>> > INSERT INTO t VALUES (32, 5);
>> >
>> > SELECT count(*) FROM t WHERE a = 32; -- 1, correct
>> > SELECT count(*) FROM t WHERE a = 32 AND b >= -7; -- 0, should be 1
>>
>> Nice catch.
>>
>> >
>> > Adding a qual that is true for every matching row makes the row disappear;
>> > the plan is a One-Time Filter: false. It takes a multi-column range key, a
>> > bound unbounded in a trailing key but finite in the first key, and a query
>> > constraining all key columns - with nvalues < partnatts,
>> > get_matching_range_bounds() sets scan_default up front, which masks it.
>> > DELETE and UPDATE silently skip rows as well, run-time pruning is affected,
>> > and FROM (13, MINVALUE) is the mirror image. A layout that hits this in
>> > the wild is RANGE (tenant_id, ts) with FROM (N, MINVALUE) TO (N, MAXVALUE)
>> > per tenant plus a default partition.
>> >
>> > At the end of get_matching_range_bounds() two adjustments drop the extreme
>> > bound offset when no partition covers the key space beyond it:
>> >
>> > int lastkey = nvalues - 1;
>> >
>> > if (boundinfo->kind[maxoff - 1][lastkey] == PARTITION_RANGE_DATUM_MAXVALUE)
>> > maxoff--;
>> >
>> > Only a bound unbounded in its *first* key has no key space beyond it.
>> > TO (19, MAXVALUE) merely means "unbounded within a = 19"; above it is real
>> > key space owned by the default partition. Dropping the offset also drops
>> > the last signal that the default has to be scanned: since 489247b0e615
>> > get_matching_partitions() derives that from a returned offset whose
>> > partindices[] entry is -1, and scan_default is not set on this path.
>>
>> Yes, I came to the same conclusion.
>>
>> > 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.
>>
>> The fix WFM. I tweaked the comments a little, as in the attached v2-0001 patch.
>> Others look good to me.
>>
>>
>>
>> --
>> Thanks,
>> Tender Wang

--
Regards,
Ewan Young