Skip to content

Commit 4c2369a

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 0e16281 commit 4c2369a

File tree

3 files changed

+187
-2
lines changed

3 files changed

+187
-2
lines changed

‎src/backend/partitioning/partprune.c

+54-2
Original file line numberDiff line numberDiff line change
@@ -1810,11 +1810,63 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
18101810
{
18111811
PartClauseInfo *partclause;
18121812

1813+
/*
1814+
* For bool tests in the form of partkey IS NOT true and IS NOT false,
1815+
* we invert these clauses. Effectively, "partkey IS NOT true"
1816+
* becomes "partkey IS false OR partkey IS NULL". We do this by
1817+
* building an OR BoolExpr and forming a clause just like that and
1818+
* punt it off to gen_partprune_steps_internal() to generate pruning
1819+
* steps.
1820+
*/
1821+
if (noteq)
1822+
{
1823+
List *new_clauses;
1824+
List *or_clause;
1825+
BooleanTest *new_booltest = (BooleanTest *) copyObject(clause);
1826+
NullTest *nulltest;
1827+
1828+
/* We expect 'noteq' to only be set to true for BooleanTests */
1829+
Assert(IsA(clause, BooleanTest));
1830+
1831+
/* reverse the bool test */
1832+
if (new_booltest->booltesttype == IS_NOT_TRUE)
1833+
new_booltest->booltesttype = IS_FALSE;
1834+
else if (new_booltest->booltesttype == IS_NOT_FALSE)
1835+
new_booltest->booltesttype = IS_TRUE;
1836+
else
1837+
{
1838+
/*
1839+
* We only expect match_boolean_partition_clause to match for
1840+
* IS_NOT_TRUE and IS_NOT_FALSE. IS_NOT_UNKNOWN is not
1841+
* supported.
1842+
*/
1843+
Assert(false);
1844+
}
1845+
1846+
nulltest = makeNode(NullTest);
1847+
nulltest->arg = copyObject(partkey);
1848+
nulltest->nulltesttype = IS_NULL;
1849+
nulltest->argisrow = false;
1850+
nulltest->location = -1;
1851+
1852+
new_clauses = list_make2(new_booltest, nulltest);
1853+
or_clause = list_make1(makeBoolExpr(OR_EXPR, new_clauses, -1));
1854+
1855+
/* Finally, generate steps */
1856+
*clause_steps = gen_partprune_steps_internal(context, or_clause);
1857+
1858+
if (context->contradictory)
1859+
return PARTCLAUSE_MATCH_CONTRADICT; /* shouldn't happen */
1860+
else if (*clause_steps == NIL)
1861+
return PARTCLAUSE_UNSUPPORTED; /* step generation failed */
1862+
return PARTCLAUSE_MATCH_STEPS;
1863+
}
1864+
18131865
partclause = (PartClauseInfo *) palloc(sizeof(PartClauseInfo));
18141866
partclause->keyno = partkeyidx;
18151867
/* Do pruning with the Boolean equality operator. */
18161868
partclause->opno = BooleanEqualOperator;
1817-
partclause->op_is_ne = noteq;
1869+
partclause->op_is_ne = false;
18181870
partclause->expr = expr;
18191871
/* We know that expr is of Boolean type. */
18201872
partclause->cmpfn = part_scheme->partsupfunc[partkeyidx].fn_oid;
@@ -2358,7 +2410,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
23582410
* For LIST and RANGE partitioned tables, callers must ensure that
23592411
* step_nullkeys is NULL, and that prefix contains at least one clause for
23602412
* each of the partition keys prior to the key that 'step_lastexpr' and
2361-
* 'step_lastcmpfn'belong to.
2413+
* 'step_lastcmpfn' belong to.
23622414
*
23632415
* For HASH partitioned tables, callers must ensure that 'prefix' contains at
23642416
* least one clause for each of the partition keys apart from the final key

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

+103
Original file line numberDiff line numberDiff line change
@@ -1169,6 +1169,57 @@ select * from boolpart where a is not unknown;
11691169
t
11701170
(2 rows)
11711171

1172+
-- try some other permutations with a NULL partition instead of a DEFAULT
1173+
delete from boolpart where a is null;
1174+
create table boolpart_null partition of boolpart for values in (null);
1175+
insert into boolpart values(null);
1176+
explain (costs off) select * from boolpart where a is not true;
1177+
QUERY PLAN
1178+
--------------------------------------------
1179+
Append
1180+
-> Seq Scan on boolpart_f boolpart_1
1181+
Filter: (a IS NOT TRUE)
1182+
-> Seq Scan on boolpart_null boolpart_2
1183+
Filter: (a IS NOT TRUE)
1184+
(5 rows)
1185+
1186+
explain (costs off) select * from boolpart where a is not true and a is not false;
1187+
QUERY PLAN
1188+
--------------------------------------------------
1189+
Seq Scan on boolpart_null boolpart
1190+
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1191+
(2 rows)
1192+
1193+
explain (costs off) select * from boolpart where a is not false;
1194+
QUERY PLAN
1195+
--------------------------------------------
1196+
Append
1197+
-> Seq Scan on boolpart_t boolpart_1
1198+
Filter: (a IS NOT FALSE)
1199+
-> Seq Scan on boolpart_null boolpart_2
1200+
Filter: (a IS NOT FALSE)
1201+
(5 rows)
1202+
1203+
select * from boolpart where a is not true;
1204+
a
1205+
---
1206+
f
1207+
1208+
(2 rows)
1209+
1210+
select * from boolpart where a is not true and a is not false;
1211+
a
1212+
---
1213+
1214+
(1 row)
1215+
1216+
select * from boolpart where a is not false;
1217+
a
1218+
---
1219+
t
1220+
1221+
(2 rows)
1222+
11721223
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
11731224
-- code for it, so we'd better test it.
11741225
create table iboolpart (a bool) partition by list ((not a));
@@ -1315,11 +1366,37 @@ select * from iboolpart where a is not unknown;
13151366
f
13161367
(2 rows)
13171368

1369+
-- Try some other permutations with a NULL partition instead of a DEFAULT
1370+
delete from iboolpart where a is null;
1371+
create table iboolpart_null partition of iboolpart for values in (null);
1372+
insert into iboolpart values(null);
1373+
-- Pruning shouldn't take place for these. Just check the result is correct
1374+
select * from iboolpart where a is not true;
1375+
a
1376+
---
1377+
f
1378+
1379+
(2 rows)
1380+
1381+
select * from iboolpart where a is not true and a is not false;
1382+
a
1383+
---
1384+
1385+
(1 row)
1386+
1387+
select * from iboolpart where a is not false;
1388+
a
1389+
---
1390+
t
1391+
1392+
(2 rows)
1393+
13181394
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
13191395
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
13201396
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
13211397
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
13221398
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
1399+
create table boolrangep_null partition of boolrangep default;
13231400
-- try a more complex case that's been known to trip up pruning in the past
13241401
explain (costs off) select * from boolrangep where not a and not b and c = 25;
13251402
QUERY PLAN
@@ -1328,6 +1405,32 @@ explain (costs off) select * from boolrangep where not a and not b and c = 25;
13281405
Filter: ((NOT a) AND (NOT b) AND (c = 25))
13291406
(2 rows)
13301407

1408+
-- ensure we prune boolrangep_tf
1409+
explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
1410+
QUERY PLAN
1411+
------------------------------------------------------------
1412+
Append
1413+
-> Seq Scan on boolrangep_ff1 boolrangep_1
1414+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1415+
-> Seq Scan on boolrangep_ff2 boolrangep_2
1416+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1417+
-> Seq Scan on boolrangep_ft boolrangep_3
1418+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1419+
-> Seq Scan on boolrangep_null boolrangep_4
1420+
Filter: ((a IS NOT TRUE) AND (NOT b) AND (c = 25))
1421+
(9 rows)
1422+
1423+
-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
1424+
explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
1425+
QUERY PLAN
1426+
-------------------------------------------------------------
1427+
Append
1428+
-> Seq Scan on boolrangep_tf boolrangep_1
1429+
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1430+
-> Seq Scan on boolrangep_null boolrangep_2
1431+
Filter: ((a IS NOT FALSE) AND (NOT b) AND (c = 25))
1432+
(5 rows)
1433+
13311434
-- test scalar-to-array operators
13321435
create table coercepart (a varchar) partition by list (a);
13331436
create table coercepart_ab partition of coercepart for values in ('ab');

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

+30
Original file line numberDiff line numberDiff line change
@@ -178,6 +178,19 @@ select * from boolpart where a is not true and a is not false;
178178
select * from boolpart where a is unknown;
179179
select * from boolpart where a is not unknown;
180180

181+
-- try some other permutations with a NULL partition instead of a DEFAULT
182+
delete from boolpart where a is null;
183+
create table boolpart_null partition of boolpart for values in (null);
184+
insert into boolpart values(null);
185+
186+
explain (costs off) select * from boolpart where a is not true;
187+
explain (costs off) select * from boolpart where a is not true and a is not false;
188+
explain (costs off) select * from boolpart where a is not false;
189+
190+
select * from boolpart where a is not true;
191+
select * from boolpart where a is not true and a is not false;
192+
select * from boolpart where a is not false;
193+
181194
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
182195
-- code for it, so we'd better test it.
183196
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;
204217
select * from iboolpart where a is unknown;
205218
select * from iboolpart where a is not unknown;
206219

220+
-- Try some other permutations with a NULL partition instead of a DEFAULT
221+
delete from iboolpart where a is null;
222+
create table iboolpart_null partition of iboolpart for values in (null);
223+
insert into iboolpart values(null);
224+
225+
-- Pruning shouldn't take place for these. Just check the result is correct
226+
select * from iboolpart where a is not true;
227+
select * from iboolpart where a is not true and a is not false;
228+
select * from iboolpart where a is not false;
229+
207230
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
208231
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
209232
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
210233
create table boolrangep_ff1 partition of boolrangep for values from ('false', 'false', 0) to ('false', 'false', 50);
211234
create table boolrangep_ff2 partition of boolrangep for values from ('false', 'false', 50) to ('false', 'false', 100);
235+
create table boolrangep_null partition of boolrangep default;
212236

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

240+
-- ensure we prune boolrangep_tf
241+
explain (costs off) select * from boolrangep where a is not true and not b and c = 25;
242+
243+
-- ensure we prune everything apart from boolrangep_tf and boolrangep_null
244+
explain (costs off) select * from boolrangep where a is not false and not b and c = 25;
245+
216246
-- test scalar-to-array operators
217247
create table coercepart (a varchar) partition by list (a);
218248
create table coercepart_ab partition of coercepart for values in ('ab');

0 commit comments

Comments
 (0)