[feature](mtmv)create mtmv can use view #56423

zddr · 2025-09-25T02:41:10Z

What problem does this PR solve?

The main changes are as follows:

If the MTMV is in a schema change state, recheck whether the materialized view's SQL statement meets the requirements before refreshing, and check whether the PCT table has changed.
Add a data structure in the StatementContext to store the tables and views directly depended on in the plan.
Add a data structure to store the relationship between materialized views and views.
The location for adding filter conditions during partition refresh has been moved to CheckPolicy, as the previous location could only access the parsed plan.
Clear the snapshot information of the materialized view when a view is deleted or modified.

Issue Number: close #xxx

Related PR: #xxx

Problem Summary:

Release note

None

Check List (For Author)

Test
- Regression test
- Unit Test
- Manual test (add detailed scripts or steps below)
- No need to test or manual test. Explain why:
  - This is a refactor/code format and no logic has been changed.
  - Previous test can cover this change.
  - No code files have been changed.
  - Other reason
Behavior changed:
- No.
- Yes.
Does this need documentation?
- No.
- Yes.

Check List (For Reviewer who merge this PR)

Confirm the release note
Confirm test cases
Confirm document
Add branch pick label

hello-stephen · 2025-09-25T02:41:15Z

Thank you for your contribution to Apache Doris.
Don't know what should be done next? See How to process your PR.

Please clearly describe your PR:

