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/doris.git
The following commit(s) were added to refs/heads/master by this push:
new cf56a35a88f [fix](mv) Add sync mv test that had been commented (#49970)
cf56a35a88f is described below
commit cf56a35a88ff46beb3b5796929ba65adb982a85e
Author: seawinde <w...@selectdb.com>
AuthorDate: Thu Apr 17 11:51:29 2025 +0800
[fix](mv) Add sync mv test that had been commented (#49970)
---
.../testAggQuqeryOnAggMV6.out | Bin 217 -> 511 bytes
.../mv_p0/test_dup_mv_div/test_dup_mv_div.groovy | 14 ++++++------
.../mv_p0/test_dup_mv_json/test_dup_mv_json.groovy | 24 ++++++++++-----------
.../suites/mv_p0/test_tcu/test_tcu.groovy | 19 ++++++++--------
.../testAggQuqeryOnAggMV6.groovy | 16 +++++++++++++-
5 files changed, 43 insertions(+), 30 deletions(-)
diff --git
a/regression-test/data/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.out
b/regression-test/data/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.out
index b10e432dadb..b00f8fa104b 100644
Binary files
a/regression-test/data/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.out
and
b/regression-test/data/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.out
differ
diff --git
a/regression-test/suites/mv_p0/test_dup_mv_div/test_dup_mv_div.groovy
b/regression-test/suites/mv_p0/test_dup_mv_div/test_dup_mv_div.groovy
index 605973f5128..eb2b28b9d5c 100644
--- a/regression-test/suites/mv_p0/test_dup_mv_div/test_dup_mv_div.groovy
+++ b/regression-test/suites/mv_p0/test_dup_mv_div/test_dup_mv_div.groovy
@@ -18,6 +18,10 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
suite ("test_dup_mv_div") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+
sql """set enable_nereids_planner=true"""
sql """SET enable_fallback_to_original_planner=false"""
sql """ DROP TABLE IF EXISTS d_table; """
@@ -38,16 +42,12 @@ suite ("test_dup_mv_div") {
sql "insert into d_table select 2,2,2,'b';"
sql "insert into d_table select 3,-3,null,'c';"
- createMV ("create materialized view kdiv as select k1,k2/1 from d_table;")
+ create_sync_mv (db, "d_table", "kdiv", "select k1,k2/1 from d_table;")
sql "insert into d_table select -4,-4,-4,'4';"
qt_select_star "select * from d_table order by k1;"
- // TODO reopen it when we could fix it in right way
- // explain {
- // sql("select k1,k2/1 from d_table order by k1;")
- // contains "(kdiv)"
- // }
+ mv_rewrite_success("select k1,k2/1 from d_table order by k1;", "kdiv")
qt_select_mv "select k1,k2/1 from d_table order by k1;"
-}
+}
\ No newline at end of file
diff --git
a/regression-test/suites/mv_p0/test_dup_mv_json/test_dup_mv_json.groovy
b/regression-test/suites/mv_p0/test_dup_mv_json/test_dup_mv_json.groovy
index da269e56f12..5cba3c67522 100644
--- a/regression-test/suites/mv_p0/test_dup_mv_json/test_dup_mv_json.groovy
+++ b/regression-test/suites/mv_p0/test_dup_mv_json/test_dup_mv_json.groovy
@@ -18,6 +18,10 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
suite ("test_dup_mv_json") {
+
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+
sql """set enable_nereids_planner=true"""
sql """SET enable_fallback_to_original_planner=false"""
sql """ DROP TABLE IF EXISTS tcu_test; """
@@ -43,27 +47,23 @@ suite ("test_dup_mv_json") {
sql """insert into tcu_test values('vin78215KHVB','2023-08-23
06:47:14','2023-08-23 06:47:15','2023-08-23 06:47:15','2023-08-23
00:00:00','MessageName','MessageType','referenceId','activityID','ProtofileVersion','{"k22":{"k221":{"k2211":2023,"k2212":8,"k2213":23,"k2214":6,"k2215":47,"k2216":14},"k222":{"k2221":84,"k2222":43,"k2223":3,"k2224":{"xxxx01_u_actl":4.164,"xxxx02_u_actl":4.163,"xxxx03_u_actl":4.155,"xxxx04_u_actl":4.164,"xxxx05_u_actl":4.162,"xxxx06_u_actl":4.159,"xxxx07_u_a
[...]
-
- createMV ("""create materialized view tcu_test_index as
- select
+ create_sync_mv(db, "tcu_test", "tcu_test_index",
+ """select
a
,(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_ac [...]
FROM tcu_test;
"""
- )
+ );
sql """insert into tcu_test values('vin78215KHVB','2023-08-23
06:47:14','2023-08-23 06:47:15','2023-08-23 06:47:15','2023-08-23
00:00:00','MessageName','MessageType','referenceId','activityID','ProtofileVersion','{"k22":{"k221":{"k2211":2023,"k2212":8,"k2213":23,"k2214":6,"k2215":47,"k2216":14},"k222":{"k2221":84,"k2222":43,"k2223":3,"k2224":{"xxxx01_u_actl":4.164,"xxxx02_u_actl":4.163,"xxxx03_u_actl":4.155,"xxxx04_u_actl":4.164,"xxxx05_u_actl":4.162,"xxxx06_u_actl":4.159,"xxxx07_u_a
[...]
qt_select_star "select * from tcu_test;"
- // TODO reopen it when we could fix it in right way
- // explain {
- // sql("""select a
- // ,(json_extract(k, '\$.k22.k222.k2224.xxxx01_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')))/1.0
- // FROM tcu_test;""")
- // contains "(tcu_test_index)"
- // }
+ mv_rewrite_success("""select a
+ ,(json_extract(k, '\$.k22.k222.k2224.xxxx01_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')))/1.0
+ FROM tcu_test;""", "tcu_test_index")
+
qt_select_mv """select a
,(json_extract(k, '\$.k22.k222.k2224.xxxx01_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_u_actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_u_actl')))/1.0
FROM tcu_test ;"""
-}
+}
\ No newline at end of file
diff --git a/regression-test/suites/mv_p0/test_tcu/test_tcu.groovy
b/regression-test/suites/mv_p0/test_tcu/test_tcu.groovy
index bd88c3dbd4c..a1f6b395a91 100644
--- a/regression-test/suites/mv_p0/test_tcu/test_tcu.groovy
+++ b/regression-test/suites/mv_p0/test_tcu/test_tcu.groovy
@@ -22,6 +22,9 @@ suite ("test_tcu") {
sql """SET enable_fallback_to_original_planner=false"""
sql """ DROP TABLE IF EXISTS tcu_test; """
+ String db = context.config.getDbNameByFile(context.file)
+ sql "use ${db}"
+
sql """
CREATE TABLE `tcu_test` (
`a` varchar(50) NULL,
@@ -43,9 +46,8 @@ suite ("test_tcu") {
sql """insert into tcu_test values('vin78215KHVB','2023-08-23
06:47:14','2023-08-23 06:47:15','2023-08-23 06:47:15','2023-08-23
00:00:00','MessageName','MessageType','referenceId','ActivityID','ProtofileVersion','{"k22":{"k221":{"k2211":2023,"k2212":8,"k2213":23,"k2214":6,"k2215":47,"k2216":14},"k222":{"k2221":84,"k2222":43,"k2223":3,"k2224":{"xxxx01_U_Actl":4.164,"xxxx02_U_Actl":4.163,"xxxx03_U_Actl":4.155,"xxxx04_U_Actl":4.164,"xxxx05_U_Actl":4.162,"xxxx06_U_Actl":4.159,"xxxx07_U_A
[...]
- createMV ("""
- create materialized view tcu_test_index as
- select
+ create_sync_mv(db, "tcu_test", "tcu_test_index",
+ """select
a as vin
,(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl') [...]
,abs((json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Ac [...]
@@ -63,12 +65,9 @@ suite ("test_tcu") {
,abs((json_extract(k, '\$.k22.k222.k2224.xxxx01_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')))/1 - [...]
,abs((json_extract(k, '\$.k22.k222.k2224.xxxx01_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')))/1 - [...]
,greatest(abs((json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl')+json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl')-greatest(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl'))-least(json_extract(k,
'\$.k22.k222.k2224.xxxx01_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx02_U_Actl'),json_extract(k,
'\$.k22.k222.k2224.xxxx03_U_Actl [...]
- """
+ """
+
+ mv_rewrite_success("select * from tcu_test_view;", "tcu_test_index")
- // TODO reopen it when we could fix it in right way
- // explain {
- // sql("select * from tcu_test_view;")
- // contains "(tcu_test_index)"
- // }
qt_select_mv "select * from tcu_test_view;"
-}
+}
\ No newline at end of file
diff --git
a/regression-test/suites/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.groovy
b/regression-test/suites/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.groovy
index cdc3178b63a..710174017f8 100644
---
a/regression-test/suites/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.groovy
+++
b/regression-test/suites/mv_p0/ut/testAggQuqeryOnAggMV6/testAggQuqeryOnAggMV6.groovy
@@ -33,15 +33,29 @@ suite ("testAggQuqeryOnAggMV6") {
"""
sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-02",2,"b",2,2,2);"""
+ sql """insert into emps values("2020-01-02",2,"b",2,2,2);"""
+ sql """insert into emps values("2020-01-02",2,"b",2,2,2);"""
sql """insert into emps values("2020-01-02",2,"b",2,2,2);"""
+ sql """insert into emps values("2020-01-02",2,"b",2,2,2);"""
+ sql """insert into emps values("2020-01-03",3,"c",3,3,3);"""
+ sql """insert into emps values("2020-01-03",3,"c",3,3,3);"""
+ sql """insert into emps values("2020-01-03",3,"c",3,3,3);"""
+ sql """insert into emps values("2020-01-03",3,"c",3,3,3);"""
sql """insert into emps values("2020-01-03",3,"c",3,3,3);"""
createMV("create materialized view emps_mv as select deptno, commission,
sum(salary) from emps group by deptno, commission;")
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
+ sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
sql """insert into emps values("2020-01-01",1,"a",1,1,1);"""
sql """analyze table emps with sync;"""
- sql """alter table emps modify column time_col set stats
('row_count'='4');"""
+ sql """alter table emps modify column time_col set stats
('row_count'='18');"""
sql """set enable_stats=false;"""
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
