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

Commit9ec6199

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 parent53685d7 commit9ec6199

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
@@ -856,11 +856,6 @@ generate_subquery_vars(PlannerInfo *root, List *tlist, Index varno)
856856
* with Params or Vars representing the results of the sub-select.The
857857
* nodes to be substituted are passed in as the List result from
858858
* generate_subquery_params or generate_subquery_vars.
859-
*
860-
* The given testexpr has already been recursively processed by
861-
* process_sublinks_mutator. Hence it can no longer contain any
862-
* PARAM_SUBLINK Params for lower SubLink nodes; we can safely assume that
863-
* any we find are for our own level of SubLink.
864859
*/
865860
staticNode*
866861
convert_testexpr(PlannerInfo*root,
@@ -899,6 +894,28 @@ convert_testexpr_mutator(Node *node,
899894
param->paramid-1));
900895
}
901896
}
897+
if (IsA(node,SubLink))
898+
{
899+
/*
900+
* If we come across a nested SubLink, it is neither necessary nor
901+
* correct to recurse into it: any PARAM_SUBLINKs we might find inside
902+
* belong to the inner SubLink not the outer. So just return it as-is.
903+
*
904+
* This reasoning depends on the assumption that nothing will pull
905+
* subexpressions into or out of the testexpr field of a SubLink, at
906+
* least not without replacing PARAM_SUBLINKs first. If we did want
907+
* to do that we'd need to rethink the parser-output representation
908+
* altogether, since currently PARAM_SUBLINKs are only unique per
909+
* SubLink not globally across the query. The whole point of
910+
* replacing them with Vars or PARAM_EXEC nodes is to make them
911+
* globally unique before they escape from the SubLink's testexpr.
912+
*
913+
* Note: this can't happen when called during SS_process_sublinks,
914+
* because that recursively processes inner SubLinks first. It can
915+
* happen when called from convert_ANY_sublink_to_join, though.
916+
*/
917+
returnnode;
918+
}
902919
returnexpression_tree_mutator(node,
903920
convert_testexpr_mutator,
904921
(void*)context);

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -710,3 +710,32 @@ select exists(select * from nocolumns);
710710
f
711711
(1 row)
712712

713+
--
714+
-- Check sane behavior with nested IN SubLinks
715+
--
716+
explain (verbose, costs off)
717+
select * from int4_tbl where
718+
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
719+
(select ten from tenk1 b);
720+
QUERY PLAN
721+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
722+
Nested Loop Semi Join
723+
Output: int4_tbl.f1
724+
Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
725+
-> Seq Scan on public.int4_tbl
726+
Output: int4_tbl.f1
727+
-> Seq Scan on public.tenk1 b
728+
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
729+
SubPlan 1
730+
-> Index Only Scan using tenk1_unique1 on public.tenk1 a
731+
Output: a.unique1
732+
(10 rows)
733+
734+
select * from int4_tbl where
735+
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
736+
(select ten from tenk1 b);
737+
f1
738+
----
739+
0
740+
(1 row)
741+

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -411,3 +411,14 @@ explain (verbose, costs off)
411411
--
412412
create temp table nocolumns();
413413
select exists(select*from nocolumns);
414+
415+
--
416+
-- Check sane behavior with nested IN SubLinks
417+
--
418+
explain (verbose, costs off)
419+
select*from int4_tblwhere
420+
(case when f1in (select unique1from tenk1 a) then f1 elsenull end)in
421+
(select tenfrom tenk1 b);
422+
select*from int4_tblwhere
423+
(case when f1in (select unique1from tenk1 a) then f1 elsenull end)in
424+
(select tenfrom tenk1 b);

0 commit comments

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