Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6848f0c

Browse files
committed
Fix incorrect partition pruning logic for boolean partitioned tables
The partition pruning logic assumed that "b IS NOT true" was exactly thesame as "b IS FALSE". This is not the case when considering NULL values.Fix this so we correctly include any partition which could hold NULLvalues for the NOT case.Additionally, this fixes a bug in the partition pruning code which handlespartitioned tables partitioned like ((NOT boolcol)). This is a seeminglyunlikely schema design, and it was untested and also broken.Here we add tests for the ((NOT boolcol)) case and insert some actual datainto those tables and verify we do get the correct rows back when runningqueries. I've also adjusted the existing boolpart tests to include somedata and verify we get the correct results too.Both the bugs being fixed here could lead to incorrect query results withfewer rows being returned than expected. No additional rows could havebeen returned accidentally.In passing, remove needless ternary expression. It's more simple just topass !is_not_clause to makeBoolConst(). It makes sense to do this so thecode 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, butthat's not what's being committed here.Reported-by: David KimuraReviewed-by: Richard Guo, David KimuraDiscussion:https://postgr.es/m/CAHnPFjQ5qxs6J_p+g8=ww7GQvfn71_JE+Tygj0S7RdRci1uwPw@mail.gmail.comBackpatch-through: 11, all supported versions
1 parent96c698e commit6848f0c

File tree

3 files changed

+288
-31
lines changed

3 files changed

+288
-31
lines changed

‎src/backend/partitioning/partprune.c

Lines changed: 35 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -199,7 +199,8 @@ static PruneStepResult *perform_pruning_combine_step(PartitionPruneContext *cont
199199
staticPartClauseMatchStatusmatch_boolean_partition_clause(Oidpartopfamily,
200200
Expr*clause,
201201
Expr*partkey,
202-
Expr**outconst);
202+
Expr**outconst,
203+
bool*noteq);
203204
staticvoidpartkey_datum_from_expr(PartitionPruneContext*context,
204205
Expr*expr,intstateidx,
205206
Datum*value,bool*isnull);
@@ -1702,12 +1703,13 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
17021703
Oidpartopfamily=part_scheme->partopfamily[partkeyidx],
17031704
partcoll=part_scheme->partcollation[partkeyidx];
17041705
Expr*expr;
1706+
boolnoteq;
17051707

17061708
/*
17071709
* Recognize specially shaped clauses that match a Boolean partition key.
17081710
*/
17091711
boolmatchstatus=match_boolean_partition_clause(partopfamily,clause,
1710-
partkey,&expr);
1712+
partkey,&expr,&noteq);
17111713

