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

Commitb94b9e4

Browse files
committed
Prevent pushing down WHERE clauses into unsafe UNION/INTERSECT nests.
The planner is aware that it mustn't push down upper-level quals intosubqueries if the quals reference subquery output columns that containset-returning functions or volatile functions, or are non-DISTINCT outputsof a DISTINCT ON subquery. However, it missed making this check whenthere were one or more levels of UNION or INTERSECT above the dangerousexpression. This could lead to "set-valued function called in context thatcannot accept a set" errors, as seen in bug #8213 from Eric Soroos, or tosilently wrong answers in the other cases.To fix, refactor the checks so that we make the column-is-unsafe checksduring subquery_is_pushdown_safe(), which already has to recursivelyinspect all arms of a set-operation tree. This makesqual_is_pushdown_safe() considerably simpler, at the cost that we willspend some cycles checking output columns that possibly aren't referencedin any upper qual. But the cases where this code gets executed at allare already nontrivial queries, so it's unlikely anybody will notice anyslowdown of planning.This has been broken since commit05f916e,which makes the bug over ten years old. A bit surprising nobody noticed itbefore now.
1 parent53eb835 commitb94b9e4

File tree

3 files changed

+257
-91
lines changed

3 files changed

+257
-91
lines changed

‎src/backend/optimizer/path/allpaths.c‎

