summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/partition_prune.out
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-21 05:05:26 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-21 05:05:26 +0000
commite75d99818dd3940be997520e64db8c9e3b207e39 (patch)
tree0003ca0de74fcc8d18433e34ea68d2e7aaf06b7c /src/test/regress/expected/partition_prune.out
parentReleasing progress-linux version 15.6-0+deb12u1~progress6.99u1. (diff)
downloadpostgresql-15-e75d99818dd3940be997520e64db8c9e3b207e39.tar.xz
postgresql-15-e75d99818dd3940be997520e64db8c9e3b207e39.zip
Merging upstream version 15.7.
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'src/test/regress/expected/partition_prune.out')
-rw-r--r--src/test/regress/expected/partition_prune.out107
1 files changed, 106 insertions, 1 deletions
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index c5bc6c3..fa1f2f2 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');
@@ -2709,6 +2812,7 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_a1_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
+ Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-> Materialize (actual rows=1 loops=1)
@@ -2724,9 +2828,10 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
Index Cond: (a = 1)
-> Bitmap Heap Scan on ab_a1_b3 ab_3 (actual rows=0 loops=1)
Recheck Cond: (a = 1)
+ Heap Blocks: exact=1
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
Index Cond: (a = 1)
-(34 rows)
+(36 rows)
table ab;
a | b