This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 9401b0d6d0f branch-3.1: [test](mtmv) Inject stats for complex mv to
make test case stable #58876 (#58995)
9401b0d6d0f is described below
commit 9401b0d6d0f7059827a603c44679e41080bd0bba
Author: seawinde <[email protected]>
AuthorDate: Mon Dec 15 11:23:30 2025 +0800
branch-3.1: [test](mtmv) Inject stats for complex mv to make test case
stable #58876 (#58995)
picked from #58876
---
.../hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy | 82 +-
.../mv/nested/nested_materialized_view.groovy | 1253 +++++++++++++++++++-
2 files changed, 1304 insertions(+), 31 deletions(-)
diff --git
a/regression-test/suites/external_table_p2/hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy
b/regression-test/suites/external_table_p2/hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy
index 680f7eaa93d..3a4d95730f1 100644
---
a/regression-test/suites/external_table_p2/hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy
+++
b/regression-test/suites/external_table_p2/hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy
@@ -37,7 +37,82 @@ suite("test_hudi_rewrite_mtmv",
"p2,external,hudi,external_remote,external_remot
);"""
sql """analyze table
${catalogName}.`hudi_mtmv_regression_test`.hudi_table_1 with sync"""
- sql """alter table ${catalogName}.`hudi_mtmv_regression_test`.hudi_table_1
modify column par set stats ('row_count'='10');"""
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column age set stats (
+ 'ndv'='10',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='10',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column _hoodie_record_key set stats (
+ 'ndv'='10',
+ 'num_nulls'='0',
+ 'min_value'='20250121171615893_0_0',
+ 'max_value'='20250121171615893_7_1',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column id set stats (
+ 'ndv'='10',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='10',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column _hoodie_file_name set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+
'min_value'='58eabd3f-1996-4cb6-83e4-56fd11cb4e7d-0_0-30-108_20250121171615893.parquet',
+
'max_value'='7f98e9ac-bd11-48fd-ac80-9ca6dc1ddb34-0_1-30-109_20250121171615893.parquet',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column _hoodie_partition_path set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+ 'min_value'='par=a',
+ 'max_value'='par=b',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column _hoodie_commit_seqno set stats (
+ 'ndv'='10',
+ 'num_nulls'='0',
+ 'min_value'='20250121171615893_0_0',
+ 'max_value'='20250121171615893_1_4',
+ 'row_count'='10'
+);
+'''
+
+ sql '''
+alter table
test_hudi_rewrite_mtmv_catalog.hudi_mtmv_regression_test.hudi_table_1
+modify column _hoodie_commit_time set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='20250121171615893',
+ 'max_value'='20250121171615893',
+ 'row_count'='10'
+);
+'''
sql """drop materialized view if exists ${mvName};"""
@@ -60,8 +135,11 @@ suite("test_hudi_rewrite_mtmv",
"p2,external,hudi,external_remote,external_remot
REFRESH MATERIALIZED VIEW ${mvName} partitions(p_a);
"""
waitingMTMVTaskFinishedByMvName(mvName)
+ sql """analyze table ${mvName} with sync"""
order_qt_refresh_one_partition "SELECT * FROM ${mvName} "
+ sql """alter table ${mvName} modify column par set stats
('row_count'='1');"""
+
mv_rewrite_success(mvSql, mvName)
order_qt_refresh_one_partition_rewrite "${mvSql}"
@@ -75,6 +153,8 @@ suite("test_hudi_rewrite_mtmv",
"p2,external,hudi,external_remote,external_remot
REFRESH MATERIALIZED VIEW ${mvName} auto
"""
waitingMTMVTaskFinishedByMvName(mvName)
+ sql """analyze table ${mvName} with sync"""
+ sql """alter table ${mvName} modify column par set stats
('row_count'='2');"""
order_qt_refresh_auto "SELECT * FROM ${mvName} "
mv_rewrite_success(mvSql, mvName)
diff --git
a/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
b/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
index 692ea03fc61..1480e3c1f50 100644
---
a/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
+++
b/regression-test/suites/nereids_rules_p0/mv/nested/nested_materialized_view.groovy
@@ -1,3 +1,5 @@
+import java.util.logging.Logger
+
// 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
@@ -72,7 +74,7 @@ suite("nested_materialized_view") {
// relate to
${DORIS_HOME}/regression-test/data/demo/streamload_input.csv.
// also, you can stream load a http stream, e.g.
http://xxx/some.csv
- file """${getS3Url()}/regression/ssb/sf1/${tableName}.tbl.gz"""
+ file """${getS3Url()}/regression/ssb/sf0.1/${tableName}.tbl.gz"""
time 10000 // limit inflight 10s
@@ -95,6 +97,586 @@ suite("nested_materialized_view") {
}
sql """ sync """
+ sql '''
+alter table customer modify column c_address set stats (
+ 'ndv'='3013',
+ 'num_nulls'='0',
+ 'min_value'=' dcVkxZ,s,9xW ab60a',
+ 'max_value'='zzB4DRh4Eg3uFygL7UZZMiBa',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_custkey set stats (
+ 'ndv'='3014',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='3000',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_nation set stats (
+ 'ndv'='25',
+ 'num_nulls'='0',
+ 'min_value'='ALGERIA',
+ 'max_value'='VIETNAM',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_mktsegment set stats (
+ 'ndv'='5',
+ 'num_nulls'='0',
+ 'min_value'='AUTOMOBILE',
+ 'max_value'='MACHINERY',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_name set stats (
+ 'ndv'='2997',
+ 'num_nulls'='0',
+ 'min_value'='Customer#000000001',
+ 'max_value'='Customer#000003000',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_region set stats (
+ 'ndv'='5',
+ 'num_nulls'='0',
+ 'min_value'='AFRICA',
+ 'max_value'='MIDDLE EAST',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_city set stats (
+ 'ndv'='250',
+ 'num_nulls'='0',
+ 'min_value'='ALGERIA 0',
+ 'max_value'='VIETNAM 9',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table customer modify column c_phone set stats (
+ 'ndv'='3003',
+ 'num_nulls'='0',
+ 'min_value'='10-109-430-5638',
+ 'max_value'='34-996-906-1652',
+ 'row_count'='3000'
+);
+'''
+
+ sql '''
+alter table date modify column d_datekey set stats (
+ 'ndv'='254',
+ 'num_nulls'='0',
+ 'min_value'='19920101',
+ 'max_value'='19920911',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_daynuminyear set stats (
+ 'ndv'='257',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='255',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_lastdayinweekfl set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='1',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_year set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='1992',
+ 'max_value'='1992',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_yearmonth set stats (
+ 'ndv'='9',
+ 'num_nulls'='0',
+ 'min_value'='Apr1992',
+ 'max_value'='Sep1992',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_daynuminmonth set stats (
+ 'ndv'='31',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='31',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_daynuminweek set stats (
+ 'ndv'='7',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='7',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_holidayfl set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='1',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_monthnuminyear set stats (
+ 'ndv'='9',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='9',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_weekdayfl set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='1',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_yearmonthnum set stats (
+ 'ndv'='9',
+ 'num_nulls'='0',
+ 'min_value'='199201',
+ 'max_value'='199209',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_month set stats (
+ 'ndv'='9',
+ 'num_nulls'='0',
+ 'min_value'='April',
+ 'max_value'='September',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_weeknuminyear set stats (
+ 'ndv'='37',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='37',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_date set stats (
+ 'ndv'='255',
+ 'num_nulls'='0',
+ 'min_value'='April 1, 1992',
+ 'max_value'='September 9, 1992',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_dayofweek set stats (
+ 'ndv'='7',
+ 'num_nulls'='0',
+ 'min_value'='Friday',
+ 'max_value'='Wednesday',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_lastdayinmonthfl set stats (
+ 'ndv'='2',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='1',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table date modify column d_sellingseason set stats (
+ 'ndv'='4',
+ 'num_nulls'='0',
+ 'min_value'='Fall',
+ 'max_value'='Winter',
+ 'row_count'='255'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_custkey set stats (
+ 'ndv'='2001',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='2999',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_extendedprice set stats (
+ 'ndv'='128773',
+ 'num_nulls'='0',
+ 'min_value'='90100',
+ 'max_value'='9594950',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_revenue set stats (
+ 'ndv'='453898',
+ 'num_nulls'='0',
+ 'min_value'='81720',
+ 'max_value'='9569950',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_suppkey set stats (
+ 'ndv'='201',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='200',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_quantity set stats (
+ 'ndv'='50',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='50',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_shipmode set stats (
+ 'ndv'='7',
+ 'num_nulls'='0',
+ 'min_value'='AIR',
+ 'max_value'='TRUCK',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_discount set stats (
+ 'ndv'='11',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='10',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_partkey set stats (
+ 'ndv'='19968',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='20000',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_shippriority set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='0',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_orderdate set stats (
+ 'ndv'='2408',
+ 'num_nulls'='0',
+ 'min_value'='19920101',
+ 'max_value'='19980802',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_tax set stats (
+ 'ndv'='9',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='8',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_commitdate set stats (
+ 'ndv'='2445',
+ 'num_nulls'='0',
+ 'min_value'='19920131',
+ 'max_value'='19981031',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_linenumber set stats (
+ 'ndv'='7',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='7',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_orderkey set stats (
+ 'ndv'='150431',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='600000',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_orderpriority set stats (
+ 'ndv'='5',
+ 'num_nulls'='0',
+ 'min_value'='1-URGENT',
+ 'max_value'='5-LOW',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_ordtotalprice set stats (
+ 'ndv'='150461',
+ 'num_nulls'='0',
+ 'min_value'='83340',
+ 'max_value'='47912921',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table lineorder modify column lo_supplycost set stats (
+ 'ndv'='2136',
+ 'num_nulls'='0',
+ 'min_value'='54060',
+ 'max_value'='115139',
+ 'row_count'='600572'
+);
+'''
+
+ sql '''
+alter table part modify column p_category set stats (
+ 'ndv'='25',
+ 'num_nulls'='0',
+ 'min_value'='MFGR#11',
+ 'max_value'='MFGR#55',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_name set stats (
+ 'ndv'='7495',
+ 'num_nulls'='0',
+ 'min_value'='almond antique',
+ 'max_value'='yellow white',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_type set stats (
+ 'ndv'='150',
+ 'num_nulls'='0',
+ 'min_value'='ECONOMY ANODIZED BRASS',
+ 'max_value'='STANDARD POLISHED TIN',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_mfgr set stats (
+ 'ndv'='5',
+ 'num_nulls'='0',
+ 'min_value'='MFGR#1',
+ 'max_value'='MFGR#5',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_color set stats (
+ 'ndv'='92',
+ 'num_nulls'='0',
+ 'min_value'='almond',
+ 'max_value'='yellow',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_partkey set stats (
+ 'ndv'='19968',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='20000',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_size set stats (
+ 'ndv'='50',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='50',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_container set stats (
+ 'ndv'='40',
+ 'num_nulls'='0',
+ 'min_value'='JUMBO BAG',
+ 'max_value'='WRAP PKG',
+ 'row_count'='20000'
+);
+'''
+
+ sql '''
+alter table part modify column p_brand set stats (
+ 'ndv'='1002',
+ 'num_nulls'='0',
+ 'min_value'='MFGR#111',
+ 'max_value'='MFGR#559',
+ 'row_count'='20000'
+);
+'''
+
+
+ sql '''
+alter table supplier modify column s_city set stats (
+ 'ndv'='143',
+ 'num_nulls'='0',
+ 'min_value'='ALGERIA 2',
+ 'max_value'='VIETNAM 8',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_region set stats (
+ 'ndv'='5',
+ 'num_nulls'='0',
+ 'min_value'='AFRICA',
+ 'max_value'='MIDDLE EAST',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_suppkey set stats (
+ 'ndv'='201',
+ 'num_nulls'='0',
+ 'min_value'='1',
+ 'max_value'='200',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_name set stats (
+ 'ndv'='201',
+ 'num_nulls'='0',
+ 'min_value'='Supplier#000000001',
+ 'max_value'='Supplier#000000200',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_phone set stats (
+ 'ndv'='200',
+ 'num_nulls'='0',
+ 'min_value'='10-127-851-8031',
+ 'max_value'='34-908-631-4424',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_address set stats (
+ 'ndv'='197',
+ 'num_nulls'='0',
+ 'min_value'=' 0W7IPdkpWycU',
+ 'max_value'='zaux5FT',
+ 'row_count'='200'
+);
+'''
+
+ sql '''
+alter table supplier modify column s_nation set stats (
+ 'ndv'='25',
+ 'num_nulls'='0',
+ 'min_value'='ALGERIA',
+ 'max_value'='VIETNAM',
+ 'row_count'='200'
+);
+'''
String db = context.config.getDbNameByFile(context.file)
sql "use ${db}"
@@ -257,6 +839,25 @@ suite("nested_materialized_view") {
// complex nest mv rewrite
+ // chose 4 different dates to create 4 groups of nested materialized views
+ def fetchFourOrderDates = {
+ def dates = []
+ def result = sql """
+ SELECT DISTINCT lo_orderdate
+ FROM lineorder
+ INNER JOIN customer ON lo_custkey = c_custkey
+ INNER JOIN date ON lo_orderdate = d_datekey
+ LIMIT 4;
+ """
+ result.each { row ->
+ dates.add(row[0])
+ }
+ return dates;
+ }
+
+ def date = fetchFourOrderDates();
+ logger.info("Fetched order dates for nested mv test: " + date);
+
create_async_mv(db, "mv1_a", """
select
lo_custkey,
@@ -268,13 +869,15 @@ suite("nested_materialized_view") {
lineorder
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19930423
+ d_daynuminweek > 0 and lo_orderdate = ${date[0]}
group by
lo_custkey,
lo_partkey,
lo_orderkey,
lo_orderdate;""")
+ sql """alter table mv1_a modify column lo_custkey set stats
('row_count'='2384');"""
+
create_async_mv(db, "mv2_a", """
select
lo_custkey,
@@ -285,11 +888,13 @@ suite("nested_materialized_view") {
inner join customer on lo_custkey = c_custkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19930423
+ d_daynuminweek > 0 and lo_orderdate = ${date[0]}
group by
lo_custkey,
lo_orderdate;""")
+ sql """alter table mv2_a modify column lo_custkey set stats
('row_count'='580');"""
+
create_async_mv(db, "mv4_a", """
select
lo_partkey,
@@ -300,11 +905,13 @@ suite("nested_materialized_view") {
inner join supplier on lo_suppkey = s_suppkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19930423
+ d_daynuminweek > 0 and lo_orderdate = ${date[0]}
group by
lo_partkey,
lo_orderdate;""")
+ sql """alter table mv4_a modify column lo_partkey set stats
('row_count'='2371');"""
+
create_async_mv(db, "mv_all_6_a", """
select
'测试1' as nm,
@@ -330,6 +937,8 @@ from
and t6.d_sellingseason = 'Spring';
""")
+ sql """alter table mv_all_6_a modify column lo_custkey set stats
('row_count'='2384');"""
+
create_async_mv(db, "mv1_b", """
select
lo_custkey,
@@ -341,13 +950,15 @@ select
lineorder
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19980421
+ d_daynuminweek > 0 and lo_orderdate = ${date[1]}
group by
lo_custkey,
lo_partkey,
lo_orderkey,
lo_orderdate;""")
+ sql """alter table mv1_b modify column lo_custkey set stats
('row_count'='2565');"""
+
create_async_mv(db, "mv2_b", """
select
lo_custkey,
@@ -358,11 +969,13 @@ select
inner join customer on lo_custkey = c_custkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19980421
+ d_daynuminweek > 0 and lo_orderdate = ${date[1]}
group by
lo_custkey,
lo_orderdate;""")
+ sql """alter table mv2_b modify column lo_custkey set stats
('row_count'='641');"""
+
create_async_mv(db, "mv4_b", """
select
lo_partkey,
@@ -373,11 +986,13 @@ select
inner join supplier on lo_suppkey = s_suppkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19980421
+ d_daynuminweek > 0 and lo_orderdate = ${date[1]}
group by
lo_partkey,
lo_orderdate;""")
+ sql """alter table mv4_b modify column lo_partkey set stats
('row_count'='2546');"""
+
create_async_mv(db, "mv_all_6_b", """
select
'测试1' as nm,
@@ -403,6 +1018,7 @@ from
and t6.d_sellingseason = 'Spring';
""")
+ sql """alter table mv_all_6_b modify column lo_custkey set stats
('row_count'='2565');"""
create_async_mv(db, "mv1_c", """
select
@@ -415,13 +1031,15 @@ from
lineorder
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940413
+ d_daynuminweek > 0 and lo_orderdate = ${date[3]}
group by
lo_custkey,
lo_partkey,
lo_orderkey,
lo_orderdate;""")
+ sql """alter table mv1_c modify column lo_custkey set stats
('row_count'='2603');"""
+
create_async_mv(db, "mv2_c", """
select
lo_custkey,
@@ -432,11 +1050,13 @@ from
inner join customer on lo_custkey = c_custkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940413
+ d_daynuminweek > 0 and lo_orderdate = ${date[3]}
group by
lo_custkey,
lo_orderdate;""")
+ sql """alter table mv2_c modify column lo_custkey set stats
('row_count'='641');"""
+
create_async_mv(db, "mv4_c", """
select
lo_partkey,
@@ -447,11 +1067,13 @@ from
inner join supplier on lo_suppkey = s_suppkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940413
+ d_daynuminweek > 0 and lo_orderdate = ${date[3]}
group by
lo_partkey,
lo_orderdate;""")
+ sql """alter table mv4_c modify column lo_partkey set stats
('row_count'='2581');"""
+
create_async_mv(db, "mv_all_6_c", """
select
'测试1' as nm,
@@ -477,6 +1099,7 @@ from
and t6.d_sellingseason = 'Spring';
""")
+ sql """alter table mv_all_6_c modify column lo_custkey set stats
('row_count'='2603');"""
create_async_mv(db, "mv1_d", """
select
@@ -489,13 +1112,15 @@ from
lineorder
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940218
+ d_daynuminweek > 0 and lo_orderdate = ${date[2]}
group by
lo_custkey,
lo_partkey,
lo_orderkey,
lo_orderdate;""")
+ sql """alter table mv1_d modify column lo_custkey set stats
('row_count'='2327');"""
+
create_async_mv(db, "mv2_d", """
select
lo_custkey,
@@ -506,11 +1131,13 @@ from
inner join customer on lo_custkey = c_custkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940218
+ d_daynuminweek > 0 and lo_orderdate = ${date[2]}
group by
lo_custkey,
lo_orderdate;""")
+ sql """alter table mv2_d modify column lo_custkey set stats
('row_count'='572');"""
+
create_async_mv(db, "mv4_d", """
select
lo_partkey,
@@ -521,11 +1148,13 @@ from
inner join supplier on lo_suppkey = s_suppkey
inner join date on lo_orderdate = d_datekey
where
- d_daynuminweek > 0 and lo_orderdate = 19940218
+ d_daynuminweek > 0 and lo_orderdate = ${date[2]}
group by
lo_partkey,
lo_orderdate;""")
+ sql """alter table mv4_d modify column lo_partkey set stats
('row_count'='2307');"""
+
create_async_mv(db, "mv_all_6_d", """
select
'测试1' as nm,
@@ -551,6 +1180,8 @@ from
and t6.d_sellingseason = 'Spring';
""")
+ sql """alter table mv_all_6_d modify column lo_custkey set stats
('row_count'='2327');"""
+
def query2_0 = """
select * from (
select
@@ -576,7 +1207,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19930423
+ and lo_orderdate = ${date[0]}
group by
lo_custkey,
lo_partkey,
@@ -594,7 +1225,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19930423
+ and lo_orderdate = ${date[0]}
group by
lo_custkey,
lo_orderdate
@@ -611,7 +1242,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19930423
+ and lo_orderdate = ${date[0]}
group by
lo_partkey,
lo_orderdate
@@ -628,7 +1259,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19930423
+ and lo_orderdate = ${date[0]}
group by
lo_partkey,
lo_orderdate
@@ -662,7 +1293,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19980421
+ and lo_orderdate = ${date[1]}
group by
lo_custkey,
lo_partkey,
@@ -680,7 +1311,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19980421
+ and lo_orderdate = ${date[1]}
group by
lo_custkey,
lo_orderdate
@@ -697,7 +1328,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19980421
+ and lo_orderdate = ${date[1]}
group by
lo_partkey,
lo_orderdate
@@ -714,7 +1345,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19980421
+ and lo_orderdate = ${date[1]}
group by
lo_partkey,
lo_orderdate
@@ -748,7 +1379,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940413
+ and lo_orderdate = ${date[3]}
group by
lo_custkey,
lo_partkey,
@@ -766,7 +1397,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940413
+ and lo_orderdate = ${date[3]}
group by
lo_custkey,
lo_orderdate
@@ -783,7 +1414,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940413
+ and lo_orderdate = ${date[3]}
group by
lo_partkey,
lo_orderdate
@@ -800,7 +1431,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940413
+ and lo_orderdate = ${date[3]}
group by
lo_partkey,
lo_orderdate
@@ -834,7 +1465,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940218
+ and lo_orderdate = ${date[2]}
group by
lo_custkey,
lo_partkey,
@@ -852,7 +1483,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940218
+ and lo_orderdate = ${date[2]}
group by
lo_custkey,
lo_orderdate
@@ -869,7 +1500,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940218
+ and lo_orderdate = ${date[2]}
group by
lo_partkey,
lo_orderdate
@@ -886,7 +1517,7 @@ select * from (
inner join date on lo_orderdate = d_datekey
where
d_daynuminweek > 0
- and lo_orderdate = 19940218
+ and lo_orderdate = ${date[2]}
group by
lo_partkey,
lo_orderdate
@@ -899,9 +1530,571 @@ select * from (
) t order by 1,2,3,4,5,6,7,8,9;
"""
+
+ sql '''
+alter table mv1_b modify column lo_orderkey set stats (
+ 'ndv'='69',
+ 'num_nulls'='0',
+ 'min_value'='16070',
+ 'max_value'='572579',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv1_b modify column sum_value1 set stats (
+ 'ndv'='252',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='85274100',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv1_b modify column lo_partkey set stats (
+ 'ndv'='273',
+ 'num_nulls'='0',
+ 'min_value'='210',
+ 'max_value'='19939',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv1_b modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920604',
+ 'max_value'='19920604',
+ 'row_count'='276'
+);
+'''
+
+
+ sql '''
+alter table mv2_a modify column sum_value2 set stats (
+ 'ndv'='66',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='185206423',
+ 'row_count'='66'
+);
+'''
+
+ sql '''
+alter table mv2_a modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920529',
+ 'max_value'='19920529',
+ 'row_count'='66'
+);
+'''
+
+ sql '''
+alter table mv1_a modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920529',
+ 'max_value'='19920529',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv1_a modify column sum_value1 set stats (
+ 'ndv'='246',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='81053280',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv1_a modify column lo_orderkey set stats (
+ 'ndv'='67',
+ 'num_nulls'='0',
+ 'min_value'='8291',
+ 'max_value'='596935',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv1_a modify column lo_partkey set stats (
+ 'ndv'='267',
+ 'num_nulls'='0',
+ 'min_value'='323',
+ 'max_value'='19957',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv1_d modify column lo_partkey set stats (
+ 'ndv'='242',
+ 'num_nulls'='0',
+ 'min_value'='253',
+ 'max_value'='19861',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv1_d modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920530',
+ 'max_value'='19920530',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv1_d modify column sum_value1 set stats (
+ 'ndv'='225',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='86919140',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv1_d modify column lo_orderkey set stats (
+ 'ndv'='64',
+ 'num_nulls'='0',
+ 'min_value'='16486',
+ 'max_value'='575302',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv2_c modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920828',
+ 'max_value'='19920828',
+ 'row_count'='54'
+);
+'''
+
+ sql '''
+alter table mv2_c modify column sum_value2 set stats (
+ 'ndv'='54',
+ 'num_nulls'='0',
+ 'min_value'='2195164',
+ 'max_value'='158761143',
+ 'row_count'='54'
+);
+'''
+
+ sql '''
+alter table mv4_a modify column sum_value4 set stats (
+ 'ndv'='245',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='81053280',
+ 'row_count'='268'
+);
+'''
+
+ sql '''
+alter table mv4_a modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920529',
+ 'max_value'='19920529',
+ 'row_count'='268'
+);
+'''
+
+ sql '''
+alter table mv2_b modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920604',
+ 'max_value'='19920604',
+ 'row_count'='67'
+);
+'''
+
+ sql '''
+alter table mv2_b modify column sum_value2 set stats (
+ 'ndv'='67',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='176373155',
+ 'row_count'='67'
+);
+'''
+
+ sql '''
+alter table mv1_c modify column sum_value1 set stats (
+ 'ndv'='189',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='83203120',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv1_c modify column lo_partkey set stats (
+ 'ndv'='212',
+ 'num_nulls'='0',
+ 'min_value'='131',
+ 'max_value'='19923',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv1_c modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920828',
+ 'max_value'='19920828',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv1_c modify column lo_orderkey set stats (
+ 'ndv'='54',
+ 'num_nulls'='0',
+ 'min_value'='1504',
+ 'max_value'='587201',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv4_c modify column sum_value4 set stats (
+ 'ndv'='188',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='83203120',
+ 'row_count'='212'
+);
+'''
+
+ sql '''
+alter table mv4_c modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920828',
+ 'max_value'='19920828',
+ 'row_count'='212'
+);
+'''
+
+ sql '''
+alter table mv4_b modify column sum_value4 set stats (
+ 'ndv'='251',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='85274100',
+ 'row_count'='275'
+);
+'''
+
+ sql '''
+alter table mv4_b modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920604',
+ 'max_value'='19920604',
+ 'row_count'='275'
+);
+'''
+
+ sql '''
+alter table mv2_d modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920530',
+ 'max_value'='19920530',
+ 'row_count'='62'
+);
+'''
+
+ sql '''
+alter table mv2_d modify column sum_value2 set stats (
+ 'ndv'='61',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='207242262',
+ 'row_count'='62'
+);
+'''
+
+ sql '''
+alter table mv4_d modify column sum_value4 set stats (
+ 'ndv'='224',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='86919140',
+ 'row_count'='243'
+);
+'''
+
+ sql '''
+alter table mv4_d modify column lo_orderdate set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='19920530',
+ 'max_value'='19920530',
+ 'row_count'='243'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column __subtract_5 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-28304424',
+ 'max_value'='0',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column __subtract_4 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-28304424',
+ 'max_value'='0',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试1',
+ 'max_value'='测试1',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column sum_value1 set stats (
+ 'ndv'='246',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='81053280',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column t_nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试2',
+ 'max_value'='测试2',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_a modify column __subtract_3 set stats (
+ 'ndv'='262',
+ 'num_nulls'='0',
+ 'min_value'='-183330928',
+ 'max_value'='2785088',
+ 'row_count'='269'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column t_nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试2',
+ 'max_value'='测试2',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试1',
+ 'max_value'='测试1',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column __subtract_4 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-4352247',
+ 'max_value'='0',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column sum_value1 set stats (
+ 'ndv'='252',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='85274100',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column __subtract_3 set stats (
+ 'ndv'='275',
+ 'num_nulls'='0',
+ 'min_value'='-176373155',
+ 'max_value'='2401722',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_b modify column __subtract_5 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-4352247',
+ 'max_value'='0',
+ 'row_count'='276'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column __subtract_3 set stats (
+ 'ndv'='209',
+ 'num_nulls'='0',
+ 'min_value'='-155357379',
+ 'max_value'='7833780',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column t_nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试2',
+ 'max_value'='测试2',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column __subtract_4 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-22727760',
+ 'max_value'='0',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试1',
+ 'max_value'='测试1',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column sum_value1 set stats (
+ 'ndv'='189',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='83203120',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_c modify column __subtract_5 set stats (
+ 'ndv'='3',
+ 'num_nulls'='0',
+ 'min_value'='-22727760',
+ 'max_value'='0',
+ 'row_count'='213'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column __subtract_3 set stats (
+ 'ndv'='240',
+ 'num_nulls'='0',
+ 'min_value'='-207242262',
+ 'max_value'='7835790',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试1',
+ 'max_value'='测试1',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column __subtract_5 set stats (
+ 'ndv'='4',
+ 'num_nulls'='0',
+ 'min_value'='-64365462',
+ 'max_value'='0',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column sum_value1 set stats (
+ 'ndv'='225',
+ 'num_nulls'='0',
+ 'min_value'='0',
+ 'max_value'='86919140',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column __subtract_4 set stats (
+ 'ndv'='4',
+ 'num_nulls'='0',
+ 'min_value'='-64365462',
+ 'max_value'='0',
+ 'row_count'='245'
+);
+'''
+
+ sql '''
+alter table mv_all_6_d modify column t_nm set stats (
+ 'ndv'='1',
+ 'num_nulls'='0',
+ 'min_value'='测试2',
+ 'max_value'='测试2',
+ 'row_count'='245'
+);
+'''
+
sql "SET enable_materialized_view_rewrite= true"
sql "SET enable_materialized_view_nest_rewrite = true"
- mv_rewrite_all_success(query2_0, ["mv_all_6_a", "mv_all_6_b",
"mv_all_6_c", "mv_all_6_d"])
+ mv_rewrite_all_success_without_check_chosen(query2_0, ["mv_all_6_a",
"mv_all_6_b", "mv_all_6_c", "mv_all_6_d"])
// Compare result when before and after mv rewrite
compare_res(query2_0)
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
