Ayush Tiwari <[email protected]> 于2026年8月25日周二 22:24写道：
>
>
> I tested v1 and it fixes the reported wrong-result case.
>
> Using the same partition layout, I also tried:
>
> EXPLAIN (COSTS OFF) SELECT * FROM mc2ap WHERE a < 1;
>
> Master scans only mc2ap_def, while v1 scans both mc2ap1 and
> mc2ap_def. The result is unchanged, but it seems that the new check
> rejects nextoff == 0 even though that is a valid index into
> boundinfo->datums.
>
> Should the check use the actual array bounds instead?
>
> if (nextoff < 0 || nextoff >= boundinfo->ndatums)
>
> Would the same apply to the similar loop in the
> BTGreaterStrategyNumber case?
Hi,
I tested the similar loop in the BTGreaterStrategyNumber case and
confirmed that it has the same issue. Here is a small example:
```
CREATE TABLE r2 (a int, b int) PARTITION BY RANGE (a, b);
CREATE TABLE r2_p1 PARTITION OF r2
FOR VALUES FROM (1, 10) TO (1, 20);
CREATE TABLE r2_def PARTITION OF r2 DEFAULT;
EXPLAIN (COSTS OFF)
SELECT * FROM r2 WHERE a > 1;
```
With v1, both `r2_p1` and `r2_def` are scanned:
```
Append
-> Seq Scan on r2_p1 r2_1
Filter: (a > 1)
-> Seq Scan on r2_def r2_2
Filter: (a > 1)
```
`r2_p1` cannot contain any rows satisfying `a > 1`, so it should be
pruned. In this case, the two range bounds `(1, 10)` and `(1, 20)`
both compare equal to the lookup prefix `{1}`. Walking to
`nextoff == boundinfo->ndatums - 1` is therefore necessary to find
the appropriate edge of the matching bounds.
So I agree that the bounds check should be against the actual
`boundinfo->datums` array bounds, and that the same change is needed
for the similar loop in the BTGreaterStrategyNumber case.
I wrote the code to cover >= cases, as in the attached v2 (including
David's v1 code), and added the test case.
While looking at this, I also found the comment above the
BTLessStrategyNumber case a little misleading for prefix lookups:
```
/*
* Look for the greatest bound that is < or <= lookup value and
* set maxoff to its offset.
*/
```
When `nvalues < partnatts`, multiple range bounds can compare equal
to the lookup prefix, and `partition_range_datum_bsearch()` may
return one of those matching bounds rather than the greatest one.
The following loop then walks the adjacent bounds to find the
appropriate edge of that group.
Perhaps the comment could mention this distinction. For example,
something along the lines of:
```
/*
* Locate a bound at or below the lookup value. If the lookup
* contains only a prefix of the partition keys, multiple bounds
* may compare equal to it, so adjust off below to find the
* appropriate edge of the matching bounds.
*/
```
Similar wording might also be useful for the corresponding
BTGreaterStrategyNumber case.
The comment adjustments are not in the patch. I'm not sure there is a
need to adjust it.
--
Thanks,
Tender Wang
From bd50977684239bf5134e64d5514a60ea377ea234 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Tue, 25 Aug 2026 20:23:54 +1200
Subject: [PATCH v2] Fix incorrect multi-column RANGE partition pruning
When performing partition pruning with a RANGE partitioned table where
the pruning quals are only present for a leading prefix of the partition
key and when using a <= operator, it was possible that partition pruning
would accidentally prune away some partitions which shouldn't be pruned
when those partitions matched the given qual.
This happened due to an incorrectly coded loop bound which was checking
the bound was within the required range. The loop failed to consider
that the loop body would adjust the bound offset to a value within
range. Here we fix this by moving the loop condition so we break out of
the loop if the adjusted offset is not within the required range.
p: partition by range (a, b);
p1: for values from (1, 4) to (1, 7);
p2: for values from (1, 7) to (3, 8);
p3: for values from (4, 8) to (6, 9);
def: default;
select * from p where a <= 1;
Here p2 was pruned by mistake.
---
src/backend/partitioning/partprune.c | 13 ++++--
src/test/regress/expected/partition_prune.out | 45 +++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 24 ++++++++++
3 files changed, 79 insertions(+), 3 deletions(-)
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 06566c8ce8a..87516792434 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -3206,12 +3206,14 @@ get_matching_range_bounds(PartitionPruneContext
*context,
* of smallest such bound) or find the
smallest one that's
* greater than the lookup values and
set minoff to that.
*/
- while (off >= 1 && off <
boundinfo->ndatums - 1)
+ while (true)
{
int32 cmpval;
int nextoff;
nextoff = inclusive ? off - 1 :
off + 1;
+ if (nextoff < 0 || nextoff >=
boundinfo->ndatums)
+ break;
cmpval =
partition_rbound_datum_cmp(partsupfunc,
partcollation,
@@ -3265,16 +3267,21 @@ get_matching_range_bounds(PartitionPruneContext
*context,
if (off >= 0)
{
/*
- * See the comment above.
+ * As above, check adjacent bounds to see if
the bound is
+ * equal to the lookup value.
*/
if (is_equal && nvalues < partnatts)
{
- while (off >= 1 && off <
boundinfo->ndatums - 1)
+ while (true)
{
int32 cmpval;
int nextoff;
nextoff = inclusive ? off + 1 :
off - 1;
+
+ if (nextoff < 1 || nextoff >=
boundinfo->ndatums - 1)
+ break;
+
cmpval =
partition_rbound_datum_cmp(partsupfunc,
partcollation,
boundinfo->datums[nextoff],
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index aa821646011..71bd009d0d2 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1099,6 +1099,26 @@ explain (costs off) select * from mc2p where b is null;
Filter: (b IS NULL)
(2 rows)
+create table mc2ap (a int, b int) partition by range (a, b);
+create table mc2ap1 partition of mc2ap for values from (1, 4) to (1, 7);
+create table mc2ap2 partition of mc2ap for values from (1, 7) to (3, 8);
+create table mc2ap3 partition of mc2ap for values from (4, 8) to (6, 9);
+create table mc2ap_def partition of mc2ap default;
+-- Ensure we scan all partitions apart from mc2ap3
+explain (costs off) select count(*) from mc2ap where a <= 1;
+ QUERY PLAN
+-------------------------------------------
+ Aggregate
+ -> Append
+ -> Seq Scan on mc2ap1 mc2ap_1
+ Filter: (a <= 1)
+ -> Seq Scan on mc2ap2 mc2ap_2
+ Filter: (a <= 1)
+ -> Seq Scan on mc2ap_def mc2ap_3
+ Filter: (a <= 1)
+(8 rows)
+
+drop table mc2ap;
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;
@@ -5002,3 +5022,28 @@ select * from (select a, b from phv_boolpart) t
(2 rows)
drop table phv_boolpart;
+-- Check prefix pruning when matching bounds reach the end of datums[].
+create table mc2p_edge (a int, b int) partition by range (a, b);
+create table mc2p_edge1 partition of mc2p_edge
+ for values from (1, 10) to (1, 20);
+create table mc2p_edge_default partition of mc2p_edge default;
+-- only the default partition can contain rows with a > 1
+explain (costs off) select * from mc2p_edge where a > 1;
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on mc2p_edge_default mc2p_edge
+ Filter: (a > 1)
+(2 rows)
+
+-- both partitions may contain rows with a >= 1
+explain (costs off) select * from mc2p_edge where a >= 1;
+ QUERY PLAN
+-------------------------------------------------
+ Append
+ -> Seq Scan on mc2p_edge1 mc2p_edge_1
+ Filter: (a >= 1)
+ -> Seq Scan on mc2p_edge_default mc2p_edge_2
+ Filter: (a >= 1)
+(5 rows)
+
+drop table mc2p_edge;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index dac673ef80a..85fce32f86b 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -192,6 +192,17 @@ explain (costs off) select * from mc2p where a is null and
b = 1;
explain (costs off) select * from mc2p where a is null;
explain (costs off) select * from mc2p where b is null;
+create table mc2ap (a int, b int) partition by range (a, b);
+create table mc2ap1 partition of mc2ap for values from (1, 4) to (1, 7);
+create table mc2ap2 partition of mc2ap for values from (1, 7) to (3, 8);
+create table mc2ap3 partition of mc2ap for values from (4, 8) to (6, 9);
+create table mc2ap_def partition of mc2ap default;
+
+-- Ensure we scan all partitions apart from mc2ap3
+explain (costs off) select count(*) from mc2ap where a <= 1;
+
+drop table mc2ap;
+
-- boolean partitioning
create table boolpart (a bool) partition by list (a);
create table boolpart_default partition of boolpart default;
@@ -1538,3 +1549,16 @@ select * from (select a, b from phv_boolpart) t
group by grouping sets (a, b);
drop table phv_boolpart;
+
+-- Check prefix pruning when matching bounds reach the end of datums[].
+create table mc2p_edge (a int, b int) partition by range (a, b);
+create table mc2p_edge1 partition of mc2p_edge
+ for values from (1, 10) to (1, 20);
+create table mc2p_edge_default partition of mc2p_edge default;
+
+-- only the default partition can contain rows with a > 1
+explain (costs off) select * from mc2p_edge where a > 1;
+-- both partitions may contain rows with a >= 1
+explain (costs off) select * from mc2p_edge where a >= 1;
+drop table mc2p_edge;
+
--
2.43.0
