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

Commit48e5cfd

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 parent70165f2 commit48e5cfd

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

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

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -713,3 +713,32 @@ select exists(select * from nocolumns);
713713
f
714714
(1 row)
715715

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

‎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