diff options
author | David Rowley <drowley@postgresql.org> | 2023-04-14 16:20:27 +1200 |
---|---|---|
committer | David Rowley <drowley@postgresql.org> | 2023-04-14 16:20:27 +1200 |
commit | e0693faf797f997f45bee8e572e8b4288cc7eaeb (patch) | |
tree | 55e22bc0e1ac73ecc7abdb12ff26b1d6fe5201cd /src/test/regress/sql | |
parent | c7dc56bd6b36b4d6b3c6cc1bb4735f7777272ca3 (diff) | |
download | postgresql-e0693faf797f997f45bee8e572e8b4288cc7eaeb.tar.gz |
Fix incorrect partition pruning logic for boolean partitioned tables
The partition pruning logic assumed that "b IS NOT true" was exactly the
same as "b IS FALSE". This is not the case when considering NULL values.
Fix this so we correctly include any partition which could hold NULL
values for the NOT case.
Additionally, this fixes a bug in the partition pruning code which handles
partitioned tables partitioned like ((NOT boolcol)). This is a seemingly
unlikely schema design, and it was untested and also broken.
Here we add tests for the ((NOT boolcol)) case and insert some actual data
into those tables and verify we do get the correct rows back when running
queries. I've also adjusted the existing boolpart tests to include some
data and verify we get the correct results too.
Both the bugs being fixed here could lead to incorrect query results with
fewer rows being returned than expected. No additional rows could have
been returned accidentally.
In passing, remove needless ternary expression. It's more simple just to
pass !is_not_clause to makeBoolConst(). It makes sense to do this so the
code is consistent with the bug fix in the "else if" condition just below.
David Kimura did submit a patch to fix the first of the issues here, but
that's not what's being committed here.
Reported-by: David Kimura
Reviewed-by: Richard Guo, David Kimura
Discussion: https://postgr.es/m/CAHnPFjQ5qxs6J_p+g8=ww7GQvfn71_JE+Tygj0S7RdRci1uwPw@mail.gmail.com
Backpatch-through: 11, all supported versions
Diffstat (limited to 'src/test/regress/sql')
-rw-r--r-- | src/test/regress/sql/partition_prune.sql | 38 |
1 files changed, 37 insertions, 1 deletions
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index fb0583f924..d1c60b8fe9 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -158,6 +158,7 @@ create table boolpart (a bool) partition by list (a); create table boolpart_default partition of boolpart default; create table boolpart_t partition of boolpart for values in ('true'); create table boolpart_f partition of boolpart for values in ('false'); +insert into boolpart values (true), (false), (null); explain (costs off) select * from boolpart where a in (true, false); explain (costs off) select * from boolpart where a = false; @@ -168,6 +169,41 @@ explain (costs off) select * from boolpart where a is not true and a is not fals explain (costs off) select * from boolpart where a is unknown; explain (costs off) select * from boolpart where a is not unknown; +select * from boolpart where a in (true, false); +select * from boolpart where a = false; +select * from boolpart where not a = false; +select * from boolpart where a is true or a is not true; +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 unknown; +select * from boolpart where a is not unknown; + +-- 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)); +create table iboolpart_default partition of iboolpart default; +create table iboolpart_f partition of iboolpart for values in ('true'); +create table iboolpart_t partition of iboolpart for values in ('false'); +insert into iboolpart values (true), (false), (null); + +explain (costs off) select * from iboolpart where a in (true, false); +explain (costs off) select * from iboolpart where a = false; +explain (costs off) select * from iboolpart where not a = false; +explain (costs off) select * from iboolpart where a is true or a is not true; +explain (costs off) select * from iboolpart where a is not true; +explain (costs off) select * from iboolpart where a is not true and a is not false; +explain (costs off) select * from iboolpart where a is unknown; +explain (costs off) select * from iboolpart where a is not unknown; + +select * from iboolpart where a in (true, false); +select * from iboolpart where a = false; +select * from iboolpart where not a = false; +select * from iboolpart where a is true or a is not true; +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 unknown; +select * from iboolpart where a is not unknown; + 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); @@ -294,7 +330,7 @@ create table rparted_by_int2_maxvalue partition of rparted_by_int2 for values fr -- all partitions but rparted_by_int2_maxvalue pruned explain (costs off) select * from rparted_by_int2 where a > 100_000_000_000_000; -drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; +drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, iboolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2; -- -- Test Partition pruning for HASH partitioning |