You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudberry.apache.org by ma...@apache.org on 2025/02/07 09:28:29 UTC
(cloudberry) 15/34: ORCA should generate plans with the ProjectSet node (#15170)
This is an automated email from the ASF dual-hosted git repository.
maxyang pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 6992643ee625bd0d0ae79d352b2b6bf8ad66f02b
Author: Dev Swaroop Chattopadhyay <10...@users.noreply.github.com>
AuthorDate: Mon Jun 12 21:02:29 2023 +0530
ORCA should generate plans with the ProjectSet node (#15170)
* ORCA should generate plans with the ProjectSet node
Motivation:
===========
In postgres 11 a new executor node ProjectSet was introduced that can evaluate
targetlists containing one or more set returning functions. Refer commit
69f4b9c. This was absorbed in ORCA in Postgres12 merge.
Query: `explain select generate_series(1,3)+1 as x;`
Currently for the above query ORCA will generate a Result dxl node containing
set returning function in the projection list(target list). But since after
postgres11, ProjectSet nodes are only allowed to evaluate targetlists
containing set returning functions, this query will fallback to planner. This
PR is to implement ProjectSet node within ORCA to handle set returning
functions in the targetlist.
Implementation:
===============
The approach taken in this PR is to use the targetlist of the Result dxl node.
While translating the Result dxl node to planned statement node, the target
list of Result dxl node is checked for any set returning functions and if
present then required ProjectSet nodes are created using that. The advantage
of this approach is that we already have a targetlist and we just need to
figure out if ProjectSet nodes are required and create them. This all can be
done by using postgres functions. But the con is that it adds a lot of logic in
the translation part of Result dxl node.
Another Approaches:
===================
There can also be another approaches to solve this problem like below
We can create ProjectSet dxl nodes while translation of expression to dxl. But
the issue with this approach is that currently the CPhysicalComputeScalar
operator which is translated to a Result dxl node contains the set returning
functions in the Project list. So in order to create ProjectSet nodes from
CPhysicalComputeScalar we need to separate out all the set returning functions
which is tricky and have to create a lot of project list trees which will be
used to create multiple ProjectSet nodes. Also we need a logic to determine if
we need a Result dxl node on top of a ProjectSet node.
Another approach would be to create a CLogicalProjectSet expression to handle
set returning functions and traverse it through ORCA to generate a
CPhysicalProjectSet and create a dxl out of it. This will be the more
appropriate approach and will be done as a part of future work.
Before Fix:
============
- ORCA would currently fallback as soon as we see an SRF in the targetlist.
```
explain select generate_series(1,3)+1 as x;
LOG: statement: explain select generate_series(1,3)+1 as x;
LOG: 2022-10-18 15:27:38:782047 IST,THD000,NOTICE,"Feature not supported: Unexpected target list entries in ProjectSet node",
QUERY PLAN
------------------------------------------------------
Result (cost=0.00..0.08 rows=3 width=4)
-> ProjectSet (cost=0.00..0.03 rows=3 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
Optimizer: Postgres query optimizer
(4 rows)
```
After Fix:
===========
- ORCA is now able to generate a ProjectSet planned statement node.
```
explain select generate_series(1,4)+1 as x;
QUERY PLAN
------------------------------------------------------
Result (cost=0.00..0.00 rows=1 width=4)
-> ProjectSet (cost=0.00..0.00 rows=1 width=4)
-> Result (cost=0.00..0.00 rows=1 width=1)
Optimizer: Pivotal Optimizer (GPORCA)
(4 rows)
select generate_series(1,4)+1 as x;
x
---
2
3
4
5
(4 rows)
```
* Refactoring Code
* Refactoring Code
* Attaching child plan after mutating FUNCEXPR to VAR
* Refactoring code and adding testcases
* Addressing Feedback
* Addressing Feedback
* Incorporating Feedback
* Modifying Code
* Incorporating Feedback
* Incorporating Feedback
* Creating gp_tsrf for testcases
* Incorporating Feedback
* Incorporating Feedback and formating comments
---
src/backend/gpopt/gpdbwrappers.cpp | 79 ++++
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 467 +++++++++++++++------
src/backend/optimizer/util/tlist.c | 11 +-
src/include/gpopt/gpdbwrappers.h | 16 +
.../gpopt/translate/CTranslatorDXLToPlStmt.h | 15 +-
.../regress/expected/DML_over_joins_optimizer.out | 190 ---------
src/test/regress/expected/gp_dqa_optimizer.out | 4 -
src/test/regress/expected/gp_tsrf.out | 262 ++++++++++++
src/test/regress/expected/gp_tsrf_optimizer.out | 263 ++++++++++++
src/test/regress/expected/gporca_optimizer.out | 8 -
src/test/regress/expected/qp_misc_optimizer.out | 2 -
src/test/regress/expected/tsrf_optimizer.out | 39 +-
src/test/regress/greenplum_schedule | 2 +-
src/test/regress/sql/gp_tsrf.sql | 45 ++
14 files changed, 1047 insertions(+), 356 deletions(-)
diff --git a/src/backend/gpopt/gpdbwrappers.cpp b/src/backend/gpopt/gpdbwrappers.cpp
index d09cb55565..73e92202de 100644
--- a/src/backend/gpopt/gpdbwrappers.cpp
+++ b/src/backend/gpopt/gpdbwrappers.cpp
@@ -2674,4 +2674,83 @@ gpdb::GetRelFdwName(Oid reloid)
return nullptr;
}
+PathTarget *
+gpdb::MakePathtargetFromTlist(List *tlist)
+{
+ GP_WRAP_START;
+ {
+ return make_pathtarget_from_tlist(tlist);
+ }
+ GP_WRAP_END;
+}
+
+void
+gpdb::SplitPathtargetAtSrfs(PlannerInfo *root, PathTarget *target,
+ PathTarget *input_target, List **targets,
+ List **targets_contain_srfs)
+{
+ GP_WRAP_START;
+ {
+ split_pathtarget_at_srfs(root, target, input_target, targets,
+ targets_contain_srfs);
+ }
+ GP_WRAP_END;
+}
+
+List *
+gpdb::MakeTlistFromPathtarget(PathTarget *target)
+{
+ GP_WRAP_START;
+ {
+ return make_tlist_from_pathtarget(target);
+ }
+ GP_WRAP_END;
+ return NIL;
+}
+
+Node *
+gpdb::Expression_tree_mutator(Node *node, Node *(*mutator)(), void *context)
+{
+ GP_WRAP_START;
+ {
+ return expression_tree_mutator(node, mutator, context);
+ }
+ GP_WRAP_END;
+
+ return nullptr;
+}
+
+TargetEntry *
+gpdb::TlistMember(Expr *node, List *targetlist)
+{
+ GP_WRAP_START;
+ {
+ return tlist_member(node, targetlist);
+ }
+ GP_WRAP_END;
+
+ return nullptr;
+}
+
+Var *
+gpdb::MakeVarFromTargetEntry(Index varno, TargetEntry *tle)
+{
+ GP_WRAP_START;
+ {
+ return makeVarFromTargetEntry(varno, tle);
+ }
+ GP_WRAP_END;
+}
+
+TargetEntry *
+gpdb::FlatCopyTargetEntry(TargetEntry *src_tle)
+{
+ GP_WRAP_START;
+ {
+ return flatCopyTargetEntry(src_tle);
+ }
+ GP_WRAP_END;
+}
+
+
// EOF
diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
index a2b5e0c366..8fc2f8447a 100644
--- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
@@ -3322,9 +3322,57 @@ CTranslatorDXLToPlStmt::TranslateDXLSubQueryScan(
return (Plan *) subquery_scan;
}
-static bool
-ContainsSetReturningFuncOrOp(const CDXLNode *project_list_dxlnode,
- CMDAccessor *md_accessor)
+//------------------------------------------------------------------------------
+// If the top level is not a function returning set then we need to check if the
+// project element contains any SRF's deep down the tree. If we found any SRF's
+// at lower levels then we will require a result node on top of ProjectSet node.
+// Eg.
+// <dxl:ProjElem ColId="1" Alias="abs">
+// <dxl:FuncExpr FuncId="0.1397.1.0" FuncRetSet="false" TypeMdid="0.23.1.0">
+// <dxl:FuncExpr FuncId="0.1067.1.0" FuncRetSet="true" TypeMdid="0.23.1.0">
+// ...
+// </dxl:FuncExpr>
+// </dxl:FuncExpr>
+// Here we have SRF present at a lower level. So we will require a result node
+// on top.
+//------------------------------------------------------------------------------
+static BOOL
+ContainsLowLevelSetReturningFunc(const CDXLNode *scalar_expr_dxlnode)
+{
+ const ULONG arity = scalar_expr_dxlnode->Arity();
+ for (ULONG ul = 0; ul < arity; ul++)
+ {
+ CDXLNode *expr_dxlnode = (*scalar_expr_dxlnode)[ul];
+ CDXLOperator *op = expr_dxlnode->GetOperator();
+ Edxlopid dxlopid = op->GetDXLOperator();
+
+ if ((EdxlopScalarFuncExpr == dxlopid &&
+ CDXLScalarFuncExpr::Cast(op)->ReturnsSet()) ||
+ ContainsLowLevelSetReturningFunc(expr_dxlnode))
+ {
+ return true;
+ }
+ }
+ return false;
+}
+
+//------------------------------------------------------------------------------
+// This method is required to check if we need a result node on top of
+// ProjectSet node. If the project element contains SRF on top then we don't
+// require a result node. Eg
+// <dxl:ProjElem ColId="1" Alias="generate_series">
+// <dxl:FuncExpr FuncId="0.1067.1.0" FuncRetSet="true" TypeMdid="0.23.1.0">
+// ...
+// <dxl:FuncExpr FuncId="0.1067.1.0" FuncRetSet="true" TypeMdid="0.23.1.0">
+// ...
+// </dxl:FuncExpr>
+// ...
+// </dxl:FuncExpr>
+// Here we have a FuncExpr which returns a set on top. So we don't require a
+// result node on top of ProjectSet node.
+//------------------------------------------------------------------------------
+static BOOL
+RequiresResultNode(const CDXLNode *project_list_dxlnode)
{
const ULONG arity = project_list_dxlnode->Arity();
for (ULONG ul = 0; ul < arity; ++ul)
@@ -3333,82 +3381,44 @@ ContainsSetReturningFuncOrOp(const CDXLNode *project_list_dxlnode,
GPOS_ASSERT(EdxlopScalarProjectElem ==
proj_elem_dxlnode->GetOperator()->GetDXLOperator());
GPOS_ASSERT(1 == proj_elem_dxlnode->Arity());
-
- // translate proj element expression
CDXLNode *expr_dxlnode = (*proj_elem_dxlnode)[0];
-
CDXLOperator *op = expr_dxlnode->GetOperator();
- switch (op->GetDXLOperator())
+ Edxlopid dxlopid = op->GetDXLOperator();
+ if (EdxlopScalarFuncExpr == dxlopid)
{
- case EdxlopScalarFuncExpr:
- if (CDXLScalarFuncExpr::Cast(op)->ReturnsSet())
- {
- return true;
- }
- break;
- case EdxlopScalarOpExpr:
+ if (!(CDXLScalarFuncExpr::Cast(op)->ReturnsSet()) &&
+ ContainsLowLevelSetReturningFunc(expr_dxlnode))
{
- const IMDScalarOp *md_sclar_op = md_accessor->RetrieveScOp(
- CDXLScalarOpExpr::Cast(op)->MDId());
- const IMDFunction *md_func =
- md_accessor->RetrieveFunc(md_sclar_op->FuncMdId());
- if (md_func->ReturnsSet())
- {
- return true;
- }
- break;
+ return true;
}
- default:
- break;
}
- }
- return false;
-}
-
-static bool
-SanityCheckProjectSetTargetList(List *targetlist)
-{
- ListCell *lc;
- ForEach(lc, targetlist)
- {
- TargetEntry *te = (TargetEntry *) lfirst(lc);
- Expr *expr = te->expr;
- List *args;
- if ((IsA(expr, FuncExpr) && ((FuncExpr *) expr)->funcretset) ||
- (IsA(expr, OpExpr) && ((OpExpr *) expr)->opretset))
+ else
{
- if (IsA(expr, FuncExpr))
- {
- args = ((FuncExpr *) expr)->args;
- }
- else
- {
- args = ((OpExpr *) expr)->args;
- }
- if (gpdb::ExpressionReturnsSet((Node *) args))
+ if (ContainsLowLevelSetReturningFunc(expr_dxlnode))
{
- return false;
+ return true;
}
- continue;
- }
-
- if (gpdb::ExpressionReturnsSet((Node *) expr))
- {
- return false;
}
}
- return true;
+ return false;
}
-// XXX: this is a copy-pasta of TranslateDXLResult
-// Is there a way to reduce the duplication?
+//------------------------------------------------------------------------------
+// @function:
+// CTranslatorDXLToPlStmt::TranslateDXLProjectSet
+//
+// @doc:
+// Translate DXL result node into project set node if SRF's are present
+//
+//------------------------------------------------------------------------------
Plan *
-CTranslatorDXLToPlStmt::TranslateDXLProjectSet(
- const CDXLNode *result_dxlnode, CDXLTranslateContext *output_context,
- CDXLTranslationContextArray *ctxt_translation_prev_siblings)
+CTranslatorDXLToPlStmt::TranslateDXLProjectSet(const CDXLNode *result_dxlnode)
{
- // GPDB_12_MERGE_FIXME: had we generated a DXLProjectSet in ORCA we wouldn't
- // have needed to be defensive here...
+ // ORCA_FEATURE_NOT_SUPPORTED: The Project Set nodes don't support a qual in
+ // the planned statement. Just being defensive here for the case when the
+ // result dxl node has a set returning function in the project list and also
+ // a qual. In that case will not create a ProjectSet node and will fall back
+ // to planner.
if ((*result_dxlnode)[EdxlresultIndexFilter]->Arity() > 0)
{
GPOS_RAISE(
@@ -3416,7 +3426,7 @@ CTranslatorDXLToPlStmt::TranslateDXLProjectSet(
GPOS_WSZ_LIT("Unsupported one-time filter in ProjectSet node"));
}
- // create project set (nee result) plan node
+ // create project set plan node
ProjectSet *project_set = MakeNode(ProjectSet);
Plan *plan = &(project_set->plan);
@@ -3425,85 +3435,265 @@ CTranslatorDXLToPlStmt::TranslateDXLProjectSet(
// translate operator costs
TranslatePlanCosts(result_dxlnode, plan);
- CDXLNode *child_dxlnode = nullptr;
- CDXLTranslateContext child_context(m_mp, false,
- output_context->GetColIdToParamIdMap());
+ SetParamIds(plan);
- if (result_dxlnode->Arity() - 1 == EdxlresultIndexChild)
+ return (Plan *) project_set;
+}
+
+//------------------------------------------------------------------------------
+// @function:
+// CTranslatorDXLToPlStmt::CreateProjectSetNodeTree
+//
+// @doc:
+// Creates a tree of project set plan nodes to contain the SRF's
+//
+//------------------------------------------------------------------------------
+Plan *
+CTranslatorDXLToPlStmt::CreateProjectSetNodeTree(const CDXLNode *result_dxlnode,
+ Plan *result_node_plan,
+ Plan *child_plan,
+ Plan *&project_set_child_plan,
+ BOOL &will_require_result_node)
+{
+ // Method split_pathtarget_at_srfs will split the given PathTarget into
+ // multiple levels to position SRFs safely. This list will hold the splited
+ // PathTarget created by split_pathtarget_at_srfs method.
+ List *targets_with_srf = NIL;
+
+ // List of bool flags indicating whether the corresponding PathTarget
+ // contains any evaluatable SRFs
+ List *targets_with_srf_bool = NIL;
+
+ // Pointer to the top level ProjectSet node. If a result node is required
+ // then this will be attached to the lefttree of the result node.
+ Plan *project_set_parent_plan = nullptr;
+
+ // Create Pathtarget object from Result node's targetlist which is required
+ // by SplitPathtargetAtSrfs method
+ PathTarget *complete_result_pathtarget =
+ gpdb::MakePathtargetFromTlist(result_node_plan->targetlist);
+
+ // Split given PathTarget into multiple levels to position SRFs safely
+ gpdb::SplitPathtargetAtSrfs(nullptr, complete_result_pathtarget, nullptr,
+ &targets_with_srf, &targets_with_srf_bool);
+
+ // If the PathTarget created from Result node's targetlist does not contain
+ // any set returning functions then split_pathtarget_at_srfs method will
+ // return the same PathTarget back. In this case a ProjectSet node is not
+ // required.
+ if (1 == gpdb::ListLength(targets_with_srf))
{
- // translate child plan
- child_dxlnode = (*result_dxlnode)[EdxlresultIndexChild];
+ return nullptr;
+ }
- Plan *child_plan = TranslateDXLOperatorToPlan(
- child_dxlnode, &child_context, ctxt_translation_prev_siblings);
+ // Do we require a result node to be attached on top of ProjectSet node?
+ will_require_result_node =
+ RequiresResultNode((*result_dxlnode)[EdxlresultIndexProjList]);
- GPOS_ASSERT(nullptr != child_plan && "child plan cannot be NULL");
+ ListCell *lc;
+ ULONG list_cell_pos = 1;
+ ULONG targets_with_srf_list_length = gpdb::ListLength(targets_with_srf);
+
+ ForEach(lc, targets_with_srf)
+ {
+ // The first element of the PathTarget list created by
+ // split_pathtarget_at_srfs method will not contain any
+ // SRF's. So skipping it.
+ if (list_cell_pos == 1)
+ {
+ list_cell_pos++;
+ continue;
+ }
+
+ // If a Result node is required on top of a ProjectSet node then the
+ // last element of PathTarget list created by split_pathtarget_at_srfs
+ // method will contain the PathTarget of the result node. Since result
+ // node is already created before, breaking out from the loop. If a
+ // result node is not required on top of a ProjectSet node, continue to
+ // create a ProjectSet node.
+ if (will_require_result_node &&
+ targets_with_srf_list_length == list_cell_pos)
+ {
+ break;
+ }
+
+ list_cell_pos++;
- project_set->plan.lefttree = child_plan;
+ List *target_list_entry =
+ gpdb::MakeTlistFromPathtarget((PathTarget *) lfirst(lc));
+
+ Plan *temp_plan_project_set = TranslateDXLProjectSet(result_dxlnode);
+
+ temp_plan_project_set->targetlist = target_list_entry;
+
+ // Creating the links between all the nested ProjectSet nodes
+ if (nullptr == project_set_parent_plan)
+ {
+ project_set_parent_plan = temp_plan_project_set;
+ project_set_child_plan = temp_plan_project_set;
+ }
+ else
+ {
+ temp_plan_project_set->lefttree = project_set_parent_plan;
+ project_set_parent_plan = temp_plan_project_set;
+ }
}
- CDXLNode *project_list_dxlnode = (*result_dxlnode)[EdxlresultIndexProjList];
- CDXLNode *filter_dxlnode = (*result_dxlnode)[EdxlresultIndexFilter];
+ return project_set_parent_plan;
+}
- List *quals_list = nullptr;
+//------------------------------------------------------------------------------
+// If a result plan node is not required on top of a project set node then the
+// alias parameter needs to be set for all the project set nodes else not
+// required as that information will already be present in the result node
+// created
+//------------------------------------------------------------------------------
+void
+SetupAliasParameter(const BOOL will_require_result_node,
+ const CDXLNode *project_list_dxlnode,
+ Plan *project_set_parent_plan)
+{
+ if (!will_require_result_node)
+ {
+ // Setting up the alias value (te->resname)
+ ULONG ul = 0;
+ ListCell *listcell_project_targetentry;
- CDXLTranslationContextArray *child_contexts =
- GPOS_NEW(m_mp) CDXLTranslationContextArray(m_mp);
- child_contexts->Append(&child_context);
+ ForEach(listcell_project_targetentry,
+ project_set_parent_plan->targetlist)
+ {
+ TargetEntry *te =
+ (TargetEntry *) lfirst(listcell_project_targetentry);
- // translate proj list and filter
- TranslateProjListAndFilter(project_list_dxlnode, filter_dxlnode,
- nullptr, // translate context for the base table
- child_contexts, &plan->targetlist, &quals_list,
- output_context);
+ CDXLNode *proj_elem_dxlnode = (*project_list_dxlnode)[ul];
+ GPOS_ASSERT(EdxlopScalarProjectElem ==
+ proj_elem_dxlnode->GetOperator()->GetDXLOperator());
- plan->qual = quals_list;
+ CDXLScalarProjElem *sc_proj_elem_dxlop =
+ CDXLScalarProjElem::Cast(proj_elem_dxlnode->GetOperator());
- SetParamIds(plan);
+ GPOS_ASSERT(1 == proj_elem_dxlnode->Arity());
- // cleanup
- child_contexts->Release();
+ te->resname =
+ CTranslatorUtils::CreateMultiByteCharStringFromWCString(
+ sc_proj_elem_dxlop->GetMdNameAlias()
+ ->GetMDName()
+ ->GetBuffer());
+ ul++;
+ }
+ }
+}
- // double check the targetlist is kosher
- // we are only doing this because ORCA didn't do it...
- if (!SanityCheckProjectSetTargetList(plan->targetlist))
+//------------------------------------------------------------------------------
+// This method is used to convert the FUNCEXPR present in upper level
+// Result/ProjectSet nodes targetlist to VAR nodes which reference the FUNCEXPR
+// present in the leftree plan targetlist.
+//------------------------------------------------------------------------------
+void
+CTranslatorDXLToPlStmt::MutateFuncExprToVarProjectSet(Plan *final_plan)
+{
+ Plan *it_set_upper_ref = final_plan;
+ while (it_set_upper_ref->lefttree != nullptr)
{
- GPOS_RAISE(
- gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature,
- GPOS_WSZ_LIT("Unexpected target list entries in ProjectSet node"));
+ Plan *subplan = it_set_upper_ref->lefttree;
+ List *output_targetlist;
+ ListCell *l;
+ output_targetlist = NIL;
+
+ foreach (l, it_set_upper_ref->targetlist)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(l);
+ Node *newexpr;
+
+ newexpr = FixUpperExprMutatorProjectSet((Node *) tle->expr,
+ subplan->targetlist);
+ tle = gpdb::FlatCopyTargetEntry(tle);
+ tle->expr = (Expr *) newexpr;
+ output_targetlist = lappend(output_targetlist, tle);
+ }
+ it_set_upper_ref->targetlist = output_targetlist;
+ it_set_upper_ref = it_set_upper_ref->lefttree;
}
+}
- return (Plan *) project_set;
+Var *
+SearchTlistForNonVarProjectset(Expr *node, List *itlist, Index newvarno)
+{
+ TargetEntry *tle;
+
+ if (IsA(node, Const))
+ {
+ return nullptr;
+ }
+
+ tle = gpdb::TlistMember(node, itlist);
+ if (nullptr != tle)
+ {
+ /* Found a matching subplan output expression */
+ Var *newvar;
+
+ newvar = gpdb::MakeVarFromTargetEntry(newvarno, tle);
+ newvar->varnosyn = 0;
+ newvar->varattnosyn = 0;
+ return newvar;
+ }
+ return nullptr; /* no match */
}
-//---------------------------------------------------------------------------
+Node *
+CTranslatorDXLToPlStmt::FixUpperExprMutatorProjectSet(Node *node, List *context)
+{
+ Var *newvar;
+
+ if (node == nullptr)
+ {
+ return nullptr;
+ }
+
+ newvar = SearchTlistForNonVarProjectset((Expr *) node, context, OUTER_VAR);
+ if (nullptr != newvar)
+ {
+ return (Node *) newvar;
+ }
+
+ return gpdb::Expression_tree_mutator(
+ node,
+ (Node * (*) ()) CTranslatorDXLToPlStmt::FixUpperExprMutatorProjectSet,
+ (void *) context);
+}
+
+//------------------------------------------------------------------------------
// @function:
// CTranslatorDXLToPlStmt::TranslateDXLResult
//
// @doc:
-// Translate DXL result node into GPDB result plan node
-//
-//---------------------------------------------------------------------------
+// Translate DXL result node into GPDB result plan node and create Project
+// Set plan node if SRV are present. The current approach is to create a
+// Project Set plan node from a result dxl node as it already contains the
+// info to create a project set node from it. But it's not the best
+// approach. The better approach will be to actually create a new Clogical
+// node to handle the set returning functions and then creating CPhysical,
+// dxl and plan nodes.
+//------------------------------------------------------------------------------
Plan *
CTranslatorDXLToPlStmt::TranslateDXLResult(
const CDXLNode *result_dxlnode, CDXLTranslateContext *output_context,
CDXLTranslationContextArray *ctxt_translation_prev_siblings)
{
- // GPDB_12_MERGE_FIXME: this *really* should be done inside ORCA
- // at the latest during CTranslatorExprToDXL, to create a DXLProjectSet
- // that way we don't have to "frisk" the DXLResult to distinguish it from an
- // actual result node
- if (ContainsSetReturningFuncOrOp((*result_dxlnode)[EdxlresultIndexProjList],
- m_md_accessor))
- {
- return TranslateDXLProjectSet(result_dxlnode, output_context,
- ctxt_translation_prev_siblings);
- }
+ // Pointer to the child plan of result node
+ Plan *child_plan = nullptr;
+
+ // Pointer to the lowest level ProjectSet node. If multiple ProjectSet nodes
+ // are required then the child plan of result dxl node will be attched to
+ // its lefttree.
+ Plan *project_set_child_plan = nullptr;
+
+ // Do we require a result node to be attached on top of ProjectSet node?
+ BOOL will_require_result_node = false;
// create result plan node
Result *result = MakeNode(Result);
-
Plan *plan = &(result->plan);
plan->plan_node_id = m_dxl_to_plstmt_context->GetNextPlanId();
@@ -3518,20 +3708,15 @@ CTranslatorDXLToPlStmt::TranslateDXLResult(
{
// translate child plan
child_dxlnode = (*result_dxlnode)[EdxlresultIndexChild];
-
- Plan *child_plan = TranslateDXLOperatorToPlan(
- child_dxlnode, &child_context, ctxt_translation_prev_siblings);
-
+ child_plan = TranslateDXLOperatorToPlan(child_dxlnode, &child_context,
+ ctxt_translation_prev_siblings);
GPOS_ASSERT(nullptr != child_plan && "child plan cannot be NULL");
-
- result->plan.lefttree = child_plan;
}
CDXLNode *project_list_dxlnode = (*result_dxlnode)[EdxlresultIndexProjList];
CDXLNode *filter_dxlnode = (*result_dxlnode)[EdxlresultIndexFilter];
CDXLNode *one_time_filter_dxlnode =
(*result_dxlnode)[EdxlresultIndexOneTimeFilter];
-
List *quals_list = nullptr;
CDXLTranslationContextArray *child_contexts =
@@ -3551,24 +3736,46 @@ CTranslatorDXLToPlStmt::TranslateDXLResult(
child_contexts, output_context);
plan->qual = quals_list;
-
result->resconstantqual = (Node *) one_time_quals_list;
-
SetParamIds(plan);
- // cleanup
- child_contexts->Release();
+ // Creating project set nodes plan tree
+ Plan *project_set_parent_plan = CreateProjectSetNodeTree(
+ result_dxlnode, plan, child_plan, project_set_child_plan,
+ will_require_result_node);
- // double check the targetlist is kosher
- // we are only doing this because ORCA didn't do it...
- if (!SanityCheckProjectSetTargetList(plan->targetlist))
+ // If Project Set plan nodes are not required return the result plan node
+ // created
+ if (nullptr == project_set_parent_plan)
{
- GPOS_RAISE(
- gpdxl::ExmaDXL, gpdxl::ExmiQuery2DXLUnsupportedFeature,
- GPOS_WSZ_LIT("Unexpected target list entries in ProjectSet node"));
+ result->plan.lefttree = child_plan;
+ child_contexts->Release();
+ return (Plan *) result;
}
- return (Plan *) result;
+ SetupAliasParameter(will_require_result_node, project_list_dxlnode,
+ project_set_parent_plan);
+
+ Plan *final_plan = nullptr;
+
+ if (will_require_result_node)
+ {
+ result->plan.lefttree = project_set_parent_plan;
+ final_plan = &(result->plan);
+ }
+ else
+ {
+ final_plan = project_set_parent_plan;
+ }
+
+ MutateFuncExprToVarProjectSet(final_plan);
+
+ // Attaching the child plan
+ project_set_child_plan->lefttree = child_plan;
+
+ // cleanup
+ child_contexts->Release();
+ return final_plan;
}
//---------------------------------------------------------------------------
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index f32d014fa8..9d0f3274db 100644
--- a/src/backend/optimizer/util/tlist.c
+++ b/src/backend/optimizer/util/tlist.c
@@ -1284,7 +1284,16 @@ split_pathtarget_at_srfs(PlannerInfo *root,
add_sp_item_to_pathtarget(ntarget, item);
}
}
- set_pathtarget_cost_width(root, ntarget);
+
+ /*
+ * This check is specific for ORCA. ORCA calls
+ * split_pathtarget_at_srfs function for creation of
+ * ProjectSet nodes during planned statement
+ * translation with PlannerInfo as NULL. So having this
+ * check prevents a crash in set_pathtarget_cost_width.
+ */
+ if (root != NULL)
+ set_pathtarget_cost_width(root, ntarget);
}
/*
diff --git a/src/include/gpopt/gpdbwrappers.h b/src/include/gpopt/gpdbwrappers.h
index 57e361f470..40ec100396 100644
--- a/src/include/gpopt/gpdbwrappers.h
+++ b/src/include/gpopt/gpdbwrappers.h
@@ -659,6 +659,22 @@ void GPDBLockRelationOid(Oid reloid, int lockmode);
char *GetRelFdwName(Oid reloid);
+PathTarget *MakePathtargetFromTlist(List *tlist);
+
+void SplitPathtargetAtSrfs(PlannerInfo *root, PathTarget *target,
+ PathTarget *input_target, List **targets,
+ List **targets_contain_srfs);
+
+List *MakeTlistFromPathtarget(PathTarget *target);
+
+Node *Expression_tree_mutator(Node *node, Node *(*mutator)(), void *context);
+
+TargetEntry *TlistMember(Expr *node, List *targetlist);
+
+Var *MakeVarFromTargetEntry(Index varno, TargetEntry *tle);
+
+TargetEntry *FlatCopyTargetEntry(TargetEntry *src_tle);
+
} //namespace gpdb
#define ForEach(cell, l) \
diff --git a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
index 932b750d1c..1d1ba9cf8a 100644
--- a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
+++ b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
@@ -293,11 +293,14 @@ private:
ctxt_translation_prev_siblings // translation contexts of previous siblings
);
- Plan *TranslateDXLProjectSet(
- const CDXLNode *result_dxlnode, CDXLTranslateContext *output_context,
- CDXLTranslationContextArray *
- ctxt_translation_prev_siblings // translation contexts of previous siblings
- );
+ Plan *TranslateDXLProjectSet(const CDXLNode *result_dxlnode);
+
+ Plan *CreateProjectSetNodeTree(const CDXLNode *result_dxlnode,
+ Plan *result_node_plan, Plan *child_plan,
+ Plan *&project_set_child_plan,
+ BOOL &will_require_result_node);
+
+ void MutateFuncExprToVarProjectSet(Plan *final_plan);
Plan *TranslateDXLResult(
const CDXLNode *result_dxlnode, CDXLTranslateContext *output_context,
@@ -598,6 +601,8 @@ private:
// get all non-dropped columns of a relation
static List *GetRelationActiveColums(const IMDRelation *md_rel);
+
+ static Node *FixUpperExprMutatorProjectSet(Node *node, List *context);
};
} // namespace gpdxl
diff --git a/src/test/regress/expected/DML_over_joins_optimizer.out b/src/test/regress/expected/DML_over_joins_optimizer.out
index 9ed171b48f..e93567cbb5 100644
--- a/src/test/regress/expected/DML_over_joins_optimizer.out
+++ b/src/test/regress/expected/DML_over_joins_optimizer.out
@@ -20,11 +20,7 @@ drop table if exists s;
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
analyze r;
analyze s;
update r set b = r.b + 1 from s where r.a = s.a;
@@ -32,13 +28,9 @@ update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
@@ -47,22 +39,14 @@ delete from r where a = (select max(a) from s);
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
@@ -70,11 +54,7 @@ delete from r using s where r.b = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
explain update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
@@ -123,23 +103,15 @@ drop table if exists s;
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
@@ -148,22 +120,14 @@ delete from r where a = (select max(a) from s);
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
@@ -171,11 +135,7 @@ delete from r using s where r.b = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
@@ -194,23 +154,15 @@ drop table if exists s;
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
@@ -219,22 +171,14 @@ delete from r where a = (select max(a) from s);
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
@@ -242,11 +186,7 @@ delete from r using s where r.b = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
@@ -265,23 +205,15 @@ drop table if exists s;
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
@@ -290,22 +222,14 @@ delete from r where a = (select max(a) from s);
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
@@ -313,11 +237,7 @@ delete from r using s where r.b = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
-- ----------------------------------------------------------------------
@@ -336,23 +256,15 @@ drop table if exists s;
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 1 from s where r.a = s.a;
update r set b = r.b + 1 from s where r.a in (select a from s);
delete from r using s where r.a = s.a;
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a in (select a from s);
delete from r;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r where a = (select max(a) from s);
------------------------------------------------------------
-- Updates with motion:
@@ -361,22 +273,14 @@ delete from r where a = (select max(a) from s);
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update r set b = r.b + 4 from s where r.b = s.b;
update r set b = b + 1 where b in (select b from s);
delete from s using r where r.a = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
delete from r using s where r.b = s.b;
------------------------------------------------------------
-- Hash aggregate group by
@@ -384,11 +288,7 @@ delete from r using s where r.b = s.b;
delete from r;
delete from s;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set b = b + 1 where exists (select 1 from r where s.a = r.b);
delete from s where exists (select 1 from r where s.a = r.b);
------------------------------------------------------------
@@ -403,18 +303,12 @@ drop table if exists p;
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a int4, b int4, c int4)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
@@ -452,18 +346,12 @@ drop table if exists p;
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a int8, b int8, c int8)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
@@ -501,18 +389,12 @@ drop table if exists p;
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float4, b float4, c float4)
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
@@ -550,18 +432,12 @@ drop table if exists p;
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float(24), b float(24), c float(24))
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
@@ -599,18 +475,12 @@ drop table if exists p;
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float(53), b float(53), c float(53))
partition by range (c) (start(1) end(5) every(1), default partition extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Cloudberry Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into p select generate_series(1,10000), generate_series(1,10000)*3, generate_series(1,10000)%6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
update s set a = r.a from r where r.b = s.b;
------------------------------------------------------------
-- Statement contains correlated subquery
@@ -650,18 +520,12 @@ drop table if exists p;
create table r (a int4, b int4) distributed by (a);
create table s (a int4, b int4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a int4, b int4, c int4)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
@@ -721,18 +585,12 @@ drop table if exists p;
create table r (a int8, b int8) distributed by (a);
create table s (a int8, b int8) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a int8, b int8, c int8)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
@@ -792,18 +650,12 @@ drop table if exists p;
create table r (a float4, b float4) distributed by (a);
create table s (a float4, b float4) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float4, b float4, c float4)
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
@@ -863,18 +715,12 @@ drop table if exists p;
create table r (a float(24), b float(24)) distributed by (a);
create table s (a float(24), b float(24)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float(24), b float(24), c float(24))
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
@@ -934,18 +780,12 @@ drop table if exists p;
create table r (a float(53), b float(53)) distributed by (a);
create table s (a float(53), b float(53)) distributed by (a);
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table p (a float(53), b float(53), c float(53))
distributed by (a)
partition by range (c) (start(1) end(5) every(1), default partition extra);
insert into p select generate_series(1,10000), generate_series(1,10000) * 3, generate_series(1,10000) % 6;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
------------------------------------------------------------
-- Update with Motion:
-- Motion on p, append node, hash agg
@@ -1238,11 +1078,7 @@ delete from r;
delete from s;
delete from sales;
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
SELECT InsertManyIntoSales(40,'sales');
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: SIRV functions
@@ -1369,11 +1205,7 @@ delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: DML(delete) on partitioned tables
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: SIRV functions
@@ -1463,11 +1295,7 @@ delete from sales_par2;
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: Multi-level partitioned tables
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
SELECT InsertManyIntoSales(20,'sales_par2');
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: SIRV functions
@@ -1568,15 +1396,9 @@ delete from s;
drop table if exists s_ao;
NOTICE: table "s_ao" does not exist, skipping
insert into r select generate_series(1, 10000), generate_series(1, 10000) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
create table s_ao (a int, b int) with (appendonly = true) distributed by (a);
insert into s_ao select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
-- heap table: delete --
select * from r where b in (select month-1 from sales_par_CO, s_ao where sales_par_CO.id = s_ao.b);
INFO: GPORCA failed to produce a plan, falling back to planner
@@ -1664,14 +1486,8 @@ delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: DML(delete) on partitioned tables
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 10), generate_series(1, 10) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into m select generate_series(1, 1000), generate_series(1, 1000) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: SIRV functions
@@ -1776,14 +1592,8 @@ delete from sales_par;
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: DML(delete) on partitioned tables
insert into s select generate_series(1, 100), generate_series(1, 100) * 3;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into s select generate_series(1, 10), generate_series(1, 10) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into m select generate_series(1, 1000), generate_series(1, 1000) * 4;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
SELECT InsertManyIntoSales(20,'sales_par');
INFO: GPORCA failed to produce a plan, falling back to planner
DETAIL: Feature not supported: SIRV functions
diff --git a/src/test/regress/expected/gp_dqa_optimizer.out b/src/test/regress/expected/gp_dqa_optimizer.out
index 1f8cd945e8..50719784c4 100644
--- a/src/test/regress/expected/gp_dqa_optimizer.out
+++ b/src/test/regress/expected/gp_dqa_optimizer.out
@@ -2623,11 +2623,7 @@ create table multiagg2(a int, b bigint, c numeric(8, 4));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into multiagg1 values(generate_series(1, 10), generate_series(1, 10), generate_series(1, 10));
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
insert into multiagg2 values(generate_series(1, 10), generate_series(1, 10), 555.55);
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
analyze multiagg1;
analyze multiagg2;
explain (verbose, costs off) select count(distinct b), sum(c) from multiagg1;
diff --git a/src/test/regress/expected/gp_tsrf.out b/src/test/regress/expected/gp_tsrf.out
new file mode 100644
index 0000000000..089481b1ea
--- /dev/null
+++ b/src/test/regress/expected/gp_tsrf.out
@@ -0,0 +1,262 @@
+--
+-- targetlist set returning function tests
+--
+-- SRF is not under any other expression --
+explain verbose select generate_series(1,4) as x;
+ QUERY PLAN
+------------------------------------------------
+ ProjectSet (cost=0.00..0.04 rows=4 width=4)
+ Output: generate_series(1, 4)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(5 rows)
+
+select generate_series(1,4) as x;
+ x
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+-- SRF is present under a FUNCEXPR which is not a SRF
+explain verbose select abs(generate_series(-5,-1)) as absolute;
+ QUERY PLAN
+----------------------------------------------------------------
+ Result (cost=0.00..0.12 rows=5 width=4)
+ Output: abs((generate_series('-5'::integer, '-1'::integer)))
+ -> ProjectSet (cost=0.00..0.04 rows=5 width=4)
+ Output: generate_series('-5'::integer, '-1'::integer)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(7 rows)
+
+select abs(generate_series(-5,-1)) as absolute;
+ absolute
+----------
+ 5
+ 4
+ 3
+ 2
+ 1
+(5 rows)
+
+-- SRF is present under a OPEXPR(+)
+explain verbose select generate_series(1,4)+1 as output;
+ QUERY PLAN
+------------------------------------------------------
+ Result (cost=0.00..0.10 rows=4 width=4)
+ Output: ((generate_series(1, 4)) + 1)
+ -> ProjectSet (cost=0.00..0.04 rows=4 width=4)
+ Output: generate_series(1, 4)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(7 rows)
+
+select generate_series(1,4)+1 as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 5
+(4 rows)
+
+-- SRF is present under an SRF expression
+explain verbose select generate_series(generate_series(1,3),4);
+ QUERY PLAN
+-------------------------------------------------------
+ ProjectSet (cost=0.00..15.06 rows=3000 width=4)
+ Output: generate_series((generate_series(1, 3)), 4)
+ -> ProjectSet (cost=0.00..0.03 rows=3 width=4)
+ Output: generate_series(1, 3)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(7 rows)
+
+select generate_series(generate_series(1,3),4) as output;
+ output
+--------
+ 1
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+ 3
+ 4
+(9 rows)
+
+-- The inner SRF is present under an OPEXPR which in turn is under an SRF
+explain verbose select generate_series(generate_series(1,2)+1,4) as output;
+ QUERY PLAN
+-------------------------------------------------------------
+ ProjectSet (cost=0.00..10.05 rows=2000 width=4)
+ Output: generate_series(((generate_series(1, 2)) + 1), 4)
+ -> ProjectSet (cost=0.00..0.03 rows=2 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(7 rows)
+
+select generate_series(generate_series(1,2)+1,4) as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 3
+ 4
+(5 rows)
+
+-- The outer SRF is present under an OPEXPR
+explain verbose select generate_series(generate_series(1,2),4)+1 as output;
+ QUERY PLAN
+---------------------------------------------------------------
+ Result (cost=0.00..45.05 rows=2000 width=4)
+ Output: ((generate_series((generate_series(1, 2)), 4)) + 1)
+ -> ProjectSet (cost=0.00..10.05 rows=2000 width=4)
+ Output: generate_series((generate_series(1, 2)), 4)
+ -> ProjectSet (cost=0.00..0.03 rows=2 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(9 rows)
+
+select generate_series(generate_series(1,2),4)+1 as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 5
+ 3
+ 4
+ 5
+(7 rows)
+
+-- Both inner and outer SRF are present under OPEXPR
+explain verbose select generate_series(generate_series(1,2)+1,4)+1 as output;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Result (cost=0.00..50.05 rows=2000 width=4)
+ Output: ((generate_series(((generate_series(1, 2)) + 1), 4)) + 1)
+ -> ProjectSet (cost=0.00..10.05 rows=2000 width=4)
+ Output: generate_series(((generate_series(1, 2)) + 1), 4)
+ -> ProjectSet (cost=0.00..0.03 rows=2 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(9 rows)
+
+select generate_series(generate_series(1,2)+1,4)+1 as output;
+ output
+--------
+ 3
+ 4
+ 5
+ 4
+ 5
+(5 rows)
+
+explain verbose select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+ QUERY PLAN
+------------------------------------------------------------
+ Result (cost=0.00..0.27 rows=9 width=4)
+ Output: ((generate_series(1, 3)) + 1)
+ -> ProjectSet (cost=0.00..0.13 rows=9 width=4)
+ Output: generate_series(1, 3)
+ -> ProjectSet (cost=0.00..0.03 rows=3 width=4)
+ Output: generate_series(1, 3)
+ -> Result (cost=0.00..0.01 rows=1 width=0)
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(9 rows)
+
+select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+ x
+---
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+(9 rows)
+
+create table test_srf(a int,b int,c int) distributed by (a);
+insert into test_srf values(2,2,2);
+insert into test_srf values(3,2,2);
+explain verbose select generate_series(1,a) as output,b,c from test_srf;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1168988.42 rows=77900000 width=12)
+ Output: (generate_series(1, a)), b, c
+ -> ProjectSet (cost=0.00..130321.75 rows=25966667 width=12)
+ Output: generate_series(1, a), b, c
+ -> Seq Scan on public.test_srf (cost=0.00..293.67 rows=25967 width=12)
+ Output: a, b, c
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(8 rows)
+
+select generate_series(1,a) as output,b,c from test_srf;
+ output | b | c
+--------+---+---
+ 1 | 2 | 2
+ 2 | 2 | 2
+ 1 | 2 | 2
+ 2 | 2 | 2
+ 3 | 2 | 2
+(5 rows)
+
+explain verbose select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1688451.58 rows=77900000 width=12)
+ Output: (generate_series(1, (a + 1))), ((b + (generate_series(1, 4)))), c
+ -> Result (cost=0.00..649784.92 rows=25966667 width=12)
+ Output: (generate_series(1, (a + 1))), (b + (generate_series(1, 4))), c
+ -> ProjectSet (cost=0.00..130451.58 rows=25966667 width=16)
+ Output: generate_series(1, (a + 1)), generate_series(1, 4), b, c
+ -> Seq Scan on public.test_srf (cost=0.00..293.67 rows=25967 width=12)
+ Output: a, b, c
+ Optimizer: Postgres query optimizer
+ Settings: optimizer = 'off'
+(10 rows)
+
+select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+ generate_series | ?column? | c
+-----------------+----------+---
+ 1 | 3 | 2
+ 2 | 4 | 2
+ 3 | 5 | 2
+ | 6 | 2
+ 1 | 3 | 2
+ 2 | 4 | 2
+ 3 | 5 | 2
+ 4 | 6 | 2
+(8 rows)
+
+drop table test_srf;
+-- Below query giving incorrect output with ORCA.Works fine on planner.Github Issue #15644
+SELECT a IN (SELECT generate_series(1,a)) AS x FROM (SELECT generate_series(1, 3) AS a) AS s;
+ x
+---
+ t
+ t
+ t
+(3 rows)
+
diff --git a/src/test/regress/expected/gp_tsrf_optimizer.out b/src/test/regress/expected/gp_tsrf_optimizer.out
new file mode 100644
index 0000000000..122ae7690f
--- /dev/null
+++ b/src/test/regress/expected/gp_tsrf_optimizer.out
@@ -0,0 +1,263 @@
+--
+-- targetlist set returning function tests
+--
+-- SRF is not under any other expression --
+explain verbose select generate_series(1,4) as x;
+ QUERY PLAN
+------------------------------------------------
+ ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 4)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(5 rows)
+
+select generate_series(1,4) as x;
+ x
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+-- SRF is present under a FUNCEXPR which is not a SRF
+explain verbose select abs(generate_series(-5,-1)) as absolute;
+ QUERY PLAN
+----------------------------------------------------------------
+ Result (cost=0.00..0.00 rows=1 width=4)
+ Output: abs((generate_series('-5'::integer, '-1'::integer)))
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series('-5'::integer, '-1'::integer)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+select abs(generate_series(-5,-1)) as absolute;
+ absolute
+----------
+ 5
+ 4
+ 3
+ 2
+ 1
+(5 rows)
+
+-- SRF is present under a OPEXPR(+)
+explain verbose select generate_series(1,4)+1 as output;
+ QUERY PLAN
+------------------------------------------------------
+ Result (cost=0.00..0.00 rows=1 width=4)
+ Output: ((generate_series(1, 4)) + 1)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 4)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+select generate_series(1,4)+1 as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 5
+(4 rows)
+
+-- SRF is present under an SRF expression
+explain verbose select generate_series(generate_series(1,3),4);
+ QUERY PLAN
+-------------------------------------------------------
+ ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series((generate_series(1, 3)), 4)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 3)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+select generate_series(generate_series(1,3),4) as output;
+ output
+--------
+ 1
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+ 3
+ 4
+(9 rows)
+
+-- The inner SRF is present under an OPEXPR which in turn is under an SRF
+explain verbose select generate_series(generate_series(1,2)+1,4) as output;
+ QUERY PLAN
+-------------------------------------------------------------
+ ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(((generate_series(1, 2)) + 1), 4)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+select generate_series(generate_series(1,2)+1,4) as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 3
+ 4
+(5 rows)
+
+-- The outer SRF is present under an OPEXPR
+explain verbose select generate_series(generate_series(1,2),4)+1 as output;
+ QUERY PLAN
+---------------------------------------------------------------
+ Result (cost=0.00..0.00 rows=1 width=4)
+ Output: ((generate_series((generate_series(1, 2)), 4)) + 1)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series((generate_series(1, 2)), 4)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select generate_series(generate_series(1,2),4)+1 as output;
+ output
+--------
+ 2
+ 3
+ 4
+ 5
+ 3
+ 4
+ 5
+(7 rows)
+
+-- Both inner and outer SRF are present under OPEXPR
+explain verbose select generate_series(generate_series(1,2)+1,4)+1 as output;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Result (cost=0.00..0.00 rows=1 width=4)
+ Output: ((generate_series(((generate_series(1, 2)) + 1), 4)) + 1)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(((generate_series(1, 2)) + 1), 4)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 2)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select generate_series(generate_series(1,2)+1,4)+1 as output;
+ output
+--------
+ 3
+ 4
+ 5
+ 4
+ 5
+(5 rows)
+
+explain verbose select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+ QUERY PLAN
+------------------------------------------------------------
+ Result (cost=0.00..0.00 rows=1 width=4)
+ Output: ((generate_series(1, 3)) + 1)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 3)
+ -> ProjectSet (cost=0.00..0.00 rows=1 width=4)
+ Output: generate_series(1, 3)
+ -> Result (cost=0.00..0.00 rows=1 width=1)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+ x
+---
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+ 2
+ 3
+ 4
+(9 rows)
+
+create table test_srf(a int,b int,c int) distributed by (a);
+insert into test_srf values(2,2,2);
+insert into test_srf values(3,2,2);
+explain verbose select generate_series(1,a) as output,b,c from test_srf;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=12)
+ Output: (generate_series(1, a)), b, c
+ -> ProjectSet (cost=0.00..431.00 rows=1 width=12)
+ Output: generate_series(1, a), b, c
+ -> Seq Scan on public.test_srf (cost=0.00..431.00 rows=1 width=12)
+ Output: a, b, c
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
+
+select generate_series(1,a) as output,b,c from test_srf;
+ output | b | c
+--------+---+---
+ 1 | 2 | 2
+ 2 | 2 | 2
+ 1 | 2 | 2
+ 2 | 2 | 2
+ 3 | 2 | 2
+(5 rows)
+
+explain verbose select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..431.00 rows=1 width=12)
+ Output: (generate_series(1, (a + 1))), ((b + (generate_series(1, 4)))), c
+ -> Result (cost=0.00..431.00 rows=1 width=12)
+ Output: (generate_series(1, (a + 1))), (b + (generate_series(1, 4))), c
+ -> ProjectSet (cost=0.00..431.00 rows=1 width=12)
+ Output: generate_series(1, (a + 1)), generate_series(1, 4), b, c
+ -> Seq Scan on public.test_srf (cost=0.00..431.00 rows=1 width=12)
+ Output: a, b, c
+ Optimizer: Pivotal Optimizer (GPORCA)
+(9 rows)
+
+select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+ generate_series | ?column? | c
+-----------------+----------+---
+ 1 | 3 | 2
+ 2 | 4 | 2
+ 3 | 5 | 2
+ | 6 | 2
+ 1 | 3 | 2
+ 2 | 4 | 2
+ 3 | 5 | 2
+ 4 | 6 | 2
+(8 rows)
+
+drop table test_srf;
+-- Below query giving incorrect output with ORCA.Works fine on planner.Github Issue #15644
+SELECT a IN (SELECT generate_series(1,a)) AS x FROM (SELECT generate_series(1, 3) AS a) AS s;
+ x
+---
+ t
+ f
+ t
+ f
+ f
+ t
+(6 rows)
+
diff --git a/src/test/regress/expected/gporca_optimizer.out b/src/test/regress/expected/gporca_optimizer.out
index ef9204fdb6..d0b9740372 100644
--- a/src/test/regress/expected/gporca_optimizer.out
+++ b/src/test/regress/expected/gporca_optimizer.out
@@ -14088,8 +14088,6 @@ select count(*) from (select regexp_split_to_table((a)::text, ','::text) from ne
(1 row)
select * from (select trim(regexp_split_to_table((a)::text, ','::text)) from nested_srf)a;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
btrim
-------
abc
@@ -14098,8 +14096,6 @@ DETAIL: Feature not supported: Unexpected target list entries in ProjectSet nod
(3 rows)
select count(*) from (select trim(regexp_split_to_table((a)::text, ','::text)) from nested_srf)a;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
count
-------
3
@@ -14123,15 +14119,11 @@ HINT: You might be able to move the set-returning function into a LATERAL FROM
truncate nested_srf;
insert into nested_srf values (NULL);
select * from (select trim(regexp_split_to_table((a)::text, ','::text)) from nested_srf)a;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
btrim
-------
(0 rows)
select count(*) from (select trim(regexp_split_to_table((a)::text, ','::text)) from nested_srf)a;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
count
-------
0
diff --git a/src/test/regress/expected/qp_misc_optimizer.out b/src/test/regress/expected/qp_misc_optimizer.out
index b6381e4f29..70ac3bc606 100644
--- a/src/test/regress/expected/qp_misc_optimizer.out
+++ b/src/test/regress/expected/qp_misc_optimizer.out
@@ -20504,8 +20504,6 @@ WITH cte_coll AS
SELECT *
FROM cte_coll
WHERE tkn_arr <> '{nullout}' ;
-INFO: GPORCA failed to produce a plan, falling back to planner
-DETAIL: Feature not supported: Unexpected target list entries in ProjectSet node
tkn_arr
---------
(0 rows)
diff --git a/src/test/regress/expected/tsrf_optimizer.out b/src/test/regress/expected/tsrf_optimizer.out
index 1555897e4d..a24d6c1a90 100644
--- a/src/test/regress/expected/tsrf_optimizer.out
+++ b/src/test/regress/expected/tsrf_optimizer.out
@@ -68,7 +68,9 @@ SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4);
-> ProjectSet
Output: generate_series(1, 3), generate_series(2, 4)
-> Result
-(5 rows)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
SELECT generate_series(1, generate_series(1, 3)), generate_series(2, 4);
generate_series | generate_series
@@ -675,8 +677,9 @@ select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1;
-> ProjectSet
Output: generate_series(1, 3)
-> Result
- Optimizer: Postgres query optimizer
-(6 rows)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1;
x | xp1
@@ -688,17 +691,21 @@ select generate_series(1,3) as x, generate_series(1,3) + 1 as xp1;
explain (verbose, costs off)
select generate_series(1,3)+1 order by generate_series(1,3);
- QUERY PLAN
-------------------------------------------------------------------------
- Sort
- Output: (((generate_series(1, 3)) + 1)), (generate_series(1, 3))
- Sort Key: (generate_series(1, 3))
- -> Result
- Output: ((generate_series(1, 3)) + 1), (generate_series(1, 3))
- -> ProjectSet
- Output: generate_series(1, 3)
- -> Result
-(8 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Result
+ Output: (((generate_series(1, 3)) + 1))
+ -> Sort
+ Output: (((generate_series(1, 3)) + 1)), (generate_series(1, 3))
+ Sort Key: (generate_series(1, 3))
+ -> Result
+ Output: ((generate_series(1, 3)) + 1), (generate_series(1, 3))
+ -> ProjectSet
+ Output: generate_series(1, 3)
+ -> Result
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(12 rows)
select generate_series(1,3)+1 order by generate_series(1,3);
?column?
@@ -718,7 +725,9 @@ select generate_series(1,3) as x, generate_series(3,6) + 1 as y;
-> ProjectSet
Output: generate_series(1, 3), generate_series(3, 6)
-> Result
-(5 rows)
+ Output: true
+ Optimizer: Pivotal Optimizer (GPORCA)
+(7 rows)
select generate_series(1,3) as x, generate_series(3,6) + 1 as y;
x | y
diff --git a/src/test/regress/greenplum_schedule b/src/test/regress/greenplum_schedule
index 24fa6da68e..59c1349a88 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -194,7 +194,7 @@ test: rpt rpt_joins rpt_tpch rpt_returning
test: bfv_cte
test: bfv_joins bfv_subquery bfv_planner bfv_legacy bfv_temp bfv_dml
-test: qp_olap_mdqa qp_misc gp_recursive_cte qp_dml_joins qp_skew qp_select partition_prune_opfamily
+test: qp_olap_mdqa qp_misc gp_recursive_cte qp_dml_joins qp_skew qp_select partition_prune_opfamily gp_tsrf
test: qp_misc_jiras qp_with_clause qp_executor qp_olap_windowerr qp_olap_window qp_derived_table qp_bitmapscan qp_dropped_cols
test: qp_with_functional_inlining qp_with_functional_noinlining
diff --git a/src/test/regress/sql/gp_tsrf.sql b/src/test/regress/sql/gp_tsrf.sql
new file mode 100644
index 0000000000..a3732e5a48
--- /dev/null
+++ b/src/test/regress/sql/gp_tsrf.sql
@@ -0,0 +1,45 @@
+--
+-- targetlist set returning function tests
+--
+
+-- SRF is not under any other expression --
+explain verbose select generate_series(1,4) as x;
+select generate_series(1,4) as x;
+
+-- SRF is present under a FUNCEXPR which is not a SRF
+explain verbose select abs(generate_series(-5,-1)) as absolute;
+select abs(generate_series(-5,-1)) as absolute;
+
+-- SRF is present under a OPEXPR(+)
+explain verbose select generate_series(1,4)+1 as output;
+select generate_series(1,4)+1 as output;
+
+-- SRF is present under an SRF expression
+explain verbose select generate_series(generate_series(1,3),4);
+select generate_series(generate_series(1,3),4) as output;
+
+-- The inner SRF is present under an OPEXPR which in turn is under an SRF
+explain verbose select generate_series(generate_series(1,2)+1,4) as output;
+select generate_series(generate_series(1,2)+1,4) as output;
+
+-- The outer SRF is present under an OPEXPR
+explain verbose select generate_series(generate_series(1,2),4)+1 as output;
+select generate_series(generate_series(1,2),4)+1 as output;
+
+-- Both inner and outer SRF are present under OPEXPR
+explain verbose select generate_series(generate_series(1,2)+1,4)+1 as output;
+select generate_series(generate_series(1,2)+1,4)+1 as output;
+explain verbose select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+select generate_series(1,3)+1 as x from (select generate_series(1, 3)) as y;
+
+create table test_srf(a int,b int,c int) distributed by (a);
+insert into test_srf values(2,2,2);
+insert into test_srf values(3,2,2);
+explain verbose select generate_series(1,a) as output,b,c from test_srf;
+select generate_series(1,a) as output,b,c from test_srf;
+explain verbose select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+select generate_series(1,a+1),b+generate_series(1,4),c from test_srf;
+drop table test_srf;
+
+-- Below query giving incorrect output with ORCA.Works fine on planner.Github Issue #15644
+SELECT a IN (SELECT generate_series(1,a)) AS x FROM (SELECT generate_series(1, 3) AS a) AS s;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@cloudberry.apache.org
For additional commands, e-mail: commits-help@cloudberry.apache.org