This is an automated email from the ASF dual-hosted git repository.
adonisling 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 55bf38dbab [feature-wip](MTMV) Use SSB ddl to test (#18150)
55bf38dbab is described below
commit 55bf38dbab46fae5a910ca83e5749872d890c01a
Author: huangzhaowei <[email protected]>
AuthorDate: Thu Mar 30 00:11:38 2023 +0800
[feature-wip](MTMV) Use SSB ddl to test (#18150)
Add regression tests for MTMV.
---
.../CreateMultiTableMaterializedViewStmt.java | 70 +++++++++++-----------
.../java/org/apache/doris/analysis/QueryStmt.java | 4 ++
.../main/java/org/apache/doris/catalog/Column.java | 6 +-
.../suites/mtmv_p0/ssb/ddl/customer_create.sql | 15 +++++
.../suites/mtmv_p0/ssb/ddl/customer_delete.sql | 1 +
.../suites/mtmv_p0/ssb/ddl/date_create.sql | 24 ++++++++
.../suites/mtmv_p0/ssb/ddl/date_delete.sql | 1 +
.../suites/mtmv_p0/ssb/ddl/lineorder_create.sql | 24 ++++++++
.../suites/mtmv_p0/ssb/ddl/lineorder_delete.sql | 1 +
.../mtmv_p0/ssb/ddl/lineorder_flat_create.sql | 45 ++++++++++++++
.../mtmv_p0/ssb/ddl/lineorder_flat_delete.sql | 1 +
.../suites/mtmv_p0/ssb/ddl/part_create.sql | 16 +++++
.../suites/mtmv_p0/ssb/ddl/part_delete.sql | 1 +
.../suites/mtmv_p0/ssb/ddl/ssb_flat_create.sql | 52 ++++++++++++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q11_create.sql | 12 ++++
.../suites/mtmv_p0/ssb/ddl/ssb_q12_create.sql | 12 ++++
.../suites/mtmv_p0/ssb/ddl/ssb_q13_create.sql | 14 +++++
.../suites/mtmv_p0/ssb/ddl/ssb_q21_create.sql | 15 +++++
.../suites/mtmv_p0/ssb/ddl/ssb_q22_create.sql | 15 +++++
.../suites/mtmv_p0/ssb/ddl/ssb_q23_create.sql | 15 +++++
.../suites/mtmv_p0/ssb/ddl/ssb_q31_create.sql | 21 +++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q32_create.sql | 21 +++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q33_create.sql | 27 +++++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q34_create.sql | 26 ++++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q41_create.sql | 23 +++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q42_create.sql | 28 +++++++++
.../suites/mtmv_p0/ssb/ddl/ssb_q43_create.sql | 24 ++++++++
.../suites/mtmv_p0/ssb/ddl/supplier_create.sql | 14 +++++
.../suites/mtmv_p0/ssb/ddl/supplier_delete.sql | 1 +
.../suites/mtmv_p0/ssb/test_mtmv_ssb_ddl.groovy | 35 +++++++++++
30 files changed, 529 insertions(+), 35 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMultiTableMaterializedViewStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMultiTableMaterializedViewStmt.java
index 5e7cbe9adb..4b1cea4e42 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMultiTableMaterializedViewStmt.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CreateMultiTableMaterializedViewStmt.java
@@ -24,7 +24,6 @@ import org.apache.doris.catalog.Database;
import org.apache.doris.catalog.Env;
import org.apache.doris.catalog.Table;
import org.apache.doris.common.AnalysisException;
-import org.apache.doris.common.DdlException;
import org.apache.doris.common.ErrorCode;
import org.apache.doris.common.ErrorReport;
import org.apache.doris.common.UserException;
@@ -63,6 +62,8 @@ public class CreateMultiTableMaterializedViewStmt extends
CreateTableStmt {
@Override
public void analyze(Analyzer analyzer) throws UserException {
refreshInfo.analyze(analyzer);
+ queryStmt.setNeedToSql(true);
+ queryStmt.setToSQLWithHint(true);
queryStmt.analyze(analyzer);
if (queryStmt instanceof SelectStmt) {
analyzeSelectClause((SelectStmt) queryStmt);
@@ -74,7 +75,7 @@ public class CreateMultiTableMaterializedViewStmt extends
CreateTableStmt {
super.analyze(analyzer);
}
- private void analyzeSelectClause(SelectStmt selectStmt) throws
AnalysisException, DdlException {
+ private void analyzeSelectClause(SelectStmt selectStmt) throws
AnalysisException {
for (TableRef tableRef : selectStmt.getTableRefs()) {
Table table = null;
if (tableRef instanceof BaseTableRef) {
@@ -98,9 +99,8 @@ public class CreateMultiTableMaterializedViewStmt extends
CreateTableStmt {
columnDefs = generateColumnDefinitions(selectStmt.getSelectList());
}
- private List<ColumnDef> generateColumnDefinitions(SelectList selectList)
throws AnalysisException, DdlException {
- List<MVColumnItem> mvColumnItems = generateMVColumnItems(selectList);
- List<Column> schema = generateSchema(mvColumnItems);
+ private List<ColumnDef> generateColumnDefinitions(SelectList selectList)
throws AnalysisException {
+ List<Column> schema = generateSchema(selectList);
return schema.stream()
.map(column -> new ColumnDef(
column.getName(),
@@ -113,45 +113,47 @@ public class CreateMultiTableMaterializedViewStmt extends
CreateTableStmt {
).collect(Collectors.toList());
}
- private List<Column> generateSchema(List<MVColumnItem> mvColumnItems)
throws DdlException {
- List<Column> columns = Lists.newArrayList();
- for (MVColumnItem mvColumnItem : mvColumnItems) {
- Table table = tables.get(mvColumnItem.getBaseTableName());
- columns.add(mvColumnItem.toMVColumn(table));
- }
- return columns;
- }
-
- private List<MVColumnItem> generateMVColumnItems(SelectList selectList)
- throws AnalysisException {
- Map<String, MVColumnItem> uniqueMVColumnItems =
Maps.newLinkedHashMap();
+ private List<Column> generateSchema(SelectList selectList) throws
AnalysisException {
+ Map<String, Column> uniqueMVColumnItems = Maps.newLinkedHashMap();
for (SelectListItem item : selectList.getItems()) {
- MVColumnItem mvColumnItem = generateMVColumnItem(item);
- if (uniqueMVColumnItems.put(mvColumnItem.getName(), mvColumnItem)
!= null) {
-
ErrorReport.reportAnalysisException(ErrorCode.ERR_DUP_FIELDNAME,
mvColumnItem.getName());
+ Column column = generateMTMVColumn(item);
+ if (uniqueMVColumnItems.put(column.getName(), column) != null) {
+
ErrorReport.reportAnalysisException(ErrorCode.ERR_DUP_FIELDNAME,
column.getName());
}
}
- return Lists.newArrayList(uniqueMVColumnItems.values().iterator());
+
+ return Lists.newArrayList(uniqueMVColumnItems.values());
}
- private MVColumnItem generateMVColumnItem(SelectListItem item) {
+ private Column generateMTMVColumn(SelectListItem item) throws
AnalysisException {
Expr itemExpr = item.getExpr();
- MVColumnItem mvColumnItem = null;
+ String alias = item.getAlias();
+ Column mtmvColumn = null;
if (itemExpr instanceof SlotRef) {
SlotRef slotRef = (SlotRef) itemExpr;
- String alias = item.getAlias();
String name = (alias != null) ? alias.toLowerCase() :
slotRef.getColumnName().toLowerCase();
- mvColumnItem = new MVColumnItem(
- name,
- slotRef.getType(),
- slotRef.getColumn().getAggregationType(),
- slotRef.getColumn().isAggregationTypeImplicit(),
- null,
- slotRef.getColumnName(),
- slotRef.getDesc().getParent().getTable().getName()
- );
+ mtmvColumn = new Column(name, slotRef.getType(), true);
+ } else if (itemExpr instanceof FunctionCallExpr && ((FunctionCallExpr)
itemExpr).isAggregateFunction()) {
+ FunctionCallExpr functionCallExpr = (FunctionCallExpr) itemExpr;
+ String functionName = functionCallExpr.getFnName().getFunction();
+ MVColumnPattern mvColumnPattern =
CreateMaterializedViewStmt.FN_NAME_TO_PATTERN
+ .get(functionName.toLowerCase());
+ if (mvColumnPattern == null) {
+ throw new AnalysisException(
+ "Materialized view does not support this function:" +
functionCallExpr.toSqlImpl());
+ }
+ if (!mvColumnPattern.match(functionCallExpr)) {
+ throw new AnalysisException("The function " + functionName + "
must match pattern:" + mvColumnPattern);
+ }
+ String name;
+ if (alias != null) {
+ name = alias.toLowerCase();
+ mtmvColumn = new Column(name, functionCallExpr.getType(),
true);
+ } else {
+ throw new AnalysisException("Function expr: " + functionName +
" must have a alias name for MTMV.");
+ }
}
- return mvColumnItem;
+ return mtmvColumn;
}
@Override
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
index 5ecb2a6838..8fc52fbe33 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
@@ -841,4 +841,8 @@ public abstract class QueryStmt extends StatementBase
implements Queriable {
toSQLWithHint = true;
return toSql();
}
+
+ public void setToSQLWithHint(boolean enableSqlSqlWithHint) {
+ this.toSQLWithHint = enableSqlSqlWithHint;
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
index 9b0a6ec445..790d16e7f0 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/Column.java
@@ -141,7 +141,11 @@ public class Column implements Writable,
GsonPostProcessable {
}
public Column(String name, PrimitiveType dataType, boolean isAllowNull) {
- this(name, ScalarType.createType(dataType), false, null, isAllowNull,
null, "");
+ this(name, ScalarType.createType(dataType), isAllowNull);
+ }
+
+ public Column(String name, Type type, boolean isAllowNull) {
+ this(name, type, false, null, isAllowNull, null, "");
}
public Column(String name, Type type) {
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/customer_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/customer_create.sql
new file mode 100644
index 0000000000..d13101b70e
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/customer_create.sql
@@ -0,0 +1,15 @@
+CREATE TABLE IF NOT EXISTS `customer` (
+ `c_custkey` int(11) NOT NULL COMMENT "",
+ `c_name` varchar(26) NOT NULL COMMENT "",
+ `c_address` varchar(41) NOT NULL COMMENT "",
+ `c_city` varchar(11) NOT NULL COMMENT "",
+ `c_nation` varchar(16) NOT NULL COMMENT "",
+ `c_region` varchar(13) NOT NULL COMMENT "",
+ `c_phone` varchar(16) NOT NULL COMMENT "",
+ `c_mktsegment` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`c_custkey`)
+DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/customer_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/customer_delete.sql
new file mode 100644
index 0000000000..fe22a226fe
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/customer_delete.sql
@@ -0,0 +1 @@
+truncate table customer;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/date_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/date_create.sql
new file mode 100644
index 0000000000..390905ebea
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/date_create.sql
@@ -0,0 +1,24 @@
+CREATE TABLE IF NOT EXISTS `dates` (
+ `d_datekey` int(11) NOT NULL COMMENT "",
+ `d_date` varchar(20) NOT NULL COMMENT "",
+ `d_dayofweek` varchar(10) NOT NULL COMMENT "",
+ `d_month` varchar(11) NOT NULL COMMENT "",
+ `d_year` int(11) NOT NULL COMMENT "",
+ `d_yearmonthnum` int(11) NOT NULL COMMENT "",
+ `d_yearmonth` varchar(9) NOT NULL COMMENT "",
+ `d_daynuminweek` int(11) NOT NULL COMMENT "",
+ `d_daynuminmonth` int(11) NOT NULL COMMENT "",
+ `d_daynuminyear` int(11) NOT NULL COMMENT "",
+ `d_monthnuminyear` int(11) NOT NULL COMMENT "",
+ `d_weeknuminyear` int(11) NOT NULL COMMENT "",
+ `d_sellingseason` varchar(14) NOT NULL COMMENT "",
+ `d_lastdayinweekfl` int(11) NOT NULL COMMENT "",
+ `d_lastdayinmonthfl` int(11) NOT NULL COMMENT "",
+ `d_holidayfl` int(11) NOT NULL COMMENT "",
+ `d_weekdayfl` int(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`d_datekey`)
+DISTRIBUTED BY HASH(`d_datekey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/date_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/date_delete.sql
new file mode 100644
index 0000000000..3841d4617a
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/date_delete.sql
@@ -0,0 +1 @@
+truncate table `dates`;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_create.sql
new file mode 100644
index 0000000000..c066ea8a0d
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_create.sql
@@ -0,0 +1,24 @@
+CREATE TABLE IF NOT EXISTS `lineorder` (
+ `lo_orderkey` bigint(20) NOT NULL COMMENT "",
+ `lo_linenumber` bigint(20) 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_orderdate` int(11) NOT NULL COMMENT "",
+ `lo_orderpriority` varchar(16) NOT NULL COMMENT "",
+ `lo_shippriority` int(11) NOT NULL COMMENT "",
+ `lo_quantity` bigint(20) NOT NULL COMMENT "",
+ `lo_extendedprice` bigint(20) NOT NULL COMMENT "",
+ `lo_ordtotalprice` bigint(20) NOT NULL COMMENT "",
+ `lo_discount` bigint(20) NOT NULL COMMENT "",
+ `lo_revenue` bigint(20) NOT NULL COMMENT "",
+ `lo_supplycost` bigint(20) NOT NULL COMMENT "",
+ `lo_tax` bigint(20) NOT NULL COMMENT "",
+ `lo_commitdate` bigint(20) NOT NULL COMMENT "",
+ `lo_shipmode` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`lo_orderkey`, `lo_linenumber`)
+DISTRIBUTED BY HASH(`lo_orderkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_delete.sql
new file mode 100644
index 0000000000..329e040060
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_delete.sql
@@ -0,0 +1 @@
+truncate table lineorder;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_create.sql
new file mode 100644
index 0000000000..35faee0786
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_create.sql
@@ -0,0 +1,45 @@
+CREATE TABLE IF NOT EXISTS `lineorder_flat` (
+ `LO_ORDERDATE` date 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
+UNIQUE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`, `LO_LINENUMBER`)
+DISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_delete.sql
new file mode 100644
index 0000000000..13f6c32bb6
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/lineorder_flat_delete.sql
@@ -0,0 +1 @@
+truncate table lineorder_flat;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/part_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/part_create.sql
new file mode 100644
index 0000000000..2405026a3c
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/part_create.sql
@@ -0,0 +1,16 @@
+CREATE TABLE IF NOT EXISTS `part` (
+ `p_partkey` int(11) NOT NULL COMMENT "",
+ `p_name` varchar(23) NOT NULL COMMENT "",
+ `p_mfgr` varchar(7) NOT NULL COMMENT "",
+ `p_category` varchar(8) NOT NULL COMMENT "",
+ `p_brand` varchar(10) NOT NULL COMMENT "",
+ `p_color` varchar(12) NOT NULL COMMENT "",
+ `p_type` varchar(26) NOT NULL COMMENT "",
+ `p_size` int(11) NOT NULL COMMENT "",
+ `p_container` varchar(11) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`p_partkey`)
+DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/part_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/part_delete.sql
new file mode 100644
index 0000000000..02c6abd253
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/part_delete.sql
@@ -0,0 +1 @@
+truncate table `part`;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_flat_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_flat_create.sql
new file mode 100644
index 0000000000..e5cbc1a556
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_flat_create.sql
@@ -0,0 +1,52 @@
+CREATE MATERIALIZED VIEW ssb_flat
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(lo_orderkey) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ 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
+FROM
+lineorder as l
+INNER JOIN customer c
+ON (c.c_custkey = l.lo_custkey)
+INNER JOIN supplier s
+ON (s.s_suppkey = l.lo_suppkey)
+INNER JOIN part p
+ON (p.p_partkey = l.lo_partkey);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q11_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q11_create.sql
new file mode 100644
index 0000000000..fdf1a1462f
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q11_create.sql
@@ -0,0 +1,12 @@
+CREATE MATERIALIZED VIEW ssb_q11
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(REVENUE) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_year = 1993
+ AND lo_discount BETWEEN 1 AND 3
+ AND lo_quantity < 25;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q12_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q12_create.sql
new file mode 100644
index 0000000000..fcf1fbd931
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q12_create.sql
@@ -0,0 +1,12 @@
+CREATE MATERIALIZED VIEW ssb_q12
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(REVENUE) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_year = 1993
+ AND lo_discount BETWEEN 1 AND 3
+ AND lo_quantity < 25;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q13_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q13_create.sql
new file mode 100644
index 0000000000..2da36bc129
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q13_create.sql
@@ -0,0 +1,14 @@
+CREATE MATERIALIZED VIEW ssb_q13
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(REVENUE) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ SUM(lo_extendedprice * lo_discount) AS REVENUE
+FROM lineorder, dates
+WHERE
+ lo_orderdate = d_datekey
+ AND d_weeknuminyear = 6
+ AND d_year = 1994
+ AND lo_discount BETWEEN 5 AND 7
+ AND lo_quantity BETWEEN 26 AND 35;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q21_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q21_create.sql
new file mode 100644
index 0000000000..9f0ffc7e10
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q21_create.sql
@@ -0,0 +1,15 @@
+CREATE MATERIALIZED VIEW ssb_q21
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(p_brand) BUCKETS 16
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT SUM(lo_revenue) as revenue, d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_category = 'MFGR#12'
+ AND s_region = 'AMERICA'
+GROUP BY d_year, p_brand
+ORDER BY p_brand;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q22_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q22_create.sql
new file mode 100644
index 0000000000..481586e1f4
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q22_create.sql
@@ -0,0 +1,15 @@
+CREATE MATERIALIZED VIEW ssb_q22
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(p_brand) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT SUM(lo_revenue) as lo_revenue, d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'
+ AND s_region = 'ASIA'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q23_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q23_create.sql
new file mode 100644
index 0000000000..dbaf3bb234
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q23_create.sql
@@ -0,0 +1,15 @@
+CREATE MATERIALIZED VIEW ssb_q23
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(p_brand) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT SUM(lo_revenue) as revenue, d_year, p_brand
+FROM lineorder, dates, part, supplier
+WHERE
+ lo_orderdate = d_datekey
+ AND lo_partkey = p_partkey
+ AND lo_suppkey = s_suppkey
+ AND p_brand = 'MFGR#2239'
+ AND s_region = 'EUROPE'
+GROUP BY d_year, p_brand
+ORDER BY d_year, p_brand;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q31_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q31_create.sql
new file mode 100644
index 0000000000..e757cca418
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q31_create.sql
@@ -0,0 +1,21 @@
+CREATE MATERIALIZED VIEW ssb_q31
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(c_nation, s_nation) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ c_nation,
+ s_nation,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'ASIA'
+ AND s_region = 'ASIA'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_nation, s_nation, d_year
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q32_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q32_create.sql
new file mode 100644
index 0000000000..fdbcd11d05
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q32_create.sql
@@ -0,0 +1,21 @@
+CREATE MATERIALIZED VIEW ssb_q32
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(c_city, s_city) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND c_nation = 'UNITED STATES'
+ AND s_nation = 'UNITED STATES'
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q33_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q33_create.sql
new file mode 100644
index 0000000000..a23ef06708
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q33_create.sql
@@ -0,0 +1,27 @@
+CREATE MATERIALIZED VIEW ssb_q33
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(c_city, s_city) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_year >= 1992
+ AND d_year <= 1997
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q34_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q34_create.sql
new file mode 100644
index 0000000000..452fa67a79
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q34_create.sql
@@ -0,0 +1,26 @@
+CREATE MATERIALIZED VIEW ssb_q34
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(c_city, s_city) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT
+ c_city,
+ s_city,
+ d_year,
+ SUM(lo_revenue) AS REVENUE
+FROM customer, lineorder, supplier, dates
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_orderdate = d_datekey
+ AND (
+ c_city = 'UNITED KI1'
+ OR c_city = 'UNITED KI5'
+ )
+ AND (
+ s_city = 'UNITED KI1'
+ OR s_city = 'UNITED KI5'
+ )
+ AND d_yearmonth = 'Dec1997'
+GROUP BY c_city, s_city, d_year
+ORDER BY d_year ASC, REVENUE DESC;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q41_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q41_create.sql
new file mode 100644
index 0000000000..244b9f94cb
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q41_create.sql
@@ -0,0 +1,23 @@
+CREATE MATERIALIZED VIEW ssb_q41
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(c_nation) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=4,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ c_nation,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, c_nation
+ORDER BY d_year, c_nation;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q42_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q42_create.sql
new file mode 100644
index 0000000000..044dea2086
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q42_create.sql
@@ -0,0 +1,28 @@
+CREATE MATERIALIZED VIEW ssb_q42
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(s_nation, p_category) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_nation,
+ p_category,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND c_region = 'AMERICA'
+ AND s_region = 'AMERICA'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND (
+ p_mfgr = 'MFGR#1'
+ OR p_mfgr = 'MFGR#2'
+ )
+GROUP BY d_year, s_nation, p_category
+ORDER BY d_year, s_nation, p_category;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q43_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q43_create.sql
new file mode 100644
index 0000000000..0d60a20c37
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/ssb_q43_create.sql
@@ -0,0 +1,24 @@
+CREATE MATERIALIZED VIEW ssb_q43
+BUILD IMMEDIATE REFRESH COMPLETE
+DISTRIBUTED BY HASH(s_city, p_brand) BUCKETS 6
+PROPERTIES ('replication_num' = '1')
+AS
+SELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2,
enable_vectorized_engine=true, batch_size=4096,
enable_cost_based_join_reorder=true, enable_projection=true) */
+ d_year,
+ s_city,
+ p_brand,
+ SUM(lo_revenue - lo_supplycost) AS PROFIT
+FROM dates, customer, supplier, part, lineorder
+WHERE
+ lo_custkey = c_custkey
+ AND lo_suppkey = s_suppkey
+ AND lo_partkey = p_partkey
+ AND lo_orderdate = d_datekey
+ AND s_nation = 'UNITED STATES'
+ AND (
+ d_year = 1997
+ OR d_year = 1998
+ )
+ AND p_category = 'MFGR#14'
+GROUP BY d_year, s_city, p_brand
+ORDER BY d_year, s_city, p_brand;
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/supplier_create.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/supplier_create.sql
new file mode 100644
index 0000000000..1580a8fcca
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/supplier_create.sql
@@ -0,0 +1,14 @@
+CREATE TABLE IF NOT EXISTS `supplier` (
+ `s_suppkey` int(11) NOT NULL COMMENT "",
+ `s_name` varchar(26) NOT NULL COMMENT "",
+ `s_address` varchar(26) NOT NULL COMMENT "",
+ `s_city` varchar(11) NOT NULL COMMENT "",
+ `s_nation` varchar(16) NOT NULL COMMENT "",
+ `s_region` varchar(13) NOT NULL COMMENT "",
+ `s_phone` varchar(16) NOT NULL COMMENT ""
+)
+UNIQUE KEY (`s_suppkey`)
+DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 1
+PROPERTIES (
+"replication_num" = "1"
+);
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/ddl/supplier_delete.sql
b/regression-test/suites/mtmv_p0/ssb/ddl/supplier_delete.sql
new file mode 100644
index 0000000000..39e663134c
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/ddl/supplier_delete.sql
@@ -0,0 +1 @@
+truncate table `supplier`;
\ No newline at end of file
diff --git a/regression-test/suites/mtmv_p0/ssb/test_mtmv_ssb_ddl.groovy
b/regression-test/suites/mtmv_p0/ssb/test_mtmv_ssb_ddl.groovy
new file mode 100644
index 0000000000..0305dccf3a
--- /dev/null
+++ b/regression-test/suites/mtmv_p0/ssb/test_mtmv_ssb_ddl.groovy
@@ -0,0 +1,35 @@
+// 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("test_mtmv_ssb_ddl") {
+ def ssb_tables = ["customer", "lineorder", "part", "date", "supplier"]
+
+ for (String table in ssb_tables) {
+ sql new File("""${context.file.parent}/ddl/${table}_create.sql""").text
+ sql new File("""${context.file.parent}/ddl/${table}_delete.sql""").text
+ }
+
+ def ssb_mtmvs = ["ssb_flat", "ssb_q11", "ssb_q12", "ssb_q13", "ssb_q21",
"ssb_q22", "ssb_q23",
+ "ssb_q31", "ssb_q32", "ssb_q33", "ssb_q34", "ssb_q31",
"ssb_q42", "ssb_q43"]
+
+ for (String mvName in ssb_mtmvs) {
+ sql "drop MATERIALIZED VIEW IF EXISTS ${mvName}"
+ println "run mtmv ddl: ${mvName}"
+ sql new
File("""${context.file.parent}/ddl/${mvName}_create.sql""").text
+ waitingMTMVTaskFinished(mvName)
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
