Skip to content

Commit 3ffcd24

Browse files
david-rowleydavid-rowley
committed
Fix incorrect pruning of NULL partition for boolean IS NOT clauses
Partition pruning wrongly assumed that, for a table partitioned on a boolean column, a clause in the form "boolcol IS NOT false" and "boolcol IS NOT true" could be inverted to correspondingly become "boolcol IS true" and "boolcol IS false". These are not equivalent as the NOT version matches the opposite boolean value *and* NULLs. This incorrect assumption meant that partition pruning pruned away partitions that could contain NULL values. Here we fix this by correctly not pruning partitions which could store NULLs. To be affected by this, the table must be partitioned by a NULLable boolean column and queries would have to contain "boolcol IS NOT false" or "boolcol IS NOT true". This could result in queries filtering out NULL values with a LIST partitioned table and "ERROR: invalid strategy number 0" for RANGE and HASH partitioned tables. Reported-by: Alexander Lakhin Bug: #18344 Discussion: https://postgr.es/m/18344-8d3f00bada6d09c6@postgresql.org Backpatch-through: 12
1 parent f2c7a6e commit 3ffcd24

File tree

3 files changed

+187
-2
lines changed

3 files changed

+187
-2
lines changed

‎src/backend/partitioning/partprune.c

Lines changed: 54 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1724,11 +1724,63 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
17241724
{
17251725
PartClauseInfo *partclause;
17261726

1727+
/*
1728+
* For bool tests in the form of partkey IS NOT true and IS NOT false,
1729+
* we invert these clauses. Effectively, "partkey IS NOT true"
1730+
* becomes "partkey IS false OR partkey IS NULL". We do this by
1731+
* building an OR BoolExpr and forming a clause just like that and
1732+
* punt it off to gen_partprune_steps_internal() to generate pruning
1733+
* steps.
1734+
*/
1735+
if (noteq)
1736+
{
1737+
List *new_clauses;
1738+
List *or_clause;
1739+
BooleanTest *new_booltest = (BooleanTest *) copyObject(clause);
1740+
NullTest *nulltest;
1741+
1742+
/* We expect 'noteq' to only be set to true for BooleanTests */
1743+
Assert(IsA(clause, BooleanTest));
1744+
1745+
/* reverse the bool test */
1746+
if (new_booltest->booltesttype == IS_NOT_TRUE)
1747+
new_booltest->booltesttype = IS_FALSE;
1748+
else if (new_booltest->booltesttype == IS_NOT_FALSE)
1749+
new_booltest->booltesttype = IS_TRUE;
1750+
else
1751+
{
1752+
/*
1753+
* We only expect match_boolean_partition_clause to match for
1754+
* IS_NOT_TRUE and IS_NOT_FALSE. IS_NOT_UNKNOWN is not
1755+
* supported.
1756+
*/
1757+
Assert(false);
1758+
}
1759+
1760+
nulltest = makeNode(NullTest);
1761+
nulltest->arg = copyObject(partkey);
1762+
nulltest->nulltesttype = IS_NULL;
1763+
nulltest->argisrow = false;
1764+
nulltest->location = -1;
1765+
1766+
new_clauses = list_make2(new_booltest, nulltest);
1767+
or_clause = list_make1(makeBoolExpr(OR_EXPR, new_clauses, -1));
1768+
1769+
/* Finally, generate steps */
1770+
*clause_steps = gen_partprune_steps_internal(context, or_clause);
1771+
1772+
if (context->contradictory)
1773+
return PARTCLAUSE_MATCH_CONTRADICT; /* shouldn't happen */
1774+
else if (*clause_steps == NIL)
1775+
return PARTCLAUSE_UNSUPPORTED; /* step generation failed */
1776+
return PARTCLAUSE_MATCH_STEPS;
1777+
}
1778+
17271779
partclause = (PartClauseInfo *) palloc(sizeof(PartClauseInfo));
17281780
partclause->keyno = partkeyidx;
17291781
/* Do pruning with the Boolean equality operator. */
17301782
partclause->opno = BooleanEqualOperator;
1731-
partclause->op_is_ne = noteq;
1783+
partclause->op_is_ne = false;
17321784
partclause->expr = expr;
17331785
/* We know that expr is of Boolean type. */
17341786
partclause->cmpfn = part_scheme->partsupfunc[partkeyidx].fn_oid;
@@ -2273,7 +2325,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
22732325
* For LIST and RANGE partitioned tables, callers must ensure that
22742326
* step_nullkeys is NULL, and that prefix contains at least one clause for
22752327
* each of the partition keys prior to the key that 'step_lastexpr' and
2276-
* 'step_lastcmpfn'belong to.
2328+
* 'step_lastcmpfn' belong to.
22772329
*
22782330
* For HASH partitioned tables, callers must ensure that 'prefix' contains at
22792331
* least one clause for each of the partition keys apart from the final key

‎src/test/regress/expected/partition_prune.out

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1149,6 +1149,57 @@ select * from boolpart where a is not unknown;
11491149
t
11501150
(2 rows)
11511151

1152+
-- try some other permutations with a NULL partition instead of a DEFAULT
1153+
delete from boolpart where a is null;
1154+
create table boolpart_null partition of boolpart for values in (null);
1155+
insert into boolpart values(null);
1156+
explain (costs off) select * from boolpart where a is not true;
1157+
QUERY PLAN
1158+
---------------------------------
1159+
Append
1160+
-> Seq Scan on boolpart_f
1161+
Filter: (a IS NOT TRUE)
1162+
-> Seq Scan on boolpart_null
1163+
Filter: (a IS NOT TRUE)
1164+
(5 rows)
1165+
1166+
explain (costs off) select * from boolpart where a is not true and a is not false;
1167+
QUERY PLAN
1168+
--------------------------------------------------
1169+
Seq Scan on boolpart_null
1170+
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1171+
(2 rows)
1172+
1173+
explain (costs off) select * from boolpart where a is not false;
1174+
QUERY PLAN
1175+
----------------------------------
1176+
Append
1177+
-> Seq Scan on boolpart_t
1178+
Filter: (a IS NOT FALSE)
1179+
-> Seq Scan on boolpart_null
1180+
Filter: (a IS NOT FALSE)
1181+
(5 rows)
1182+
1183+
select * from boolpart where a is not true;
1184+
a
1185+
---
1186+
f
1187+
1188+
(2 rows)
1189+
1190+
select * from boolpart where a is not true and a is not false;
1191+
a
1192+
---
1193+
1194+
(1 row)
1195+
1196+
select * from boolpart where a is not false;
1197+
a
1198+
---
1199+
t
1200+
1201+
(2 rows)
1202+
11521203
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
11531204
-- code for it, so we'd better test it.
11541205
create table iboolpart (a bool) partition by list ((not a));
@@ -1295,11 +1346,37 @@ select * from iboolpart where a is not unknown;
12951346
f
12961347
(2 rows)
12971348

1349+
-- Try some other permutations with a NULL partition instead of a DEFAULT
1350+
delete from iboolpart where a is null;
1351+
create table iboolpart_null partition of iboolpart for values in (null);
1352+
insert into iboolpart values(null);
1353+
-- Pruning shouldn't take place for these. Just check the result is correct
1354+
select * from iboolpart where a is not true;
1355+
a
1356+
---
1357+
f
1358+
1359+
(2 rows)
1360+
1361+
select * from iboolpart where a is not true and a is not false;
1362+
a
1363+
---
1364+
1365+
(1 row)
1366+
1367+
select * from iboolpart where a is not false;
1368+
a
1369+
---
1370+
t
1371+
1372+
(2 rows)
1373+
12981374
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
12991375
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
13001376
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
13011377
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
13021378
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
1379+
create table boolrangep_null partition of boolrangep default;
13031380
-- try a more complex case that's been known to trip up pruning in the past
13041381
explain (costs off) select * from boolrangep where not a and not b and c = 25;
13051382
QUERY PLAN
@@ -1308,6 +1385,32 @@ explain (costs off) select * from boolrangep where not a and not b and c = 25;
13081385
Filter: ((NOT a) AND (NOT b) AND (c = 25))
13091386
(2 rows)
13101387

1388+
-- ensure we prune boolrangep_tf
1389+
explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
1390+
QUERY PLAN
1391+
------------------------------------------------------------
1392+
Append
1393+
-> Seq Scan on boolrangep_ff1
1394+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1395+
-> Seq Scan on boolrangep_ff2
1396+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1397+
-> Seq Scan on boolrangep_ft
1398+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1399+
-> Seq Scan on boolrangep_null
1400+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1401+
(9 rows)
1402+
1403+
-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
1404+
explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
1405+
QUERY PLAN
1406+
-------------------------------------------------------------
1407+
Append
1408+
-> Seq Scan on boolrangep_tf
1409+
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1410+
-> Seq Scan on boolrangep_null
1411+
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1412+
(5 rows)
1413+
13111414
-- test scalar-to-array operators
13121415
create table coercepart (a varchar) partition by list (a);
13131416
create table coercepart_ab partition of coercepart for values in ('ab');

‎src/test/regress/sql/partition_prune.sql

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -170,6 +170,19 @@ select * from boolpart where a is not true and a is not false;
170170
select * from boolpart where a is unknown;
171171
select * from boolpart where a is not unknown;
172172

173+
-- try some other permutations with a NULL partition instead of a DEFAULT
174+
delete from boolpart where a is null;
175+
create table boolpart_null partition of boolpart for values in (null);
176+
insert into boolpart values(null);
177+
178+
explain (costs off) select * from boolpart where a is not true;
179+
explain (costs off) select * from boolpart where a is not true and a is not false;
180+
explain (costs off) select * from boolpart where a is not false;
181+
182+
select * from boolpart where a is not true;
183+
select * from boolpart where a is not true and a is not false;
184+
select * from boolpart where a is not false;
185+
173186
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
174187
-- code for it, so we'd better test it.
175188
create table iboolpart (a bool) partition by list ((not a));
@@ -196,15 +209,32 @@ select * from iboolpart where a is not true and a is not false;
196209
select * from iboolpart where a is unknown;
197210
select * from iboolpart where a is not unknown;
198211

212+
-- Try some other permutations with a NULL partition instead of a DEFAULT
213+
delete from iboolpart where a is null;
214+
create table iboolpart_null partition of iboolpart for values in (null);
215+
insert into iboolpart values(null);
216+
217+
-- Pruning shouldn't take place for these. Just check the result is correct
218+
select * from iboolpart where a is not true;
219+
select * from iboolpart where a is not true and a is not false;
220+
select * from iboolpart where a is not false;
221+
199222
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
200223
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
201224
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
202225
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
203226
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
227+
create table boolrangep_null partition of boolrangep default;
204228

205229
-- try a more complex case that's been known to trip up pruning in the past
206230
explain (costs off) select * from boolrangep where not a and not b and c = 25;
207231

232+
-- ensure we prune boolrangep_tf
233+
explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
234+
235+
-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
236+
explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
237+
208238
-- test scalar-to-array operators
209239
create table coercepart (a varchar) partition by list (a);
210240
create table coercepart_ab partition of coercepart for values in ('ab');

0 commit comments

Comments
 (0)