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

Commitb97ee66

Browse files
committed
Make contain_volatile_functions/contain_mutable_functions look into SubLinks.
This change prevents us from doing inappropriate subquery flattening incases such as dangerous functions hidden inside a sub-SELECT in thetargetlist of another sub-SELECT. That could result in unexpected behaviordue to multiple evaluations of a volatile function, as in a recentcomplaint from Etienne Dube. It's been questionable from the verybeginning whether these functions should look into subqueries (as noted intheir comments), and this case seems to provide proof that they should.Because the new code only descends into SubLinks, not SubPlans orInitPlans, the change only affects the planner's behavior duringprepjointree processing and not later on --- for example, you can still getit to use a volatile function in an indexqual if you wrap the function in(SELECT ...). That's a historical behavior, for sure, but it's reasonablegiven that the executor's evaluation rules for subplans don't depend onwhether there are volatile functions inside them. In any case, we need toconstrain the behavioral change as narrowly as we can to make thisreasonable to back-patch.
1 parent060b22a commitb97ee66

File tree

3 files changed

+107
-6
lines changed

3 files changed

+107
-6
lines changed

‎src/backend/optimizer/util/clauses.c

Lines changed: 27 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -833,8 +833,8 @@ contain_subplans_walker(Node *node, void *context)
833833
* mistakenly think that something like "WHERE random() < 0.5" can be treated
834834
* as a constant qualification.
835835
*
836-
*XXX we do not examine sub-selects to see if they contain uses of
837-
*mutable functions. It'snotreal clear if that is correct or not...
836+
*We will recursively look into Query nodes (i.e., SubLink sub-selects)
837+
*butnotinto SubPlans. See comments for contain_volatile_functions().
838838
*/
839839
bool
840840
contain_mutable_functions(Node*clause)
@@ -931,6 +931,13 @@ contain_mutable_functions_walker(Node *node, void *context)
931931
}
932932
/* else fall through to check args */
933933
}
934+
elseif (IsA(node,Query))
935+
{
936+
/* Recurse into subselects */
937+
returnquery_tree_walker((Query*)node,
938+
contain_mutable_functions_walker,
939+
context,0);
940+
}
934941
returnexpression_tree_walker(node,contain_mutable_functions_walker,
935942
context);
936943
}
@@ -945,11 +952,18 @@ contain_mutable_functions_walker(Node *node, void *context)
945952
* Recursively search for volatile functions within a clause.
946953
*
947954
* Returns true if any volatile function (or operator implemented by a
948-
* volatile function) is found. This test prevents invalid conversions
949-
* of volatile expressions into indexscan quals.
955+
* volatile function) is found. This test prevents, for example,
956+
*invalid conversionsof volatile expressions into indexscan quals.
950957
*
951-
* XXX we do not examine sub-selects to see if they contain uses of
952-
* volatile functions.It's not real clear if that is correct or not...
958+
* We will recursively look into Query nodes (i.e., SubLink sub-selects)
959+
* but not into SubPlans. This is a bit odd, but intentional.If we are
960+
* looking at a SubLink, we are probably deciding whether a query tree
961+
* transformation is safe, and a contained sub-select should affect that;
962+
* for example, duplicating a sub-select containing a volatile function
963+
* would be bad. However, once we've got to the stage of having SubPlans,
964+
* subsequent planning need not consider volatility within those, since
965+
* the executor won't change its evaluation rules for a SubPlan based on
966+
* volatility.
953967
*/
954968
bool
955969
contain_volatile_functions(Node*clause)
@@ -1047,6 +1061,13 @@ contain_volatile_functions_walker(Node *node, void *context)
10471061
}
10481062
/* else fall through to check args */
10491063
}
1064+
elseif (IsA(node,Query))
1065+
{
1066+
/* Recurse into subselects */
1067+
returnquery_tree_walker((Query*)node,
1068+
contain_volatile_functions_walker,
1069+
context,0);
1070+
}
10501071
returnexpression_tree_walker(node,contain_volatile_functions_walker,
10511072
context);
10521073
}

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

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -636,3 +636,67 @@ where a.thousand = b.thousand
636636
----------
637637
(0 rows)
638638

639+
--
640+
-- Check that nested sub-selects are not pulled up if they contain volatiles
641+
--
642+
explain (verbose, costs off)
643+
select x, x from
644+
(select (select now()) as x from (values(1),(2)) v(y)) ss;
645+
QUERY PLAN
646+
---------------------------
647+
Values Scan on "*VALUES*"
648+
Output: $0, $1
649+
InitPlan 1 (returns $0)
650+
-> Result
651+
Output: now()
652+
InitPlan 2 (returns $1)
653+
-> Result
654+
Output: now()
655+
(8 rows)
656+
657+
explain (verbose, costs off)
658+
select x, x from
659+
(select (select random()) as x from (values(1),(2)) v(y)) ss;
660+
QUERY PLAN
661+
----------------------------------
662+
Subquery Scan on ss
663+
Output: ss.x, ss.x
664+
-> Values Scan on "*VALUES*"
665+
Output: $0
666+
InitPlan 1 (returns $0)
667+
-> Result
668+
Output: random()
669+
(7 rows)
670+
671+
explain (verbose, costs off)
672+
select x, x from
673+
(select (select now() where y=y) as x from (values(1),(2)) v(y)) ss;
674+
QUERY PLAN
675+
----------------------------------------------------------------------
676+
Values Scan on "*VALUES*"
677+
Output: (SubPlan 1), (SubPlan 2)
678+
SubPlan 1
679+
-> Result
680+
Output: now()
681+
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
682+
SubPlan 2
683+
-> Result
684+
Output: now()
685+
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
686+
(10 rows)
687+
688+
explain (verbose, costs off)
689+
select x, x from
690+
(select (select random() where y=y) as x from (values(1),(2)) v(y)) ss;
691+
QUERY PLAN
692+
----------------------------------------------------------------------------
693+
Subquery Scan on ss
694+
Output: ss.x, ss.x
695+
-> Values Scan on "*VALUES*"
696+
Output: (SubPlan 1)
697+
SubPlan 1
698+
-> Result
699+
Output: random()
700+
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
701+
(8 rows)
702+

‎src/test/regress/sql/subselect.sql

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -389,3 +389,19 @@ where a.thousand = b.thousand
389389
and exists (select1from tenk1 cwhereb.hundred=c.hundred
390390
and not exists (select1from tenk1 d
391391
wherea.thousand=d.thousand ) );
392+
393+
--
394+
-- Check that nested sub-selects are not pulled up if they contain volatiles
395+
--
396+
explain (verbose, costs off)
397+
select x, xfrom
398+
(select (select now())as xfrom (values(1),(2)) v(y)) ss;
399+
explain (verbose, costs off)
400+
select x, xfrom
401+
(select (select random())as xfrom (values(1),(2)) v(y)) ss;
402+
explain (verbose, costs off)
403+
select x, xfrom
404+
(select (select now()where y=y)as xfrom (values(1),(2)) v(y)) ss;
405+
explain (verbose, costs off)
406+
select x, xfrom
407+
(select (select random()where y=y)as xfrom (values(1),(2)) v(y)) ss;

0 commit comments

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