Hi All: On Tue, Sep 10, 2019 at 9:49 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Richard Guo <ri...@pivotal.io> writes: > > Currently we do not try to pull up sub-select of type ANY_SUBLINK if it > > refers to any Vars of the parent query, as indicated in the code snippet > > below: > > if (contain_vars_of_level((Node *) subselect, 1)) > > return NULL; > > Why do we have this check? > > Because the result would not be a join between two independent tables. > I think this situation is caused by we pull-up the ANY-sublink with 2 steps, the first step is to pull up the sublink as a subquery, and the next step is to pull up the subquery if it is allowed. The benefits of this method are obvious, pulling up the subquery has more requirements, even if we can just finish the first step, we still get huge benefits. However the bad stuff happens if varlevelsup = 1 involves, step 1 fails! The solution here is to use the lateral join to overcome the two independent tables, the issue of this solution includes: 1. LATERAL pretty much constrains things to use a nestloop like below, but this reason is questioned since if we can pull-up the subquery, if so the constraint gone. [1] 2. It has something with unique-ify the inner path. [2] , but Richard thought it should be fixed but without an agreement for all people [3]. 3. Richard [4] found it would fail to get a plan for some query. (the error is below per my testing) > ERROR: failed to build any 3-way joins So back to the root cause of this issue, IIUC, if varlevelsup = 1 involves, can we just bypass the 2-steps method, just as what we do for EXISTS sublinks? If so, we just need to convert the ANY-SUBLINK to EXIST-SUBLINK under the case. The attached is the one commit which includes the 2 methods discussed here, controlled by different GUC separately, for easy testing. Per my test, Query 2 choosed the Unique Join with the IN-to-EXISTS method, but not with the Lateral method, and query 3 raises error with the lateral method, but not with the IN-to-EXISTS method. [1] https://www.postgresql.org/message-id/flat/60794.1568104308%40antos#365d5ec69fd605a8569a2674a33909a1 [2] https://www.postgresql.org/message-id/60794.1568104308%40antos [3] https://www.postgresql.org/message-id/CAN_9JTzqa-3RmHAw3wZv099Rk8xX480YdEvGy%2BJAdVw8dTnHRA%40mail.gmail.com [4] https://www.postgresql.org/message-id/CAMbWs49cvkF9akbomz_fCCKS%3DD5TY%3D4KGHEQcfHPZCXS1GVhkA%40mail.gmail.com > > Can we try to pull up direct-correlated ANY SubLink with the help of > > LATERAL? > > Perhaps. But what's the argument that you'd end up with a better > plan? LATERAL pretty much constrains things to use a nestloop, > so I'm not sure there's anything fundamentally different. > > regards, tom lane > > -- Best Regards Andy Fan
test.sql
Description: application/sql
From 1bdc3b9098851ab1f6897f497daf90c601eca27e Mon Sep 17 00:00:00 2001
From: Andy Fan <zhihui.fan1213@gmail.com>
Date: Sun, 9 Oct 2022 17:47:23 +0800
Subject: [PATCH v1] 2 methods for Pulling up direct-correlated ANY_SUBLINK
---
.../postgres_fdw/expected/postgres_fdw.out | 24 +-
src/backend/optimizer/plan/subselect.c | 7 +-
src/backend/optimizer/prep/prepjointree.c | 291 ++++++++++++++++++
src/backend/utils/misc/guc_tables.c | 23 ++
src/test/regress/expected/join.out | 35 ++-
src/test/regress/expected/subselect.out | 121 ++++++++
src/test/regress/sql/subselect.sql | 61 ++++
7 files changed, 531 insertions(+), 31 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index b3c8ce01313..870590df562 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11377,19 +11377,19 @@ CREATE FOREIGN TABLE foreign_tbl2 () INHERITS (foreign_tbl)
SERVER loopback OPTIONS (table_name 'base_tbl');
EXPLAIN (VERBOSE, COSTS OFF)
SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl);
- QUERY PLAN
------------------------------------------------------------------------------
- Seq Scan on public.base_tbl
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Nested Loop Semi Join
Output: base_tbl.a
- Filter: (SubPlan 1)
- SubPlan 1
- -> Result
- Output: base_tbl.a
- -> Append
- -> Async Foreign Scan on public.foreign_tbl foreign_tbl_1
- Remote SQL: SELECT NULL FROM public.base_tbl
- -> Async Foreign Scan on public.foreign_tbl2 foreign_tbl_2
- Remote SQL: SELECT NULL FROM public.base_tbl
+ -> Seq Scan on public.base_tbl
+ Output: base_tbl.a, base_tbl.b
+ Filter: (base_tbl.a IS NOT NULL)
+ -> Materialize
+ -> Append
+ -> Async Foreign Scan on public.foreign_tbl foreign_tbl_1
+ Remote SQL: SELECT NULL FROM public.base_tbl
+ -> Async Foreign Scan on public.foreign_tbl2 foreign_tbl_2
+ Remote SQL: SELECT NULL FROM public.base_tbl
(11 rows)
SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl);
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 92e33385842..c1e8d8e0c2c 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1264,6 +1264,8 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
* subselect to the query's rangetable, so that it can be referenced in
* the JoinExpr's rarg.
*/
+extern bool enable_lateral_pullup;
+
JoinExpr *
convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
Relids available_rels)
@@ -1286,7 +1288,7 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
* The sub-select must not refer to any Vars of the parent query. (Vars of
* higher levels should be okay, though.)
*/
- if (contain_vars_of_level((Node *) subselect, 1))
+ if (contain_vars_of_level((Node *) subselect, 1) && !enable_lateral_pullup)
return NULL;
/*
@@ -1324,7 +1326,8 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink,
nsitem = addRangeTableEntryForSubquery(pstate,
subselect,
makeAlias("ANY_subquery", NIL),
- false,
+ /* lateral */
+ enable_lateral_pullup ? contain_vars_of_level((Node *) subselect, 1) : false,
false);
rte = nsitem->p_rte;
parse->rtable = lappend(parse->rtable, rte);
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index 41c7066d90a..a8470be1c20 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -130,6 +130,7 @@ static void substitute_phv_relids(Node *node,
static void fix_append_rel_relids(List *append_rel_list, int varno,
Relids subrelids);
static Node *find_jointree_node_for_rel(Node *jtnode, int relid);
+static void transform_IN_sublink_to_EXIST_recurse(PlannerInfo *root, Node *jtnode);
/*
@@ -256,6 +257,291 @@ replace_empty_jointree(Query *parse)
parse->jointree->fromlist = list_make1(rtr);
}
+
+/*
+ * sublink_should_be_transformed
+ *
+ * Check if the sublink is a simple IN sublink.
+ */
+static bool
+sublink_should_be_transformed(PlannerInfo *root, SubLink *sublink)
+{
+ const char * operName;
+ Query *subselect = (Query *) sublink->subselect;
+ Node *whereClause;
+
+ if (sublink->subLinkType != ANY_SUBLINK || list_length(sublink->operName) != 1)
+ return false;
+
+ operName = linitial_node(String, sublink->operName)->sval;
+
+ if (strcmp(operName, "=") != 0)
+ return false;
+
+ if (!contain_vars_of_level((Node *) subselect, 1))
+ /* The existing framework can handle it well, so no action needed. */
+ return false;
+
+ if (list_length(subselect->rtable) == 0)
+ /* Get rid of this special cases for safety. */
+ return false;
+
+ /*
+ * The below checks are similar with the checks in convert_EXISTS_sublink_to_join
+ * so that that the new EXISTS-Sublink can be pull-up later.
+ */
+ if (subselect->cteList)
+ return false;
+
+ /* See simplify_EXISTS_query */
+
+ if (subselect->commandType != CMD_SELECT ||
+ subselect->setOperations ||
+ subselect->hasAggs ||
+ subselect->groupingSets ||
+ subselect->hasWindowFuncs ||
+ subselect->hasTargetSRFs ||
+ subselect->hasModifyingCTE ||
+ subselect->havingQual ||
+ subselect->limitOffset ||
+ subselect->rowMarks)
+ return false;
+
+ if (subselect->limitCount)
+ {
+ /*
+ * The LIMIT clause has not yet been through eval_const_expressions,
+ * so we have to apply that here. It might seem like this is a waste
+ * of cycles, since the only case plausibly worth worrying about is
+ * "LIMIT 1" ... but what we'll actually see is "LIMIT int8(1::int4)",
+ * so we have to fold constants or we're not going to recognize it.
+ */
+ Node *node = eval_const_expressions(root, subselect->limitCount);
+ Const *limit;
+
+ /* Might as well update the query if we simplified the clause. */
+
+ /* XXX: we do have the modification, but it is not harmful. */
+ subselect->limitCount = node;
+
+ if (!IsA(node, Const))
+ return false;
+
+ limit = (Const *) node;
+ Assert(limit->consttype == INT8OID);
+ if (!limit->constisnull && DatumGetInt64(limit->constvalue) <= 0)
+ return false;
+ }
+
+ whereClause = subselect->jointree->quals;
+ subselect->jointree->quals = NULL;
+
+ if (contain_vars_of_level((Node *) subselect, 1) ||
+ !contain_vars_of_level(whereClause, 1) ||
+ contain_volatile_functions(whereClause))
+ {
+ subselect->jointree->quals = whereClause;
+ return false;
+ }
+
+ /* Restore the whereClause. */
+ subselect->jointree->quals = whereClause;
+
+ /*
+ * No need to check the avaiable_rels like convert_EXISTS_sublink_to_join
+ * since here we just transform the sublinks type, no SEMIJOIN related.
+ */
+ return true;
+}
+
+/*
+ * replace_param_sublink_node
+ *
+ * Replace the PARAM_SUBLINK in src with target.
+ */
+static Node *
+replace_param_sublink_node(Node *src, Node *target)
+{
+
+ if (IsA(src, Param))
+ return target;
+
+ switch (nodeTag(src))
+ {
+ case T_RelabelType:
+ {
+ RelabelType *rtype = castNode(RelabelType, src);
+ rtype->arg = (Expr *)target;
+ break;
+ }
+ case T_FuncExpr:
+ {
+ FuncExpr *fexpr = castNode(FuncExpr, src);
+ Assert(list_length(fexpr->args));
+ Assert(linitial_node(Param, fexpr->args)->paramkind == PARAM_SUBLINK);
+ linitial(fexpr->args) = target;
+ break;
+ }
+ default:
+ {
+ Assert(false);
+ elog(ERROR, "Unexpected node type: %d", nodeTag(src));
+ }
+ }
+
+ /* src is in-placed updated. */
+ return src;
+
+}
+
+/*
+ * transform_IN_sublink_to_EXIST_qual_recurse
+ *
+ * Transform IN-SUBLINK with level-1 var to EXISTS-SUBLINK recursly.
+ */
+static Node *
+transform_IN_sublink_to_EXIST_qual_recurse(PlannerInfo *root, Node *node)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, SubLink))
+ {
+ SubLink *sublink = (SubLink *) node;
+ Query *subselect = (Query *)sublink->subselect;
+ FromExpr *sub_fromexpr;
+
+ Assert(IsA(subselect, Query));
+
+ if (!sublink_should_be_transformed(root, sublink))
+ {
+ /* We still need to transform the subselect->jointree. */
+ transform_IN_sublink_to_EXIST_recurse(root, (Node *) subselect->jointree);
+ return node;
+ }
+
+ /*
+ * Make up the push-downed node from sublink->testexpr, the testexpr
+ * will be set to NULL later, so in-place update would be OK.
+ */
+ IncrementVarSublevelsUp(sublink->testexpr, 1, 0);
+
+ if (is_andclause(sublink->testexpr))
+ {
+ BoolExpr *and_expr = castNode(BoolExpr, sublink->testexpr);
+ ListCell *l1, *l2;
+ forboth(l1, and_expr->args, l2, subselect->targetList)
+ {
+ OpExpr *opexpr = lfirst_node(OpExpr, l1);
+ TargetEntry *tle = lfirst_node(TargetEntry, l2);
+ lsecond(opexpr->args) = replace_param_sublink_node(lsecond(opexpr->args),
+ (Node *) tle->expr);
+ }
+ }
+ else
+ {
+ OpExpr *opexpr = (OpExpr *) sublink->testexpr;
+ TargetEntry *tle = linitial_node(TargetEntry, subselect->targetList);
+ Assert(IsA(sublink->testexpr, OpExpr));
+ lsecond(opexpr->args) = replace_param_sublink_node(lsecond(opexpr->args),
+ (Node *) tle->expr);
+ }
+
+ /* Push down the transformed testexpr into subselect */
+ sub_fromexpr = subselect->jointree;
+ if (sub_fromexpr->quals == NULL)
+ sub_fromexpr->quals = sublink->testexpr;
+ else
+ sub_fromexpr->quals = make_and_qual(sub_fromexpr->quals,
+ (Node *) sublink->testexpr);
+
+ /*
+ * Turn the IN-Sublink to exist-SUBLINK for the parent query.
+ * sublink->subselect has already been modified.
+ */
+ sublink->subLinkType = EXISTS_SUBLINK;
+ sublink->operName = NIL;
+ sublink->testexpr = NULL;
+
+ /* Now transform the FromExpr in the subselect->jointree. */
+ transform_IN_sublink_to_EXIST_recurse(root, (Node *)sub_fromexpr);
+ return node;
+ }
+
+ if (is_andclause(node))
+ {
+ List *newclauses = NIL;
+ ListCell *l;
+ foreach(l, ((BoolExpr *) node)->args)
+ {
+ Node *oldclause = (Node *) lfirst(l);
+ Node *newclause;
+
+ newclause = transform_IN_sublink_to_EXIST_qual_recurse(root, oldclause);
+ newclauses = lappend(newclauses, newclause);
+ }
+
+ if (newclauses == NIL)
+ return NULL;
+ else if (list_length(newclauses) == 1)
+ return (Node *) linitial(newclauses);
+ else
+ return (Node *) make_andclause(newclauses);
+ }
+ else if (is_notclause(node))
+ {
+ /*
+ * NOT-IN can't be converted into NOT-exists.
+ */
+ return node;
+ }
+
+ return node;
+}
+
+/*
+ * transform_IN_sublink_to_EXIST_recurse
+ *
+ * Transform IN sublink to EXIST sublink if it benefits for sublink
+ * pull-ups.
+ */
+extern bool enable_geqo;
+static void
+transform_IN_sublink_to_EXIST_recurse(PlannerInfo *root, Node *jtnode)
+{
+ if (!enable_geqo)
+ return;
+
+ if (jtnode == NULL || IsA(jtnode, RangeTblRef))
+ {
+ return;
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *l;
+ foreach(l, f->fromlist)
+ {
+ transform_IN_sublink_to_EXIST_recurse(root, lfirst(l));
+ }
+ f->quals = transform_IN_sublink_to_EXIST_qual_recurse(root, f->quals);
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+ transform_IN_sublink_to_EXIST_recurse(root, j->larg);
+ transform_IN_sublink_to_EXIST_recurse(root, j->rarg);
+
+ j->quals = transform_IN_sublink_to_EXIST_qual_recurse(root, j->quals);
+ }
+ else
+ {
+ elog(ERROR, "unrecognized node type: %d",
+ (int) nodeTag(jtnode));
+ }
+}
+
+
/*
* pull_up_sublinks
* Attempt to pull up ANY and EXISTS SubLinks to be treated as
@@ -284,12 +570,17 @@ replace_empty_jointree(Query *parse)
* to be AND/OR-flat either. That means we need to recursively search through
* explicit AND clauses. We stop as soon as we hit a non-AND item.
*/
+extern bool enable_in_exists_transfrom;
void
pull_up_sublinks(PlannerInfo *root)
{
Node *jtnode;
Relids relids;
+ if (enable_in_exists_transfrom)
+ transform_IN_sublink_to_EXIST_recurse(root,
+ (Node *)root->parse->jointree);
+
/* Begin recursion through the jointree */
jtnode = pull_up_sublinks_jointree_recurse(root,
(Node *) root->parse->jointree,
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 05ab087934c..600f0506043 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -763,8 +763,31 @@ StaticAssertDecl(lengthof(config_type_names) == (PGC_ENUM + 1),
* variable_is_guc_list_quote() in src/bin/pg_dump/dumputils.c.
*/
+bool enable_in_exists_transfrom = false;
+bool enable_lateral_pullup = false;
+
struct config_bool ConfigureNamesBool[] =
{
+ {
+ {"enable_in_exists_transfrom", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("Enables the transform from in to exists."),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &enable_in_exists_transfrom,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"enable_lateral_pullup", PGC_USERSET, QUERY_TUNING_METHOD,
+ gettext_noop("pull up any sublink with lateral"),
+ NULL,
+ GUC_EXPLAIN
+ },
+ &enable_lateral_pullup,
+ false,
+ NULL, NULL, NULL
+ },
{
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 08334761ae6..6d5ccb7de2f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5983,8 +5983,8 @@ lateral (select * from int8_tbl t1,
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
- QUERY PLAN
------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Nested Loop
Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
-> Seq Scan on public.int8_tbl t1
@@ -5996,23 +5996,24 @@ lateral (select * from int8_tbl t1,
-> Subquery Scan on ss2
Output: ss2.q1, ss2.q2
Filter: (t1.q1 = ss2.q2)
- -> Seq Scan on public.int8_tbl t2
+ -> Result
Output: t2.q1, t2.q2
- Filter: (SubPlan 3)
- SubPlan 3
+ One-Time Filter: $3
+ InitPlan 2 (returns $3)
-> Result
- Output: t3.q2
- One-Time Filter: $4
- InitPlan 1 (returns $2)
- -> Result
- Output: GREATEST($0, t2.q2)
- InitPlan 2 (returns $4)
- -> Result
- Output: ($3 = 0)
- -> Seq Scan on public.int8_tbl t3
- Output: t3.q1, t3.q2
- Filter: (t3.q2 = $2)
-(27 rows)
+ Output: ($2 = 0)
+ -> Nested Loop Semi Join
+ Output: t2.q1, t2.q2
+ Join Filter: (t2.q1 = t3.q2)
+ -> Seq Scan on public.int8_tbl t2
+ Output: t2.q1, t2.q2
+ Filter: ((SubPlan 1) = t2.q1)
+ SubPlan 1
+ -> Result
+ Output: GREATEST($0, t2.q2)
+ -> Seq Scan on public.int8_tbl t3
+ Output: t3.q1, t3.q2
+(28 rows)
select * from (values (0), (1)) v(id),
lateral (select * from int8_tbl t1,
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index 63d26d44fc3..5e0ce397233 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -1926,3 +1926,124 @@ select * from x for update;
Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3
(2 rows)
+-- Test transform the level-1 in-sublink to existing sublink.
+create temp table temp_t1 (a int, b int, c int) on commit delete rows;
+create temp table temp_t2 (a int, b int, c int) on commit delete rows;
+create temp table temp_t3 (a int, b int, c int) on commit delete rows;
+create temp table temp_t4 (a int, b int, c int, d int) on commit delete rows;
+begin;
+insert into temp_t1 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t2 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t3 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t4 values (1, 1, 1, 1), (2, 2, null, null), (3, null, null, null);
+analyze temp_t1;
+analyze temp_t2;
+analyze temp_t3;
+-- one-elem in subquery
+select * from temp_t1 t1 where a in (select a from temp_t2 t2 where t2.b > t1.b);
+ a | b | c
+---+---+---
+(0 rows)
+
+explain (costs off)
+select * from temp_t1 t1 where a in (select a from temp_t2 t2 where t2.b > t1.b);
+ QUERY PLAN
+------------------------------------
+ Hash Semi Join
+ Hash Cond: (t1.a = t2.a)
+ Join Filter: (t2.b > t1.b)
+ -> Seq Scan on temp_t1 t1
+ -> Hash
+ -> Seq Scan on temp_t2 t2
+(6 rows)
+
+-- two-elem in subquery
+select * from temp_t1 t1 where (a, b) in (select a, b from temp_t2 t2 where t2.c = t1.c);
+ a | b | c
+---+---+---
+ 1 | 1 | 1
+(1 row)
+
+explain (costs off)
+select * from temp_t1 t1 where (a, b) in (select a, b from temp_t2 t2 where t2.c = t1.c);
+ QUERY PLAN
+------------------------------------------------------------------
+ Hash Semi Join
+ Hash Cond: ((t1.c = t2.c) AND (t1.a = t2.a) AND (t1.b = t2.b))
+ -> Seq Scan on temp_t1 t1
+ -> Hash
+ -> Seq Scan on temp_t2 t2
+(5 rows)
+
+-- sublink in sublink
+select * from temp_t1 t1
+where (a, b) in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t2.c in (select c from temp_t3 t3 where t3.b = t2.b));
+ a | b | c
+---+---+---
+(0 rows)
+
+explain (costs off)
+select * from temp_t1 t1
+where (a, b) in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t2.c in (select c from temp_t3 t3 where t3.b = t2.b));
+ QUERY PLAN
+--------------------------------------------------------------------
+ Nested Loop Semi Join
+ Join Filter: ((t2.c < t1.c) AND (t1.b = t3.b) AND (t1.a = t2.a))
+ -> Seq Scan on temp_t1 t1
+ -> Materialize
+ -> Hash Semi Join
+ Hash Cond: ((t2.b = t3.b) AND (t2.c = t3.c))
+ -> Seq Scan on temp_t2 t2
+ -> Hash
+ -> Seq Scan on temp_t3 t3
+(9 rows)
+
+-- sublink in not-in sublinks. not in will not be transformed but the in-clause
+-- in the subselect should be transformed.
+explain (costs off)
+select * from temp_t1 t1
+where (a, b) not in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t1.c in (SELECT c from temp_t3 t3 where t3.b = t2.b ))
+and c > 3;
+ QUERY PLAN
+-------------------------------------------
+ Seq Scan on temp_t1 t1
+ Filter: ((c > 3) AND (NOT (SubPlan 1)))
+ SubPlan 1
+ -> Nested Loop Semi Join
+ Join Filter: (t2.b = t3.b)
+ -> Seq Scan on temp_t2 t2
+ Filter: (c < t1.c)
+ -> Seq Scan on temp_t3 t3
+ Filter: (t1.c = c)
+(9 rows)
+
+-- The clause in the ON-clause should be transformed.
+explain (costs off)
+select * from temp_t1 t1, (temp_t2 t2 join temp_t4 t4
+ on t2.a in (select a from temp_t3 t3 where t4.b = t3.b)) v
+where t1.a = v.d;
+ QUERY PLAN
+------------------------------------------------------
+ Nested Loop
+ Join Filter: (t3.a = t2.a)
+ -> Hash Join
+ Hash Cond: (t4.d = t1.a)
+ -> Hash Join
+ Hash Cond: (t4.b = t3.b)
+ -> Seq Scan on temp_t4 t4
+ -> Hash
+ -> HashAggregate
+ Group Key: t3.b, t3.a
+ -> Seq Scan on temp_t3 t3
+ -> Hash
+ -> Seq Scan on temp_t1 t1
+ -> Seq Scan on temp_t2 t2
+(14 rows)
+
+commit;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 40276708c99..83ad18cf9b1 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -968,3 +968,64 @@ select * from (with x as (select 2 as y) select * from x) ss;
explain (verbose, costs off)
with x as (select * from subselect_tbl)
select * from x for update;
+
+
+-- Test transform the level-1 in-sublink to existing sublink.
+create temp table temp_t1 (a int, b int, c int) on commit delete rows;
+create temp table temp_t2 (a int, b int, c int) on commit delete rows;
+create temp table temp_t3 (a int, b int, c int) on commit delete rows;
+create temp table temp_t4 (a int, b int, c int, d int) on commit delete rows;
+
+begin;
+insert into temp_t1 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t2 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t3 values (1, 1, 1), (2, 2, null), (3, null, null);
+insert into temp_t4 values (1, 1, 1, 1), (2, 2, null, null), (3, null, null, null);
+
+analyze temp_t1;
+analyze temp_t2;
+analyze temp_t3;
+
+-- one-elem in subquery
+select * from temp_t1 t1 where a in (select a from temp_t2 t2 where t2.b > t1.b);
+explain (costs off)
+select * from temp_t1 t1 where a in (select a from temp_t2 t2 where t2.b > t1.b);
+
+-- two-elem in subquery
+select * from temp_t1 t1 where (a, b) in (select a, b from temp_t2 t2 where t2.c = t1.c);
+explain (costs off)
+select * from temp_t1 t1 where (a, b) in (select a, b from temp_t2 t2 where t2.c = t1.c);
+
+-- sublink in sublink
+select * from temp_t1 t1
+where (a, b) in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t2.c in (select c from temp_t3 t3 where t3.b = t2.b));
+explain (costs off)
+select * from temp_t1 t1
+where (a, b) in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t2.c in (select c from temp_t3 t3 where t3.b = t2.b));
+
+
+-- sublink in not-in sublinks. not in will not be transformed but the in-clause
+-- in the subselect should be transformed.
+explain (costs off)
+select * from temp_t1 t1
+where (a, b) not in (select a, b from temp_t2 t2
+ where t2.c < t1.c
+ and t1.c in (SELECT c from temp_t3 t3 where t3.b = t2.b ))
+and c > 3;
+
+
+-- The clause in the ON-clause should be transformed.
+explain (costs off)
+select * from temp_t1 t1, (temp_t2 t2 join temp_t4 t4
+ on t2.a in (select a from temp_t3 t3 where t4.b = t3.b)) v
+where t1.a = v.d;
+
+commit;
+
+
+
+
--
2.21.0
