This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 3b2f7b49fdce64bfb1ac16dd9647b9b130c106d8
Author: Jibing-Li <64681310+jibing...@users.noreply.github.com>
AuthorDate: Tue Feb 6 23:23:31 2024 +0800
[test](statistics)Add analyze mtmv test case (#30847)
---
.../data/statistics/test_analyze_mtmv.out | 6 +
.../suites/statistics/test_analyze_mtmv.groovy | 420 +++++++++++++++++++++
2 files changed, 426 insertions(+)
diff --git a/regression-test/data/statistics/test_analyze_mtmv.out
b/regression-test/data/statistics/test_analyze_mtmv.out
new file mode 100644
index 00000000000..49fb86f7ce2
--- /dev/null
+++ b/regression-test/data/statistics/test_analyze_mtmv.out
@@ -0,0 +1,6 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql1 --
+2023-10-17 2023-10-17 2 3 99.50
+2023-10-18 2023-10-18 2 3 109.20
+2023-10-19 2023-10-19 2 3 99.50
+
diff --git a/regression-test/suites/statistics/test_analyze_mtmv.groovy
b/regression-test/suites/statistics/test_analyze_mtmv.groovy
new file mode 100644
index 00000000000..fca40910aef
--- /dev/null
+++ b/regression-test/suites/statistics/test_analyze_mtmv.groovy
@@ -0,0 +1,420 @@
+// 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_analyze_mtmv") {
+
+ def wait_row_count_reported = { ->
+ while(true) {
+ Thread.sleep(5000)
+ boolean reported = true;
+ def result = sql """SHOW DATA;"""
+ logger.info("result " + result)
+ for (int i = 0; i < result.size(); i++) {
+ if (result[i][1] == "0.000 ") {
+ reported = false;
+ break;
+ }
+ }
+ if (reported) {
+ break;
+ }
+ }
+ }
+
+ sql """drop database if exists test_analyze_mtmv"""
+ sql """create database test_analyze_mtmv"""
+ sql """use test_analyze_mtmv"""
+
+ sql """CREATE TABLE IF NOT EXISTS orders (
+ o_orderkey integer not null,
+ o_custkey integer not null,
+ o_orderstatus char(1) not null,
+ o_totalprice decimalv3(15,2) not null,
+ o_orderdate date not null,
+ o_orderpriority char(15) not null,
+ o_clerk char(15) not null,
+ o_shippriority integer not null,
+ o_comment varchar(79) not null
+ )
+ DUPLICATE KEY(o_orderkey, o_custkey)
+ PARTITION BY RANGE(o_orderdate)(
+ FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+ DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
+ PROPERTIES ("replication_num" = "1");
+ """
+
+ sql """insert into orders values
+ (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'),
+ (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy');
+ """
+
+ sql """CREATE TABLE IF NOT EXISTS lineitem (
+ l_orderkey integer not null,
+ l_partkey integer not null,
+ l_suppkey integer not null,
+ l_linenumber integer not null,
+ l_quantity decimalv3(15,2) not null,
+ l_extendedprice decimalv3(15,2) not null,
+ l_discount decimalv3(15,2) not null,
+ l_tax decimalv3(15,2) not null,
+ l_returnflag char(1) not null,
+ l_linestatus char(1) not null,
+ l_shipdate date not null,
+ l_commitdate date not null,
+ l_receiptdate date not null,
+ l_shipinstruct char(25) not null,
+ l_shipmode char(10) not null,
+ l_comment varchar(44) not null
+ )
+ DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
+ PARTITION BY RANGE(l_shipdate)
+ (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+ DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
+ PROPERTIES ("replication_num" = "1");
+ """
+
+ sql """insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17',
'2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18',
'2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19',
'2023-10-19', '2023-10-19', 'c', 'd', 'xxxxxxxxx');
+ """
+
+ sql """
+ CREATE MATERIALIZED VIEW mv1
+ BUILD DEFERRED REFRESH AUTO ON MANUAL
+ partition by(l_shipdate)
+ DISTRIBUTED BY RANDOM BUCKETS 2
+ PROPERTIES ('replication_num' = '1')
+ AS
+ select l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) as sum_total
+ from lineitem
+ left join orders on lineitem.l_orderkey =
orders.o_orderkey and l_shipdate = o_orderdate
+ group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+ """
+ sql """REFRESH MATERIALIZED VIEW mv1"""
+ while(true) {
+ Thread.sleep(1000)
+ def result = sql """select * from
mv_infos("database"="test_analyze_mtmv") where Name="mv1";"""
+ if (result[0][5] == "SUCCESS") {
+ break;
+ }
+ }
+
+ def dup_sql1 = """select * from mv1 order by l_shipdate;"""
+ qt_sql1 dup_sql1
+
+ sql """analyze table mv1 with sync"""
+
+ def result_sample = sql """show column stats mv1"""
+ assertEquals(5, result_sample.size())
+
+ result_sample = sql """show column stats mv1(l_shipdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_shipdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_shipdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_shipdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(o_orderdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("o_orderdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(o_orderdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("o_orderdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(l_partkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_partkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("2", result_sample[0][7])
+ assertEquals("2", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_partkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_partkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("2", result_sample[0][7])
+ assertEquals("2", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(l_suppkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_suppkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("3", result_sample[0][7])
+ assertEquals("3", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_suppkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_suppkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("3", result_sample[0][7])
+ assertEquals("3", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(sum_total)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("sum_total", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("2.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("48.0", result_sample[0][5])
+ assertEquals("16.0", result_sample[0][6])
+ assertEquals("99.50", result_sample[0][7])
+ assertEquals("109.20", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(sum_total)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("sum_total", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("2.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("48.0", result_sample[0][5])
+ assertEquals("16.0", result_sample[0][6])
+ assertEquals("99.50", result_sample[0][7])
+ assertEquals("109.20", result_sample[0][8])
+ assertEquals("FULL", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ sql """drop stats mv1"""
+ result_sample = sql """show column stats mv1(sum_total)"""
+ assertEquals(0, result_sample.size())
+ result_sample = sql """show column cached stats mv1(sum_total)"""
+ assertEquals(0, result_sample.size())
+
+ wait_row_count_reported()
+ sql """analyze table mv1 with sync with sample rows 4000000"""
+ result_sample = sql """show column stats mv1(l_shipdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_shipdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_shipdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_shipdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(o_orderdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("o_orderdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(o_orderdate)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("o_orderdate", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("3.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("'2023-10-17'", result_sample[0][7])
+ assertEquals("'2023-10-19'", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(l_partkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_partkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("2", result_sample[0][7])
+ assertEquals("2", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_partkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_partkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("2", result_sample[0][7])
+ assertEquals("2", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(l_suppkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_suppkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("3", result_sample[0][7])
+ assertEquals("3", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(l_suppkey)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("l_suppkey", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("1.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("12.0", result_sample[0][5])
+ assertEquals("4.0", result_sample[0][6])
+ assertEquals("3", result_sample[0][7])
+ assertEquals("3", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column stats mv1(sum_total)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("sum_total", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("2.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("48.0", result_sample[0][5])
+ assertEquals("16.0", result_sample[0][6])
+ assertEquals("99.50", result_sample[0][7])
+ assertEquals("109.20", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ result_sample = sql """show column cached stats mv1(sum_total)"""
+ assertEquals(1, result_sample.size())
+ assertEquals("sum_total", result_sample[0][0])
+ assertEquals("N/A", result_sample[0][1])
+ assertEquals("3.0", result_sample[0][2])
+ assertEquals("2.0", result_sample[0][3])
+ assertEquals("0.0", result_sample[0][4])
+ assertEquals("48.0", result_sample[0][5])
+ assertEquals("16.0", result_sample[0][6])
+ assertEquals("99.50", result_sample[0][7])
+ assertEquals("109.20", result_sample[0][8])
+ assertEquals("SAMPLE", result_sample[0][9])
+ assertEquals("MANUAL", result_sample[0][11])
+
+ sql """drop database if exists test_analyze_mtmv"""
+}
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