17121714
if (boolmatchstatus==PARTCLAUSE_MATCH_CLAUSE)
17131715
{
@@ -1717,7 +1719,7 @@ match_clause_to_partition_key(GeneratePruningStepsContext *context,
17171719
partclause->keyno=partkeyidx;
17181720
/* Do pruning with the Boolean equality operator. */
17191721
partclause->opno=BooleanEqualOperator;
1720-
partclause->op_is_ne=false;
1722+
partclause->op_is_ne=noteq;
17211723
partclause->expr=expr;
17221724
/* We know that expr is of Boolean type. */
17231725
partclause->cmpfn=part_scheme->partsupfunc[partkeyidx].fn_oid;
@@ -3480,20 +3482,22 @@ perform_pruning_combine_step(PartitionPruneContext *context,
34803482
* match_boolean_partition_clause
34813483
*
34823484
* If we're able to match the clause to the partition key as specially-shaped
3483-
* boolean clause, set *outconst to a Const containing a true or false value
3484-
* and return PARTCLAUSE_MATCH_CLAUSE. Returns PARTCLAUSE_UNSUPPORTED if the
3485-
* clause is not a boolean clause or if the boolean clause is unsuitable for
3486-
* partition pruning. Returns PARTCLAUSE_NOMATCH if it's a bool quals but
3487-
* just does not match this partition key. *outconst is set to NULL in the
3488-
* latter two cases.
3485+
* boolean clause, set *outconst to a Const containing a true or false value,
3486+
* set *noteq according to if the clause was in the "not" form, i.e. "is not
3487+
* true" or "is not false", and return PARTCLAUSE_MATCH_CLAUSE. Returns
3488+
* PARTCLAUSE_UNSUPPORTED if the clause is not a boolean clause or if the
3489+
* boolean clause is unsuitable for partition pruning. Returns
3490+
* PARTCLAUSE_NOMATCH if it's a bool quals but just does not match this
3491+
* partition key. *outconst is set to NULL in the latter two cases.
34893492
*/
34903493
staticPartClauseMatchStatus
34913494
match_boolean_partition_clause(Oidpartopfamily,Expr*clause,Expr*partkey,
3492-
Expr**outconst)
3495+
Expr**outconst,bool*noteq)
34933496
{
34943497
Expr*leftop;
34953498

34963499
*outconst=NULL;
3500+
*noteq= false;
34973501

34983502
if (!IsBooleanOpfamily(partopfamily))
34993503
returnPARTCLAUSE_UNSUPPORTED;
@@ -3512,11 +3516,25 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
35123516
leftop= ((RelabelType*)leftop)->arg;
35133517

35143518
if (equal(leftop,partkey))
3515-
*outconst= (btest->booltesttype==IS_TRUE||
3516-
btest->booltesttype==IS_NOT_FALSE)
3517-
? (Expr*)makeBoolConst(true, false)
3518-
: (Expr*)makeBoolConst(false, false);
3519-
3519+
{
3520+
switch (btest->booltesttype)
3521+
{
3522+
caseIS_NOT_TRUE:
3523+
*noteq= true;
3524+
/* fall through */
3525+
caseIS_TRUE:
3526+
*outconst= (Expr*)makeBoolConst(true, false);
3527+
break;
3528+
caseIS_NOT_FALSE:
3529+
*noteq= true;
3530+
/* fall through */
3531+
caseIS_FALSE:
3532+
*outconst= (Expr*)makeBoolConst(false, false);
3533+
break;
3534+
default:
3535+
returnPARTCLAUSE_UNSUPPORTED;
3536+
}
3537+
}
35203538
if (*outconst)
35213539
returnPARTCLAUSE_MATCH_CLAUSE;
35223540
}
@@ -3531,11 +3549,9 @@ match_boolean_partition_clause(Oid partopfamily, Expr *clause, Expr *partkey,
35313549

35323550
/* Compare to the partition key, and make up a clause ... */
35333551
if (equal(leftop,partkey))
3534-
*outconst=is_not_clause ?
3535-
(Expr*)makeBoolConst(false, false) :
3536-
(Expr*)makeBoolConst(true, false);
3552+
*outconst= (Expr*)makeBoolConst(!is_not_clause, false);
35373553
elseif (equal(negate_clause((Node*)leftop),partkey))
3538-
*outconst= (Expr*)makeBoolConst(false, false);
3554+
*outconst= (Expr*)makeBoolConst(is_not_clause, false);
35393555

35403556
if (*outconst)
35413557
returnPARTCLAUSE_MATCH_CLAUSE;

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

Lines changed: 216 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1038,6 +1038,7 @@ create table boolpart (a bool) partition by list (a);
10381038
create table boolpart_default partition of boolpart default;
10391039
create table boolpart_t partition of boolpart for values in ('true');
10401040
create table boolpart_f partition of boolpart for values in ('false');
1041+
insert into boolpart values (true), (false), (null);
10411042
explain (costs off) select * from boolpart where a in (true, false);
10421043
QUERY PLAN
10431044
------------------------------------------------
@@ -1070,20 +1071,25 @@ explain (costs off) select * from boolpart where a is true or a is not true;
10701071
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
10711072
-> Seq Scan on boolpart_t boolpart_2
10721073
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1073-
(5 rows)
1074+
-> Seq Scan on boolpart_default boolpart_3
1075+
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1076+
(7 rows)
10741077

10751078
explain (costs off) select * from boolpart where a is not true;
1076-
QUERY PLAN
1077-
---------------------------------
1078-
Seq Scan on boolpart_f boolpart
1079-
Filter: (a IS NOT TRUE)
1080-
(2 rows)
1079+
QUERY PLAN
1080+
-----------------------------------------------
1081+
Append
1082+
-> Seq Scan on boolpart_f boolpart_1
1083+
Filter: (a IS NOT TRUE)
1084+
-> Seq Scan on boolpart_default boolpart_2
1085+
Filter: (a IS NOT TRUE)
1086+
(5 rows)
10811087

10821088
explain (costs off) select * from boolpart where a is not true and a is not false;
1083-
QUERY PLAN
1084-
--------------------------
1085-
Result
1086-
One-TimeFilter:false
1089+
QUERY PLAN
1090+
--------------------------------------------------
1091+
Seq Scan on boolpart_default boolpart
1092+
Filter:((a IS NOT TRUE) AND (a IS NOT FALSE))
10871093
(2 rows)
10881094

10891095
explain (costs off) select * from boolpart where a is unknown;
@@ -1110,6 +1116,205 @@ explain (costs off) select * from boolpart where a is not unknown;
11101116
Filter: (a IS NOT UNKNOWN)
11111117
(7 rows)
11121118

1119+
select * from boolpart where a in (true, false);
1120+
a
1121+
---
1122+
f
1123+
t
1124+
(2 rows)
1125+
1126+
select * from boolpart where a = false;
1127+
a
1128+
---
1129+
f
1130+
(1 row)
1131+
1132+
select * from boolpart where not a = false;
1133+
a
1134+
---
1135+
t
1136+
(1 row)
1137+
1138+
select * from boolpart where a is true or a is not true;
1139+
a
1140+
---
1141+
f
1142+
t
1143+
1144+
(3 rows)
1145+
1146+
select * from boolpart where a is not true;
1147+
a
1148+
---
1149+
f
1150+
1151+
(2 rows)
1152+
1153+
select * from boolpart where a is not true and a is not false;
1154+
a
1155+
---
1156+
1157+
(1 row)
1158+
1159+
select * from boolpart where a is unknown;
1160+
a
1161+
---
1162+
1163+
(1 row)
1164+
1165+
select * from boolpart where a is not unknown;
1166+
a
1167+
---
1168+
f
1169+
t
1170+
(2 rows)
1171+
1172+
-- inverse boolean partitioning - a seemingly unlikely design, but we've got
1173+
-- code for it, so we'd better test it.
1174+
create table iboolpart (a bool) partition by list ((not a));
1175+
create table iboolpart_default partition of iboolpart default;
1176+
create table iboolpart_f partition of iboolpart for values in ('true');
1177+
create table iboolpart_t partition of iboolpart for values in ('false');
1178+
insert into iboolpart values (true), (false), (null);
1179+
explain (costs off) select * from iboolpart where a in (true, false);
1180+
QUERY PLAN
1181+
-------------------------------------------------
1182+
Append
1183+
-> Seq Scan on iboolpart_t iboolpart_1
1184+
Filter: (a = ANY ('{t,f}'::boolean[]))
1185+
-> Seq Scan on iboolpart_f iboolpart_2
1186+
Filter: (a = ANY ('{t,f}'::boolean[]))
1187+
-> Seq Scan on iboolpart_default iboolpart_3
1188+
Filter: (a = ANY ('{t,f}'::boolean[]))
1189+
(7 rows)
1190+
1191+
explain (costs off) select * from iboolpart where a = false;
1192+
QUERY PLAN
1193+
-----------------------------------
1194+
Seq Scan on iboolpart_f iboolpart
1195+
Filter: (NOT a)
1196+
(2 rows)
1197+
1198+
explain (costs off) select * from iboolpart where not a = false;
1199+
QUERY PLAN
1200+
-----------------------------------
1201+
Seq Scan on iboolpart_t iboolpart
1202+
Filter: a
1203+
(2 rows)
1204+
1205+
explain (costs off) select * from iboolpart where a is true or a is not true;
1206+
QUERY PLAN
1207+
--------------------------------------------------
1208+
Append
1209+
-> Seq Scan on iboolpart_t iboolpart_1
1210+
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1211+
-> Seq Scan on iboolpart_f iboolpart_2
1212+
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1213+
-> Seq Scan on iboolpart_default iboolpart_3
1214+
Filter: ((a IS TRUE) OR (a IS NOT TRUE))
1215+
(7 rows)
1216+
1217+
explain (costs off) select * from iboolpart where a is not true;
1218+
QUERY PLAN
1219+
-------------------------------------------------
1220+
Append
1221+
-> Seq Scan on iboolpart_t iboolpart_1
1222+
Filter: (a IS NOT TRUE)
1223+
-> Seq Scan on iboolpart_f iboolpart_2
1224+
Filter: (a IS NOT TRUE)
1225+
-> Seq Scan on iboolpart_default iboolpart_3
1226+
Filter: (a IS NOT TRUE)
1227+
(7 rows)
1228+
1229+
explain (costs off) select * from iboolpart where a is not true and a is not false;
1230+
QUERY PLAN
1231+
--------------------------------------------------------
1232+
Append
1233+
-> Seq Scan on iboolpart_t iboolpart_1
1234+
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1235+
-> Seq Scan on iboolpart_f iboolpart_2
1236+
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1237+
-> Seq Scan on iboolpart_default iboolpart_3
1238+
Filter: ((a IS NOT TRUE) AND (a IS NOT FALSE))
1239+
(7 rows)
1240+
1241+
explain (costs off) select * from iboolpart where a is unknown;
1242+
QUERY PLAN
1243+
-------------------------------------------------
1244+
Append
1245+
-> Seq Scan on iboolpart_t iboolpart_1
1246+
Filter: (a IS UNKNOWN)
1247+
-> Seq Scan on iboolpart_f iboolpart_2
1248+
Filter: (a IS UNKNOWN)
1249+
-> Seq Scan on iboolpart_default iboolpart_3
1250+
Filter: (a IS UNKNOWN)
1251+
(7 rows)
1252+
1253+
explain (costs off) select * from iboolpart where a is not unknown;
1254+
QUERY PLAN
1255+
-------------------------------------------------
1256+
Append
1257+
-> Seq Scan on iboolpart_t iboolpart_1
1258+
Filter: (a IS NOT UNKNOWN)
1259+
-> Seq Scan on iboolpart_f iboolpart_2
1260+
Filter: (a IS NOT UNKNOWN)
1261+
-> Seq Scan on iboolpart_default iboolpart_3
1262+
Filter: (a IS NOT UNKNOWN)
1263+
(7 rows)
1264+
1265+
select * from iboolpart where a in (true, false);
1266+
a
1267+
---
1268+
t
1269+
f
1270+
(2 rows)
1271+
1272+
select * from iboolpart where a = false;
1273+
a
1274+
---
1275+
f
1276+
(1 row)
1277+
1278+
select * from iboolpart where not a = false;
1279+
a
1280+
---
1281+
t
1282+
(1 row)
1283+
1284+
select * from iboolpart where a is true or a is not true;
1285+
a
1286+
---
1287+
t
1288+
f
1289+
1290+
(3 rows)
1291+
1292+
select * from iboolpart where a is not true;
1293+
a
1294+
---
1295+
f
1296+
1297+
(2 rows)
1298+
1299+
select * from iboolpart where a is not true and a is not false;
1300+
a
1301+
---
1302+
1303+
(1 row)
1304+
1305+
select * from iboolpart where a is unknown;
1306+
a
1307+
---
1308+
1309+
(1 row)
1310+
1311+
select * from iboolpart where a is not unknown;
1312+
a
1313+
---
1314+
t
1315+
f
1316+
(2 rows)
1317+
11131318
create table boolrangep (a bool, b bool, c int) partition by range (a,b,c);
11141319
create table boolrangep_tf partition of boolrangep for values from ('true', 'false', 0) to ('true', 'false', 100);
11151320
create table boolrangep_ft partition of boolrangep for values from ('false', 'true', 0) to ('false', 'true', 100);
@@ -1530,7 +1735,7 @@ explain (costs off) select * from rparted_by_int2 where a > 100000000000000;
15301735
Filter: (a > '100000000000000'::bigint)
15311736
(2 rows)
15321737

1533-
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart, boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
1738+
drop table lp, coll_pruning, rlp, mc3p, mc2p, boolpart,iboolpart,boolrangep, rp, coll_pruning_multi, like_op_noprune, lparted_by_int2, rparted_by_int2;
15341739
--
15351740
-- Test Partition pruning for HASH partitioning
15361741
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp