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
The following commit(s) were added to refs/heads/main by this push:
new 05c4015208 [ORCA] Remove the IntoClause related logic in the CTAS
(#978)
05c4015208 is described below
commit 05c401520888819509880bdb1770bcd55c58b65a
Author: jiaqizho <[email protected]>
AuthorDate: Tue Apr 1 13:53:51 2025 +0800
[ORCA] Remove the IntoClause related logic in the CTAS (#978)
Under PG14, for CTAS (or SELECT INTO), the 'IntoClause' only exists in the
parser
stage and no longer exists in the Query structure. It was probably removed
at
PG9 ~ PG10?
The logic related to 'IntoClause' has been deleted from the current commit.
And rewritten some CTAS interfaces.
In addition, an ORCA fallback caused by an empty targetlist has been fixed.
explain select into empty_tl from generate_series(0, 100);
will get the fallabck when enable the cassert:
INFO: GPORCA failed to produce a plan, falling back to Postgres-based
planner
DETAIL: CTranslatorDXLToPlStmt.cpp:6154: Failed assertion: nullptr !=
target_list
---
.../gpopt/translate/CContextDXLToPlStmt.cpp | 8 +-
.../gpopt/translate/CTranslatorDXLToPlStmt.cpp | 85 +---------
.../gpopt/translate/CTranslatorQueryToDXL.cpp | 137 +---------------
.../dxl/operators/CDXLCtasStorageOptions.h | 2 +
.../src/operators/CDXLCtasStorageOptions.cpp | 4 +
src/include/gpopt/translate/CContextDXLToPlStmt.h | 12 +-
.../gpopt/translate/CTranslatorDXLToPlStmt.h | 4 -
.../gpopt/translate/CTranslatorQueryToDXL.h | 3 -
src/test/regress/expected/gpctas.out | 102 ++++++++++++
.../expected/{gpctas.out => gpctas_optimizer.out} | 181 ++++++++++++++++++++-
src/test/regress/expected/select_into.out | 21 +++
.../regress/expected/select_into_optimizer.out | 22 +++
src/test/regress/sql/gpctas.sql | 12 +-
src/test/regress/sql/select_into.sql | 8 +
14 files changed, 355 insertions(+), 246 deletions(-)
diff --git a/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
b/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
index ac27e47597..b0d7caef33 100644
--- a/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CContextDXLToPlStmt.cpp
@@ -55,7 +55,6 @@ CContextDXLToPlStmt::CContextDXLToPlStmt(
m_subplan_sliceids_list(nullptr),
m_slices_list(nullptr),
m_result_relation_index(0),
- m_into_clause(nullptr),
m_distribution_policy(nullptr),
m_part_selector_to_param_map(nullptr)
{
@@ -348,16 +347,11 @@ CContextDXLToPlStmt::AddSlice(PlanSlice *slice)
// Add CTAS info
//
//---------------------------------------------------------------------------
-// GPDB_92_MERGE_FIXME: we really should care about intoClause
-// But planner cheats. FIX that and re-enable ORCA's handling of intoClause
void
-CContextDXLToPlStmt::AddCtasInfo(IntoClause *into_clause,
- GpPolicy
*distribution_policy)
+CContextDXLToPlStmt::AddCtasInfo(GpPolicy *distribution_policy)
{
- // GPOS_ASSERT(NULL != into_clause);
GPOS_ASSERT(nullptr != distribution_policy);
- m_into_clause = into_clause;
m_distribution_policy = distribution_policy;
}
diff --git a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
index b329655ac9..1408e5eaa4 100644
--- a/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
+++ b/src/backend/gpopt/translate/CTranslatorDXLToPlStmt.cpp
@@ -253,10 +253,6 @@ CTranslatorDXLToPlStmt::GetPlannedStmtFromDXL(const
CDXLNode *dxlnode,
planned_stmt->commandType = m_cmd_type;
planned_stmt->resultRelations = m_result_rel_list;
- // GPDB_92_MERGE_FIXME: we really *should* be handling intoClause
- // but currently planner cheats (c.f. createas.c)
- // shift the intoClause handling into planner and re-enable this
- // pplstmt->intoClause = m_pctxdxltoplstmt->Pintocl();
planned_stmt->intoPolicy =
m_dxl_to_plstmt_context->GetDistributionPolicy();
planned_stmt->paramExecTypes = m_dxl_to_plstmt_context->GetParamTypes();
@@ -6218,8 +6214,7 @@ void
CTranslatorDXLToPlStmt::SetVarTypMod(const CDXLPhysicalCTAS *phy_ctas_dxlop,
List
*target_list)
{
- GPOS_ASSERT(nullptr != target_list);
-
+ // target list can be nullptr in CTAS
IntPtrArray *var_type_mod_array = phy_ctas_dxlop->GetVarTypeModArray();
GPOS_ASSERT(var_type_mod_array->Size() ==
gpdb::ListLength(target_list));
@@ -6282,15 +6277,12 @@ CTranslatorDXLToPlStmt::TranslateDXLCtas(
// cleanup
child_contexts->Release();
-
// translate operator costs
TranslatePlanCosts(ctas_dxlnode, plan);
- //IntoClause *into_clause =
TranslateDXLPhyCtasToIntoClause(phy_ctas_dxlop);
- IntoClause *into_clause = nullptr;
GpPolicy *distr_policy =
TranslateDXLPhyCtasToDistrPolicy(phy_ctas_dxlop, target_list);
- m_dxl_to_plstmt_context->AddCtasInfo(into_clause, distr_policy);
+ m_dxl_to_plstmt_context->AddCtasInfo(distr_policy);
GPOS_ASSERT(IMDRelation::EreldistrMasterOnly !=
phy_ctas_dxlop->Ereldistrpolicy());
@@ -6311,79 +6303,6 @@ CTranslatorDXLToPlStmt::TranslateDXLCtas(
return (Plan *) plan;
}
-//---------------------------------------------------------------------------
-// @function:
-// CTranslatorDXLToPlStmt::TranslateDXLPhyCtasToIntoClause
-//
-// @doc:
-// Translates a DXL CTAS into clause
-//
-//---------------------------------------------------------------------------
-IntoClause *
-CTranslatorDXLToPlStmt::TranslateDXLPhyCtasToIntoClause(
- const CDXLPhysicalCTAS *phy_ctas_dxlop)
-{
- IntoClause *into_clause = MakeNode(IntoClause);
- into_clause->rel = MakeNode(RangeVar);
- /* GPDB_91_MERGE_FIXME: what about unlogged? */
- into_clause->rel->relpersistence = phy_ctas_dxlop->IsTemporary()
-
? RELPERSISTENCE_TEMP
-
: RELPERSISTENCE_PERMANENT;
- into_clause->rel->relname =
- CTranslatorUtils::CreateMultiByteCharStringFromWCString(
- phy_ctas_dxlop->MdName()->GetMDName()->GetBuffer());
- into_clause->rel->schemaname = nullptr;
- if (nullptr != phy_ctas_dxlop->GetMdNameSchema())
- {
- into_clause->rel->schemaname =
- CTranslatorUtils::CreateMultiByteCharStringFromWCString(
-
phy_ctas_dxlop->GetMdNameSchema()->GetMDName()->GetBuffer());
- }
-
- CDXLCtasStorageOptions *dxl_ctas_storage_option =
- phy_ctas_dxlop->GetDxlCtasStorageOption();
- if (nullptr != dxl_ctas_storage_option->GetMdNameTableSpace())
- {
- into_clause->tableSpaceName =
- CTranslatorUtils::CreateMultiByteCharStringFromWCString(
- phy_ctas_dxlop->GetDxlCtasStorageOption()
- ->GetMdNameTableSpace()
- ->GetMDName()
- ->GetBuffer());
- }
-
- into_clause->onCommit =
- (OnCommitAction) dxl_ctas_storage_option->GetOnCommitAction();
- into_clause->options = TranslateDXLCtasStorageOptions(
- dxl_ctas_storage_option->GetDXLCtasOptionArray());
-
- // get column names
- CDXLColDescrArray *dxl_col_descr_array =
- phy_ctas_dxlop->GetDXLColumnDescrArray();
- const ULONG num_of_cols = dxl_col_descr_array->Size();
- into_clause->colNames = NIL;
- for (ULONG ul = 0; ul < num_of_cols; ++ul)
- {
- const CDXLColDescr *dxl_col_descr = (*dxl_col_descr_array)[ul];
-
- CHAR *col_name_char_array =
- CTranslatorUtils::CreateMultiByteCharStringFromWCString(
-
dxl_col_descr->MdName()->GetMDName()->GetBuffer());
-
- ColumnDef *col_def = MakeNode(ColumnDef);
- col_def->colname = col_name_char_array;
- col_def->is_local = true;
-
- // GPDB_91_MERGE_FIXME: collation
- col_def->collClause = nullptr;
- col_def->collOid = gpdb::TypeCollation(
- CMDIdGPDB::CastMdid(dxl_col_descr->MdidType())->Oid());
- into_clause->colNames = gpdb::LAppend(into_clause->colNames,
col_def);
- }
-
- return into_clause;
-}
-
//---------------------------------------------------------------------------
// @function:
// CTranslatorDXLToPlStmt::TranslateDXLPhyCtasToDistrPolicy
diff --git a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
index 889619346f..392f650664 100644
--- a/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
+++ b/src/backend/gpopt/translate/CTranslatorQueryToDXL.cpp
@@ -979,17 +979,10 @@ CDXLNode *
CTranslatorQueryToDXL::TranslateCTASToDXL()
{
GPOS_ASSERT(CMD_SELECT == m_query->commandType);
- //GPOS_ASSERT(NULL != m_query->intoClause);
+ const char *const relname = "FAKE_CTAS_RELNAME";
m_is_ctas_query = true;
CDXLNode *query_dxlnode = TranslateSelectQueryToDXL();
-
- // GPDB_92_MERGE_FIXME: we should plumb through the intoClause
- // IntoClause *pintocl = m_pquery->intoClause;
- IntoClause *into_clause = nullptr;
-
- // const char *const relname = pintocl->rel->relname;
- const char *const relname = "fake ctas rel";
CMDName *md_relname = CDXLUtils::CreateMDNameFromCharArray(m_mp,
relname);
CDXLColDescrArray *dxl_col_descr_array =
@@ -1000,7 +993,6 @@ CTranslatorQueryToDXL::TranslateCTASToDXL()
ULongPtrArray *source_array = GPOS_NEW(m_mp) ULongPtrArray(m_mp);
IntPtrArray *var_typmods = GPOS_NEW(m_mp) IntPtrArray(m_mp);
- // List *col_names = into_clause->colNames;
List *col_names = NIL;
for (ULONG ul = 0; ul < num_columns; ul++)
{
@@ -1091,134 +1083,23 @@ CTranslatorQueryToDXL::TranslateCTASToDXL()
GPOS_ASSERT(IMDRelation::EreldistrMasterOnly != rel_distr_policy);
m_context->m_has_distributed_tables = true;
- // TODO: Mar 5, 2014; reserve an OID
OID oid = 1;
CMDIdGPDB *mdid = GPOS_NEW(m_mp) CMDIdGPDBCtas(oid);
- CMDName *md_tablespace_name = nullptr;
- // if (NULL != into_clause->tableSpaceName)
- if (false)
- {
- md_tablespace_name = CDXLUtils::CreateMDNameFromCharArray(
- m_mp, into_clause->tableSpaceName);
- }
-
- CMDName *md_schema_name = nullptr;
- // if (NULL != into_clause->rel->schemaname)
- if (false)
- {
- md_schema_name = CDXLUtils::CreateMDNameFromCharArray(
- m_mp, into_clause->rel->schemaname);
- }
-
- // CDXLCtasStorageOptions::ECtasOnCommitAction ctas_commit_action
= (CDXLCtasStorageOptions::ECtasOnCommitAction) into_clause->onCommit;
- CDXLCtasStorageOptions::ECtasOnCommitAction ctas_commit_action =
- CDXLCtasStorageOptions::EctascommitNOOP;
- IMDRelation::Erelstoragetype rel_storage_type =
- IMDRelation::ErelstorageHeap;
- // CDXLCtasStorageOptions::CDXLCtasOptionArray
*ctas_storage_options = GetDXLCtasOptionArray(into_clause->options,
&rel_storage_type);
- CDXLCtasStorageOptions::CDXLCtasOptionArray *ctas_storage_options =
- GetDXLCtasOptionArray(NIL, &rel_storage_type);
-
- BOOL fTempTable = true;
+ // Used to create a `CMDRelationCtasGPDB` in `PexprLogicalCTAS`
+ // In the end, the "fake" relation will be generated as
CPhysicalDML(Result node)
+ // So the empty option/storagetype/relname/oid is fine. Cause we won't
use it
+ // in physical plan.
CDXLLogicalCTAS *ctas_dxlop = GPOS_NEW(m_mp) CDXLLogicalCTAS(
- m_mp, mdid, md_schema_name, md_relname, dxl_col_descr_array,
- GPOS_NEW(m_mp) CDXLCtasStorageOptions(
- md_tablespace_name, ctas_commit_action,
ctas_storage_options),
+ m_mp, mdid, nullptr, md_relname, dxl_col_descr_array,
+ GPOS_NEW(m_mp) CDXLCtasStorageOptions(), // empty
rel_distr_policy, distribution_colids, distr_opfamilies,
- distr_opclasses, fTempTable, rel_storage_type, source_array,
- var_typmods);
+ distr_opclasses, true /*fTempTable*/,
IMDRelation::ErelstorageHeap, // heap by defualt
+ source_array, var_typmods);
return GPOS_NEW(m_mp) CDXLNode(m_mp, ctas_dxlop, query_dxlnode);
}
-//---------------------------------------------------------------------------
-// @function:
-// CTranslatorQueryToDXL::GetDXLCtasOptionArray
-//
-// @doc:
-// Translate CTAS storage options
-//
-//---------------------------------------------------------------------------
-CDXLCtasStorageOptions::CDXLCtasOptionArray *
-CTranslatorQueryToDXL::GetDXLCtasOptionArray(
- List *options,
- IMDRelation::Erelstoragetype
- *storage_type // output parameter: storage type
-)
-{
- if (nullptr == options)
- {
- return nullptr;
- }
-
- GPOS_ASSERT(nullptr != storage_type);
-
- CDXLCtasStorageOptions::CDXLCtasOptionArray *ctas_storage_options =
- GPOS_NEW(m_mp)
CDXLCtasStorageOptions::CDXLCtasOptionArray(m_mp);
- ListCell *lc = nullptr;
- BOOL is_ao_table = false;
- BOOL is_AOCO = false;
-
- CWStringConst str_append_only(GPOS_WSZ_LIT("appendonly"));
- CWStringConst str_orientation(GPOS_WSZ_LIT("orientation"));
- CWStringConst str_orientation_column(GPOS_WSZ_LIT("column"));
-
- ForEach(lc, options)
- {
- DefElem *def_elem = (DefElem *) lfirst(lc);
- CWStringDynamic *name_str =
CDXLUtils::CreateDynamicStringFromCharArray(
- m_mp, def_elem->defname);
- CWStringDynamic *value_str = nullptr;
-
- BOOL is_null_arg = (nullptr == def_elem->arg);
-
- // def_elem->arg is NULL for queries of the form "create table
t with (oids) as ... "
- if (is_null_arg)
- {
- // we represent null options as an empty arg string and
set the IsNull flag on
- value_str = GPOS_NEW(m_mp) CWStringDynamic(m_mp);
- }
- else
- {
- value_str = ExtractStorageOptionStr(def_elem);
-
- if (name_str->Equals(&str_append_only) &&
-
value_str->Equals(CDXLTokens::GetDXLTokenStr(EdxltokenTrue)))
- {
- is_ao_table = true;
- }
-
- if (name_str->Equals(&str_orientation) &&
- value_str->Equals(&str_orientation_column))
- {
- is_AOCO = true;
- }
- }
-
- NodeTag arg_type = T_Null;
- if (!is_null_arg)
- {
- arg_type = def_elem->arg->type;
- }
-
- CDXLCtasStorageOptions::CDXLCtasOption *dxl_ctas_storage_option
=
- GPOS_NEW(m_mp) CDXLCtasStorageOptions::CDXLCtasOption(
- arg_type, name_str, value_str, is_null_arg);
- ctas_storage_options->Append(dxl_ctas_storage_option);
- }
- if (is_AOCO)
- {
- *storage_type = IMDRelation::ErelstorageAppendOnlyCols;
- }
- else if (is_ao_table)
- {
- *storage_type = IMDRelation::ErelstorageAppendOnlyRows;
- }
-
- return ctas_storage_options;
-}
-
//---------------------------------------------------------------------------
// @function:
// CTranslatorQueryToDXL::ExtractStorageOptionStr
diff --git
a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLCtasStorageOptions.h
b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLCtasStorageOptions.h
index 6fead1c487..5dec01686c 100644
---
a/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLCtasStorageOptions.h
+++
b/src/backend/gporca/libnaucrates/include/naucrates/dxl/operators/CDXLCtasStorageOptions.h
@@ -113,6 +113,8 @@ public:
CDXLCtasStorageOptions(CMDName *mdname_tablespace,
ECtasOnCommitAction
ctas_on_commit_action,
CDXLCtasOptionArray
*ctas_storage_option_array);
+ // ctor
+ CDXLCtasStorageOptions();
// dtor
~CDXLCtasStorageOptions() override;
diff --git
a/src/backend/gporca/libnaucrates/src/operators/CDXLCtasStorageOptions.cpp
b/src/backend/gporca/libnaucrates/src/operators/CDXLCtasStorageOptions.cpp
index 91a5f05750..399abd8979 100644
--- a/src/backend/gporca/libnaucrates/src/operators/CDXLCtasStorageOptions.cpp
+++ b/src/backend/gporca/libnaucrates/src/operators/CDXLCtasStorageOptions.cpp
@@ -35,6 +35,10 @@ CDXLCtasStorageOptions::CDXLCtasStorageOptions(
GPOS_ASSERT(EctascommitSentinel > ctas_on_commit_action);
}
+CDXLCtasStorageOptions::CDXLCtasStorageOptions()
+ : CDXLCtasStorageOptions(nullptr,
CDXLCtasStorageOptions::EctascommitNOOP, nullptr)
+{}
+
//---------------------------------------------------------------------------
// @function:
// CDXLCtasStorageOptions::~CDXLCtasStorageOptions
diff --git a/src/include/gpopt/translate/CContextDXLToPlStmt.h
b/src/include/gpopt/translate/CContextDXLToPlStmt.h
index e5a5a91291..f10d45456f 100644
--- a/src/include/gpopt/translate/CContextDXLToPlStmt.h
+++ b/src/include/gpopt/translate/CContextDXLToPlStmt.h
@@ -124,9 +124,6 @@ private:
// hash map of the cte identifiers and the cte consumers with the same
cte identifier
HMUlCTEConsumerInfo *m_cte_consumer_info;
- // into clause
- IntoClause *m_into_clause;
-
// CTAS distribution policy
GpPolicy *m_distribution_policy;
@@ -215,14 +212,7 @@ public:
}
// add CTAS information
- void AddCtasInfo(IntoClause *into_clause, GpPolicy
*distribution_policy);
-
- // into clause
- IntoClause *
- GetIntoClause() const
- {
- return m_into_clause;
- }
+ void AddCtasInfo(GpPolicy *distribution_policy);
// CTAS distribution policy
GpPolicy *
diff --git a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
index c377aa9ba0..34b5789472 100644
--- a/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
+++ b/src/include/gpopt/translate/CTranslatorDXLToPlStmt.h
@@ -607,10 +607,6 @@ private:
// sets the vartypmod fields in the target entries of the given target
list
static void SetVarTypMod(const CDXLPhysicalCTAS *dxlop, List
*target_list);
- // translate the into clause for a DXL physical CTAS operator
- static IntoClause *TranslateDXLPhyCtasToIntoClause(
- const CDXLPhysicalCTAS *dxlop);
-
// translate the distribution policy for a DXL physical CTAS operator
static GpPolicy *TranslateDXLPhyCtasToDistrPolicy(
const CDXLPhysicalCTAS *dxlop, List *target_list);
diff --git a/src/include/gpopt/translate/CTranslatorQueryToDXL.h
b/src/include/gpopt/translate/CTranslatorQueryToDXL.h
index b9e02a9b97..c74892e5df 100644
--- a/src/include/gpopt/translate/CTranslatorQueryToDXL.h
+++ b/src/include/gpopt/translate/CTranslatorQueryToDXL.h
@@ -380,9 +380,6 @@ private:
// translate a CTAS query
CDXLNode *TranslateCTASToDXL();
- // translate CTAS storage options
- CDXLCtasStorageOptions::CDXLCtasOptionArray *GetDXLCtasOptionArray(
- List *options, IMDRelation::Erelstoragetype *storage_type);
// extract storage option value from defelem
CWStringDynamic *ExtractStorageOptionStr(DefElem *def_elem);
diff --git a/src/test/regress/expected/gpctas.out
b/src/test/regress/expected/gpctas.out
index 328202d19c..cb0c2536bf 100644
--- a/src/test/regress/expected/gpctas.out
+++ b/src/test/regress/expected/gpctas.out
@@ -1,4 +1,5 @@
set optimizer_print_missing_stats = off;
+set optimizer_trace_fallback = on;
drop table if exists ctas_src;
NOTICE: table "ctas_src" does not exist, skipping
drop table if exists ctas_dst;
@@ -8,14 +9,114 @@ insert into ctas_src values(1, 1, 'A', 1);
insert into ctas_src values(2, 1, 'A', 0);
insert into ctas_src values(3, 0, 'B', 1);
-- MPP-2859
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ InitPlan 1 (returns $0) (slice2)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Partial Aggregate
+ -> HashAggregate
+ Group Key: ctas_src_1.class
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: ctas_src_1.class
+ -> Streaming HashAggregate
+ Group Key: ctas_src_1.class
+ -> Seq Scan on ctas_src ctas_src_1
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src.attr
+ -> HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ -> Seq Scan on ctas_src
+ Optimizer: Postgres query optimizer
+(19 rows)
+
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | dclass
+------+-------+--------
+ B | 0 | 2
+ A | 1 | 2
+(2 rows)
+
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+-----------------------------------------------------------
+ HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ InitPlan 1 (returns $0) (slice2)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on ctas_src ctas_src_1
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src.attr
+ -> HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ -> Seq Scan on ctas_src
+ Optimizer: Postgres query optimizer
+(13 rows)
+
create table ctas_dst as
SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | maxclass
+------+-------+----------
+ B | 0 | 1
+ A | 1 | 1
+(2 rows)
+
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ InitPlan 1 (returns $0) (slice2)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Partial Aggregate
+ -> HashAggregate
+ Group Key: ctas_src_1.class
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: ctas_src_1.class
+ -> Streaming HashAggregate
+ Group Key: ctas_src_1.class
+ -> Seq Scan on ctas_src ctas_src_1
+ InitPlan 2 (returns $1) (slice5)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice6; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on ctas_src ctas_src_2
+ InitPlan 3 (returns $2) (slice7)
+ -> Finalize Aggregate
+ -> Gather Motion 3:1 (slice8; segments: 3)
+ -> Partial Aggregate
+ -> Seq Scan on ctas_src ctas_src_3
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src.attr
+ -> HashAggregate
+ Group Key: ctas_src.attr, ctas_src.class
+ -> Seq Scan on ctas_src
+ Optimizer: Postgres query optimizer
+(29 rows)
+
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | dclass | maxclass | minclass
+------+-------+--------+----------+----------
+ A | 1 | 2 | 1 | 0
+ B | 0 | 2 | 1 | 0
+(2 rows)
+
-- MPP-4298: "unknown" datatypes.
drop table if exists ctas_foo;
NOTICE: table "ctas_foo" does not exist, skipping
@@ -327,3 +428,4 @@ SELECT * FROM test_tmp2;
DROP FUNCTION public.exception_func();
DROP TABLE test_tmp1;
DROP TABLE test_tmp2;
+reset optimizer_trace_fallback;
diff --git a/src/test/regress/expected/gpctas.out
b/src/test/regress/expected/gpctas_optimizer.out
similarity index 54%
copy from src/test/regress/expected/gpctas.out
copy to src/test/regress/expected/gpctas_optimizer.out
index 328202d19c..bd62c706aa 100644
--- a/src/test/regress/expected/gpctas.out
+++ b/src/test/regress/expected/gpctas_optimizer.out
@@ -1,4 +1,5 @@
set optimizer_print_missing_stats = off;
+set optimizer_trace_fallback = on;
drop table if exists ctas_src;
NOTICE: table "ctas_src" does not exist, skipping
drop table if exists ctas_dst;
@@ -8,14 +9,121 @@ insert into ctas_src values(1, 1, 'A', 1);
insert into ctas_src values(2, 1, 'A', 0);
insert into ctas_src values(3, 0, 'B', 1);
-- MPP-2859
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src_1.attr
+ -> Nested Loop Left Join
+ Join Filter: true
+ -> GroupAggregate
+ Group Key: ctas_src_1.attr, ctas_src_1.class
+ -> Sort
+ Sort Key: ctas_src_1.attr, ctas_src_1.class
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: ctas_src_1.attr, ctas_src_1.class
+ -> Seq Scan on ctas_src ctas_src_1
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice2; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on ctas_src
+ Optimizer: GPORCA
+(18 rows)
+
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | dclass
+------+-------+--------
+ B | 0 | 2
+ A | 1 | 2
+(2 rows)
+
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src_1.attr
+ -> Nested Loop Left Join
+ Join Filter: true
+ -> GroupAggregate
+ Group Key: ctas_src_1.attr, ctas_src_1.class
+ -> Sort
+ Sort Key: ctas_src_1.attr, ctas_src_1.class
+ -> Redistribute Motion 3:3 (slice4; segments: 3)
+ Hash Key: ctas_src_1.attr, ctas_src_1.class
+ -> Seq Scan on ctas_src ctas_src_1
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice2; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on ctas_src
+ Optimizer: GPORCA
+(18 rows)
+
create table ctas_dst as
SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | maxclass
+------+-------+----------
+ B | 0 | 1
+ A | 1 | 1
+(2 rows)
+
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
+ QUERY PLAN
+------------------------------------------------------------------------------------------
+ Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ Hash Key: ctas_src_3.attr
+ -> Nested Loop Left Join
+ Join Filter: true
+ -> Nested Loop Left Join
+ Join Filter: true
+ -> Nested Loop Left Join
+ Join Filter: true
+ -> GroupAggregate
+ Group Key: ctas_src_3.attr, ctas_src_3.class
+ -> Sort
+ Sort Key: ctas_src_3.attr,
ctas_src_3.class
+ -> Redistribute Motion 3:3 (slice8;
segments: 3)
+ Hash Key: ctas_src_3.attr,
ctas_src_3.class
+ -> Seq Scan on ctas_src
ctas_src_3
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice6; segments:
1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice7;
segments: 3)
+ -> Seq Scan on ctas_src
ctas_src_2
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice4; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice5;
segments: 3)
+ -> Seq Scan on ctas_src
ctas_src_1
+ -> Materialize
+ -> Broadcast Motion 1:3 (slice2; segments: 1)
+ -> Aggregate
+ -> Gather Motion 3:1 (slice3; segments: 3)
+ -> Seq Scan on ctas_src
+ Optimizer: GPORCA
+(32 rows)
+
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
+ attr | class | dclass | maxclass | minclass
+------+-------+--------+----------+----------
+ A | 1 | 2 | 1 | 0
+ B | 0 | 2 | 1 | 0
+(2 rows)
+
-- MPP-4298: "unknown" datatypes.
drop table if exists ctas_foo;
NOTICE: table "ctas_foo" does not exist, skipping
@@ -30,6 +138,24 @@ NOTICE: Table doesn't have 'DISTRIBUTED BY' clause.
Creating a NULL policy entr
create table ctas_baz as select 'delete me' as action, * from ctas_bar
distributed by (a);
-- "action" becomes text
\d ctas_baz
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
Table "public.ctas_baz"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -70,6 +196,24 @@ select action::text, b from ctas_baz order by 1,2 limit 5;
alter table ctas_baz alter column action type text;
\d ctas_baz
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-default collation
Table "public.ctas_baz"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
@@ -112,7 +256,7 @@ select action::text, b from ctas_baz order by 1,2 limit 5;
-- Once upon a time, we had a bug in dispatching the table's OID in this
-- scenario.
create table ctas_input(x int);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x'
as the Apache Cloudberry data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x'
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 ctas_input select * from generate_series(1, 10);
CREATE FUNCTION ctas_inputArray() RETURNS INT[] AS $$
@@ -124,8 +268,12 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
create table ctas_output as select ctas_inputArray()::int[] as x;
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'x' as the Apache Cloudberry data distribution key for this table.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: SIRV functions
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'x' 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.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Non-Scalar Subquery
-- Test CTAS with VALUES.
CREATE TEMP TABLE yolo(i, j, k) AS (VALUES (0,0,0), (1, NULL, 0), (2, NULL,
0), (3, NULL, 0)) DISTRIBUTED BY (i);
--
@@ -183,7 +331,7 @@ $BODY$ LANGUAGE SQL IMMUTABLE
DROP TABLE IF EXISTS unnest_2d_tbl01;
NOTICE: table "unnest_2d_tbl01" does not exist, skipping
CREATE TABLE unnest_2d_tbl01 (id INT, val DOUBLE PRECISION[][]);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id'
as the Apache Cloudberry data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id'
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 unnest_2d_tbl01 VALUES
(1, ARRAY[[1::float8,2],[3::float8,4],[5::float8,6],[7::float8,8]]),
@@ -194,12 +342,12 @@ NOTICE: table "unnest_2d_tbl01_out" does not exist,
skipping
-- The following CTAS fails previously, see Github Issue 9365
CREATE TABLE unnest_2d_tbl01_out AS
SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl01;
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'id' as the Apache Cloudberry data distribution key for this table.
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'id' 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.
-- Github issue 9790.
-- Previously, CTAS with no data won't handle the 'WITH' clause
CREATE TABLE ctas_base(a int, b int);
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a'
as the Apache Cloudberry data distribution key for this table.
+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.
CREATE TABLE ctas_aocs WITH (appendonly=true, orientation=column) AS SELECT *
FROM ctas_base WITH NO DATA;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
@@ -256,10 +404,14 @@ reset optimizer;
create or replace function mv_action_select_issue_11999() returns bool
language sql as
'declare c cursor for select 1/0; select true';
create materialized view sro_mv_issue_11999 as select
mv_action_select_issue_11999() with no data;
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mv_action_select_issue_11999' as the Apache Cloudberry data distribution key
for this table.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: SIRV functions
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mv_action_select_issue_11999' 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.
create table t_sro_mv_issue_11999 as select mv_action_select_issue_11999()
with no data;
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mv_action_select_issue_11999' as the Apache Cloudberry data distribution key
for this table.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: SIRV functions
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'mv_action_select_issue_11999' 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.
select count(*)
from
@@ -274,6 +426,8 @@ from
where localoid::regclass::text = 'sro_mv_issue_11999' or
localoid::regclass::text = 't_sro_mv_issue_11999'
)x;
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Queries on master-only tables
count
-------
8
@@ -292,6 +446,8 @@ from
where localoid::regclass::text = 'sro_mv_issue_11999' or
localoid::regclass::text = 't_sro_mv_issue_11999'
)x;
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: ROW EXPRESSION
count
-------
2
@@ -299,6 +455,8 @@ from
-- then refresh should error out
refresh materialized view sro_mv_issue_11999;
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: Refresh matview is not supported with GPORCA
ERROR: division by zero
CONTEXT: SQL function "mv_action_select_issue_11999" statement 1
-- Test CTAS + initplan, and an exception was raised in preprocess_initplans
@@ -307,7 +465,9 @@ CREATE OR REPLACE FUNCTION public.exception_func()
LANGUAGE plpgsql
AS $function$declare cname refcursor = 'result'; begin open cname for select
1; raise sqlstate '02000'; return cname; exception when sqlstate '02000' then
return cname; end;$function$;
SELECT exception_func() INTO TEMPORARY test_tmp1;
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' as the Apache Cloudberry data distribution key for this table.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: SIRV functions
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' 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.
SELECT * FROM test_tmp1;
exception_func
@@ -316,7 +476,9 @@ SELECT * FROM test_tmp1;
(1 row)
CREATE TEMPORARY TABLE test_tmp2 AS SELECT exception_func();
-NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' as the Apache Cloudberry data distribution key for this table.
+INFO: GPORCA failed to produce a plan, falling back to Postgres-based planner
+DETAIL: Falling back to Postgres-based planner because GPORCA does not
support the following feature: SIRV functions
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' 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.
SELECT * FROM test_tmp2;
exception_func
@@ -327,3 +489,4 @@ SELECT * FROM test_tmp2;
DROP FUNCTION public.exception_func();
DROP TABLE test_tmp1;
DROP TABLE test_tmp2;
+reset optimizer_trace_fallback;
diff --git a/src/test/regress/expected/select_into.out
b/src/test/regress/expected/select_into.out
index 9e38d2fe4f..03dcf62f7f 100644
--- a/src/test/regress/expected/select_into.out
+++ b/src/test/regress/expected/select_into.out
@@ -210,6 +210,27 @@ INSERT INTO b SELECT 1 INTO f;
ERROR: SELECT ... INTO is not allowed here
LINE 1: INSERT INTO b SELECT 1 INTO f;
^
+--
+-- Empty target list
+--
+explain (costs off) select into empty_tl from generate_series(0, 10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' 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.
+ QUERY PLAN
+------------------------------------------------
+ Redistribute Motion 1:3 (slice1; segments: 1)
+ -> Function Scan on generate_series
+ Optimizer: Postgres query optimizer
+(3 rows)
+
+select into empty_tl from generate_series(0, 10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named
'' 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.
+select * from empty_tl;
+--
+(11 rows)
+
+drop table empty_tl;
-- Test CREATE TABLE AS ... IF NOT EXISTS
CREATE TABLE ctas_ine_tbl AS SELECT 1;
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
diff --git a/src/test/regress/expected/select_into_optimizer.out
b/src/test/regress/expected/select_into_optimizer.out
index c8442b79a3..083a617f30 100644
--- a/src/test/regress/expected/select_into_optimizer.out
+++ b/src/test/regress/expected/select_into_optimizer.out
@@ -218,6 +218,28 @@ INSERT INTO b SELECT 1 INTO f;
ERROR: SELECT ... INTO is not allowed here
LINE 1: INSERT INTO b SELECT 1 INTO f;
^
+--
+-- Empty target list
+--
+explain (costs off) select into empty_tl from generate_series(0, 10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+ QUERY PLAN
+-------------------------------------------------------------
+ Result
+ -> Redistribute Motion 3:3 (slice1; segments: 3)
+ -> Result
+ One-Time Filter: (gp_execution_segment() = 2)
+ -> Function Scan on generate_series
+ Optimizer: GPORCA
+(6 rows)
+
+select into empty_tl from generate_series(0, 10);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy
entry.
+select * from empty_tl;
+--
+(11 rows)
+
+drop table empty_tl;
-- Test CREATE TABLE AS ... IF NOT EXISTS
CREATE TABLE ctas_ine_tbl AS SELECT 1;
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
diff --git a/src/test/regress/sql/gpctas.sql b/src/test/regress/sql/gpctas.sql
index 01a47b1f3d..7f51cb4b08 100644
--- a/src/test/regress/sql/gpctas.sql
+++ b/src/test/regress/sql/gpctas.sql
@@ -1,4 +1,5 @@
set optimizer_print_missing_stats = off;
+set optimizer_trace_fallback = on;
drop table if exists ctas_src;
drop table if exists ctas_dst;
@@ -8,18 +9,26 @@ insert into ctas_src values(2, 1, 'A', 0);
insert into ctas_src values(3, 0, 'B', 1);
-- MPP-2859
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass
FROM ctas_src GROUP BY attr, class distributed by (attr);
-
+select * from ctas_dst;
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
create table ctas_dst as
SELECT attr, class, (select max(class) from ctas_src) as maxclass FROM
ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
drop table ctas_dst;
+explain (costs off) create table ctas_dst as
+SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
create table ctas_dst as
SELECT attr, class, (select count(distinct class) from ctas_src) as dclass,
(select max(class) from ctas_src) as maxclass, (select min(class) from
ctas_src) as minclass FROM ctas_src GROUP BY attr, class distributed by (attr);
+select * from ctas_dst;
-- MPP-4298: "unknown" datatypes.
drop table if exists ctas_foo;
@@ -218,3 +227,4 @@ SELECT * FROM test_tmp2;
DROP FUNCTION public.exception_func();
DROP TABLE test_tmp1;
DROP TABLE test_tmp2;
+reset optimizer_trace_fallback;
diff --git a/src/test/regress/sql/select_into.sql
b/src/test/regress/sql/select_into.sql
index 0c706a8c33..9b7b01b708 100644
--- a/src/test/regress/sql/select_into.sql
+++ b/src/test/regress/sql/select_into.sql
@@ -133,6 +133,14 @@ SELECT * FROM (SELECT 1 INTO f) bar;
CREATE VIEW foo AS SELECT 1 INTO b;
INSERT INTO b SELECT 1 INTO f;
+--
+-- Empty target list
+--
+explain (costs off) select into empty_tl from generate_series(0, 10);
+select into empty_tl from generate_series(0, 10);
+select * from empty_tl;
+drop table empty_tl;
+
-- Test CREATE TABLE AS ... IF NOT EXISTS
CREATE TABLE ctas_ine_tbl AS SELECT 1;
CREATE TABLE ctas_ine_tbl AS SELECT 1 / 0; -- error
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
