This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 7bad2e1d9fa [opt](nereids) infer result column name in ctas and query
stmt (#26055)
7bad2e1d9fa is described below
commit 7bad2e1d9fa3f8cab1584a7248fcc4c466b37714
Author: seawinde <[email protected]>
AuthorDate: Wed Nov 8 11:28:48 2023 +0800
[opt](nereids) infer result column name in ctas and query stmt (#26055)
Infer name if it is an expression and doesn't alias artificially when
create or select stmt in nereids.
The infer name strategy is the same as #24990
---
.../java/org/apache/doris/analysis/SelectStmt.java | 8 +-
.../doris/nereids/analyzer/UnboundFunction.java | 2 +-
.../nereids/rules/analysis/BindExpression.java | 17 +-
.../trees/expressions/AggregateExpression.java | 2 +-
.../doris/nereids/trees/expressions/Alias.java | 4 +
.../trees/expressions/AssertNumRowsElement.java | 2 +-
.../nereids/trees/expressions/Expression.java | 2 +-
.../nereids/trees/expressions/NamedExpression.java | 2 +-
.../nereids/trees/expressions/SubqueryExpr.java | 2 +-
.../trees/expressions/functions/BoundFunction.java | 2 +-
.../nereids/trees/expressions/literal/Literal.java | 2 +-
.../trees/plans/visitor/InferPlanOutputAlias.java | 76 ++
.../org/apache/doris/qe/OlapQueryCacheTest.java | 22 +-
.../suites/nereids_p0/infer_expr_name/load.groovy | 821 +++++++++++++++++++++
.../nereids_p0/infer_expr_name/query13.groovy | 81 ++
.../nereids_p0/infer_expr_name/query14.groovy | 137 ++++
.../nereids_p0/infer_expr_name/query15.groovy | 49 ++
.../nereids_p0/infer_expr_name/query2.groovy | 94 +++
.../nereids_p0/infer_expr_name/query23.groovy | 80 ++
.../nereids_p0/infer_expr_name/query35.groovy | 102 +++
.../nereids_p0/infer_expr_name/query38.groovy | 51 ++
.../nereids_p0/infer_expr_name/query41.groovy | 80 ++
.../nereids_p0/infer_expr_name/query42.groovy | 53 ++
.../nereids_p0/infer_expr_name/query45.groovy | 50 ++
.../nereids_p0/infer_expr_name/query59.groovy | 81 ++
.../nereids_p0/infer_expr_name/query61.groovy | 74 ++
.../nereids_p0/infer_expr_name/query62.groovy | 63 ++
.../nereids_p0/infer_expr_name/query8.groovy | 136 ++++
.../nereids_p0/infer_expr_name/query85.groovy | 115 +++
29 files changed, 2181 insertions(+), 29 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
index 48fd213e4c7..a9811cf4e02 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
@@ -554,12 +554,8 @@ public class SelectStmt extends QueryStmt {
}
resultExprs.add(rewriteQueryExprByMvColumnExpr(item.getExpr(), analyzer));
String columnLabel = null;
- Class<? extends StatementBase> statementClazz =
analyzer.getRootStatementClazz();
- if (statementClazz != null
- &&
(!QueryStmt.class.isAssignableFrom(statementClazz) || hasOutFileClause())) {
- // Infer column name when item is expr
- columnLabel = item.toColumnLabel(i);
- }
+ // Infer column name when item is expr, both query and ddl
+ columnLabel = item.toColumnLabel(i);
if (columnLabel == null) {
// column label without position is applicative for
query and do not infer
// column name when item is expr
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/UnboundFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/UnboundFunction.java
index c5cb960dbe5..d73474906a9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/UnboundFunction.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/analyzer/UnboundFunction.java
@@ -62,7 +62,7 @@ public class UnboundFunction extends Function implements
Unbound, PropagateNulla
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return Utils.normalizeName(getName(), DEFAULT_EXPRESSION_NAME);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
index b2b4c30b934..0d36ca31c32 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
@@ -37,6 +37,7 @@ import org.apache.doris.nereids.trees.UnaryNode;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.BoundStar;
import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.ExprId;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
import org.apache.doris.nereids.trees.expressions.Properties;
@@ -72,12 +73,14 @@ import
org.apache.doris.nereids.trees.plans.logical.LogicalSort;
import org.apache.doris.nereids.trees.plans.logical.LogicalSubQueryAlias;
import org.apache.doris.nereids.trees.plans.logical.LogicalTVFRelation;
import org.apache.doris.nereids.trees.plans.logical.UsingJoin;
+import org.apache.doris.nereids.trees.plans.visitor.InferPlanOutputAlias;
import org.apache.doris.nereids.util.TypeCoercionUtils;
import org.apache.doris.qe.ConnectContext;
import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableList.Builder;
+import com.google.common.collect.ImmutableListMultimap;
import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Sets;
import org.apache.commons.lang3.StringUtils;
@@ -564,10 +567,16 @@ public class BindExpression implements
AnalysisRuleFactory {
),
RuleType.BINDING_RESULT_SINK.build(
unboundResultSink().then(sink -> {
- List<NamedExpression> outputExprs =
sink.child().getOutput().stream()
- .map(NamedExpression.class::cast)
- .collect(ImmutableList.toImmutableList());
- return new LogicalResultSink<>(outputExprs, sink.child());
+
+ final ImmutableListMultimap.Builder<ExprId, Integer>
exprIdToIndexMapBuilder =
+ ImmutableListMultimap.builder();
+ List<Slot> childOutput = sink.child().getOutput();
+ for (int index = 0; index < childOutput.size(); index++) {
+
exprIdToIndexMapBuilder.put(childOutput.get(index).getExprId(), index);
+ }
+ InferPlanOutputAlias aliasInfer = new
InferPlanOutputAlias(childOutput);
+ sink.child().accept(aliasInfer,
exprIdToIndexMapBuilder.build());
+ return new LogicalResultSink<>(aliasInfer.getOutputs(),
sink.child());
})
)
).stream().map(ruleCondition).collect(ImmutableList.toImmutableList());
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AggregateExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AggregateExpression.java
index 4eb4653ded1..d097efd7aed 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AggregateExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AggregateExpression.java
@@ -119,7 +119,7 @@ public class AggregateExpression extends Expression
implements UnaryExpression {
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return Utils.normalizeName(function.getName(),
DEFAULT_EXPRESSION_NAME);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
index b02c968baba..877f792e501 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
@@ -146,4 +146,8 @@ public class Alias extends NamedExpression implements
UnaryExpression {
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitAlias(this, context);
}
+
+ public boolean isNameFromChild() {
+ return nameFromChild;
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
index 8744266c86b..9b2261e73bc 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/AssertNumRowsElement.java
@@ -88,7 +88,7 @@ public class AssertNumRowsElement extends Expression
implements LeafExpression,
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return assertion.name().toLowerCase();
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
index 12a3a9768ca..2a432aaa80b 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Expression.java
@@ -96,7 +96,7 @@ public abstract class Expression extends
AbstractTreeNode<Expression> implements
// Name of expr, this is used by generating column name automatically when
there is no
// alias
- protected String getExpressionName() {
+ public String getExpressionName() {
return this.exprName;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/NamedExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/NamedExpression.java
index 6c2e14191c0..2854704b922 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/NamedExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/NamedExpression.java
@@ -58,7 +58,7 @@ public abstract class NamedExpression extends Expression {
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return Utils.normalizeName(getName(), DEFAULT_EXPRESSION_NAME);
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
index 6715bed6cc9..d6876873ed3 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
@@ -85,7 +85,7 @@ public abstract class SubqueryExpr extends Expression
implements LeafExpression
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return "subquery";
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/BoundFunction.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/BoundFunction.java
index d5008748482..e3970161c75 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/BoundFunction.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/BoundFunction.java
@@ -56,7 +56,7 @@ public abstract class BoundFunction extends Function
implements ComputeSignature
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return Utils.normalizeName(getName(), DEFAULT_EXPRESSION_NAME);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
index e716d66994c..c83061f1959 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/Literal.java
@@ -132,7 +132,7 @@ public abstract class Literal extends Expression implements
LeafExpression, Comp
}
@Override
- protected String getExpressionName() {
+ public String getExpressionName() {
return "literal";
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/InferPlanOutputAlias.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/InferPlanOutputAlias.java
new file mode 100644
index 00000000000..48248e1c588
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/visitor/InferPlanOutputAlias.java
@@ -0,0 +1,76 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.plans.visitor;
+
+import org.apache.doris.nereids.trees.expressions.Alias;
+import org.apache.doris.nereids.trees.expressions.ExprId;
+import org.apache.doris.nereids.trees.expressions.NamedExpression;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.plans.Plan;
+
+import com.google.common.collect.ImmutableCollection;
+import com.google.common.collect.ImmutableMultimap;
+import com.google.common.collect.ImmutableSet;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.stream.Collectors;
+
+/**
+ * Infer output column name when it refers an expression and not has an alias
manually.
+ */
+public class InferPlanOutputAlias extends DefaultPlanVisitor<Void,
ImmutableMultimap<ExprId, Integer>> {
+
+ private final List<Slot> currentOutputs;
+ private final List<NamedExpression> finalOutputs;
+
+ public InferPlanOutputAlias(List<Slot> currentOutputs) {
+ this.currentOutputs = currentOutputs;
+ this.finalOutputs = new ArrayList<>(currentOutputs);
+ }
+
+ @Override
+ public Void visit(Plan plan, ImmutableMultimap<ExprId, Integer>
currentExprIdAndIndexMap) {
+
+ List<Alias> aliasProjects = plan.getExpressions().stream()
+ .filter(expression -> expression instanceof Alias)
+ .map(Alias.class::cast)
+ .collect(Collectors.toList());
+
+ ImmutableSet<ExprId> currentOutputExprIdSet =
currentExprIdAndIndexMap.keySet();
+ for (Alias projectItem : aliasProjects) {
+ ExprId exprId = projectItem.getExprId();
+ // Infer name when alias child is expression and alias's name is
from child
+ if (currentOutputExprIdSet.contains(projectItem.getExprId())
+ && projectItem.isNameFromChild()) {
+ String inferredAliasName =
projectItem.child().getExpressionName();
+ ImmutableCollection<Integer> outPutExprIndexes =
currentExprIdAndIndexMap.get(exprId);
+ // replace output name by inferred name
+ outPutExprIndexes.forEach(index -> {
+ Slot slot = currentOutputs.get(index);
+ finalOutputs.set(index, slot.withName("__" +
inferredAliasName + "_" + index));
+ });
+ }
+ }
+ return super.visit(plan, currentExprIdAndIndexMap);
+ }
+
+ public List<NamedExpression> getOutputs() {
+ return finalOutputs;
+ }
+}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/qe/OlapQueryCacheTest.java
b/fe/fe-core/src/test/java/org/apache/doris/qe/OlapQueryCacheTest.java
index 5e2d13003f2..29201d08711 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/qe/OlapQueryCacheTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/qe/OlapQueryCacheTest.java
@@ -1044,10 +1044,10 @@ public class OlapQueryCacheTest {
cache.rewriteSelectStmt(null);
LOG.warn("Sub nokey={}", cache.getNokeyStmt().toSql());
Assert.assertEquals(cache.getNokeyStmt().toSql(),
- "SELECT <slot 7> `eventdate` AS `eventdate`, <slot 8>
sum(`pv`) AS `sum(``pv``)` FROM ("
- + "SELECT <slot 3> `eventdate` AS `eventdate`,
<slot 4> count(`userid`) AS `pv` FROM "
- + "`testCluster:testDb`.`appevent` WHERE `eventid`
= 1"
- + " GROUP BY `eventdate`) tbl GROUP BY
`eventdate`");
+ "SELECT <slot 7> `eventdate` AS `eventdate`, <slot 8>
sum(`pv`) AS `__sum_1` "
+ + "FROM (SELECT <slot 3> `eventdate` AS
`eventdate`, <slot 4> count(`userid`) AS `pv` "
+ + "FROM `testCluster:testDb`.`appevent` WHERE
`eventid` = 1 "
+ + "GROUP BY `eventdate`) tbl GROUP BY
`eventdate`");
PartitionRange range = cache.getPartitionRange();
boolean flag = range.analytics();
@@ -1066,11 +1066,11 @@ public class OlapQueryCacheTest {
sql = ca.getRewriteStmt().toSql();
LOG.warn("Sub rewrite={}", sql);
Assert.assertEquals(sql,
- "SELECT <slot 7> `eventdate` AS `eventdate`, <slot 8>
sum(`pv`) AS `sum(``pv``)` FROM ("
- + "SELECT <slot 3> `eventdate` AS `eventdate`,
<slot 4> count(`userid`) AS `pv` FROM "
- + "`testCluster:testDb`.`appevent` WHERE "
- + "`eventdate` > '2020-01-13' AND `eventdate` <
'2020-01-16' AND `eventid` = 1 GROUP BY "
- + "`eventdate`) tbl GROUP BY `eventdate`");
+ "SELECT <slot 7> `eventdate` AS `eventdate`, <slot 8>
sum(`pv`) AS `__sum_1` "
+ + "FROM (SELECT <slot 3> `eventdate` AS
`eventdate`, <slot 4> count(`userid`) AS `pv` "
+ + "FROM `testCluster:testDb`.`appevent` WHERE
`eventdate` > '2020-01-13' "
+ + "AND `eventdate` < '2020-01-16' AND `eventid` =
1 "
+ + "GROUP BY `eventdate`) tbl GROUP BY
`eventdate`");
} catch (Exception e) {
LOG.warn("sub ex={}", e);
Assert.fail(e.getMessage());
@@ -1122,8 +1122,8 @@ public class OlapQueryCacheTest {
SqlCache sqlCache = (SqlCache) ca.getCache();
String cacheKey = sqlCache.getSqlWithViewStmt();
- Assert.assertEquals(cacheKey, "SELECT <slot 2> `eventdate` AS
`eventdate`, <slot 3> count(`userid`) AS "
- + "`count(``userid``)` FROM `testCluster:testDb`.`appevent`
WHERE `eventdate` >= '2020-01-12' AND "
+ Assert.assertEquals(cacheKey, "SELECT <slot 2> `eventdate` AS
`eventdate`, <slot 3> count(`userid`) "
+ + "AS `__count_1` FROM `testCluster:testDb`.`appevent` WHERE
`eventdate` >= '2020-01-12' AND "
+ "`eventdate` <= '2020-01-14' GROUP BY `eventdate`|");
Assert.assertEquals(selectedPartitionIds.size(),
sqlCache.getSumOfPartitionNum());
}
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/load.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/load.groovy
new file mode 100644
index 00000000000..ecc84655e05
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/load.groovy
@@ -0,0 +1,821 @@
+package infer_expr_name
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("load") {
+ String database = context.config.getDbNameByFile(context.file)
+ sql "drop database if exists ${database}"
+ sql "create database ${database}"
+ sql "use ${database}"
+
+ sql '''
+ drop table if exists customer_demographics
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer_demographics (
+ cd_demo_sk bigint not null,
+ cd_gender char(1),
+ cd_marital_status char(1),
+ cd_education_status char(20),
+ cd_purchase_estimate integer,
+ cd_credit_rating char(10),
+ cd_dep_count integer,
+ cd_dep_employed_count integer,
+ cd_dep_college_count integer
+ )
+ DUPLICATE KEY(cd_demo_sk)
+ DISTRIBUTED BY HASH(cd_gender) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists reason
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS reason (
+ r_reason_sk bigint not null,
+ r_reason_id char(16) not null,
+ r_reason_desc char(100)
+ )
+ DUPLICATE KEY(r_reason_sk)
+ DISTRIBUTED BY HASH(r_reason_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists date_dim
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS date_dim (
+ d_date_sk bigint not null,
+ d_date_id char(16) not null,
+ d_date datev2,
+ d_month_seq integer,
+ d_week_seq integer,
+ d_quarter_seq integer,
+ d_year integer,
+ d_dow integer,
+ d_moy integer,
+ d_dom integer,
+ d_qoy integer,
+ d_fy_year integer,
+ d_fy_quarter_seq integer,
+ d_fy_week_seq integer,
+ d_day_name char(9),
+ d_quarter_name char(6),
+ d_holiday char(1),
+ d_weekend char(1),
+ d_following_holiday char(1),
+ d_first_dom integer,
+ d_last_dom integer,
+ d_same_day_ly integer,
+ d_same_day_lq integer,
+ d_current_day char(1),
+ d_current_week char(1),
+ d_current_month char(1),
+ d_current_quarter char(1),
+ d_current_year char(1)
+ )
+ DUPLICATE KEY(d_date_sk)
+ PARTITION BY RANGE(d_date_sk)
+ (
+ PARTITION `ppast` values less than("2450815"),
+ PARTITION `p1998` values less than("2451180"),
+ PARTITION `p1999` values less than("2451545"),
+ PARTITION `p2000` values less than("2451911"),
+ PARTITION `p2001` values less than("2452276"),
+ PARTITION `p2002` values less than("2452641"),
+ PARTITION `p2003` values less than("2453006"),
+ PARTITION `pfuture` values less than("9999999")
+ )
+ DISTRIBUTED BY HASH(d_date_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists warehouse
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS warehouse (
+ w_warehouse_sk bigint not null,
+ w_warehouse_id char(16) not null,
+ w_warehouse_name varchar(20),
+ w_warehouse_sq_ft integer,
+ w_street_number char(10),
+ w_street_name varchar(60),
+ w_street_type char(15),
+ w_suite_number char(10),
+ w_city varchar(60),
+ w_county varchar(30),
+ w_state char(2),
+ w_zip char(10),
+ w_country varchar(20),
+ w_gmt_offset decimalv3(5,2)
+ )
+ DUPLICATE KEY(w_warehouse_sk)
+ DISTRIBUTED BY HASH(w_warehouse_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_sales (
+ cs_sold_date_sk bigint,
+ cs_item_sk bigint not null,
+ cs_order_number bigint not null,
+ cs_sold_time_sk bigint,
+ cs_ship_date_sk bigint,
+ cs_bill_customer_sk bigint,
+ cs_bill_cdemo_sk bigint,
+ cs_bill_hdemo_sk bigint,
+ cs_bill_addr_sk bigint,
+ cs_ship_customer_sk bigint,
+ cs_ship_cdemo_sk bigint,
+ cs_ship_hdemo_sk bigint,
+ cs_ship_addr_sk bigint,
+ cs_call_center_sk bigint,
+ cs_catalog_page_sk bigint,
+ cs_ship_mode_sk bigint,
+ cs_warehouse_sk bigint,
+ cs_promo_sk bigint,
+ cs_quantity integer,
+ cs_wholesale_cost decimalv3(7,2),
+ cs_list_price decimalv3(7,2),
+ cs_sales_price decimalv3(7,2),
+ cs_ext_discount_amt decimalv3(7,2),
+ cs_ext_sales_price decimalv3(7,2),
+ cs_ext_wholesale_cost decimalv3(7,2),
+ cs_ext_list_price decimalv3(7,2),
+ cs_ext_tax decimalv3(7,2),
+ cs_coupon_amt decimalv3(7,2),
+ cs_ext_ship_cost decimalv3(7,2),
+ cs_net_paid decimalv3(7,2),
+ cs_net_paid_inc_tax decimalv3(7,2),
+ cs_net_paid_inc_ship decimalv3(7,2),
+ cs_net_paid_inc_ship_tax decimalv3(7,2),
+ cs_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(cs_sold_date_sk, cs_item_sk)
+ DISTRIBUTED BY HASH(cs_item_sk, cs_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "catalog"
+ )
+ '''
+
+ sql '''
+ drop table if exists call_center
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS call_center (
+ cc_call_center_sk bigint not null,
+ cc_call_center_id char(16) not null,
+ cc_rec_start_date datev2,
+ cc_rec_end_date datev2,
+ cc_closed_date_sk integer,
+ cc_open_date_sk integer,
+ cc_name varchar(50),
+ cc_class varchar(50),
+ cc_employees integer,
+ cc_sq_ft integer,
+ cc_hours char(20),
+ cc_manager varchar(40),
+ cc_mkt_id integer,
+ cc_mkt_class char(50),
+ cc_mkt_desc varchar(100),
+ cc_market_manager varchar(40),
+ cc_division integer,
+ cc_division_name varchar(50),
+ cc_company integer,
+ cc_company_name char(50),
+ cc_street_number char(10),
+ cc_street_name varchar(60),
+ cc_street_type char(15),
+ cc_suite_number char(10),
+ cc_city varchar(60),
+ cc_county varchar(30),
+ cc_state char(2),
+ cc_zip char(10),
+ cc_country varchar(20),
+ cc_gmt_offset decimalv3(5,2),
+ cc_tax_percentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(cc_call_center_sk)
+ DISTRIBUTED BY HASH(cc_call_center_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists inventory
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS inventory (
+ inv_date_sk bigint not null,
+ inv_item_sk bigint not null,
+ inv_warehouse_sk bigint,
+ inv_quantity_on_hand integer
+ )
+ DUPLICATE KEY(inv_date_sk, inv_item_sk, inv_warehouse_sk)
+ DISTRIBUTED BY HASH(inv_date_sk, inv_item_sk, inv_warehouse_sk) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_returns (
+ cr_item_sk bigint not null,
+ cr_order_number bigint not null,
+ cr_returned_date_sk bigint,
+ cr_returned_time_sk bigint,
+ cr_refunded_customer_sk bigint,
+ cr_refunded_cdemo_sk bigint,
+ cr_refunded_hdemo_sk bigint,
+ cr_refunded_addr_sk bigint,
+ cr_returning_customer_sk bigint,
+ cr_returning_cdemo_sk bigint,
+ cr_returning_hdemo_sk bigint,
+ cr_returning_addr_sk bigint,
+ cr_call_center_sk bigint,
+ cr_catalog_page_sk bigint,
+ cr_ship_mode_sk bigint,
+ cr_warehouse_sk bigint,
+ cr_reason_sk bigint,
+ cr_return_quantity integer,
+ cr_return_amount decimalv3(7,2),
+ cr_return_tax decimalv3(7,2),
+ cr_return_amt_inc_tax decimalv3(7,2),
+ cr_fee decimalv3(7,2),
+ cr_return_ship_cost decimalv3(7,2),
+ cr_refunded_cash decimalv3(7,2),
+ cr_reversed_charge decimalv3(7,2),
+ cr_store_credit decimalv3(7,2),
+ cr_net_loss decimalv3(7,2)
+ )
+ DUPLICATE KEY(cr_item_sk, cr_order_number)
+ DISTRIBUTED BY HASH(cr_item_sk, cr_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "catalog"
+ )
+ '''
+
+ sql '''
+ drop table if exists household_demographics
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS household_demographics (
+ hd_demo_sk bigint not null,
+ hd_income_band_sk bigint,
+ hd_buy_potential char(15),
+ hd_dep_count integer,
+ hd_vehicle_count integer
+ )
+ DUPLICATE KEY(hd_demo_sk)
+ DISTRIBUTED BY HASH(hd_demo_sk) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists customer_address
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer_address (
+ ca_address_sk bigint not null,
+ ca_address_id char(16) not null,
+ ca_street_number char(10),
+ ca_street_name varchar(60),
+ ca_street_type char(15),
+ ca_suite_number char(10),
+ ca_city varchar(60),
+ ca_county varchar(30),
+ ca_state char(2),
+ ca_zip char(10),
+ ca_country varchar(20),
+ ca_gmt_offset decimalv3(5,2),
+ ca_location_type char(20)
+ )
+ DUPLICATE KEY(ca_address_sk)
+ DISTRIBUTED BY HASH(ca_address_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists income_band
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS income_band (
+ ib_income_band_sk bigint not null,
+ ib_lower_bound integer,
+ ib_upper_bound integer
+ )
+ DUPLICATE KEY(ib_income_band_sk)
+ DISTRIBUTED BY HASH(ib_income_band_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists catalog_page
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS catalog_page (
+ cp_catalog_page_sk bigint not null,
+ cp_catalog_page_id char(16) not null,
+ cp_start_date_sk integer,
+ cp_end_date_sk integer,
+ cp_department varchar(50),
+ cp_catalog_number integer,
+ cp_catalog_page_number integer,
+ cp_description varchar(100),
+ cp_type varchar(100)
+ )
+ DUPLICATE KEY(cp_catalog_page_sk)
+ DISTRIBUTED BY HASH(cp_catalog_page_sk) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists item
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS item (
+ i_item_sk bigint not null,
+ i_item_id char(16) not null,
+ i_rec_start_date datev2,
+ i_rec_end_date datev2,
+ i_item_desc varchar(200),
+ i_current_price decimalv3(7,2),
+ i_wholesale_cost decimalv3(7,2),
+ i_brand_id integer,
+ i_brand char(50),
+ i_class_id integer,
+ i_class char(50),
+ i_category_id integer,
+ i_category char(50),
+ i_manufact_id integer,
+ i_manufact char(50),
+ i_size char(20),
+ i_formulation char(20),
+ i_color char(20),
+ i_units char(10),
+ i_container char(10),
+ i_manager_id integer,
+ i_product_name char(50)
+ )
+ DUPLICATE KEY(i_item_sk)
+ DISTRIBUTED BY HASH(i_item_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_returns (
+ wr_item_sk bigint not null,
+ wr_order_number bigint not null,
+ wr_returned_date_sk bigint,
+ wr_returned_time_sk bigint,
+ wr_refunded_customer_sk bigint,
+ wr_refunded_cdemo_sk bigint,
+ wr_refunded_hdemo_sk bigint,
+ wr_refunded_addr_sk bigint,
+ wr_returning_customer_sk bigint,
+ wr_returning_cdemo_sk bigint,
+ wr_returning_hdemo_sk bigint,
+ wr_returning_addr_sk bigint,
+ wr_web_page_sk bigint,
+ wr_reason_sk bigint,
+ wr_return_quantity integer,
+ wr_return_amt decimalv3(7,2),
+ wr_return_tax decimalv3(7,2),
+ wr_return_amt_inc_tax decimalv3(7,2),
+ wr_fee decimalv3(7,2),
+ wr_return_ship_cost decimalv3(7,2),
+ wr_refunded_cash decimalv3(7,2),
+ wr_reversed_charge decimalv3(7,2),
+ wr_account_credit decimalv3(7,2),
+ wr_net_loss decimalv3(7,2)
+ )
+ DUPLICATE KEY(wr_item_sk, wr_order_number)
+ DISTRIBUTED BY HASH(wr_item_sk, wr_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "web"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_site
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_site (
+ web_site_sk bigint not null,
+ web_site_id char(16) not null,
+ web_rec_start_date datev2,
+ web_rec_end_date datev2,
+ web_name varchar(50),
+ web_open_date_sk bigint,
+ web_close_date_sk bigint,
+ web_class varchar(50),
+ web_manager varchar(40),
+ web_mkt_id integer,
+ web_mkt_class varchar(50),
+ web_mkt_desc varchar(100),
+ web_market_manager varchar(40),
+ web_company_id integer,
+ web_company_name char(50),
+ web_street_number char(10),
+ web_street_name varchar(60),
+ web_street_type char(15),
+ web_suite_number char(10),
+ web_city varchar(60),
+ web_county varchar(30),
+ web_state char(2),
+ web_zip char(10),
+ web_country varchar(20),
+ web_gmt_offset decimalv3(5,2),
+ web_tax_percentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(web_site_sk)
+ DISTRIBUTED BY HASH(web_site_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists promotion
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS promotion (
+ p_promo_sk bigint not null,
+ p_promo_id char(16) not null,
+ p_start_date_sk bigint,
+ p_end_date_sk bigint,
+ p_item_sk bigint,
+ p_cost decimalv3(15,2),
+ p_response_targe integer,
+ p_promo_name char(50),
+ p_channel_dmail char(1),
+ p_channel_email char(1),
+ p_channel_catalog char(1),
+ p_channel_tv char(1),
+ p_channel_radio char(1),
+ p_channel_press char(1),
+ p_channel_event char(1),
+ p_channel_demo char(1),
+ p_channel_details varchar(100),
+ p_purpose char(15),
+ p_discount_active char(1)
+ )
+ DUPLICATE KEY(p_promo_sk)
+ DISTRIBUTED BY HASH(p_promo_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_sales (
+ ws_sold_date_sk bigint,
+ ws_item_sk bigint not null,
+ ws_order_number bigint not null,
+ ws_sold_time_sk bigint,
+ ws_ship_date_sk bigint,
+ ws_bill_customer_sk bigint,
+ ws_bill_cdemo_sk bigint,
+ ws_bill_hdemo_sk bigint,
+ ws_bill_addr_sk bigint,
+ ws_ship_customer_sk bigint,
+ ws_ship_cdemo_sk bigint,
+ ws_ship_hdemo_sk bigint,
+ ws_ship_addr_sk bigint,
+ ws_web_page_sk bigint,
+ ws_web_site_sk bigint,
+ ws_ship_mode_sk bigint,
+ ws_warehouse_sk bigint,
+ ws_promo_sk bigint,
+ ws_quantity integer,
+ ws_wholesale_cost decimalv3(7,2),
+ ws_list_price decimalv3(7,2),
+ ws_sales_price decimalv3(7,2),
+ ws_ext_discount_amt decimalv3(7,2),
+ ws_ext_sales_price decimalv3(7,2),
+ ws_ext_wholesale_cost decimalv3(7,2),
+ ws_ext_list_price decimalv3(7,2),
+ ws_ext_tax decimalv3(7,2),
+ ws_coupon_amt decimalv3(7,2),
+ ws_ext_ship_cost decimalv3(7,2),
+ ws_net_paid decimalv3(7,2),
+ ws_net_paid_inc_tax decimalv3(7,2),
+ ws_net_paid_inc_ship decimalv3(7,2),
+ ws_net_paid_inc_ship_tax decimalv3(7,2),
+ ws_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(ws_sold_date_sk, ws_item_sk)
+ DISTRIBUTED BY HASH(ws_item_sk, ws_order_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "web"
+ )
+ '''
+
+ sql '''
+ drop table if exists store
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store (
+ s_store_sk bigint not null,
+ s_store_id char(16) not null,
+ s_rec_start_date datev2,
+ s_rec_end_date datev2,
+ s_closed_date_sk bigint,
+ s_store_name varchar(50),
+ s_number_employees integer,
+ s_floor_space integer,
+ s_hours char(20),
+ s_manager varchar(40),
+ s_market_id integer,
+ s_geography_class varchar(100),
+ s_market_desc varchar(100),
+ s_market_manager varchar(40),
+ s_division_id integer,
+ s_division_name varchar(50),
+ s_company_id integer,
+ s_company_name varchar(50),
+ s_street_number varchar(10),
+ s_street_name varchar(60),
+ s_street_type char(15),
+ s_suite_number char(10),
+ s_city varchar(60),
+ s_county varchar(30),
+ s_state char(2),
+ s_zip char(10),
+ s_country varchar(20),
+ s_gmt_offset decimalv3(5,2),
+ s_tax_precentage decimalv3(5,2)
+ )
+ DUPLICATE KEY(s_store_sk)
+ DISTRIBUTED BY HASH(s_store_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists time_dim
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS time_dim (
+ t_time_sk bigint not null,
+ t_time_id char(16) not null,
+ t_time integer,
+ t_hour integer,
+ t_minute integer,
+ t_second integer,
+ t_am_pm char(2),
+ t_shift char(20),
+ t_sub_shift char(20),
+ t_meal_time char(20)
+ )
+ DUPLICATE KEY(t_time_sk)
+ DISTRIBUTED BY HASH(t_time_sk) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists web_page
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS web_page (
+ wp_web_page_sk bigint not null,
+ wp_web_page_id char(16) not null,
+ wp_rec_start_date datev2,
+ wp_rec_end_date datev2,
+ wp_creation_date_sk bigint,
+ wp_access_date_sk bigint,
+ wp_autogen_flag char(1),
+ wp_customer_sk bigint,
+ wp_url varchar(100),
+ wp_type char(50),
+ wp_char_count integer,
+ wp_link_count integer,
+ wp_image_count integer,
+ wp_max_ad_count integer
+ )
+ DUPLICATE KEY(wp_web_page_sk)
+ DISTRIBUTED BY HASH(wp_web_page_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists store_returns
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store_returns (
+ sr_item_sk bigint not null,
+ sr_ticket_number bigint not null,
+ sr_returned_date_sk bigint,
+ sr_return_time_sk bigint,
+ sr_customer_sk bigint,
+ sr_cdemo_sk bigint,
+ sr_hdemo_sk bigint,
+ sr_addr_sk bigint,
+ sr_store_sk bigint,
+ sr_reason_sk bigint,
+ sr_return_quantity integer,
+ sr_return_amt decimalv3(7,2),
+ sr_return_tax decimalv3(7,2),
+ sr_return_amt_inc_tax decimalv3(7,2),
+ sr_fee decimalv3(7,2),
+ sr_return_ship_cost decimalv3(7,2),
+ sr_refunded_cash decimalv3(7,2),
+ sr_reversed_charge decimalv3(7,2),
+ sr_store_credit decimalv3(7,2),
+ sr_net_loss decimalv3(7,2)
+ )
+ duplicate key(sr_item_sk, sr_ticket_number)
+ distributed by hash (sr_item_sk, sr_ticket_number) buckets 32
+ properties (
+ "replication_num" = "1",
+ "colocate_with" = "store"
+ )
+ '''
+
+ sql '''
+ drop table if exists store_sales
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS store_sales (
+ ss_sold_date_sk bigint,
+ ss_item_sk bigint not null,
+ ss_ticket_number bigint not null,
+ ss_sold_time_sk bigint,
+ ss_customer_sk bigint,
+ ss_cdemo_sk bigint,
+ ss_hdemo_sk bigint,
+ ss_addr_sk bigint,
+ ss_store_sk bigint,
+ ss_promo_sk bigint,
+ ss_quantity integer,
+ ss_wholesale_cost decimalv3(7,2),
+ ss_list_price decimalv3(7,2),
+ ss_sales_price decimalv3(7,2),
+ ss_ext_discount_amt decimalv3(7,2),
+ ss_ext_sales_price decimalv3(7,2),
+ ss_ext_wholesale_cost decimalv3(7,2),
+ ss_ext_list_price decimalv3(7,2),
+ ss_ext_tax decimalv3(7,2),
+ ss_coupon_amt decimalv3(7,2),
+ ss_net_paid decimalv3(7,2),
+ ss_net_paid_inc_tax decimalv3(7,2),
+ ss_net_profit decimalv3(7,2)
+ )
+ DUPLICATE KEY(ss_sold_date_sk, ss_item_sk)
+ DISTRIBUTED BY HASH(ss_item_sk, ss_ticket_number) BUCKETS 32
+ PROPERTIES (
+ "replication_num" = "1",
+ "colocate_with" = "store"
+ )
+ '''
+
+ sql '''
+ drop table if exists ship_mode
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS ship_mode (
+ sm_ship_mode_sk bigint not null,
+ sm_ship_mode_id char(16) not null,
+ sm_type char(30),
+ sm_code char(10),
+ sm_carrier char(20),
+ sm_contract char(20)
+ )
+ DUPLICATE KEY(sm_ship_mode_sk)
+ DISTRIBUTED BY HASH(sm_ship_mode_sk) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists customer
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS customer (
+ c_customer_sk bigint not null,
+ c_customer_id char(16) not null,
+ c_current_cdemo_sk bigint,
+ c_current_hdemo_sk bigint,
+ c_current_addr_sk bigint,
+ c_first_shipto_date_sk bigint,
+ c_first_sales_date_sk bigint,
+ c_salutation char(10),
+ c_first_name char(20),
+ c_last_name char(30),
+ c_preferred_cust_flag char(1),
+ c_birth_day integer,
+ c_birth_month integer,
+ c_birth_year integer,
+ c_birth_country varchar(20),
+ c_login char(13),
+ c_email_address char(50),
+ c_last_review_date_sk bigint
+ )
+ DUPLICATE KEY(c_customer_sk)
+ DISTRIBUTED BY HASH(c_customer_id) BUCKETS 12
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+
+ sql '''
+ drop table if exists dbgen_version
+ '''
+
+ sql '''
+ CREATE TABLE IF NOT EXISTS dbgen_version
+ (
+ dv_version varchar(16) ,
+ dv_create_date datev2 ,
+ dv_create_time datetime ,
+ dv_cmdline_args varchar(200)
+ )
+ DUPLICATE KEY(dv_version)
+ DISTRIBUTED BY HASH(dv_version) BUCKETS 1
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ '''
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query13.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query13.groovy
new file mode 100644
index 00000000000..f0a44ffff43
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query13.groovy
@@ -0,0 +1,81 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select avg(ss_quantity)
+ ,avg(ss_ext_sales_price)
+ ,avg(ss_ext_wholesale_cost)
+ ,sum(ss_ext_wholesale_cost)
+ from store_sales
+ ,store
+ ,customer_demographics
+ ,household_demographics
+ ,customer_address
+ ,date_dim
+ where s_store_sk = ss_store_sk
+ and ss_sold_date_sk = d_date_sk and d_year = 2001
+ and((ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'D'
+ and cd_education_status = 'Unknown'
+ and ss_sales_price between 100.00 and 150.00
+ and hd_dep_count = 3
+ )or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'S'
+ and cd_education_status = 'College'
+ and ss_sales_price between 50.00 and 100.00
+ and hd_dep_count = 1
+ ) or
+ (ss_hdemo_sk=hd_demo_sk
+ and cd_demo_sk = ss_cdemo_sk
+ and cd_marital_status = 'M'
+ and cd_education_status = '4 yr Degree'
+ and ss_sales_price between 150.00 and 200.00
+ and hd_dep_count = 1
+ ))
+ and((ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('SD', 'KS', 'MI')
+ and ss_net_profit between 100 and 200
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('MO', 'ND', 'CO')
+ and ss_net_profit between 150 and 300
+ ) or
+ (ss_addr_sk = ca_address_sk
+ and ca_country = 'United States'
+ and ca_state in ('NH', 'OH', 'TX')
+ and ss_net_profit between 50 and 250
+ ));"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("__avg_0"))
+ assertTrue(topPlan.contains("__avg_1"))
+ assertTrue(topPlan.contains("__avg_2"))
+ assertTrue(topPlan.contains("__sum_3"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query14.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query14.groovy
new file mode 100644
index 00000000000..6218108d388
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query14.groovy
@@ -0,0 +1,137 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+with cross_items as
+ (select i_item_sk ss_item_sk
+ from item,
+ (select iss.i_brand_id brand_id
+ ,iss.i_class_id class_id
+ ,iss.i_category_id category_id
+ from store_sales
+ ,item iss
+ ,date_dim d1
+ where ss_item_sk = iss.i_item_sk
+ and ss_sold_date_sk = d1.d_date_sk
+ and d1.d_year between 2000 AND 2000 + 2
+ intersect
+ select ics.i_brand_id
+ ,ics.i_class_id
+ ,ics.i_category_id
+ from catalog_sales
+ ,item ics
+ ,date_dim d2
+ where cs_item_sk = ics.i_item_sk
+ and cs_sold_date_sk = d2.d_date_sk
+ and d2.d_year between 2000 AND 2000 + 2
+ intersect
+ select iws.i_brand_id
+ ,iws.i_class_id
+ ,iws.i_category_id
+ from web_sales
+ ,item iws
+ ,date_dim d3
+ where ws_item_sk = iws.i_item_sk
+ and ws_sold_date_sk = d3.d_date_sk
+ and d3.d_year between 2000 AND 2000 + 2)
+ t where i_brand_id = brand_id
+ and i_class_id = class_id
+ and i_category_id = category_id
+),
+ avg_sales as
+ (select avg(quantity*list_price) average_sales
+ from (select ss_quantity quantity
+ ,ss_list_price list_price
+ from store_sales
+ ,date_dim
+ where ss_sold_date_sk = d_date_sk
+ and d_year between 2000 and 2000 + 2
+ union all
+ select cs_quantity quantity
+ ,cs_list_price list_price
+ from catalog_sales
+ ,date_dim
+ where cs_sold_date_sk = d_date_sk
+ and d_year between 2000 and 2000 + 2
+ union all
+ select ws_quantity quantity
+ ,ws_list_price list_price
+ from web_sales
+ ,date_dim
+ where ws_sold_date_sk = d_date_sk
+ and d_year between 2000 and 2000 + 2) x)
+ select channel, i_brand_id,i_class_id,i_category_id,sum(sales),
sum(number_sales)
+ from(
+ select 'store' channel, i_brand_id,i_class_id
+ ,i_category_id,sum(ss_quantity*ss_list_price) sales
+ , count(*) number_sales
+ from store_sales
+ ,item
+ ,date_dim
+ where ss_item_sk in (select ss_item_sk from cross_items)
+ and ss_item_sk = i_item_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year = 2000+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ss_quantity*ss_list_price) > (select average_sales from
avg_sales)
+ union all
+ select 'catalog' channel, i_brand_id,i_class_id,i_category_id,
sum(cs_quantity*cs_list_price) sales, count(*) number_sales
+ from catalog_sales
+ ,item
+ ,date_dim
+ where cs_item_sk in (select ss_item_sk from cross_items)
+ and cs_item_sk = i_item_sk
+ and cs_sold_date_sk = d_date_sk
+ and d_year = 2000+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(cs_quantity*cs_list_price) > (select average_sales from
avg_sales)
+ union all
+ select 'web' channel, i_brand_id,i_class_id,i_category_id,
sum(ws_quantity*ws_list_price) sales , count(*) number_sales
+ from web_sales
+ ,item
+ ,date_dim
+ where ws_item_sk in (select ss_item_sk from cross_items)
+ and ws_item_sk = i_item_sk
+ and ws_sold_date_sk = d_date_sk
+ and d_year = 2000+2
+ and d_moy = 11
+ group by i_brand_id,i_class_id,i_category_id
+ having sum(ws_quantity*ws_list_price) > (select average_sales from
avg_sales)
+ ) y
+ group by rollup (channel, i_brand_id,i_class_id,i_category_id)
+ order by channel,i_brand_id,i_class_id,i_category_id
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("channel"))
+ assertTrue(topPlan.contains("i_brand_id"))
+ assertTrue(topPlan.contains("i_class_id"))
+ assertTrue(topPlan.contains("i_category_id"))
+ assertTrue(topPlan.contains("__sum_4"))
+ assertTrue(topPlan.contains("__sum_5"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query15.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query15.groovy
new file mode 100644
index 00000000000..f7b87dc8e8e
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query15.groovy
@@ -0,0 +1,49 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select ca_zip
+ ,sum(cs_sales_price)
+ from catalog_sales
+ ,customer
+ ,customer_address
+ ,date_dim
+ where cs_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
+ '85392', '85460', '80348', '81792')
+ or ca_state in ('CA','WA','GA')
+ or cs_sales_price > 500)
+ and cs_sold_date_sk = d_date_sk
+ and d_qoy = 1 and d_year = 2001
+ group by ca_zip
+ order by ca_zip
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("ca_zip"))
+ assertTrue(topPlan.contains("__sum_1"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query2.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query2.groovy
new file mode 100644
index 00000000000..8ea2ff18a9a
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query2.groovy
@@ -0,0 +1,94 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+with wscs as
+ (select sold_date_sk
+ ,sales_price
+ from (select ws_sold_date_sk sold_date_sk
+ ,ws_ext_sales_price sales_price
+ from web_sales
+ union all
+ select cs_sold_date_sk sold_date_sk
+ ,cs_ext_sales_price sales_price
+ from catalog_sales) t),
+ wswscs as
+ (select d_week_seq,
+ sum(case when (d_day_name='Sunday') then sales_price else null end)
sun_sales,
+ sum(case when (d_day_name='Monday') then sales_price else null end)
mon_sales,
+ sum(case when (d_day_name='Tuesday') then sales_price else null end)
tue_sales,
+ sum(case when (d_day_name='Wednesday') then sales_price else null end)
wed_sales,
+ sum(case when (d_day_name='Thursday') then sales_price else null end)
thu_sales,
+ sum(case when (d_day_name='Friday') then sales_price else null end)
fri_sales,
+ sum(case when (d_day_name='Saturday') then sales_price else null end)
sat_sales
+ from wscs
+ ,date_dim
+ where d_date_sk = sold_date_sk
+ group by d_week_seq)
+ select d_week_seq1
+ ,round(sun_sales1/sun_sales2,2)
+ ,round(mon_sales1/mon_sales2,2)
+ ,round(tue_sales1/tue_sales2,2)
+ ,round(wed_sales1/wed_sales2,2)
+ ,round(thu_sales1/thu_sales2,2)
+ ,round(fri_sales1/fri_sales2,2)
+ ,round(sat_sales1/sat_sales2,2)
+ from
+ (select wswscs.d_week_seq d_week_seq1
+ ,sun_sales sun_sales1
+ ,mon_sales mon_sales1
+ ,tue_sales tue_sales1
+ ,wed_sales wed_sales1
+ ,thu_sales thu_sales1
+ ,fri_sales fri_sales1
+ ,sat_sales sat_sales1
+ from wswscs,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 1998) y,
+ (select wswscs.d_week_seq d_week_seq2
+ ,sun_sales sun_sales2
+ ,mon_sales mon_sales2
+ ,tue_sales tue_sales2
+ ,wed_sales wed_sales2
+ ,thu_sales thu_sales2
+ ,fri_sales fri_sales2
+ ,sat_sales sat_sales2
+ from wswscs
+ ,date_dim
+ where date_dim.d_week_seq = wswscs.d_week_seq and
+ d_year = 1998+1) z
+ where d_week_seq1=d_week_seq2-53
+ order by d_week_seq1;"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("d_week_seq1"))
+ assertTrue(topPlan.contains("__round_1"))
+ assertTrue(topPlan.contains("__round_2"))
+ assertTrue(topPlan.contains("__round_3"))
+ assertTrue(topPlan.contains("__round_4"))
+ assertTrue(topPlan.contains("__round_5"))
+ assertTrue(topPlan.contains("__round_6"))
+ assertTrue(topPlan.contains("__round_7"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query23.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query23.groovy
new file mode 100644
index 00000000000..0480d314e31
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query23.groovy
@@ -0,0 +1,80 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+with frequent_ss_items as
+ (select substr(i_item_desc,1,30) itemdesc,i_item_sk item_sk,d_date
solddate,count(*) cnt
+ from store_sales
+ ,date_dim
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_item_sk = i_item_sk
+ and d_year in (2000,2000+1,2000+2,2000+3)
+ group by substr(i_item_desc,1,30),i_item_sk,d_date
+ having count(*) >4),
+ max_store_sales as
+ (select max(csales) tpcds_cmax
+ from (select c_customer_sk,sum(ss_quantity*ss_sales_price) csales
+ from store_sales
+ ,customer
+ ,date_dim
+ where ss_customer_sk = c_customer_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_year in (2000,2000+1,2000+2,2000+3)
+ group by c_customer_sk) t),
+ best_ss_customer as
+ (select c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
+ from store_sales
+ ,customer
+ where ss_customer_sk = c_customer_sk
+ group by c_customer_sk
+ having sum(ss_quantity*ss_sales_price) > (95/100.0) * (select
+ *
+from
+ max_store_sales))
+ select sum(sales)
+ from (select cs_quantity*cs_list_price sales
+ from catalog_sales
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 5
+ and cs_sold_date_sk = d_date_sk
+ and cs_item_sk in (select item_sk from frequent_ss_items)
+ and cs_bill_customer_sk in (select c_customer_sk from
best_ss_customer)
+ union all
+ select ws_quantity*ws_list_price sales
+ from web_sales
+ ,date_dim
+ where d_year = 2000
+ and d_moy = 5
+ and ws_sold_date_sk = d_date_sk
+ and ws_item_sk in (select item_sk from frequent_ss_items)
+ and ws_bill_customer_sk in (select c_customer_sk from
best_ss_customer)) t2
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("__sum_0"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query35.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query35.groovy
new file mode 100644
index 00000000000..f10efec6dbf
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query35.groovy
@@ -0,0 +1,102 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select
+ ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ count(*) cnt1,
+ max(cd_dep_count),
+ sum(cd_dep_count),
+ max(cd_dep_count),
+ cd_dep_employed_count,
+ count(*) cnt2,
+ max(cd_dep_employed_count),
+ sum(cd_dep_employed_count),
+ max(cd_dep_employed_count),
+ cd_dep_college_count,
+ count(*) cnt3,
+ max(cd_dep_college_count),
+ sum(cd_dep_college_count),
+ max(cd_dep_college_count)
+ from
+ customer c,customer_address ca,customer_demographics
+ where
+ c.c_current_addr_sk = ca.ca_address_sk and
+ cd_demo_sk = c.c_current_cdemo_sk and
+ exists (select *
+ from store_sales,date_dim
+ where c.c_customer_sk = ss_customer_sk and
+ ss_sold_date_sk = d_date_sk and
+ d_year = 2001 and
+ d_qoy < 4) and
+ (exists (select *
+ from web_sales,date_dim
+ where c.c_customer_sk = ws_bill_customer_sk and
+ ws_sold_date_sk = d_date_sk and
+ d_year = 2001 and
+ d_qoy < 4) or
+ exists (select *
+ from catalog_sales,date_dim
+ where c.c_customer_sk = cs_ship_customer_sk and
+ cs_sold_date_sk = d_date_sk and
+ d_year = 2001 and
+ d_qoy < 4))
+ group by ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ order by ca_state,
+ cd_gender,
+ cd_marital_status,
+ cd_dep_count,
+ cd_dep_employed_count,
+ cd_dep_college_count
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("ca_state"))
+ assertTrue(topPlan.contains("cd_gender"))
+ assertTrue(topPlan.contains("cd_marital_status"))
+ assertTrue(topPlan.contains("cd_dep_count"))
+ assertTrue(topPlan.contains("cnt1"))
+ assertTrue(topPlan.contains("__max_5"))
+ assertTrue(topPlan.contains("__sum_6"))
+ assertTrue(topPlan.contains("__max_7"))
+ assertTrue(topPlan.contains("cd_dep_employed_count"))
+ assertTrue(topPlan.contains("cnt2"))
+ assertTrue(topPlan.contains("__max_10"))
+ assertTrue(topPlan.contains("__sum_11"))
+ assertTrue(topPlan.contains("__max_12"))
+ assertTrue(topPlan.contains("cd_dep_college_count"))
+ assertTrue(topPlan.contains("cnt3"))
+ assertTrue(topPlan.contains("__sum_16"))
+ assertTrue(topPlan.contains("__max_17"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query38.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query38.groovy
new file mode 100644
index 00000000000..a1182b55c34
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query38.groovy
@@ -0,0 +1,51 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select count(*) from (
+ select distinct c_last_name, c_first_name, d_date
+ from store_sales, date_dim, customer
+ where store_sales.ss_sold_date_sk = date_dim.d_date_sk
+ and store_sales.ss_customer_sk = customer.c_customer_sk
+ and d_month_seq between 1183 and 1183 + 11
+ intersect
+ select distinct c_last_name, c_first_name, d_date
+ from catalog_sales, date_dim, customer
+ where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
+ and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
+ and d_month_seq between 1183 and 1183 + 11
+ intersect
+ select distinct c_last_name, c_first_name, d_date
+ from web_sales, date_dim, customer
+ where web_sales.ws_sold_date_sk = date_dim.d_date_sk
+ and web_sales.ws_bill_customer_sk = customer.c_customer_sk
+ and d_month_seq between 1183 and 1183 + 11
+) hot_cust
+limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("__count_0"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query41.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query41.groovy
new file mode 100644
index 00000000000..46a8a7909ca
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query41.groovy
@@ -0,0 +1,80 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select distinct(i_product_name)
+ from item i1
+ where i_manufact_id between 748 and 748+40
+ and (select count(*) as item_cnt
+ from item
+ where (i_manufact = i1.i_manufact and
+ ((i_category = 'Women' and
+ (i_color = 'gainsboro' or i_color = 'aquamarine') and
+ (i_units = 'Ounce' or i_units = 'Dozen') and
+ (i_size = 'medium' or i_size = 'economy')
+ ) or
+ (i_category = 'Women' and
+ (i_color = 'chiffon' or i_color = 'violet') and
+ (i_units = 'Ton' or i_units = 'Pound') and
+ (i_size = 'extra large' or i_size = 'small')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'chartreuse' or i_color = 'blue') and
+ (i_units = 'Each' or i_units = 'Oz') and
+ (i_size = 'N/A' or i_size = 'large')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'tan' or i_color = 'dodger') and
+ (i_units = 'Bunch' or i_units = 'Tsp') and
+ (i_size = 'medium' or i_size = 'economy')
+ ))) or
+ (i_manufact = i1.i_manufact and
+ ((i_category = 'Women' and
+ (i_color = 'blanched' or i_color = 'tomato') and
+ (i_units = 'Tbl' or i_units = 'Case') and
+ (i_size = 'medium' or i_size = 'economy')
+ ) or
+ (i_category = 'Women' and
+ (i_color = 'almond' or i_color = 'lime') and
+ (i_units = 'Box' or i_units = 'Dram') and
+ (i_size = 'extra large' or i_size = 'small')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'peru' or i_color = 'saddle') and
+ (i_units = 'Pallet' or i_units = 'Gram') and
+ (i_size = 'N/A' or i_size = 'large')
+ ) or
+ (i_category = 'Men' and
+ (i_color = 'indian' or i_color = 'spring') and
+ (i_units = 'Unknown' or i_units = 'Carton') and
+ (i_size = 'medium' or i_size = 'economy')
+ )))) > 0
+ order by i_product_name
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("i_product_name"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query42.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query42.groovy
new file mode 100644
index 00000000000..045b8202a13
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query42.groovy
@@ -0,0 +1,53 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select dt.d_year
+ ,item.i_category_id
+ ,item.i_category
+ ,sum(ss_ext_sales_price)
+ from date_dim dt
+ ,store_sales
+ ,item
+ where dt.d_date_sk = store_sales.ss_sold_date_sk
+ and store_sales.ss_item_sk = item.i_item_sk
+ and item.i_manager_id = 1
+ and dt.d_moy=11
+ and dt.d_year=2002
+ group by dt.d_year
+ ,item.i_category_id
+ ,item.i_category
+ order by sum(ss_ext_sales_price) desc,dt.d_year
+ ,item.i_category_id
+ ,item.i_category
+limit 100 ;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("d_year"))
+ assertTrue(topPlan.contains("i_category_id"))
+ assertTrue(topPlan.contains("i_category"))
+ assertTrue(topPlan.contains("__sum_3"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query45.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query45.groovy
new file mode 100644
index 00000000000..d0203b66508
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query45.groovy
@@ -0,0 +1,50 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select ca_zip, ca_city, sum(ws_sales_price)
+ from web_sales, customer, customer_address, date_dim, item
+ where ws_bill_customer_sk = c_customer_sk
+ and c_current_addr_sk = ca_address_sk
+ and ws_item_sk = i_item_sk
+ and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475',
'85392', '85460', '80348', '81792')
+ or
+ i_item_id in (select i_item_id
+ from item
+ where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19,
23, 29)
+ )
+ )
+ and ws_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 2000
+ group by ca_zip, ca_city
+ order by ca_zip, ca_city
+ limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("ca_zip"))
+ assertTrue(topPlan.contains("ca_city"))
+ assertTrue(topPlan.contains("__sum_2"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query59.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query59.groovy
new file mode 100644
index 00000000000..7df0d53bc53
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query59.groovy
@@ -0,0 +1,81 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+with wss as
+ (select d_week_seq,
+ ss_store_sk,
+ sum(case when (d_day_name='Sunday') then ss_sales_price else null end)
sun_sales,
+ sum(case when (d_day_name='Monday') then ss_sales_price else null end)
mon_sales,
+ sum(case when (d_day_name='Tuesday') then ss_sales_price else null
end) tue_sales,
+ sum(case when (d_day_name='Wednesday') then ss_sales_price else null
end) wed_sales,
+ sum(case when (d_day_name='Thursday') then ss_sales_price else null
end) thu_sales,
+ sum(case when (d_day_name='Friday') then ss_sales_price else null end)
fri_sales,
+ sum(case when (d_day_name='Saturday') then ss_sales_price else null
end) sat_sales
+ from store_sales,date_dim
+ where d_date_sk = ss_sold_date_sk
+ group by d_week_seq,ss_store_sk
+ )
+ select s_store_name1,s_store_id1,d_week_seq1
+ ,sun_sales1/sun_sales2,mon_sales1/mon_sales2
+ ,tue_sales1/tue_sales2,wed_sales1/wed_sales2,thu_sales1/thu_sales2
+ ,fri_sales1/fri_sales2,sat_sales1/sat_sales2
+ from
+ (select s_store_name s_store_name1,wss.d_week_seq d_week_seq1
+ ,s_store_id s_store_id1,sun_sales sun_sales1
+ ,mon_sales mon_sales1,tue_sales tue_sales1
+ ,wed_sales wed_sales1,thu_sales thu_sales1
+ ,fri_sales fri_sales1,sat_sales sat_sales1
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196 and 1196 + 11) y,
+ (select s_store_name s_store_name2,wss.d_week_seq d_week_seq2
+ ,s_store_id s_store_id2,sun_sales sun_sales2
+ ,mon_sales mon_sales2,tue_sales tue_sales2
+ ,wed_sales wed_sales2,thu_sales thu_sales2
+ ,fri_sales fri_sales2,sat_sales sat_sales2
+ from wss,store,date_dim d
+ where d.d_week_seq = wss.d_week_seq and
+ ss_store_sk = s_store_sk and
+ d_month_seq between 1196+ 12 and 1196 + 23) x
+ where s_store_id1=s_store_id2
+ and d_week_seq1=d_week_seq2-52
+ order by s_store_name1,s_store_id1,d_week_seq1
+limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("s_store_name1"))
+ assertTrue(topPlan.contains("s_store_id1"))
+ assertTrue(topPlan.contains("d_week_seq1"))
+ assertTrue(topPlan.contains("__divide_3"))
+ assertTrue(topPlan.contains("__divide_4"))
+ assertTrue(topPlan.contains("__divide_5"))
+ assertTrue(topPlan.contains("__divide_6"))
+ assertTrue(topPlan.contains("__divide_7"))
+ assertTrue(topPlan.contains("__divide_8"))
+ assertTrue(topPlan.contains("__divide_9"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query61.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query61.groovy
new file mode 100644
index 00000000000..2aa572e5b23
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query61.groovy
@@ -0,0 +1,74 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select promotions,total,cast(promotions as decimal(15,4))/cast(total as
decimal(15,4))*100
+from
+ (select sum(ss_ext_sales_price) promotions
+ from store_sales
+ ,store
+ ,promotion
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_promo_sk = p_promo_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) promotional_sales,
+ (select sum(ss_ext_sales_price) total
+ from store_sales
+ ,store
+ ,date_dim
+ ,customer
+ ,customer_address
+ ,item
+ where ss_sold_date_sk = d_date_sk
+ and ss_store_sk = s_store_sk
+ and ss_customer_sk= c_customer_sk
+ and ca_address_sk = c_current_addr_sk
+ and ss_item_sk = i_item_sk
+ and ca_gmt_offset = -7
+ and i_category = 'Jewelry'
+ and s_gmt_offset = -7
+ and d_year = 1999
+ and d_moy = 11) all_sales
+order by promotions, total
+limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("promotions"))
+ assertTrue(topPlan.contains("total"))
+ assertTrue(topPlan.contains("__multiply_2"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query62.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query62.groovy
new file mode 100644
index 00000000000..47c2924afd7
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query62.groovy
@@ -0,0 +1,63 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk <= 30 ) then 1 else 0 end)
as "30 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 30) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 60) then 1 else 0 end )
as "31-60 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 60) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 90) then 1 else 0 end)
as "61-90 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 90) and
+ (ws_ship_date_sk - ws_sold_date_sk <= 120) then 1 else 0 end)
as "91-120 days"
+ ,sum(case when (ws_ship_date_sk - ws_sold_date_sk > 120) then 1 else 0 end)
as ">120 days"
+from
+ web_sales
+ ,warehouse
+ ,ship_mode
+ ,web_site
+ ,date_dim
+where
+ d_month_seq between 1194 and 1194 + 11
+and ws_ship_date_sk = d_date_sk
+and ws_warehouse_sk = w_warehouse_sk
+and ws_ship_mode_sk = sm_ship_mode_sk
+and ws_web_site_sk = web_site_sk
+group by
+ substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+order by substr(w_warehouse_name,1,20)
+ ,sm_type
+ ,web_name
+limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("__substring_0"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query8.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query8.groovy
new file mode 100644
index 00000000000..c73af1e1c49
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query8.groovy
@@ -0,0 +1,136 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select s_store_name
+ ,sum(ss_net_profit)
+ from store_sales
+ ,date_dim
+ ,store,
+ (select ca_zip
+ from (
+ SELECT substr(ca_zip,1,5) ca_zip
+ FROM customer_address
+ WHERE substr(ca_zip,1,5) IN (
+ '47602','16704','35863','28577','83910','36201',
+ '58412','48162','28055','41419','80332',
+ '38607','77817','24891','16226','18410',
+ '21231','59345','13918','51089','20317',
+ '17167','54585','67881','78366','47770',
+ '18360','51717','73108','14440','21800',
+ '89338','45859','65501','34948','25973',
+ '73219','25333','17291','10374','18829',
+ '60736','82620','41351','52094','19326',
+ '25214','54207','40936','21814','79077',
+ '25178','75742','77454','30621','89193',
+ '27369','41232','48567','83041','71948',
+ '37119','68341','14073','16891','62878',
+ '49130','19833','24286','27700','40979',
+ '50412','81504','94835','84844','71954',
+ '39503','57649','18434','24987','12350',
+ '86379','27413','44529','98569','16515',
+ '27287','24255','21094','16005','56436',
+ '91110','68293','56455','54558','10298',
+ '83647','32754','27052','51766','19444',
+ '13869','45645','94791','57631','20712',
+ '37788','41807','46507','21727','71836',
+ '81070','50632','88086','63991','20244',
+ '31655','51782','29818','63792','68605',
+ '94898','36430','57025','20601','82080',
+ '33869','22728','35834','29086','92645',
+ '98584','98072','11652','78093','57553',
+ '43830','71144','53565','18700','90209',
+ '71256','38353','54364','28571','96560',
+ '57839','56355','50679','45266','84680',
+ '34306','34972','48530','30106','15371',
+ '92380','84247','92292','68852','13338',
+ '34594','82602','70073','98069','85066',
+ '47289','11686','98862','26217','47529',
+ '63294','51793','35926','24227','14196',
+ '24594','32489','99060','49472','43432',
+ '49211','14312','88137','47369','56877',
+ '20534','81755','15794','12318','21060',
+ '73134','41255','63073','81003','73873',
+ '66057','51184','51195','45676','92696',
+ '70450','90669','98338','25264','38919',
+ '59226','58581','60298','17895','19489',
+ '52301','80846','95464','68770','51634',
+ '19988','18367','18421','11618','67975',
+ '25494','41352','95430','15734','62585',
+ '97173','33773','10425','75675','53535',
+ '17879','41967','12197','67998','79658',
+ '59130','72592','14851','43933','68101',
+ '50636','25717','71286','24660','58058',
+ '72991','95042','15543','33122','69280',
+ '11912','59386','27642','65177','17672',
+ '33467','64592','36335','54010','18767',
+ '63193','42361','49254','33113','33159',
+ '36479','59080','11855','81963','31016',
+ '49140','29392','41836','32958','53163',
+ '13844','73146','23952','65148','93498',
+ '14530','46131','58454','13376','13378',
+ '83986','12320','17193','59852','46081',
+ '98533','52389','13086','68843','31013',
+ '13261','60560','13443','45533','83583',
+ '11489','58218','19753','22911','25115',
+ '86709','27156','32669','13123','51933',
+ '39214','41331','66943','14155','69998',
+ '49101','70070','35076','14242','73021',
+ '59494','15782','29752','37914','74686',
+ '83086','34473','15751','81084','49230',
+ '91894','60624','17819','28810','63180',
+ '56224','39459','55233','75752','43639',
+ '55349','86057','62361','50788','31830',
+ '58062','18218','85761','60083','45484',
+ '21204','90229','70041','41162','35390',
+ '16364','39500','68908','26689','52868',
+ '81335','40146','11340','61527','61794',
+ '71997','30415','59004','29450','58117',
+ '69952','33562','83833','27385','61860',
+ '96435','48333','23065','32961','84919',
+ '61997','99132','22815','56600','68730',
+ '48017','95694','32919','88217','27116',
+ '28239','58032','18884','16791','21343',
+ '97462','18569','75660','15475')
+ intersect
+ select ca_zip
+ from (SELECT substr(ca_zip,1,5) ca_zip,count(*) cnt
+ FROM customer_address, customer
+ WHERE ca_address_sk = c_current_addr_sk and
+ c_preferred_cust_flag='Y'
+ group by ca_zip
+ having count(*) > 10)A1)A2) V1
+ where ss_store_sk = s_store_sk
+ and ss_sold_date_sk = d_date_sk
+ and d_qoy = 2 and d_year = 1998
+ and (substr(s_zip,1,2) = substr(V1.ca_zip,1,2))
+ group by s_store_name
+ order by s_store_name
+ limit 100;"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("s_store_name"))
+ assertTrue(topPlan.contains("__sum_1"))
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_p0/infer_expr_name/query85.groovy
b/regression-test/suites/nereids_p0/infer_expr_name/query85.groovy
new file mode 100644
index 00000000000..88970ce6b1a
--- /dev/null
+++ b/regression-test/suites/nereids_p0/infer_expr_name/query85.groovy
@@ -0,0 +1,115 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package infer_expr_name
+
+suite("nereids_test_create_blocked") {
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+ def queryResult = sql """
+explain analyzed plan
+select substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+ from web_sales, web_returns, web_page, customer_demographics cd1,
+ customer_demographics cd2, customer_address, date_dim, reason
+ where ws_web_page_sk = wp_web_page_sk
+ and ws_item_sk = wr_item_sk
+ and ws_order_number = wr_order_number
+ and ws_sold_date_sk = d_date_sk and d_year = 2000
+ and cd1.cd_demo_sk = wr_refunded_cdemo_sk
+ and cd2.cd_demo_sk = wr_returning_cdemo_sk
+ and ca_address_sk = wr_refunded_addr_sk
+ and r_reason_sk = wr_reason_sk
+ and
+ (
+ (
+ cd1.cd_marital_status = 'M'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = '4 yr Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 100.00 and 150.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'S'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Secondary'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 50.00 and 100.00
+ )
+ or
+ (
+ cd1.cd_marital_status = 'W'
+ and
+ cd1.cd_marital_status = cd2.cd_marital_status
+ and
+ cd1.cd_education_status = 'Advanced Degree'
+ and
+ cd1.cd_education_status = cd2.cd_education_status
+ and
+ ws_sales_price between 150.00 and 200.00
+ )
+ )
+ and
+ (
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('FL', 'TX', 'DE')
+ and ws_net_profit between 100 and 200
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('IN', 'ND', 'ID')
+ and ws_net_profit between 150 and 300
+ )
+ or
+ (
+ ca_country = 'United States'
+ and
+ ca_state in ('MT', 'IL', 'OH')
+ and ws_net_profit between 50 and 250
+ )
+ )
+group by r_reason_desc
+order by substr(r_reason_desc,1,20)
+ ,avg(ws_quantity)
+ ,avg(wr_refunded_cash)
+ ,avg(wr_fee)
+limit 100;
+"""
+
+ def topPlan = queryResult[0][0].toString()
+ assertTrue(topPlan.contains("LogicalResultSink"))
+ assertTrue(topPlan.contains("__substring_0"))
+ assertTrue(topPlan.contains("__avg_1"))
+ assertTrue(topPlan.contains("__avg_2"))
+ assertTrue(topPlan.contains("__avg_3"))
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