Lines changed: 125 additions & 91 deletions
Original file line numberDiff line numberDiff line change
@@ -64,13 +64,14 @@ static void set_worktable_pathlist(PlannerInfo *root, RelOptInfo *rel,
6464
RangeTblEntry*rte);
6565
staticRelOptInfo*make_rel_from_joinlist(PlannerInfo*root,List*joinlist);
6666
staticboolsubquery_is_pushdown_safe(Query*subquery,Query*topquery,
67-
bool*differentTypes);
67+
bool*unsafeColumns);
6868
staticboolrecurse_pushdown_safe(Node*setOp,Query*topquery,
69-
bool*differentTypes);
69+
bool*unsafeColumns);
70+
staticvoidcheck_output_expressions(Query*subquery,bool*unsafeColumns);
7071
staticvoidcompare_tlist_datatypes(List*tlist,List*colTypes,
71-
bool*differentTypes);
72+
bool*unsafeColumns);
7273
staticboolqual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual,
73-
bool*differentTypes);
74+
bool*unsafeColumns);
7475
staticvoidsubquery_push_qual(Query*subquery,
7576
RangeTblEntry*rte,Indexrti,Node*qual);
7677
staticvoidrecurse_push_qual(Node*setOp,Query*topquery,
@@ -545,7 +546,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
545546
{
546547
Query*parse=root->parse;
547548
Query*subquery=rte->subquery;
548-
bool*differentTypes;
549+
bool*unsafeColumns;
549550
doubletuple_fraction;
550551
PlannerInfo*subroot;
551552
List*pathkeys;
@@ -557,8 +558,12 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
557558
*/
558559
subquery=copyObject(subquery);
559560

560-
/* We need a workspace for keeping track of set-op type coercions */
561-
differentTypes= (bool*)
561+
/*
562+
* We need a workspace for keeping track of unsafe-to-reference columns.
563+
* unsafeColumns[i] is set TRUE if we've found that output column i of the
564+
* subquery is unsafe to use in a pushed-down qual.
565+
*/
566+
unsafeColumns= (bool*)
562567
palloc0((list_length(subquery->targetList)+1)*sizeof(bool));
563568

564569
/*
@@ -582,7 +587,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
582587
* push down a pushable qual, because it'd result in a worse plan?
583588
*/
584589
if (rel->baserestrictinfo!=NIL&&
585-
subquery_is_pushdown_safe(subquery,subquery,differentTypes))
590+
subquery_is_pushdown_safe(subquery,subquery,unsafeColumns))
586591
{
587592
/* OK to consider pushing down individual quals */
588593
List*upperrestrictlist=NIL;
@@ -594,7 +599,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
594599
Node*clause= (Node*)rinfo->clause;
595600

596601
if (!rinfo->pseudoconstant&&
597-
qual_is_pushdown_safe(subquery,rti,clause,differentTypes))
602+
qual_is_pushdown_safe(subquery,rti,clause,unsafeColumns))
598603
{
599604
/* Push it down */
600605
subquery_push_qual(subquery,rte,rti,clause);
@@ -608,7 +613,7 @@ set_subquery_pathlist(PlannerInfo *root, RelOptInfo *rel,
608613
rel->baserestrictinfo=upperrestrictlist;
609614
}
610615

611-
pfree(differentTypes);
616+
pfree(unsafeColumns);
612617

613618
/*
614619
* We can safely pass the outer tuple_fraction down to the subquery if the
@@ -995,17 +1000,19 @@ standard_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
9951000
* 3. If the subquery contains EXCEPT or EXCEPT ALL set ops we cannot push
9961001
* quals into it, because that could change the results.
9971002
*
998-
* 4. For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
999-
* push quals into each component query, but the quals can only reference
1000-
* subquery columns that suffer no type coercions in the set operation.
1001-
* Otherwise there are possible semantic gotchas. So, we check the
1002-
* component queries to see if any of them have different output types;
1003-
* differentTypes[k] is set true if column k has different type in any
1004-
* component.
1003+
* In addition, we make several checks on the subquery's output columns
1004+
* to see if it is safe to reference them in pushed-down quals. If output
1005+
* column k is found to be unsafe to reference, we set unsafeColumns[k] to
1006+
* TRUE, but we don't reject the subquery overall since column k might
1007+
* not be referenced by some/all quals. The unsafeColumns[] array will be
1008+
* consulted later by qual_is_pushdown_safe().It's better to do it this
1009+
* way than to make the checks directly in qual_is_pushdown_safe(), because
1010+
* when the subquery involves set operations we have to check the output
1011+
* expressions in each arm of the set op.
10051012
*/
10061013
staticbool
10071014
subquery_is_pushdown_safe(Query*subquery,Query*topquery,
1008-
bool*differentTypes)
1015+
bool*unsafeColumns)
10091016
{
10101017
SetOperationStmt*topop;
10111018

@@ -1017,13 +1024,22 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
10171024
if (subquery->hasWindowFuncs)
10181025
return false;
10191026

1027+
/*
1028+
* If we're at a leaf query, check for unsafe expressions in its target
1029+
* list, and mark any unsafe ones in unsafeColumns[]. (Non-leaf nodes in
1030+
* setop trees have only simple Vars in their tlists, so no need to check
1031+
* them.)
1032+
*/
1033+
if (subquery->setOperations==NULL)
1034+
check_output_expressions(subquery,unsafeColumns);
1035+
10201036
/* Are we at top level, or looking at a setop component? */
10211037
if (subquery==topquery)
10221038
{
10231039
/* Top level, so check any component queries */
10241040
if (subquery->setOperations!=NULL)
10251041
if (!recurse_pushdown_safe(subquery->setOperations,topquery,
1026-
differentTypes))
1042+
unsafeColumns))
10271043
return false;
10281044
}
10291045
else
@@ -1036,7 +1052,7 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
10361052
Assert(topop&&IsA(topop,SetOperationStmt));
10371053
compare_tlist_datatypes(subquery->targetList,
10381054
topop->colTypes,
1039-
differentTypes);
1055+
unsafeColumns);
10401056
}
10411057
return true;
10421058
}
@@ -1046,7 +1062,7 @@ subquery_is_pushdown_safe(Query *subquery, Query *topquery,
10461062
*/
10471063
staticbool
10481064
recurse_pushdown_safe(Node*setOp,Query*topquery,
1049-
bool*differentTypes)
1065+
bool*unsafeColumns)
10501066
{
10511067
if (IsA(setOp,RangeTblRef))
10521068
{
@@ -1055,19 +1071,19 @@ recurse_pushdown_safe(Node *setOp, Query *topquery,
10551071
Query*subquery=rte->subquery;
10561072

10571073
Assert(subquery!=NULL);
1058-
returnsubquery_is_pushdown_safe(subquery,topquery,differentTypes);
1074+
returnsubquery_is_pushdown_safe(subquery,topquery,unsafeColumns);
10591075
}
10601076
elseif (IsA(setOp,SetOperationStmt))
10611077
{
10621078
SetOperationStmt*op= (SetOperationStmt*)setOp;
10631079

1064-
/* EXCEPT is no good */
1080+
/* EXCEPT is no good(point 3 for subquery_is_pushdown_safe)*/
10651081
if (op->op==SETOP_EXCEPT)
10661082
return false;
10671083
/* Else recurse */
1068-
if (!recurse_pushdown_safe(op->larg,topquery,differentTypes))
1084+
if (!recurse_pushdown_safe(op->larg,topquery,unsafeColumns))
10691085
return false;
1070-
if (!recurse_pushdown_safe(op->rarg,topquery,differentTypes))
1086+
if (!recurse_pushdown_safe(op->rarg,topquery,unsafeColumns))
10711087
return false;
10721088
}
10731089
else
@@ -1079,17 +1095,92 @@ recurse_pushdown_safe(Node *setOp, Query *topquery,
10791095
}
10801096

10811097
/*
1082-
* Compare tlist's datatypes against the list of set-operation result types.
1083-
* For any items that are different, mark the appropriate element of
1084-
* differentTypes[] to show that this column will have type conversions.
1098+
* check_output_expressions - check subquery's output expressions for safety
1099+
*
1100+
* There are several cases in which it's unsafe to push down an upper-level
1101+
* qual if it references a particular output column of a subquery.We check
1102+
* each output column of the subquery and set unsafeColumns[k] to TRUE if
1103+
* that column is unsafe for a pushed-down qual to reference. The conditions
1104+
* checked here are:
1105+
*
1106+
* 1. We must not push down any quals that refer to subselect outputs that
1107+
* return sets, else we'd introduce functions-returning-sets into the
1108+
* subquery's WHERE/HAVING quals.
1109+
*
1110+
* 2. We must not push down any quals that refer to subselect outputs that
1111+
* contain volatile functions, for fear of introducing strange results due
1112+
* to multiple evaluation of a volatile function.
1113+
*
1114+
* 3. If the subquery uses DISTINCT ON, we must not push down any quals that
1115+
* refer to non-DISTINCT output columns, because that could change the set
1116+
* of rows returned. (This condition is vacuous for DISTINCT, because then
1117+
* there are no non-DISTINCT output columns, so we needn't check. But note
1118+
* we are assuming that the qual can't distinguish values that the DISTINCT
1119+
* operator sees as equal.This is a bit shaky but we have no way to test
1120+
* for the case, and it's unlikely enough that we shouldn't refuse the
1121+
* optimization just because it could theoretically happen.)
1122+
*/
1123+
staticvoid
1124+
check_output_expressions(Query*subquery,bool*unsafeColumns)
1125+
{
1126+
ListCell*lc;
1127+
1128+
foreach(lc,subquery->targetList)
1129+
{
1130+
TargetEntry*tle= (TargetEntry*)lfirst(lc);
1131+
1132+
if (tle->resjunk)
1133+
continue;/* ignore resjunk columns */
1134+
1135+
/* We need not check further if output col is already known unsafe */
1136+
if (unsafeColumns[tle->resno])
1137+
continue;
1138+
1139+
/* Functions returning sets are unsafe (point 1) */
1140+
if (expression_returns_set((Node*)tle->expr))
1141+
{
1142+
unsafeColumns[tle->resno]= true;
1143+
continue;
1144+
}
1145+
1146+
/* Volatile functions are unsafe (point 2) */
1147+
if (contain_volatile_functions((Node*)tle->expr))
1148+
{
1149+
unsafeColumns[tle->resno]= true;
1150+
continue;
1151+
}
1152+
1153+
/* If subquery uses DISTINCT ON, check point 3 */
1154+
if (subquery->hasDistinctOn&&
1155+
!targetIsInSortList(tle,InvalidOid,subquery->distinctClause))
1156+
{
1157+
/* non-DISTINCT column, so mark it unsafe */
1158+
unsafeColumns[tle->resno]= true;
1159+
continue;
1160+
}
1161+
}
1162+
}
1163+
1164+
/*
1165+
* For subqueries using UNION/UNION ALL/INTERSECT/INTERSECT ALL, we can
1166+
* push quals into each component query, but the quals can only reference
1167+
* subquery columns that suffer no type coercions in the set operation.
1168+
* Otherwise there are possible semantic gotchas. So, we check the
1169+
* component queries to see if any of them have output types different from
1170+
* the top-level setop outputs. unsafeColumns[k] is set true if column k
1171+
* has different type in any component.
10851172
*
10861173
* We don't have to care about typmods here: the only allowed difference
10871174
* between set-op input and output typmods is input is a specific typmod
10881175
* and output is -1, and that does not require a coercion.
1176+
*
1177+
* tlist is a subquery tlist.
1178+
* colTypes is an OID list of the top-level setop's output column types.
1179+
* unsafeColumns[] is the result array.
10891180
*/
10901181
staticvoid
10911182
compare_tlist_datatypes(List*tlist,List*colTypes,
1092-
bool*differentTypes)
1183+
bool*unsafeColumns)
10931184
{
10941185
ListCell*l;
10951186
ListCell*colType=list_head(colTypes);
@@ -1103,7 +1194,7 @@ compare_tlist_datatypes(List *tlist, List *colTypes,
11031194
if (colType==NULL)
11041195
elog(ERROR,"wrong number of tlist entries");
11051196
if (exprType((Node*)tle->expr)!=lfirst_oid(colType))
1106-
differentTypes[tle->resno]= true;
1197+
unsafeColumns[tle->resno]= true;
11071198
colType=lnext(colType);
11081199
}
11091200
if (colType!=NULL)
@@ -1126,34 +1217,15 @@ compare_tlist_datatypes(List *tlist, List *colTypes,
11261217
* (since there is no easy way to name that within the subquery itself).
11271218
*
11281219
* 3. The qual must not refer to any subquery output columns that were
1129-
* found to have inconsistent types across a set operation tree by
1130-
* subquery_is_pushdown_safe().
1131-
*
1132-
* 4. If the subquery uses DISTINCT ON, we must not push down any quals that
1133-
* refer to non-DISTINCT output columns, because that could change the set
1134-
* of rows returned. (This condition is vacuous for DISTINCT, because then
1135-
* there are no non-DISTINCT output columns, so we needn't check. But note
1136-
* we are assuming that the qual can't distinguish values that the DISTINCT
1137-
* operator sees as equal.This is a bit shaky but we have no way to test
1138-
* for the case, and it's unlikely enough that we shouldn't refuse the
1139-
* optimization just because it could theoretically happen.)
1140-
*
1141-
* 5. We must not push down any quals that refer to subselect outputs that
1142-
* return sets, else we'd introduce functions-returning-sets into the
1143-
* subquery's WHERE/HAVING quals.
1144-
*
1145-
* 6. We must not push down any quals that refer to subselect outputs that
1146-
* contain volatile functions, for fear of introducing strange results due
1147-
* to multiple evaluation of a volatile function.
1220+
* found to be unsafe to reference by subquery_is_pushdown_safe().
11481221
*/
11491222
staticbool
11501223
qual_is_pushdown_safe(Query*subquery,Indexrti,Node*qual,
1151-
bool*differentTypes)
1224+
bool*unsafeColumns)
11521225
{
11531226
boolsafe= true;
11541227
List*vars;
11551228
ListCell*vl;
1156-
Bitmapset*tested=NULL;
11571229

11581230
/* Refuse subselects (point 1) */
11591231
if (contain_subplans(qual))
@@ -1173,7 +1245,6 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual,
11731245
foreach(vl,vars)
11741246
{
11751247
Var*var= (Var*)lfirst(vl);
1176-
TargetEntry*tle;
11771248

11781249
/*
11791250
* XXX Punt if we find any PlaceHolderVars in the restriction clause.
@@ -1189,6 +1260,7 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual,
11891260
}
11901261

11911262
Assert(var->varno==rti);
1263+
Assert(var->varattno >=0);
11921264

11931265
/* Check point 2 */
11941266
if (var->varattno==0)
@@ -1197,53 +1269,15 @@ qual_is_pushdown_safe(Query *subquery, Index rti, Node *qual,
11971269
break;
11981270
}
11991271

1200-
/*
1201-
* We use a bitmapset to avoid testing the same attno more than once.
1202-
* (NB: this only works because subquery outputs can't have negative
1203-
* attnos.)
1204-
*/
1205-
if (bms_is_member(var->varattno,tested))
1206-
continue;
1207-
tested=bms_add_member(tested,var->varattno);
1208-
12091272
/* Check point 3 */
1210-
if (differentTypes[var->varattno])
1211-
{
1212-
safe= false;
1213-
break;
1214-
}
1215-
1216-
/* Must find the tlist element referenced by the Var */
1217-
tle=get_tle_by_resno(subquery->targetList,var->varattno);
1218-
Assert(tle!=NULL);
1219-
Assert(!tle->resjunk);
1220-
1221-
/* If subquery uses DISTINCT ON, check point 4 */
1222-
if (subquery->hasDistinctOn&&
1223-
!targetIsInSortList(tle,InvalidOid,subquery->distinctClause))
1224-
{
1225-
/* non-DISTINCT column, so fail */
1226-
safe= false;
1227-
break;
1228-
}
1229-
1230-
/* Refuse functions returning sets (point 5) */
1231-
if (expression_returns_set((Node*)tle->expr))
1232-
{
1233-
safe= false;
1234-
break;
1235-
}
1236-
1237-
/* Refuse volatile functions (point 6) */
1238-
if (contain_volatile_functions((Node*)tle->expr))
1273+
if (unsafeColumns[var->varattno])
12391274
{
12401275
safe= false;
12411276
break;
12421277
}
12431278
}
12441279

12451280
list_free(vars);
1246-
bms_free(tested);
12471281

12481282
returnsafe;
12491283
}

0 commit comments

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