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

Commit41e9990

Browse files
committed
Fix possible crash with nested SubLinks.
An expression such as WHERE (... x IN (SELECT ...) ...) IN (SELECT ...)could produce an invalid plan that results in a crash at execution time,if the planner attempts to flatten the outer IN into a semi-join.This happens because convert_testexpr() was not expecting any nestedSubLinks and would wrongly replace any PARAM_SUBLINK Params belongingto the inner SubLink. (I think the comment denying that this case couldhappen was wrong when written; it's certainly been wrong for quite a longtime, since very early versions of the semijoin flattening logic.)Per report from Teodor Sigaev. Back-patch to all supported branches.
1 parent9057adc commit41e9990

File tree

3 files changed

+62
-5
lines changed

3 files changed

+62
-5
lines changed

‎src/backend/optimizer/plan/subselect.c‎

Lines changed: 22 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -766,11 +766,6 @@ generate_subquery_vars(PlannerInfo *root, List *tlist, Index varno)
766766
* with Params or Vars representing the results of the sub-select.The
767767
* nodes to be substituted are passed in as the List result from
768768
* generate_subquery_params or generate_subquery_vars.
769-
*
770-
* The given testexpr has already been recursively processed by
771-
* process_sublinks_mutator. Hence it can no longer contain any
772-
* PARAM_SUBLINK Params for lower SubLink nodes; we can safely assume that
773-
* any we find are for our own level of SubLink.
774769
*/
775770
staticNode*
776771
convert_testexpr(PlannerInfo*root,
@@ -809,6 +804,28 @@ convert_testexpr_mutator(Node *node,
809804
param->paramid-1));
810805
}
811806
}
807+
if (IsA(node,SubLink))
808+
{
809+
/*
810+
* If we come across a nested SubLink, it is neither necessary nor
811+
* correct to recurse into it: any PARAM_SUBLINKs we might find inside
812+
* belong to the inner SubLink not the outer. So just return it as-is.
813+
*
814+
* This reasoning depends on the assumption that nothing will pull
815+
* subexpressions into or out of the testexpr field of a SubLink, at
816+
* least not without replacing PARAM_SUBLINKs first. If we did want
817+
* to do that we'd need to rethink the parser-output representation
818+
* altogether, since currently PARAM_SUBLINKs are only unique per
819+
* SubLink not globally across the query. The whole point of
820+
* replacing them with Vars or PARAM_EXEC nodes is to make them
821+
* globally unique before they escape from the SubLink's testexpr.
822+
*
823+
* Note: this can't happen when called during SS_process_sublinks,
824+
* because that recursively processes inner SubLinks first. It can
825+
* happen when called from convert_ANY_sublink_to_join, though.
826+
*/
827+
returnnode;
828+
}
812829
returnexpression_tree_mutator(node,
813830
convert_testexpr_mutator,
814831
(void*)context);

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -703,3 +703,32 @@ explain (verbose, costs off)
703703
One-Time Filter: ($0 = $0)
704704
(8 rows)
705705

706+
--
707+
-- Check sane behavior with nested IN SubLinks
708+
--
709+
explain (verbose, costs off)
710+
select * from int4_tbl where
711+
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
712+
(select ten from tenk1 b);
713+
QUERY PLAN
714+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
715+
Nested Loop Semi Join
716+
Output: int4_tbl.f1
717+
Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
718+
-> Seq Scan on public.int4_tbl
719+
Output: int4_tbl.f1
720+
-> Seq Scan on public.tenk1 b
721+
Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand, b.fivethous, b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4
722+
SubPlan 1
723+
-> Seq Scan on public.tenk1 a
724+
Output: a.unique1
725+
(10 rows)
726+
727+
select * from int4_tbl where
728+
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
729+
(select ten from tenk1 b);
730+
f1
731+
----
732+
0
733+
(1 row)
734+

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -405,3 +405,14 @@ explain (verbose, costs off)
405405
explain (verbose, costs off)
406406
select x, xfrom
407407
(select (select random()where y=y)as xfrom (values(1),(2)) v(y)) ss;
408+
409+
--
410+
-- Check sane behavior with nested IN SubLinks
411+
--
412+
explain (verbose, costs off)
413+
select*from int4_tblwhere
414+
(case when f1in (select unique1from tenk1 a) then f1 elsenull end)in
415+
(select tenfrom tenk1 b);
416+
select*from int4_tblwhere
417+
(case when f1in (select unique1from tenk1 a) then f1 elsenull end)in
418+
(select tenfrom tenk1 b);

0 commit comments

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