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 <[email protected]> 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 <[email protected]> wrote: >> >> Hi Ewan, >> >> Ewan Young <[email protected]> 于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