What problem was fixed (it's best to include specific error reporting information). How it was fixed.
Which behaviors were modified. What was the previous behavior, what is it now, why was it modified, and what possible impacts might there be.
What features were added. Why was this function added?
Which code was refactored and why was this part of the code refactored?
Which functions were optimized and what is the difference before and after the optimization?

seawinde · 2025-10-14T13:25:23Z

fe/fe-core/src/main/java/org/apache/doris/mtmv/MTMVPartitionUtil.java

        }
        try {
-            return isMTMVSync(MTMVRefreshContext.buildContext(mtmv), mtmvRelation.getBaseTablesOneLevel(),
+            return isMTMVSync(MTMVRefreshContext.buildContext(mtmv), mtmvRelation.getBaseTablesOneLevelAndFromView(),


getBaseTablesOneLevelAndFromView look like contain views, how can compare mv snapshot to view snapshot?

getBaseTablesOneLevelAndFromView not contains view, but contains table from view, not need compare mv snapshot to view snapshot

seawinde · 2025-10-14T13:27:29Z

fe/fe-core/src/main/java/org/apache/doris/job/extensions/mtmv/MTMVTask.java

        if (LOG.isDebugEnabled()) {
            LOG.debug("mtmv task run, taskId: {}", super.getTaskId());
        }
+        mtmvSchemaChangeVersion = mtmv.getSchemaChangeVersion();


mtmvSchemaChangeVersion is not used?

after task refresh,will compare schema of base table if changed in org.apache.doris.catalog.MTMV#addTaskResult

seawinde · 2025-10-15T01:52:46Z

regression-test/suites/mtmv_p0/test_create_mtmv_with_view.groovy

+
+import org.junit.Assert;
+
+suite("test_create_mtmv_with_view","mtmv") {


maybe should add some cases which are views contains mv, and mv is partitioned

seawinde · 2025-10-15T01:55:25Z

fe/fe-core/src/main/java/org/apache/doris/job/extensions/mtmv/MTMVTask.java

    private MTMVRelation relation;
    private StmtExecutor executor;
    private Map<String, MTMVRefreshPartitionSnapshot> partitionSnapshots;
+    private long mtmvSchemaChangeVersion;


what does mtmvSchemaChangeVersion means? why add this

Used to determine whether the SQL of the view has changed during the refresh period. If a change has occurred, the result of this refresh may be incorrect and will be ignored

# Conflicts: # fe/fe-core/src/main/java/org/apache/doris/datasource/InternalCatalog.java

zddr · 2025-10-16T03:56:23Z

run buildall

doris-robot · 2025-10-16T05:24:57Z

TPC-DS: Total hot run time: 190358 ms

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit cc4646bd44b593844f94185ba02c7200a969bb6f, data reload: false

query1	1080	448	422	422
query2	6567	1703	1665	1665
query3	6750	228	233	228
query4	26632	23844	23486	23486
query5	5106	696	486	486
query6	359	250	229	229
query7	4650	521	308	308
query8	327	298	266	266
query9	8778	2593	2593	2593
query10	549	355	298	298
query11	15745	15419	14844	14844
query12	190	122	123	122
query13	1707	604	444	444
query14	11926	9389	9374	9374
query15	258	190	188	188
query16	7763	638	517	517
query17	1604	795	710	710
query18	2172	443	348	348
query19	309	228	194	194
query20	153	143	131	131
query21	216	145	121	121
query22	4641	4721	4557	4557
query23	35475	33575	33899	33575
query24	8428	2530	2532	2530
query25	609	543	457	457
query26	1241	283	162	162
query27	2775	525	375	375
query28	5108	2302	2190	2190
query29	845	648	528	528
query30	298	240	210	210
query31	949	869	764	764
query32	104	82	79	79
query33	816	446	361	361
query34	1316	852	547	547
query35	852	910	784	784
query36	989	1032	901	901
query37	135	110	82	82
query38	3510	3556	3486	3486
query39	1435	1430	1424	1424
query40	221	133	118	118
query41	78	59	65	59
query42	119	112	111	111
query43	506	493	475	475
query44	1407	830	835	830
query45	193	181	174	174
query46	884	1000	642	642
query47	1736	1778	1686	1686
query48	406	426	324	324
query49	769	528	420	420
query50	676	717	411	411
query51	3953	3914	3859	3859
query52	113	111	99	99
query53	250	277	207	207
query54	615	612	537	537
query55	88	85	84	84
query56	340	337	309	309
query57	1175	1186	1113	1113
query58	289	276	284	276
query59	2473	2620	2566	2566
query60	348	359	353	353
query61	165	161	157	157
query62	800	709	696	696
query63	241	198	196	196
query64	4434	1204	879	879
query65	4061	3986	3987	3986
query66	1089	497	334	334
query67	15539	15580	15076	15076
query68	9595	966	593	593
query69	489	349	287	287
query70	1367	1317	1283	1283
query71	519	330	318	318
query72	5784	4902	4861	4861
query73	701	616	362	362
query74	9216	9020	8767	8767
query75	4482	3369	2794	2794
query76	4310	1180	788	788
query77	1019	437	310	310
query78	9534	9782	8964	8964
query79	4690	858	574	574
query80	686	585	508	508
query81	492	274	223	223
query82	284	159	133	133
query83	295	268	256	256
query84	304	121	96	96
query85	862	474	429	429
query86	362	313	301	301
query87	3863	3725	3639	3639
query88	2978	2264	2314	2264
query89	441	343	294	294
query90	2141	231	229	229
query91	178	170	135	135
query92	81	73	68	68
query93	2909	987	634	634
query94	697	446	323	323
query95	414	327	314	314
query96	507	592	287	287
query97	2906	2972	2876	2876
query98	233	235	219	219
query99	1454	1400	1266	1266
Total cold run time: 289128 ms
Total hot run time: 190358 ms

doris-robot · 2025-10-16T05:30:28Z

ClickBench: Total hot run time: 30.75 s

machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit cc4646bd44b593844f94185ba02c7200a969bb6f, data reload: false

query1	0.06	0.04	0.05
query2	0.09	0.05	0.06
query3	0.27	0.08	0.08
query4	1.62	0.12	0.12
query5	0.27	0.26	0.25
query6	1.17	0.66	0.64
query7	0.04	0.03	0.03
query8	0.06	0.04	0.04
query9	0.63	0.53	0.52
query10	0.58	0.58	0.58
query11	0.16	0.11	0.12
query12	0.17	0.12	0.12
query13	0.64	0.62	0.61
query14	1.02	1.03	1.03
query15	0.86	0.84	0.84
query16	0.39	0.41	0.40
query17	1.05	1.06	1.03
query18	0.21	0.19	0.20
query19	1.94	1.83	1.88
query20	0.02	0.02	0.01
query21	15.46	0.92	0.56
query22	0.76	1.07	0.62
query23	15.10	1.38	0.62
query24	7.11	1.36	1.11
query25	0.52	0.33	0.10
query26	0.46	0.15	0.14
query27	0.06	0.06	0.06
query28	10.03	1.36	0.95
query29	12.55	3.96	3.32
query30	0.29	0.15	0.12
query31	2.82	0.62	0.39
query32	3.24	0.55	0.49
query33	3.12	3.10	3.12
query34	16.14	5.47	4.88
query35	4.87	4.91	4.88
query36	0.70	0.53	0.50
query37	0.10	0.08	0.08
query38	0.06	0.05	0.05
query39	0.04	0.02	0.03
query40	0.19	0.14	0.15
query41	0.09	0.04	0.03
query42	0.04	0.03	0.03
query43	0.04	0.04	0.04
Total cold run time: 105.04 s
Total hot run time: 30.75 s

hello-stephen · 2025-10-16T08:09:37Z

FE Regression Coverage Report

Increment line coverage 86.53% (289/334) 🎉
Increment coverage report
Complete coverage report

github-actions · 2025-10-20T04:12:58Z

PR approved by at least one committer and no changes requested.

github-actions · 2025-10-20T04:13:00Z

PR approved by anyone and no changes requested.

The main changes are as follows: - If the MTMV is in a schema change state, recheck whether the materialized view's SQL statement meets the requirements before refreshing, and check whether the PCT table has changed. - Add a data structure in the StatementContext to store the tables and views directly depended on in the plan. - Add a data structure to store the relationship between materialized views and views. - The location for adding filter conditions during partition refresh has been moved to CheckPolicy, as the previous location could only access the parsed plan. - Clear the snapshot information of the materialized view when a view is deleted or modified. # Conflicts: # fe/fe-core/src/main/java/org/apache/doris/datasource/InternalCatalog.java # fe/fe-core/src/main/java/org/apache/doris/job/extensions/mtmv/MTMVTask.java # fe/fe-core/src/main/java/org/apache/doris/mtmv/MTMVRewriteUtil.java # fe/fe-core/src/main/java/org/apache/doris/nereids/trees/plans/commands/info/CreateMTMVInfo.java

…ew (#57958) ### What problem does this PR solve? Related PR: #56423 #56958 Problem Summary: 1. Fix partition trace fail when create partition mv with view and date_trunc 2. Fix err if use data_trunc('day', col) partition column when create partition materialized view the mv def sql would be success after this fix CREATE VIEW lineitem_daily_summary_view AS SELECT DATE_TRUNC('day', L_SHIPDATE) AS ship_date, L_RETURNFLAG, L_LINESTATUS, COUNT(*) AS order_count, SUM(L_QUANTITY) AS total_quantity, SUM(L_EXTENDEDPRICE) AS total_price, AVG(L_DISCOUNT) AS avg_discount FROM lineitem WHERE L_SHIPDATE IS NOT NULL GROUP BY ship_date, L_RETURNFLAG, L_LINESTATUS; mv def is as following SELECT ship_date, L_RETURNFLAG, SUM(order_count) AS total_orders, SUM(total_quantity) AS sum_quantity, SUM(total_price) AS sum_price, AVG(avg_discount) AS average_discount FROM lineitem_daily_summary_view GROUP BY ship_date, L_RETURNFLAG ORDER BY ship_date, L_RETURNFLAG, total_orders, sum_quantity, sum_price;

cherry-pick: #56423

…ew (apache#57958) ### What problem does this PR solve? Related PR: apache#56423 apache#56958 Problem Summary: 1. Fix partition trace fail when create partition mv with view and date_trunc 2. Fix err if use data_trunc('day', col) partition column when create partition materialized view the mv def sql would be success after this fix CREATE VIEW lineitem_daily_summary_view AS SELECT DATE_TRUNC('day', L_SHIPDATE) AS ship_date, L_RETURNFLAG, L_LINESTATUS, COUNT(*) AS order_count, SUM(L_QUANTITY) AS total_quantity, SUM(L_EXTENDEDPRICE) AS total_price, AVG(L_DISCOUNT) AS avg_discount FROM lineitem WHERE L_SHIPDATE IS NOT NULL GROUP BY ship_date, L_RETURNFLAG, L_LINESTATUS; mv def is as following SELECT ship_date, L_RETURNFLAG, SUM(order_count) AS total_orders, SUM(total_quantity) AS sum_quantity, SUM(total_price) AS sum_price, AVG(avg_discount) AS average_discount FROM lineitem_daily_summary_view GROUP BY ship_date, L_RETURNFLAG ORDER BY ship_date, L_RETURNFLAG, total_orders, sum_quantity, sum_price;

zddr added 7 commits September 22, 2025 15:59

1

e3f19b9

Merge remote-tracking branch 'apache/master' into mtmv_create_view

0ebb515

1

b41ffc4

1

91cfbb6

one level tables

48e6631

schema change

29c5a33

ut

fdd4f50

zddr marked this pull request as draft September 25, 2025 02:41

zddr added 21 commits September 25, 2025 11:12

ut

1d06364

1

03a0f19

1

6789970

1

169684b

1

6723c24

1

d6fd464

1

265bb7d

1

531a5f4

1

6a40448

1

f9fc7cc

1

5657426

1

00134cd

1

a9e966d

1

f4c1b9c

1

1c7f2d4

1

12e5663

1

54bd5b8

1

7ef95a7

1

d09b0c3

1

8539d2d

1

d5bc023

seawinde reviewed Oct 14, 2025

View reviewed changes

seawinde reviewed Oct 15, 2025

View reviewed changes

zddr and others added 5 commits October 16, 2025 10:20

Merge branch 'master' into mtmv_create_view

48d1725

# Conflicts: # fe/fe-core/src/main/java/org/apache/doris/datasource/InternalCatalog.java

resolve comment

a139f27

add case

b705638

add case

17d64fb

case

cc4646b

morrySnow approved these changes Oct 20, 2025

View reviewed changes

github-actions bot added the approved Indicates a PR has been approved by one committer. label Oct 20, 2025

github-actions bot added the reviewed label Oct 20, 2025

starocean999 approved these changes Oct 20, 2025

View reviewed changes

seawinde approved these changes Oct 20, 2025

View reviewed changes

starocean999 merged commit fa36b1c into apache:master Oct 20, 2025
31 of 32 checks passed

zddr mentioned this pull request Nov 14, 2025

branch-4.0:[feature](mtmv)create mtmv can use view (#56423) #58010

Merged

seawinde mentioned this pull request Nov 17, 2025

[fix](mtmv) Fix partition trace fail when create partition mv with view #57958

Merged

16 tasks

yiguolei pushed a commit that referenced this pull request Nov 18, 2025

branch-4.0:[feature](mtmv)create mtmv can use view (#56423) (#58010)

c8f7784

cherry-pick: #56423

yiguolei added the dev/4.0.2-merged label Nov 18, 2025

yiguolei mentioned this pull request Dec 2, 2025

4.0.2 Release Notes #58605

Open


		import org.junit.Assert;

		suite("test_create_mtmv_with_view","mtmv") {

[feature](mtmv)create mtmv can use view #56423

[feature](mtmv)create mtmv can use view #56423

Uh oh!

Conversation

zddr commented Sep 25, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

What problem does this PR solve?

Release note

Check List (For Author)

Check List (For Reviewer who merge this PR)

Uh oh!

hello-stephen commented Sep 25, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

zddr commented Oct 16, 2025

Uh oh!

doris-robot commented Oct 16, 2025

Uh oh!

doris-robot commented Oct 16, 2025

Uh oh!

hello-stephen commented Oct 16, 2025

FE Regression Coverage Report

Uh oh!

github-actions bot commented Oct 20, 2025

Uh oh!

github-actions bot commented Oct 20, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

7 participants

zddr commented Sep 25, 2025 •

edited

Loading