You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by pa...@apache.org on 2023/06/19 02:51:57 UTC
[doris] branch master updated: [Chore](materialized-view) add ssb_flat mv test case (#20869)
This is an automated email from the ASF dual-hosted git repository.
panxiaolei 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 85c5d7c6a9 [Chore](materialized-view) add ssb_flat mv test case (#20869)
85c5d7c6a9 is described below
commit 85c5d7c6a90d36185079cd06cf32157a40eeaa85
Author: Pxl <px...@qq.com>
AuthorDate: Mon Jun 19 10:51:50 2023 +0800
[Chore](materialized-view) add ssb_flat mv test case (#20869)
add ssb_flat mv test case
---
be/src/vec/exprs/vcompound_pred.h | 16 +---
be/src/vec/exprs/vectorized_fn_call.cpp | 16 ++--
be/src/vec/exprs/vectorized_fn_call.h | 2 +-
be/src/vec/exprs/vexpr.cpp | 36 ++++-----
be/src/vec/exprs/vexpr.h | 11 +++
.../java/org/apache/doris/alter/RollupJobV2.java | 5 +-
regression-test/data/mv_p0/ssb/q_1_1/q_1_1.out | 11 +++
regression-test/data/mv_p0/ssb/q_2_1/q_2_1.out | 11 +++
regression-test/data/mv_p0/ssb/q_3_1/q_3_1.out | 11 +++
.../ssb/{q_4_1/q_4_1.groovy => q_1_1/q_1_1.groovy} | 67 +++++++---------
.../ssb/{q_4_1/q_4_1.groovy => q_2_1/q_2_1.groovy} | 74 ++++++++----------
.../ssb/{q_4_1/q_4_1.groovy => q_3_1/q_3_1.groovy} | 90 ++++++++++++----------
.../suites/mv_p0/ssb/q_4_1/q_4_1.groovy | 1 -
13 files changed, 178 insertions(+), 173 deletions(-)
diff --git a/be/src/vec/exprs/vcompound_pred.h b/be/src/vec/exprs/vcompound_pred.h
index 3d633d6776..30d040aa02 100644
--- a/be/src/vec/exprs/vcompound_pred.h
+++ b/be/src/vec/exprs/vcompound_pred.h
@@ -45,7 +45,8 @@ public:
VCompoundPred(const TExprNode& node) : VectorizedFnCall(node) {
_op = node.opcode;
_fn.name.function_name = compound_operator_to_string(_op);
- _expr_name = "VCompoundPredicate (" + _fn.name.function_name + ")";
+ _expr_name = fmt::format("VCompoundPredicate[{}](arguments={},return={})",
+ _fn.name.function_name, get_child_names(), _data_type->get_name());
}
VExprSPtr clone() const override { return VCompoundPred::create_shared(*this); }
@@ -144,17 +145,6 @@ public:
return Status::OK();
}
- std::string debug_string() const override {
- std::stringstream out;
- out << _expr_name << "{\n";
- out << _children[0]->debug_string();
- if (children().size() > 1) {
- out << ",\n" << _children[1]->debug_string();
- }
- out << "}";
- return out.str();
- }
-
bool is_compound_predicate() const override { return true; }
private:
@@ -195,7 +185,5 @@ private:
}
TExprOpcode::type _op;
-
- std::string _expr_name;
};
} // namespace doris::vectorized
diff --git a/be/src/vec/exprs/vectorized_fn_call.cpp b/be/src/vec/exprs/vectorized_fn_call.cpp
index 3334bf0e81..7055125ba4 100644
--- a/be/src/vec/exprs/vectorized_fn_call.cpp
+++ b/be/src/vec/exprs/vectorized_fn_call.cpp
@@ -56,17 +56,18 @@ namespace doris::vectorized {
const std::string AGG_STATE_SUFFIX = "_state";
-VectorizedFnCall::VectorizedFnCall(const TExprNode& node) : VExpr(node) {}
+VectorizedFnCall::VectorizedFnCall(const TExprNode& node) : VExpr(node) {
+ _expr_name = fmt::format("VectorizedFnCall[{}](arguments={},return={})", _fn.name.function_name,
+ get_child_names(), _data_type->get_name());
+}
Status VectorizedFnCall::prepare(RuntimeState* state, const RowDescriptor& desc,
VExprContext* context) {
RETURN_IF_ERROR_OR_PREPARED(VExpr::prepare(state, desc, context));
ColumnsWithTypeAndName argument_template;
argument_template.reserve(_children.size());
- std::vector<std::string_view> child_expr_name;
for (auto child : _children) {
argument_template.emplace_back(nullptr, child->data_type(), child->expr_name());
- child_expr_name.emplace_back(child->expr_name());
}
if (_fn.binary_type == TFunctionBinaryType::RPC) {
@@ -106,17 +107,12 @@ Status VectorizedFnCall::prepare(RuntimeState* state, const RowDescriptor& desc,
_fn.name.function_name, argument_template, _data_type, state->be_exec_version());
}
if (_function == nullptr) {
- std::string type_str;
- for (auto arg : argument_template) {
- type_str = type_str + " " + arg.type->get_name();
- }
return Status::InternalError(
- "Function {} is not implemented, input param type is {}, "
+ "Function {} get failed, expr is {} "
"and return type is {}.",
- _fn.name.function_name, type_str, _data_type->get_name());
+ _fn.name.function_name, _expr_name, _data_type->get_name());
}
VExpr::register_function_context(state, context);
- _expr_name = fmt::format("{}({})", _fn.name.function_name, child_expr_name);
_function_name = _fn.name.function_name;
_can_fast_execute = _function->can_fast_execute();
diff --git a/be/src/vec/exprs/vectorized_fn_call.h b/be/src/vec/exprs/vectorized_fn_call.h
index 034fbb19f9..846fdc8d96 100644
--- a/be/src/vec/exprs/vectorized_fn_call.h
+++ b/be/src/vec/exprs/vectorized_fn_call.h
@@ -64,7 +64,7 @@ public:
bool fast_execute(FunctionContext* context, Block& block, const ColumnNumbers& arguments,
size_t result, size_t input_rows_count);
-private:
+protected:
FunctionBasePtr _function;
bool _can_fast_execute = false;
std::string _expr_name;
diff --git a/be/src/vec/exprs/vexpr.cpp b/be/src/vec/exprs/vexpr.cpp
index 919c2f305e..4a7b198a5b 100644
--- a/be/src/vec/exprs/vexpr.cpp
+++ b/be/src/vec/exprs/vexpr.cpp
@@ -58,10 +58,6 @@ class RuntimeState;
} // namespace doris
namespace doris::vectorized {
-using doris::Status;
-using doris::RuntimeState;
-using doris::RowDescriptor;
-using doris::TypeDescriptor;
VExpr::VExpr(const TExprNode& node)
: _node_type(node.node_type),
@@ -127,7 +123,7 @@ void VExpr::close(VExprContext* context, FunctionContext::FunctionStateScope sco
}
}
-Status VExpr::create_expr(const doris::TExprNode& expr_node, VExprSPtr& expr) {
+Status VExpr::create_expr(const TExprNode& expr_node, VExprSPtr& expr) {
try {
switch (expr_node.node_type) {
case TExprNodeType::BOOL_LITERAL:
@@ -154,30 +150,30 @@ Status VExpr::create_expr(const doris::TExprNode& expr_node, VExprSPtr& expr) {
expr = VStructLiteral::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::SLOT_REF: {
+ case TExprNodeType::SLOT_REF: {
expr = VSlotRef::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::COLUMN_REF: {
+ case TExprNodeType::COLUMN_REF: {
expr = VColumnRef::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::COMPOUND_PRED: {
+ case TExprNodeType::COMPOUND_PRED: {
expr = VCompoundPred::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::LAMBDA_FUNCTION_EXPR: {
+ case TExprNodeType::LAMBDA_FUNCTION_EXPR: {
expr = VLambdaFunctionExpr::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::LAMBDA_FUNCTION_CALL_EXPR: {
+ case TExprNodeType::LAMBDA_FUNCTION_CALL_EXPR: {
expr = VLambdaFunctionCallExpr::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::ARITHMETIC_EXPR:
- case doris::TExprNodeType::BINARY_PRED:
- case doris::TExprNodeType::FUNCTION_CALL:
- case doris::TExprNodeType::COMPUTE_FUNCTION_CALL: {
+ case TExprNodeType::ARITHMETIC_EXPR:
+ case TExprNodeType::BINARY_PRED:
+ case TExprNodeType::FUNCTION_CALL:
+ case TExprNodeType::COMPUTE_FUNCTION_CALL: {
expr = VectorizedFnCall::create_shared(expr_node);
break;
}
@@ -185,15 +181,15 @@ Status VExpr::create_expr(const doris::TExprNode& expr_node, VExprSPtr& expr) {
expr = VMatchPredicate::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::CAST_EXPR: {
+ case TExprNodeType::CAST_EXPR: {
expr = VCastExpr::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::IN_PRED: {
+ case TExprNodeType::IN_PRED: {
expr = VInPredicate::create_shared(expr_node);
break;
}
- case doris::TExprNodeType::CASE_EXPR: {
+ case TExprNodeType::CASE_EXPR: {
if (!expr_node.__isset.case_expr) {
return Status::InternalError("Case expression not set in thrift node");
}
@@ -224,7 +220,7 @@ Status VExpr::create_expr(const doris::TExprNode& expr_node, VExprSPtr& expr) {
return Status::OK();
}
-Status VExpr::create_tree_from_thrift(const std::vector<doris::TExprNode>& nodes, int* node_idx,
+Status VExpr::create_tree_from_thrift(const std::vector<TExprNode>& nodes, int* node_idx,
VExprSPtr& root_expr, VExprContextSPtr& ctx) {
// propagate error case
if (*node_idx >= nodes.size()) {
@@ -269,7 +265,7 @@ Status VExpr::create_tree_from_thrift(const std::vector<doris::TExprNode>& nodes
return Status::OK();
}
-Status VExpr::create_expr_tree(const doris::TExpr& texpr, VExprContextSPtr& ctx) {
+Status VExpr::create_expr_tree(const TExpr& texpr, VExprContextSPtr& ctx) {
if (texpr.nodes.size() == 0) {
ctx = nullptr;
return Status::OK();
@@ -290,7 +286,7 @@ Status VExpr::create_expr_tree(const doris::TExpr& texpr, VExprContextSPtr& ctx)
return status;
}
-Status VExpr::create_expr_trees(const std::vector<doris::TExpr>& texprs, VExprContextSPtrs& ctxs) {
+Status VExpr::create_expr_trees(const std::vector<TExpr>& texprs, VExprContextSPtrs& ctxs) {
ctxs.clear();
for (int i = 0; i < texprs.size(); ++i) {
VExprContextSPtr ctx;
diff --git a/be/src/vec/exprs/vexpr.h b/be/src/vec/exprs/vexpr.h
index ca1e241768..6b632eeee5 100644
--- a/be/src/vec/exprs/vexpr.h
+++ b/be/src/vec/exprs/vexpr.h
@@ -207,6 +207,17 @@ protected:
return out.str();
}
+ std::string get_child_names() {
+ std::string res;
+ for (auto child : _children) {
+ if (!res.empty()) {
+ res += ", ";
+ }
+ res += child->expr_name();
+ }
+ return res;
+ }
+
Status check_constant(const Block& block, ColumnNumbers arguments) const;
/// Helper function that calls ctx->register(), sets fn_context_index_, and returns the
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 49decbfe1b..cfb30f3492 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,9 +526,8 @@ public class RollupJobV2 extends AlterJobV2 implements GsonPostProcessable {
.getReplicaAllocation(task.getPartitionId()).getTotalReplicaNum();
int failedTaskCount = failedAgentTasks.get(task.getTabletId()).size();
if (expectSucceedTaskNum - failedTaskCount < expectSucceedTaskNum / 2 + 1) {
- throw new AlterCancelException(
- "rollup tasks failed on same tablet reach threshold "
- + failedAgentTasks.get(task.getTabletId()));
+ throw new AlterCancelException("rollup tasks failed on same tablet reach threshold "
+ + failedAgentTasks.get(task.getTabletId()) + ", reason=" + task.getErrorMsg());
}
}
}
diff --git a/regression-test/data/mv_p0/ssb/q_1_1/q_1_1.out b/regression-test/data/mv_p0/ssb/q_1_1/q_1_1.out
new file mode 100644
index 0000000000..1f27d180c7
--- /dev/null
+++ b/regression-test/data/mv_p0/ssb/q_1_1/q_1_1.out
@@ -0,0 +1,11 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select_star --
+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 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_mv --
+4
+
+-- !select --
+4
+
diff --git a/regression-test/data/mv_p0/ssb/q_2_1/q_2_1.out b/regression-test/data/mv_p0/ssb/q_2_1/q_2_1.out
new file mode 100644
index 0000000000..c504591733
--- /dev/null
+++ b/regression-test/data/mv_p0/ssb/q_2_1/q_2_1.out
@@ -0,0 +1,11 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select_star --
+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
+
+-- !select_mv --
+1 1993 brand
+
+-- !select --
+1 1993 brand
+
diff --git a/regression-test/data/mv_p0/ssb/q_3_1/q_3_1.out b/regression-test/data/mv_p0/ssb/q_3_1/q_3_1.out
new file mode 100644
index 0000000000..9fa23643ba
--- /dev/null
+++ b/regression-test/data/mv_p0/ssb/q_3_1/q_3_1.out
@@ -0,0 +1,11 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select_star --
+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 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_mv --
+ASIA ASIA 1992 1
+
+-- !select --
+ASIA ASIA 1992 1
+
diff --git a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy b/regression-test/suites/mv_p0/ssb/q_1_1/q_1_1.groovy
similarity index 69%
copy from regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
copy to regression-test/suites/mv_p0/ssb/q_1_1/q_1_1.groovy
index b69a76ffbe..0e7b042d68 100644
--- a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
+++ b/regression-test/suites/mv_p0/ssb/q_1_1/q_1_1.groovy
@@ -17,9 +17,8 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
-suite ("mv_ssb_q_4_1") {
+suite ("mv_ssb_q_1_1") {
- // because nereids cannot support rollup correctly forbid it temporary
sql """set enable_nereids_planner=false"""
sql """ DROP TABLE IF EXISTS lineorder_flat; """
@@ -84,58 +83,48 @@ suite ("mv_ssb_q_4_1") {
);
"""
- 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 [...]
+ 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 [...]
createMV ("""create materialized view lineorder_mv as
- SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
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;""")
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
+ AND LO_QUANTITY < 25
+ GROUP BY
+ LO_ORDERKEY;""")
- 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 (19930101 , 2 , 2 , 2 , 2 , [...]
- qt_select_star "select * from lineorder_flat order by 1;"
+ qt_select_star "select * from lineorder_flat order by 1,2;"
explain {
- sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ sql("""SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
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;""")
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
+ AND LO_QUANTITY < 25;""")
contains "(lineorder_mv)"
}
- qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ qt_select_mv """SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
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;"""
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
+ AND LO_QUANTITY < 25;"""
sql""" drop materialized view lineorder_mv on lineorder_flat; """
- qt_select """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;"""
+ qt_select """SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
+ FROM lineorder_flat
+ WHERE
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
+ AND LO_QUANTITY < 25;"""
}
diff --git a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy b/regression-test/suites/mv_p0/ssb/q_2_1/q_2_1.groovy
similarity index 68%
copy from regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
copy to regression-test/suites/mv_p0/ssb/q_2_1/q_2_1.groovy
index b69a76ffbe..02b61ceab5 100644
--- a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
+++ b/regression-test/suites/mv_p0/ssb/q_2_1/q_2_1.groovy
@@ -17,9 +17,8 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
-suite ("mv_ssb_q_4_1") {
+suite ("mv_ssb_q_2_1") {
- // because nereids cannot support rollup correctly forbid it temporary
sql """set enable_nereids_planner=false"""
sql """ DROP TABLE IF EXISTS lineorder_flat; """
@@ -84,58 +83,47 @@ suite ("mv_ssb_q_4_1") {
);
"""
- 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 [...]
+ 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 [...]
createMV ("""create materialized view lineorder_mv as
- SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ SELECT
+ (LO_ORDERDATE DIV 10000) AS YEAR,
+ P_BRAND,
+ SUM(LO_REVENUE)
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;""")
+ WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
+ GROUP BY YEAR, P_BRAND
+ ORDER BY YEAR, P_BRAND;""")
- 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 (19930101 , 2 , 2 , 2 , 2 , [...]
- qt_select_star "select * from lineorder_flat order by 1;"
+ qt_select_star "select * from lineorder_flat order by 1,2;"
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;""")
+ 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 YEAR, P_BRAND
+ ORDER BY YEAR, P_BRAND;""")
contains "(lineorder_mv)"
}
- qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ qt_select_mv """SELECT
+ SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
+ P_BRAND
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;"""
+ WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
+ GROUP BY YEAR, P_BRAND
+ ORDER BY YEAR, P_BRAND;"""
sql""" drop materialized view lineorder_mv on lineorder_flat; """
- qt_select """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;"""
+ qt_select """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;"""
}
diff --git a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy b/regression-test/suites/mv_p0/ssb/q_3_1/q_3_1.groovy
similarity index 67%
copy from regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
copy to regression-test/suites/mv_p0/ssb/q_3_1/q_3_1.groovy
index b69a76ffbe..b8c6ed70c0 100644
--- a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
+++ b/regression-test/suites/mv_p0/ssb/q_3_1/q_3_1.groovy
@@ -17,9 +17,8 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
-suite ("mv_ssb_q_4_1") {
+suite ("mv_ssb_q_3_1") {
- // because nereids cannot support rollup correctly forbid it temporary
sql """set enable_nereids_planner=false"""
sql """ DROP TABLE IF EXISTS lineorder_flat; """
@@ -84,58 +83,65 @@ suite ("mv_ssb_q_4_1") {
);
"""
- 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 [...]
+ 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 [...]
createMV ("""create materialized view lineorder_mv as
- SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
+ SELECT
+ C_NATION,
+ S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
+ SUM(LO_REVENUE) AS revenue
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;""")
+ C_REGION = 'ASIA'
+ AND S_REGION = 'ASIA'
+ AND LO_ORDERDATE >= 19920101
+ AND LO_ORDERDATE <= 19971231
+ GROUP BY C_NATION, S_NATION, YEAR;""")
- 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 (19930101 , 2 , 2 , 2 , 2 , [...]
- qt_select_star "select * from lineorder_flat order by 1;"
+ qt_select_star "select * from lineorder_flat order by 1,2;"
explain {
- sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
+ sql("""SELECT
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;""")
+ 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_mv)"
}
- qt_select_mv """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;"""
+ qt_select_mv """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;"""
sql""" drop materialized view lineorder_mv on lineorder_flat; """
- qt_select """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;"""
+ qt_select """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;"""
}
diff --git a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy b/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
index b69a76ffbe..5869e05754 100644
--- a/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
+++ b/regression-test/suites/mv_p0/ssb/q_4_1/q_4_1.groovy
@@ -19,7 +19,6 @@ import org.codehaus.groovy.runtime.IOGroovyMethods
suite ("mv_ssb_q_4_1") {
- // because nereids cannot support rollup correctly forbid it temporary
sql """set enable_nereids_planner=false"""
sql """ DROP TABLE IF EXISTS lineorder_flat; """
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org