You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/05/31 11:18:01 UTC
[incubator-doris] branch master updated: [test]Add MATERIALIZED VIEW test for regression test (#9855)
This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 3025a23f83 [test]Add MATERIALIZED VIEW test for regression test (#9855)
3025a23f83 is described below
commit 3025a23f83e6cd520d033c9116c07fe6256f5c48
Author: zy-kkk <81...@qq.com>
AuthorDate: Tue May 31 19:17:56 2022 +0800
[test]Add MATERIALIZED VIEW test for regression test (#9855)
* add MATERIALIZED VIEW test
* Fix DESC message does not match out file
* fix
---
.../data/rollup/test_materialized_view.out | 36 ++++++++++
.../suites/rollup/test_materialized_view.groovy | 83 ++++++++++++++++++++++
2 files changed, 119 insertions(+)
diff --git a/regression-test/data/rollup/test_materialized_view.out b/regression-test/data/rollup/test_materialized_view.out
new file mode 100644
index 0000000000..444968df72
--- /dev/null
+++ b/regression-test/data/rollup/test_materialized_view.out
@@ -0,0 +1,36 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !sql --
+test_materialized_view1 DUP_KEYS record_id INT Yes true \N true
+ seller_id INT Yes true \N true
+ store_id INT Yes true \N true
+ sale_date DATE Yes false \N NONE true
+ sale_amt BIGINT Yes false \N NONE true
+
+amt_sum AGG_KEYS store_id INT Yes true \N true
+ sale_amt BIGINT Yes false \N SUM true
+
+-- !sql --
+test_materialized_view2 DUP_KEYS record_id INT Yes true \N true
+ seller_id INT Yes true \N true
+ store_id INT Yes true \N true
+ sale_date DATE Yes false \N NONE true
+ sale_amt BIGINT Yes false \N NONE true
+
+seller_id_order DUP_KEYS store_id INT Yes true \N true
+ seller_id INT Yes true \N true
+ sale_amt BIGINT Yes false \N NONE true
+
+-- !sql --
+1 1 1 2020-05-30 100
+2 1 1 2020-05-30 100
+
+-- !sql --
+1 200
+
+-- !sql --
+1 1 1 2020-05-30 100
+2 1 1 2020-05-30 100
+
+-- !sql --
+1 200
+
diff --git a/regression-test/suites/rollup/test_materialized_view.groovy b/regression-test/suites/rollup/test_materialized_view.groovy
new file mode 100644
index 0000000000..5d41dca6de
--- /dev/null
+++ b/regression-test/suites/rollup/test_materialized_view.groovy
@@ -0,0 +1,83 @@
+// 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_materialized_view", "rollup") {
+ def tbName1 = "test_materialized_view1"
+ def tbName2 = "test_materialized_view2"
+ sql "DROP TABLE IF EXISTS ${tbName1}"
+ sql """
+ CREATE TABLE ${tbName1}(
+ record_id int,
+ seller_id int,
+ store_id int,
+ sale_date date,
+ sale_amt bigint
+ )
+ DISTRIBUTED BY HASH(record_id) properties("replication_num" = "1");
+ """
+ sql "DROP TABLE IF EXISTS ${tbName2}"
+ sql """
+ CREATE TABLE ${tbName2}(
+ record_id int,
+ seller_id int,
+ store_id int,
+ sale_date date,
+ sale_amt bigint
+ )
+ DISTRIBUTED BY HASH(record_id) properties("replication_num" = "1");
+ """
+ sql "CREATE materialized VIEW amt_sum AS SELECT store_id, sum(sale_amt) FROM ${tbName1} GROUP BY store_id;"
+ String res = "null"
+ while (!res.contains("FINISHED")){
+ res = sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE TableName='${tbName1}' ORDER BY CreateTime DESC LIMIT 1;"
+ if(res.contains("CANCELLED")){
+ print("job is cancelled")
+ break
+ }
+ Thread.sleep(1000)
+ }
+ sql "CREATE materialized VIEW seller_id_order AS SELECT store_id,seller_id, sale_amt FROM ${tbName2} ORDER BY store_id,seller_id;"
+ res = "null"
+ while (!res.contains("FINISHED")){
+ res = sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE TableName='${tbName2}' ORDER BY CreateTime DESC LIMIT 1;"
+ if(res.contains("CANCELLED")){
+ print("job is cancelled")
+ break
+ }
+ Thread.sleep(1000)
+ }
+ sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE TableName='${tbName1}';"
+ sql "SHOW ALTER TABLE MATERIALIZED VIEW WHERE TableName='${tbName2}';"
+ qt_sql "DESC ${tbName1} ALL;"
+ qt_sql "DESC ${tbName2} ALL;"
+ sql "insert into ${tbName1} values(1, 1, 1, '2020-05-30',100);"
+ sql "insert into ${tbName1} values(2, 1, 1, '2020-05-30',100);"
+ sql "insert into ${tbName2} values(1, 1, 1, '2020-05-30',100);"
+ sql "insert into ${tbName2} values(2, 1, 1, '2020-05-30',100);"
+ Thread.sleep(5000)
+ explain{
+ sql("SELECT store_id, sum(sale_amt) FROM ${tbName1} GROUP BY store_id")
+ contains("rollup: amt_sum")
+ }
+ qt_sql "SELECT * FROM ${tbName1} order by record_id;"
+ qt_sql "SELECT store_id, sum(sale_amt) FROM ${tbName1} GROUP BY store_id order by store_id;"
+ qt_sql "SELECT * FROM ${tbName2} order by record_id;"
+ qt_sql "SELECT store_id, sum(sale_amt) FROM ${tbName2} GROUP BY store_id order by store_id;"
+ sql "DROP TABLE ${tbName1}"
+ sql "DROP TABLE ${tbName2}"
+}
+
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org