summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/partition_prune.out103
-rw-r--r--src/test/regress/sql/partition_prune.sql30
2 files changed, 133 insertions, 0 deletions
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9a4c48c0556..b41950d923b 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -1169,6 +1169,57 @@ select * from boolpart where a is not unknown;
t
(2 rows)
+-- try some other permutations with a NULL partition instead of a DEFAULT
+delete from boolpart where a is null;
+create table boolpart_null partition of boolpart for values in (null);
+insert into boolpart values(null);
+explain (costs off) select * from boolpart where a is not true;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on boolpart_f boolpart_1
+ Filter: (a IS NOT TRUE)
+ -> Seq Scan on boolpart_null boolpart_2
+ Filter: (a IS NOT TRUE)
+(5 rows)
+
+explain (costs off) select * from boolpart where a is not true and a is not false;
+ QUERY PLAN
+--------------------------------------------------
+ Seq Scan on boolpart_null boolpart
+ Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
+(2 rows)
+
+explain (costs off) select * from boolpart where a is not false;
+ QUERY PLAN
+--------------------------------------------
+ Append
+ -> Seq Scan on boolpart_t boolpart_1
+ Filter: (a IS NOT FALSE)
+ -> Seq Scan on boolpart_null boolpart_2
+ Filter: (a IS NOT FALSE)
+(5 rows)
+
+select * from boolpart where a is not true;
+ a
+---
+ f
+
+(2 rows)
+
+select * from boolpart where a is not true and a is not false;
+ a
+---
+
+(1 row)
+
+select * from boolpart where a is not false;
+ a
+---
+ t
+
+(2 rows)
+
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
-- code for it, so we'd better test it.
create table iboolpart (a bool) partition by list ((not a));
@@ -1315,11 +1366,37 @@ select * from iboolpart where a is not unknown;
f
(2 rows)
+-- Try some other permutations with a NULL partition instead of a DEFAULT
+delete from iboolpart where a is null;
+create table iboolpart_null partition of iboolpart for values in (null);
+insert into iboolpart values(null);
+-- Pruning shouldn't take place for these. Just check the result is correct
+select * from iboolpart where a is not true;
+ a
+---
+ f
+
+(2 rows)
+
+select * from iboolpart where a is not true and a is not false;
+ a
+---
+
+(1 row)
+
+select * from iboolpart where a is not false;
+ a
+---
+ t
+
+(2 rows)
+
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
+create table boolrangep_null partition of boolrangep default;
-- try a more complex case that's been known to trip up pruning in the past
explain (costs off) select * from boolrangep where not a and not b and c = 25;
QUERY PLAN
@@ -1328,6 +1405,32 @@ explain (costs off) select * from boolrangep where not a and not b and c = 25;
Filter: ((NOT a) AND (NOT b) AND (c = 25))
(2 rows)
+-- ensure we prune boolrangep_tf
+explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
+ QUERY PLAN
+------------------------------------------------------------
+ Append
+ -> Seq Scan on boolrangep_ff1 boolrangep_1
+ Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
+ -> Seq Scan on boolrangep_ff2 boolrangep_2
+ Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
+ -> Seq Scan on boolrangep_ft boolrangep_3
+ Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
+ -> Seq Scan on boolrangep_null boolrangep_4
+ Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
+(9 rows)
+
+-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
+explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
+ QUERY PLAN
+-------------------------------------------------------------
+ Append
+ -> Seq Scan on boolrangep_tf boolrangep_1
+ Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
+ -> Seq Scan on boolrangep_null boolrangep_2
+ Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
+(5 rows)
+
-- test scalar-to-array operators
create table coercepart (a varchar) partition by list (a);
create table coercepart_ab partition of coercepart for values in ('ab');
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 7bf3920827f..7ba6a9ff370 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -178,6 +178,19 @@ select * from boolpart where a is not true and a is not false;
select * from boolpart where a is unknown;
select * from boolpart where a is not unknown;
+-- try some other permutations with a NULL partition instead of a DEFAULT
+delete from boolpart where a is null;
+create table boolpart_null partition of boolpart for values in (null);
+insert into boolpart values(null);
+
+explain (costs off) select * from boolpart where a is not true;
+explain (costs off) select * from boolpart where a is not true and a is not false;
+explain (costs off) select * from boolpart where a is not false;
+
+select * from boolpart where a is not true;
+select * from boolpart where a is not true and a is not false;
+select * from boolpart where a is not false;
+
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
-- code for it, so we'd better test it.
create table iboolpart (a bool) partition by list ((not a));
@@ -204,15 +217,32 @@ select * from iboolpart where a is not true and a is not false;
select * from iboolpart where a is unknown;
select * from iboolpart where a is not unknown;
+-- Try some other permutations with a NULL partition instead of a DEFAULT
+delete from iboolpart where a is null;
+create table iboolpart_null partition of iboolpart for values in (null);
+insert into iboolpart values(null);
+
+-- Pruning shouldn't take place for these. Just check the result is correct
+select * from iboolpart where a is not true;
+select * from iboolpart where a is not true and a is not false;
+select * from iboolpart where a is not false;
+
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
+create table boolrangep_null partition of boolrangep default;
-- try a more complex case that's been known to trip up pruning in the past
explain (costs off) select * from boolrangep where not a and not b and c = 25;
+-- ensure we prune boolrangep_tf
+explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
+
+-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
+explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
+
-- test scalar-to-array operators
create table coercepart (a varchar) partition by list (a);
create table coercepart_ab partition of coercepart for values in ('ab');
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-14 19:29:07 +0000