This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new c80c3a85de3 branch-4.0: [test](mtmv) Inject stats for complex mv to
make test case stable #58876 (#58966)
c80c3a85de3 is described below
commit c80c3a85de35b4bb3e51f9d512ff6af8364b0243
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Wed Dec 17 14:46:24 2025 +0800
branch-4.0: [test](mtmv) Inject stats for complex mv to make test case
stable #58876 (#58966)
Cherry-picked from #58876
Co-authored-by: seawinde <[email protected]>
---
.../hudi/hudi_mtmv/test_hudi_rewrite_mtmv.groovy | 82 +-
.../mv/nested/nested_materialized_view.groovy | 1252 +++++++++++++++++++-
2 files changed, 1304 insertions(+), 30 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 d0f8d94150b..0f7a2ce63f7 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
@@ -71,7 +73,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
@@ -94,6 +96,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}"
@@ -256,6 +838,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,
@@ -267,13 +868,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,
@@ -284,11 +887,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,
@@ -299,11 +904,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,
@@ -329,6 +936,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,
@@ -340,13 +949,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,
@@ -357,11 +968,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,
@@ -372,11 +985,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,
@@ -402,6 +1017,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
@@ -414,13 +1030,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,
@@ -431,11 +1049,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,
@@ -446,11 +1066,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,
@@ -476,6 +1098,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
@@ -488,13 +1111,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,
@@ -505,11 +1130,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,
@@ -520,11 +1147,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,
@@ -550,6 +1179,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
@@ -575,7 +1206,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,
@@ -593,7 +1224,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
@@ -610,7 +1241,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
@@ -627,7 +1258,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
@@ -661,7 +1292,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,
@@ -679,7 +1310,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
@@ -696,7 +1327,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
@@ -713,7 +1344,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
@@ -747,7 +1378,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,
@@ -765,7 +1396,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
@@ -782,7 +1413,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
@@ -799,7 +1430,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
@@ -833,7 +1464,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,
@@ -851,7 +1482,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
@@ -868,7 +1499,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
@@ -885,7 +1516,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
@@ -898,8 +1529,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"
+ sql "SET materialized_view_rewrite_duration_threshold_ms = 60000"
// DP Hyper can not use pre materialized view rewrite
sql """SET enable_dphyp_optimizer = false"""
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"],
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
