This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 56500f7b0f8b3462f75a70a416a5c18edbaa90e7 Author: Pxl <[email protected]> AuthorDate: Mon Oct 16 20:10:52 2023 +0800 [Improvement](materialized-view) set job failed when toAgentTaskRequest meet error (#25358) set job failed when toAgentTaskRequest meet error --- .../java/org/apache/doris/alter/RollupJobV2.java | 2 +- .../org/apache/doris/alter/SchemaChangeJobV2.java | 4 +- .../doris/analysis/CreateMaterializedViewStmt.java | 45 +++- .../apache/doris/analysis/ExpressionFunctions.java | 2 +- .../mv/AbstractSelectMaterializedIndexRule.java | 8 - .../mv/SelectMaterializedIndexWithAggregate.java | 36 +-- .../java/org/apache/doris/task/AgentBatchTask.java | 7 +- .../multiple_ssb_between/multiple_ssb_between.out | 24 ++ .../multiple_ssb_between.groovy | 244 +++++++++++++++++++++ 9 files changed, 337 insertions(+), 35 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/alter/RollupJobV2.java b/fe/fe-core/src/main/java/org/apache/doris/alter/RollupJobV2.java index cf70af5b122..afcbe261bb4 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/alter/RollupJobV2.java +++ b/fe/fe-core/src/main/java/org/apache/doris/alter/RollupJobV2.java @@ -526,7 +526,7 @@ public class RollupJobV2 extends AlterJobV2 implements GsonPostProcessable { if (task.getFailedTimes() > 0) { task.setFinished(true); AgentTaskQueue.removeTask(task.getBackendId(), TTaskType.ALTER, task.getSignature()); - LOG.warn("rollup task failed after try three times: " + task.getErrorMsg()); + LOG.warn("rollup task failed: " + task.getErrorMsg()); if (!failedAgentTasks.containsKey(task.getTabletId())) { failedAgentTasks.put(task.getTabletId(), Lists.newArrayList(task)); } else { diff --git a/fe/fe-core/src/main/java/org/apache/doris/alter/SchemaChangeJobV2.java b/fe/fe-core/src/main/java/org/apache/doris/alter/SchemaChangeJobV2.java index 49c6610b354..574a1bf129f 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/alter/SchemaChangeJobV2.java +++ b/fe/fe-core/src/main/java/org/apache/doris/alter/SchemaChangeJobV2.java @@ -519,10 +519,10 @@ public class SchemaChangeJobV2 extends AlterJobV2 { LOG.info("schema change tasks not finished. job: {}", jobId); List<AgentTask> tasks = schemaChangeBatchTask.getUnfinishedTasks(2000); for (AgentTask task : tasks) { - if (task.getFailedTimes() >= 3) { + if (task.getFailedTimes() > 0) { task.setFinished(true); AgentTaskQueue.removeTask(task.getBackendId(), TTaskType.ALTER, task.getSignature()); - LOG.warn("schema change task failed after try three times: " + task.getErrorMsg()); + LOG.warn("schema change task failed: " + task.getErrorMsg()); if (!failedAgentTasks.containsKey(task.getTabletId())) { failedAgentTasks.put(task.getTabletId(), Lists.newArrayList(task)); } else { diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java index 195e67485ea..2f7a23ed674 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMaterializedViewStmt.java @@ -32,9 +32,11 @@ import org.apache.doris.common.DdlException; import org.apache.doris.common.FeConstants; import org.apache.doris.common.FeNameFormat; import org.apache.doris.common.UserException; +import org.apache.doris.rewrite.ExprRewriter; import org.apache.doris.rewrite.mvrewrite.CountFieldToSum; import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableSet; import com.google.common.collect.Lists; import com.google.common.collect.Maps; import org.apache.logging.log4j.LogManager; @@ -79,6 +81,9 @@ public class CreateMaterializedViewStmt extends DdlStmt { FN_NAME_TO_PATTERN.put(FunctionSet.HLL_UNION, new MVColumnHLLUnionPattern()); } + public static final ImmutableSet<String> invalidFn = ImmutableSet.of("now", "current_time", "current_date", + "utc_timestamp", "uuid", "random", "unix_timestamp", "curdate"); + private String mvName; private SelectStmt selectStmt; private Map<String, String> properties; @@ -158,14 +163,50 @@ public class CreateMaterializedViewStmt extends DdlStmt { return selectStmt.getWhereClause(); } + private void checkExprValidInMv(Expr expr, String functionName) throws AnalysisException { + if (!isReplay && expr.haveFunction(functionName)) { + throw new AnalysisException("The materialized view contain " + functionName + " is disallowed"); + } + } + + private void checkExprValidInMv(Expr expr) throws AnalysisException { + if (isReplay) { + return; + } + for (String function : invalidFn) { + checkExprValidInMv(expr, function); + } + } + + private void checkExprValidInMv() throws AnalysisException { + if (selectStmt.getWhereClause() != null) { + checkExprValidInMv(selectStmt.getWhereClause()); + } + SelectList selectList = selectStmt.getSelectList(); + for (SelectListItem selectListItem : selectList.getItems()) { + checkExprValidInMv(selectListItem.getExpr()); + } + } + @Override public void analyze(Analyzer analyzer) throws UserException { super.analyze(analyzer); + + checkExprValidInMv(); + FeNameFormat.checkTableName(mvName); rewriteToBitmapWithCheck(); // TODO(ml): The mv name in from clause should pass the analyze without error. selectStmt.forbiddenMVRewrite(); selectStmt.analyze(analyzer); + + ExprRewriter rewriter = analyzer.getExprRewriter(); + rewriter.reset(); + selectStmt.rewriteExprs(rewriter); + selectStmt.reset(); + analyzer = new Analyzer(analyzer.getEnv(), analyzer.getContext()); + selectStmt.analyze(analyzer); + if (selectStmt.getAggInfo() != null) { mvKeysType = KeysType.AGG_KEYS; } @@ -227,10 +268,6 @@ public class CreateMaterializedViewStmt extends DdlStmt { + selectListItemExpr.toSql()); } - if (!isReplay && selectListItemExpr.haveFunction("curdate")) { - throw new AnalysisException( - "The materialized view contain curdate is disallowed"); - } if (selectListItemExpr instanceof FunctionCallExpr && ((FunctionCallExpr) selectListItemExpr).isAggregateFunction()) { diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/ExpressionFunctions.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/ExpressionFunctions.java index f7ace7ce4e8..a45954a9b93 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/ExpressionFunctions.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/ExpressionFunctions.java @@ -51,7 +51,7 @@ public enum ExpressionFunctions { private static final Logger LOG = LogManager.getLogger(ExpressionFunctions.class); private ImmutableMultimap<String, FEFunctionInvoker> functions; - private static final Set<String> unfixedFn = ImmutableSet.of( + public static final Set<String> unfixedFn = ImmutableSet.of( "now", "current_time", "current_date", diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java index 45453f760df..52ba749cf60 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/AbstractSelectMaterializedIndexRule.java @@ -570,14 +570,6 @@ public abstract class AbstractSelectMaterializedIndexRule { return visit(aggregateFunction, context); } - @Override - public Expression visitAlias(Alias alias, Void context) { - if (mvNameToMvSlot.containsKey(alias.toSlot().toSql())) { - return mvNameToMvSlot.get(alias.toSlot().toSql()); - } - return visit(alias, context); - } - @Override public Expression visitScalarFunction(ScalarFunction scalarFunction, Void context) { List<Expression> newChildrenWithoutCast = scalarFunction.children().stream() diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java index 1ccae2e0e33..b4286472dfd 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/mv/SelectMaterializedIndexWithAggregate.java @@ -124,7 +124,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial .replace(agg.withChildren(mvPlan), mvPlan); } else { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -168,12 +168,12 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren(filter.withChildren(mvPlan)), mvPlan)); } else { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -226,7 +226,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial generateNewOutputsWithMvOutputs(mvPlan, newProjectList), scan.withMaterializedIndexSelected(result.preAggStatus, result.indexId)); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -271,7 +271,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( project.withProjectsAndChild( @@ -285,7 +285,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial filter.withChildren(mvPlan)); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -328,7 +328,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( filter.withChildren( @@ -342,7 +342,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial generateNewOutputsWithMvOutputs(mvPlan, newProjectList), mvPlan); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -374,7 +374,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( repeat.withAggOutputAndChild( @@ -382,7 +382,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial ), mvPlan)); } else { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -428,7 +428,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( repeat.withAggOutputAndChild( @@ -437,7 +437,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial )), mvPlan)); } else { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -480,7 +480,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( repeat.withAggOutputAndChild( @@ -496,7 +496,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial generateNewOutputsWithMvOutputs(mvPlan, newProjectList), mvPlan); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -545,7 +545,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( repeat.withAggOutputAndChild( @@ -564,7 +564,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial filter.withChildren(mvPlan)); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), @@ -611,7 +611,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial if (result.exprRewriteMap.isEmpty()) { return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( agg.withChildren( repeat.withAggOutputAndChild( @@ -630,7 +630,7 @@ public class SelectMaterializedIndexWithAggregate extends AbstractSelectMaterial scan.withMaterializedIndexSelected(result.preAggStatus, result.indexId)); return new LogicalProject<>( - generateProjectsAlias(agg.getOutput(), slotContext), + generateProjectsAlias(agg.getOutputs(), slotContext), new ReplaceExpressions(slotContext).replace( new LogicalAggregate<>( agg.getGroupByExpressions(), diff --git a/fe/fe-core/src/main/java/org/apache/doris/task/AgentBatchTask.java b/fe/fe-core/src/main/java/org/apache/doris/task/AgentBatchTask.java index 3c12160610c..aa654fcd21a 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/task/AgentBatchTask.java +++ b/fe/fe-core/src/main/java/org/apache/doris/task/AgentBatchTask.java @@ -165,7 +165,12 @@ public class AgentBatchTask implements Runnable { client = ClientPool.backendPool.borrowObject(address); List<TAgentTaskRequest> agentTaskRequests = new LinkedList<TAgentTaskRequest>(); for (AgentTask task : tasks) { - agentTaskRequests.add(toAgentTaskRequest(task)); + try { + agentTaskRequests.add(toAgentTaskRequest(task)); + } catch (Exception e) { + task.failed(); + throw e; + } } client.submitTasks(agentTaskRequests); if (LOG.isDebugEnabled()) { diff --git a/regression-test/data/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.out b/regression-test/data/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.out new file mode 100644 index 00000000000..00e05ab400e --- /dev/null +++ b/regression-test/data/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.out @@ -0,0 +1,24 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select_star -- +1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#1 category brand color type 4 container +2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19920101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA ASIA MFGR#12 MFGR#12 brand color type 4 container +19930101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#1 category brand color type 4 container +19930101 1 1 1 1 1 1 1 1 1 1 100 1 1 1 2023-06-09 shipmode name address city nation AMERICA phone mktsegment name address city nation AMERICA phone name MFGR#12 MFGR#12 brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container +19930101 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2023-06-09 shipmode name address city nation region phone mktsegment name address city nation region phone name mfgr category brand color type 4 container + +-- !select_q_1_1 -- +12 + +-- !select_q_2_1 -- +1 1993 brand + +-- !select_q_3_1 -- +ASIA ASIA 1992 1 + +-- !select_q_4_1 -- +0 nation 0 +1993 nation 0 + diff --git a/regression-test/suites/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.groovy b/regression-test/suites/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.groovy new file mode 100644 index 00000000000..3e787d9aee0 --- /dev/null +++ b/regression-test/suites/mv_p0/ssb/multiple_ssb_between/multiple_ssb_between.groovy @@ -0,0 +1,244 @@ +// 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. + +import org.codehaus.groovy.runtime.IOGroovyMethods + +suite ("multiple_ssb_between") { + sql """ DROP TABLE IF EXISTS lineorder_flat; """ + sql """set enable_nereids_planner=true""" + sql """SET enable_fallback_to_original_planner=false""" + + sql """ + CREATE TABLE IF NOT EXISTS `lineorder_flat` ( + `LO_ORDERDATE` int(11) NOT NULL COMMENT "", + `LO_ORDERKEY` int(11) NOT NULL COMMENT "", + `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "", + `LO_CUSTKEY` int(11) NOT NULL COMMENT "", + `LO_PARTKEY` int(11) NOT NULL COMMENT "", + `LO_SUPPKEY` int(11) NOT NULL COMMENT "", + `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "", + `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "", + `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "", + `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "", + `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "", + `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "", + `LO_REVENUE` int(11) NOT NULL COMMENT "", + `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "", + `LO_TAX` tinyint(4) NOT NULL COMMENT "", + `LO_COMMITDATE` date NOT NULL COMMENT "", + `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "", + `C_NAME` varchar(100) NOT NULL COMMENT "", + `C_ADDRESS` varchar(100) NOT NULL COMMENT "", + `C_CITY` varchar(100) NOT NULL COMMENT "", + `C_NATION` varchar(100) NOT NULL COMMENT "", + `C_REGION` varchar(100) NOT NULL COMMENT "", + `C_PHONE` varchar(100) NOT NULL COMMENT "", + `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "", + `S_NAME` varchar(100) NOT NULL COMMENT "", + `S_ADDRESS` varchar(100) NOT NULL COMMENT "", + `S_CITY` varchar(100) NOT NULL COMMENT "", + `S_NATION` varchar(100) NOT NULL COMMENT "", + `S_REGION` varchar(100) NOT NULL COMMENT "", + `S_PHONE` varchar(100) NOT NULL COMMENT "", + `P_NAME` varchar(100) NOT NULL COMMENT "", + `P_MFGR` varchar(100) NOT NULL COMMENT "", + `P_CATEGORY` varchar(100) NOT NULL COMMENT "", + `P_BRAND` varchar(100) NOT NULL COMMENT "", + `P_COLOR` varchar(100) NOT NULL COMMENT "", + `P_TYPE` varchar(100) NOT NULL COMMENT "", + `P_SIZE` tinyint(4) NOT NULL COMMENT "", + `P_CONTAINER` varchar(100) NOT NULL COMMENT "" + ) ENGINE=OLAP + DUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`) + COMMENT "OLAP" + PARTITION BY RANGE(`LO_ORDERDATE`) + (PARTITION p1992 VALUES [("-2147483648"), ("19930101")), + PARTITION p1993 VALUES [("19930101"), ("19940101")), + PARTITION p1994 VALUES [("19940101"), ("19950101")), + PARTITION p1995 VALUES [("19950101"), ("19960101")), + PARTITION p1996 VALUES [("19960101"), ("19970101")), + PARTITION p1997 VALUES [("19970101"), ("19980101")), + PARTITION p1998 VALUES [("19980101"), ("19990101"))) + DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48 + PROPERTIES ( + "replication_num" = "1", + "colocate_with" = "groupxx1", + "in_memory" = "false", + "storage_format" = "DEFAULT" + ); + """ + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 [...] + + test { + sql """create materialized view lineorder_q_1_1 as + SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) + FROM lineorder_flat GROUP BY + LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;""" + exception "not in select list" + } + + createMV ("""create materialized view lineorder_q_1_1 as + SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue + FROM lineorder_flat + WHERE + LO_ORDERDATE >= 19930101 + AND LO_ORDERDATE <= 19931231 + AND LO_DISCOUNT BETWEEN 1 AND 3 + AND LO_QUANTITY < 25 + GROUP BY + LO_ORDERKEY;""") + + createMV ("""create materialized view lineorder_q_2_1 as + SELECT + (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND, + SUM(LO_REVENUE) + FROM lineorder_flat + WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' + GROUP BY YEAR, P_BRAND + ORDER BY YEAR, P_BRAND;""") + + createMV ("""create materialized view lineorder_q_3_1 as + SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + WHERE + C_REGION = 'ASIA' + AND S_REGION = 'ASIA' + AND LO_ORDERDATE >= 19920101 + AND LO_ORDERDATE <= 19971231 + GROUP BY C_NATION, S_NATION, YEAR;""") + + createMV ("""create materialized view lineorder_q_4_1 as + SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + WHERE + C_REGION = 'AMERICA' + AND S_REGION = 'AMERICA' + AND P_MFGR IN ('MFGR#1', 'MFGR#2') + GROUP BY YEAR, C_NATION + ORDER BY YEAR ASC, C_NATION ASC;""") + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19920101 [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 , 2 , 2 , 2 , 2 , [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (2 , 2 , 2 , 2 , 2 , 2 ,'2', [...] + + sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY, LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY, LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT, LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME, C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME, S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY, P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (1 , 1 , 1 [...] + + qt_select_star "select * from lineorder_flat order by 1,2;" + + explain { + sql("""SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue + FROM lineorder_flat + WHERE + LO_ORDERDATE >= 19930101 + AND LO_ORDERDATE <= 19931231 + AND LO_DISCOUNT BETWEEN 1 AND 3 + AND LO_QUANTITY < 25;""") + contains "(lineorder_q_1_1)" + } + qt_select_q_1_1 """SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue + FROM lineorder_flat + WHERE + LO_ORDERDATE >= 19930101 + AND LO_ORDERDATE <= 19931231 + AND LO_DISCOUNT BETWEEN 1 AND 3 + AND LO_QUANTITY < 25;""" + + explain { + sql("""SELECT + SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND + FROM lineorder_flat + WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' + GROUP BY (LO_ORDERDATE DIV 10000), P_BRAND + ORDER BY YEAR, P_BRAND;""") + contains "(lineorder_q_2_1)" + } + qt_select_q_2_1 """SELECT + SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, + P_BRAND + FROM lineorder_flat + WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA' + GROUP BY YEAR, P_BRAND + ORDER BY YEAR, P_BRAND;""" + + explain { + sql("""SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + WHERE + C_REGION = 'ASIA' + AND S_REGION = 'ASIA' + AND LO_ORDERDATE >= 19920101 + AND LO_ORDERDATE <= 19971231 + GROUP BY C_NATION, S_NATION, YEAR + ORDER BY YEAR ASC, revenue DESC;""") + contains "(lineorder_q_3_1)" + } + qt_select_q_3_1 """SELECT + C_NATION, + S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, + SUM(LO_REVENUE) AS revenue + FROM lineorder_flat + WHERE + C_REGION = 'ASIA' + AND S_REGION = 'ASIA' + AND LO_ORDERDATE >= 19920101 + AND LO_ORDERDATE <= 19971231 + GROUP BY C_NATION, S_NATION, YEAR + ORDER BY YEAR ASC, revenue DESC;""" + + explain { + sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + WHERE + C_REGION = 'AMERICA' + AND S_REGION = 'AMERICA' + AND P_MFGR IN ('MFGR#1', 'MFGR#2') + GROUP BY YEAR, C_NATION + ORDER BY YEAR ASC, C_NATION ASC;""") + contains "(lineorder_q_4_1)" + } + qt_select_q_4_1 """SELECT (LO_ORDERDATE DIV 10000) AS YEAR, + C_NATION, + SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit + FROM lineorder_flat + WHERE + C_REGION = 'AMERICA' + AND S_REGION = 'AMERICA' + AND P_MFGR IN ('MFGR#1', 'MFGR#2') + GROUP BY YEAR, C_NATION + ORDER BY YEAR ASC, C_NATION ASC;""" +} --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
