Skip to content

[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group by key eliminate both happened #55674

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
morrySnow merged 7 commits into from
Nov 10, 2025
Merged

[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group by key eliminate both happened #55674

morrySnow merged 7 commits into from
Nov 10, 2025

Conversation

@seawinde
Copy link
Contributor

@seawinde seawinde commented Sep 4, 2025

What problem does this PR solve?

if mv def is as fllowing:

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO
PROPERTIES (
    "replication_num" = "1"
)
as
select 
    a.cust_id,
    a.event_type_id, 
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(a.created_date, '%Y%m') month,
    a.created_date
from data_event a
join customer b on(a.cust_id=b.cust_id)
group by a.cust_id, a.event_type_id, a.bind_attr, month, a.created_date;

if query is as fllowing, the query pattern, the query can be optimized by rule ELIMINATE_CONST_JOIN_CONDITION and
ELIMINATE_GROUP_BY_KEY_BY_UNIFORM this would cause rewrite fail by mv, the pr fix this

select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(created_date, '%Y%m') month
from data_event a
inner join customer b on (a.cust_id = b.cust_id)
where a.cust_id = 410723002257
    and date_format(a.created_date, '%Y%m') = '202509'
group by a.cust_id, a.event_type_id, a.bind_attr, month;

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

@Thearas
Copy link
Contributor

Thearas commented Sep 4, 2025

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:

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

@seawinde
Copy link
Contributor Author

seawinde commented Sep 4, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Sep 4, 2025

TPC-H: Total hot run time: 34183 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 4c387ac30fef7a8a458f2442dfa3cbabbce1cc15, data reload: false

------ Round 1 ----------------------------------
q1	17654	5257	5072	5072
q2	2030	329	216	216
q3	10253	1270	750	750
q4	10219	1029	528	528
q5	7547	2437	2330	2330
q6	181	164	138	138
q7	923	767	617	617
q8	9358	1357	1124	1124
q9	6964	5168	5146	5146
q10	6945	2392	1984	1984
q11	501	310	282	282
q12	366	368	241	241
q13	17796	3677	3082	3082
q14	235	238	224	224
q15	574	507	490	490
q16	431	419	398	398
q17	602	858	368	368
q18	7484	7208	6980	6980
q19	1491	946	572	572
q20	331	349	235	235
q21	3959	3178	2428	2428
q22	1035	1043	978	978
Total cold run time: 106879 ms
Total hot run time: 34183 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5198	5119	5116	5116
q2	251	351	242	242
q3	2213	2754	2327	2327
q4	1334	1782	1337	1337
q5	4176	4409	4618	4409
q6	221	174	136	136
q7	2021	1956	1936	1936
q8	2646	2718	2630	2630
q9	7436	7447	7321	7321
q10	3181	3310	2824	2824
q11	570	523	494	494
q12	724	784	672	672
q13	3724	3873	3366	3366
q14	300	311	293	293
q15	535	485	507	485
q16	459	507	445	445
q17	1174	1571	1394	1394
q18	7965	7906	7554	7554
q19	877	929	1150	929
q20	1972	2018	1903	1903
q21	4970	4395	4330	4330
q22	1097	1051	1024	1024
Total cold run time: 53044 ms
Total hot run time: 51167 ms

@doris-robot
Copy link

doris-robot commented Sep 4, 2025

TPC-DS: Total hot run time: 187125 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 4c387ac30fef7a8a458f2442dfa3cbabbce1cc15, data reload: false

query1	1049	448	420	420
query2	6562	1711	1734	1711
query3	6773	226	224	224
query4	26259	23809	22961	22961
query5	4863	682	524	524
query6	351	263	237	237
query7	4683	534	314	314
query8	329	261	256	256
query9	8673	2943	2929	2929
query10	522	354	309	309
query11	15765	15402	15263	15263
query12	183	131	126	126
query13	1686	596	460	460
query14	9550	5978	5863	5863
query15	220	203	178	178
query16	7693	646	456	456
query17	1176	736	625	625
query18	2036	429	339	339
query19	203	251	169	169
query20	139	125	123	123
query21	214	136	116	116
query22	4074	4229	4044	4044
query23	33744	33084	32885	32885
query24	8141	2379	2461	2379
query25	581	528	468	468
query26	945	276	172	172
query27	2693	507	359	359
query28	4346	2268	2221	2221
query29	711	604	507	507
query30	296	225	200	200
query31	935	807	759	759
query32	89	79	83	79
query33	566	430	363	363
query34	799	858	523	523
query35	806	833	772	772
query36	957	1003	942	942
query37	135	117	93	93
query38	3992	4049	3992	3992
query39	1520	1426	1433	1426
query40	230	138	130	130
query41	64	65	63	63
query42	132	122	120	120
query43	529	511	462	462
query44	1390	886	869	869
query45	187	180	169	169
query46	888	1045	666	666
query47	1760	1781	1737	1737
query48	401	439	340	340
query49	735	513	423	423
query50	667	674	415	415
query51	4171	4081	4193	4081
query52	118	118	105	105
query53	249	282	209	209
query54	616	600	542	542
query55	92	90	90	90
query56	344	346	326	326
query57	1196	1225	1131	1131
query58	291	276	273	273
query59	2562	2615	2594	2594
query60	362	353	350	350
query61	168	156	159	156
query62	835	743	642	642
query63	231	207	204	204
query64	3757	1136	841	841
query65	4293	4216	4221	4216
query66	953	439	344	344
query67	15698	15296	15073	15073
query68	9697	906	589	589
query69	493	340	294	294
query70	1356	1155	1186	1155
query71	590	357	326	326
query72	5895	5054	5054	5054
query73	751	657	364	364
query74	8850	9017	9042	9017
query75	4290	3099	2632	2632
query76	4493	1190	779	779
query77	1059	422	343	343
query78	9512	9712	8756	8756
query79	2130	856	610	610
query80	716	657	527	527
query81	475	265	228	228
query82	320	145	107	107
query83	290	273	252	252
query84	303	113	102	102
query85	835	454	429	429
query86	362	330	304	304
query87	4386	4352	4210	4210
query88	2870	2250	2260	2250
query89	419	324	300	300
query90	2078	235	229	229
query91	161	164	130	130
query92	93	79	73	73
query93	1795	1017	694	694
query94	704	405	319	319
query95	410	338	330	330
query96	495	589	282	282
query97	2619	2675	2596	2596
query98	244	221	218	218
query99	1442	1424	1268	1268
Total cold run time: 277311 ms
Total hot run time: 187125 ms

@doris-robot
Copy link

doris-robot commented Sep 4, 2025

ClickBench: Total hot run time: 33.01 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 4c387ac30fef7a8a458f2442dfa3cbabbce1cc15, data reload: false

query1	0.06	0.05	0.05
query2	0.09	0.05	0.05
query3	0.25	0.08	0.08
query4	1.61	0.12	0.12
query5	0.44	0.42	0.41
query6	1.19	0.64	0.68
query7	0.04	0.03	0.03
query8	0.06	0.05	0.05
query9	0.60	0.54	0.52
query10	0.58	0.59	0.58
query11	0.16	0.11	0.11
query12	0.15	0.12	0.11
query13	0.63	0.64	0.62
query14	0.80	0.85	0.85
query15	0.89	0.84	0.87
query16	0.40	0.41	0.39
query17	1.06	1.10	1.08
query18	0.22	0.21	0.20
query19	1.96	1.83	1.83
query20	0.01	0.01	0.01
query21	15.40	0.95	0.60
query22	0.80	1.11	0.71
query23	14.93	1.38	0.63
query24	6.80	0.92	1.56
query25	0.49	0.19	0.17
query26	0.55	0.16	0.13
query27	0.06	0.06	0.06
query28	10.45	0.98	0.42
query29	12.55	3.93	3.23
query30	3.09	3.04	3.02
query31	2.83	0.61	0.39
query32	3.25	0.56	0.47
query33	3.07	3.02	3.10
query34	16.09	5.50	4.88
query35	4.93	4.96	4.92
query36	0.71	0.51	0.50
query37	0.11	0.08	0.07
query38	0.06	0.04	0.05
query39	0.04	0.03	0.03
query40	0.18	0.16	0.14
query41	0.08	0.03	0.03
query42	0.04	0.03	0.03
query43	0.04	0.04	0.03
Total cold run time: 107.75 s
Total hot run time: 33.01 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 4, 2025

FE UT Coverage Report

Increment line coverage 14.29% (4/28) 🎉
Increment coverage report
Complete coverage report

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 4, 2025

FE Regression Coverage Report

Increment line coverage 78.57% (22/28) 🎉
Increment coverage report
Complete coverage report

@seawinde seawinde force-pushed the fix_mv_rewrite_fail_when_rewritten_plan_output_dff branch from 8515b54 to 3e3ecdf Compare September 19, 2025 14:10
@seawinde
Copy link
Contributor Author

seawinde commented Sep 19, 2025

run buildall

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 19, 2025

FE Regression Coverage Report

Increment line coverage 84.21% (32/38) 🎉
Increment coverage report
Complete coverage report

@seawinde
Copy link
Contributor Author

seawinde commented Sep 20, 2025

run buildall

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 20, 2025

FE Regression Coverage Report

Increment line coverage 82.05% (32/39) 🎉
Increment coverage report
Complete coverage report

@seawinde seawinde force-pushed the fix_mv_rewrite_fail_when_rewritten_plan_output_dff branch 2 times, most recently from 1f86cde to ef11d48 Compare September 22, 2025 11:34
@seawinde
Copy link
Contributor Author

seawinde commented Sep 22, 2025

run buildall

1 similar comment
@seawinde
Copy link
Contributor Author

seawinde commented Sep 22, 2025

run buildall

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 22, 2025

FE UT Coverage Report

Increment line coverage 35.71% (15/42) 🎉
Increment coverage report
Complete coverage report

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 22, 2025

FE Regression Coverage Report

Increment line coverage 83.33% (35/42) 🎉
Increment coverage report
Complete coverage report

@seawinde
Copy link
Contributor Author

seawinde commented Sep 23, 2025

run buildall

@seawinde seawinde force-pushed the fix_mv_rewrite_fail_when_rewritten_plan_output_dff branch from 2b97636 to b49e6f4 Compare September 23, 2025 01:51
@seawinde
Copy link
Contributor Author

seawinde commented Sep 23, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Sep 23, 2025

TPC-H: Total hot run time: 1475 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit b49e6f4eafc1d9ae068dea9434a6c6ef8ccfd70c, data reload: false

------ Round 1 ----------------------------------
q1	6708	27	20	20
q2	615	27	26	26
q3	881	17	16	16
q4	951	15	15	15
q5	2218	17	16	16
q6	225	14	13	13
q7	930	23	22	22
q8	1200	15	13	13
q9	16984	13	12	12
q10	4853	13	13	13
q11	458	22	19	19
q12	303	13	11	11
q13	17613	13	12	12
q14	240	12	11	11
q15	614	12	10	10
q16	989	992	942	942
q17	545	13	11	11
q18	7769	22	11	11
q19	1440	12	12	12
q20	365	358	237	237
q21	3704	21	22	21
q22	1047	12	12	12
Total cold run time: 70652 ms
Total hot run time: 1475 ms

----- Round 2, with runtime_filter_mode=off -----
q1	12	12	10	10
q2	20	20	20	20
q3	12	12	11	11
q4	12	11	12	11
q5	12	12	13	12
q6	13	12	11	11
q7	20	19	18	18
q8	12	11	11	11
q9	11	11	10	10
q10	11	10	10	10
q11	18	20	19	19
q12	10	10	11	10
q13	19	10	10	10
q14	10	10	13	10
q15	11	11	10	10
q16	1057	1078	1023	1023
q17	11	11	10	10
q18	10	11	10	10
q19	10	11	10	10
q20	1851	2003	1841	1841
q21	22	19	18	18
q22	11	10	10	10
Total cold run time: 3175 ms
Total hot run time: 3105 ms

@doris-robot
Copy link

doris-robot commented Sep 23, 2025

TPC-DS: Total hot run time: 2757 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 b49e6f4eafc1d9ae068dea9434a6c6ef8ccfd70c, data reload: false

query1	1105	20	12	12
query2	7101	17	16	16
query3	7635	11	11	11
query4	27160	12	13	12
query5	4426	13	12	12
query6	397	12	10	10
query7	5415	12	15	12
query8	361	20	20	20
query9	9190	12	9	9
query10	733	11	12	11
query11	15807	10	10	10
query12	176	11	9	9
query13	1706	11	9	9
query14	11229	15	12	12
query15	407	10	9	9
query16	7320	11	11	11
query17	2150	11	10	10
query18	2920	11	10	10
query19	236	10	10	10
query20	140	12	10	10
query21	247	12	11	11
query22	4071	11	9	9
query23	33946	15	10	10
query24	10820	14	11	11
query25	767	10	9	9
query26	1831	10	10	10
query27	3407	10	9	9
query28	6262	10	9	9
query29	1878	9	10	9
query30	630	10	9	9
query31	1753	9	10	9
query32	114	11	9	9
query33	1356	12	11	11
query34	1636	817	529	529
query35	1047	10	11	10
query36	981	10	10	10
query37	246	9	8	8
query38	3662	9	8	8
query39	1516	737	751	737
query40	316	11	14	11
query41	98	11	11	11
query42	149	11	10	10
query43	493	10	10	10
query44	1313	10	10	10
query45	378	11	11	11
query46	1174	10	9	9
query47	1840	10	10	10
query48	399	11	10	10
query49	1294	11	10	10
query50	764	10	9	9
query51	3933	10	10	10
query52	113	11	10	10
query53	247	12	11	11
query54	759	10	10	10
query55	94	11	9	9
query56	338	11	10	10
query57	1250	9	9	9
query58	380	10	10	10
query59	2684	9	10	9
query60	391	11	9	9
query61	229	11	9	9
query62	833	9	9	9
query63	284	11	11	11
query64	5668	10	9	9
query65	4207	12	10	10
query66	1637	13	11	11
query67	17126	27	8	8
query68	3610	10	8	8
query69	786	11	10	10
query70	1444	9	10	9
query71	448	391	336	336
query72	6871	10	9	9
query73	548	10	9	9
query74	10294	10	9	9
query75	3464	10	9	9
query76	2831	9	11	9
query77	836	12	12	12
query78	9755	18	10	10
query79	1053	11	13	11
query80	724	11	9	9
query81	677	10	11	10
query82	274	10	9	9
query83	310	11	11	11
query84	284	10	9	9
query85	1870	10	8	8
query86	450	9	10	9
query87	3940	9	8	8
query88	2817	12	9	9
query89	370	11	9	9
query90	2046	9	9	9
query91	168	8	9	8
query92	99	10	9	9
query93	1156	8	7	7
query94	785	8	9	8
query95	651	11	8	8
query96	432	11	9	9
query97	3084	10	9	9
query98	236	221	253	221
query99	1559	10	10	10
Total cold run time: 294047 ms
Total hot run time: 2757 ms

@doris-robot
Copy link

doris-robot commented Sep 23, 2025

ClickBench: Total hot run time: 0.08 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit b49e6f4eafc1d9ae068dea9434a6c6ef8ccfd70c, data reload: false

query1	0.06	0.02	0.01
query2	0.10	0.00	0.00
query3	0.28	0.01	0.02
query4	1.75	0.01	0.01
query5	0.30	0.01	0.01
query6	1.65	0.01	0.01
query7	0.05	0.00	0.00
query8	0.07	0.00	0.01
query9	0.60	0.00	0.00
query10	0.59	0.01	0.00
query11	0.18	0.00	0.00
query12	0.16	0.01	0.01
query13	0.64	0.00	0.00
query14	1.06	0.01	0.00
query15	0.90	0.01	0.01
query16	0.40	0.00	0.01
query17	1.07	0.00	0.00
query18	0.22	0.00	0.01
query19	2.27	0.00	0.00
query20	0.02	0.00	0.00
query21	15.94	0.00	0.00
query22	6.73	0.00	0.01
query23	15.74	0.00	0.00
query24	1.45	0.00	0.00
query25	0.23	0.00	0.00
query26	0.17	0.00	0.01
query27	0.12	0.00	0.00
query28	1.29	0.00	0.00
query29	13.08	0.00	0.00
query30	0.31	0.00	0.01
query31	2.28	0.01	0.01
query32	5.87	0.00	0.01
query33	4.34	0.01	0.00
query34	7.68	0.00	0.01
query35	6.16	0.00	0.00
query36	0.69	0.00	0.00
query37	0.12	0.01	0.00
query38	0.08	0.00	0.00
query39	0.05	0.00	0.00
query40	0.18	0.00	0.00
query41	0.10	0.01	0.00
query42	0.06	0.00	0.01
query43	0.05	0.00	0.01
Total cold run time: 95.09 s
Total hot run time: 0.08 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 23, 2025

FE UT Coverage Report

Increment line coverage 35.71% (15/42) 🎉
Increment coverage report
Complete coverage report

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 23, 2025

FE Regression Coverage Report

Increment line coverage 83.33% (35/42) 🎉
Increment coverage report
Complete coverage report

@seawinde
Copy link
Contributor Author

seawinde commented Sep 23, 2025

run buildall

1 similar comment
@seawinde
Copy link
Contributor Author

seawinde commented Sep 23, 2025

run buildall

@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 23, 2025

FE UT Coverage Report

Increment line coverage 57.14% (40/70) 🎉
Increment coverage report
Complete coverage report

1 similar comment
@hello-stephen
Copy link
Contributor

hello-stephen commented Sep 23, 2025

FE UT Coverage Report

Increment line coverage 57.14% (40/70) 🎉
Increment coverage report
Complete coverage report

@seawinde seawinde force-pushed the fix_mv_rewrite_fail_when_rewritten_plan_output_dff branch from 4c85ba7 to 650e487 Compare November 3, 2025 09:10
@seawinde
Copy link
Contributor Author

seawinde commented Nov 3, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Nov 3, 2025

ClickBench: Total hot run time: 28.07 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 650e4879693cf032060242f60166380987fc0e0e, data reload: false

query1	0.06	0.05	0.05
query2	0.09	0.06	0.05
query3	0.26	0.09	0.09
query4	1.60	0.12	0.11
query5	0.30	0.27	0.26
query6	1.17	0.66	0.66
query7	0.04	0.03	0.03
query8	0.06	0.04	0.05
query9	0.62	0.55	0.53
query10	0.60	0.60	0.60
query11	0.17	0.12	0.12
query12	0.16	0.13	0.12
query13	0.63	0.62	0.61
query14	1.03	1.02	1.02
query15	0.88	0.84	0.85
query16	0.39	0.41	0.41
query17	1.02	1.03	1.05
query18	0.24	0.21	0.22
query19	1.93	1.86	1.82
query20	0.02	0.01	0.01
query21	15.44	0.23	0.16
query22	4.86	0.08	0.05
query23	15.61	0.27	0.10
query24	1.90	0.47	0.68
query25	0.08	0.07	0.06
query26	0.15	0.14	0.13
query27	0.08	0.06	0.06
query28	4.72	1.18	0.93
query29	12.58	4.23	3.42
query30	0.29	0.14	0.12
query31	2.83	0.64	0.41
query32	3.25	0.56	0.48
query33	3.15	3.04	3.13
query34	15.88	5.18	4.55
query35	4.65	4.61	4.57
query36	0.67	0.51	0.51
query37	0.11	0.07	0.07
query38	0.07	0.05	0.04
query39	0.04	0.04	0.04
query40	0.18	0.14	0.15
query41	0.10	0.04	0.04
query42	0.05	0.04	0.03
query43	0.05	0.05	0.04
Total cold run time: 98.01 s
Total hot run time: 28.07 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Nov 3, 2025

FE UT Coverage Report

Increment line coverage 46.43% (39/84) 🎉
Increment coverage report
Complete coverage report

@github-actions github-actions bot added the approved Indicates a PR has been approved by one committer. label Nov 7, 2025
@github-actions
Copy link
Contributor

github-actions bot commented Nov 7, 2025

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

@github-actions
Copy link
Contributor

github-actions bot commented Nov 7, 2025

PR approved by anyone and no changes requested.

@morrySnow morrySnow merged commit c185cde into apache:master Nov 10, 2025
27 of 28 checks passed
seawinde added a commit to seawinde/doris that referenced this pull request Nov 11, 2025
@seawinde
[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group …
912f667 
…by key eliminate both happened (apache#55674)

if mv def is as fllowing:

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO
PROPERTIES (
    "replication_num" = "1"
)
as
select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(a.created_date, '%Y%m') month,
    a.created_date
from data_event a
join customer b on(a.cust_id=b.cust_id)
group by a.cust_id, a.event_type_id, a.bind_attr, month, a.created_date;

if query is as fllowing, the query pattern, the query can be optimized
by rule `ELIMINATE_CONST_JOIN_CONDITION` and
`ELIMINATE_GROUP_BY_KEY_BY_UNIFORM` this would cause rewrite fail by mv,
the pr fix this

select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(created_date, '%Y%m') month
from data_event a
inner join customer b on (a.cust_id = b.cust_id)
where a.cust_id = 410723002257
    and date_format(a.created_date, '%Y%m') = '202509'
group by a.cust_id, a.event_type_id, a.bind_attr, month;
@seawinde seawinde mentioned this pull request Nov 11, 2025
Merged
16 tasks
yiguolei pushed a commit to seawinde/doris that referenced this pull request Nov 13, 2025
@seawinde @yiguolei
[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group …
ea1f252 
…by key eliminate both happened (apache#55674)

if mv def is as fllowing:

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO
PROPERTIES (
    "replication_num" = "1"
)
as
select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(a.created_date, '%Y%m') month,
    a.created_date
from data_event a
join customer b on(a.cust_id=b.cust_id)
group by a.cust_id, a.event_type_id, a.bind_attr, month, a.created_date;

if query is as fllowing, the query pattern, the query can be optimized
by rule `ELIMINATE_CONST_JOIN_CONDITION` and
`ELIMINATE_GROUP_BY_KEY_BY_UNIFORM` this would cause rewrite fail by mv,
the pr fix this

select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(created_date, '%Y%m') month
from data_event a
inner join customer b on (a.cust_id = b.cust_id)
where a.cust_id = 410723002257
    and date_format(a.created_date, '%Y%m') = '202509'
group by a.cust_id, a.event_type_id, a.bind_attr, month;
yiguolei pushed a commit to seawinde/doris that referenced this pull request Nov 13, 2025
@seawinde @yiguolei
[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group …
daf8c0b 
…by key eliminate both happened (apache#55674)

if mv def is as fllowing:

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO
PROPERTIES (
    "replication_num" = "1"
)
as
select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(a.created_date, '%Y%m') month,
    a.created_date
from data_event a
join customer b on(a.cust_id=b.cust_id)
group by a.cust_id, a.event_type_id, a.bind_attr, month, a.created_date;

if query is as fllowing, the query pattern, the query can be optimized
by rule `ELIMINATE_CONST_JOIN_CONDITION` and
`ELIMINATE_GROUP_BY_KEY_BY_UNIFORM` this would cause rewrite fail by mv,
the pr fix this

select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(created_date, '%Y%m') month
from data_event a
inner join customer b on (a.cust_id = b.cust_id)
where a.cust_id = 410723002257
    and date_format(a.created_date, '%Y%m') = '202509'
group by a.cust_id, a.event_type_id, a.bind_attr, month;
yiguolei pushed a commit that referenced this pull request Nov 13, 2025
@seawinde
[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group …
6ffa518 
…by key eliminate both happened (#55674) (#57923)

### What problem does this PR solve?

pr: #55674
commitId: c185cde

Issue Number: close #xxx

Related PR: #xxx

Problem Summary:

### Release note

None

### Check List (For Author)

- Test <!-- At least one of them must be included. -->
    - [ ] 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 <!-- Add your reason?  -->

- Behavior changed:
    - [ ] No.
    - [ ] Yes. <!-- Explain the behavior change -->

- Does this need documentation?
    - [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
apache/doris-website#1214 -->

### Check List (For Reviewer who merge this PR)

- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
@seawinde seawinde mentioned this pull request Nov 14, 2025
Merged
16 tasks
wyxxxcat pushed a commit to wyxxxcat/doris that referenced this pull request Nov 18, 2025
@seawinde @wyxxxcat
[fix](mtmv) Fix rewrite fail when join conjuncts eliminate and group …
053123a 
…by key eliminate both happened (apache#55674)

if mv def is as fllowing:

CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO
PROPERTIES (
    "replication_num" = "1"
)
as
select 
    a.cust_id,
    a.event_type_id, 
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(a.created_date, '%Y%m') month,
    a.created_date
from data_event a
join customer b on(a.cust_id=b.cust_id)
group by a.cust_id, a.event_type_id, a.bind_attr, month, a.created_date;

if query is as fllowing, the query pattern, the query can be optimized
by rule `ELIMINATE_CONST_JOIN_CONDITION` and
`ELIMINATE_GROUP_BY_KEY_BY_UNIFORM` this would cause rewrite fail by mv,
the pr fix this

select
    a.cust_id,
    a.event_type_id,
    a.bind_attr,
    sum(sum_amount) sum_amount,
    date_format(created_date, '%Y%m') month
from data_event a
inner join customer b on (a.cust_id = b.cust_id)
where a.cust_id = 410723002257
    and date_format(a.created_date, '%Y%m') = '202509'
group by a.cust_id, a.event_type_id, a.bind_attr, month;
morrySnow pushed a commit that referenced this pull request Nov 26, 2025
@seawinde
[fix](mtmv) Fix materialized view regression test err (#58018)
f09290f 
This PR aims to fix several failing regression tests related to
Materialized Views (MTMV), ensuring the stability and correctness of the
MTMV feature.

The specific failing test cases addressed are:

nereids_rules_p0.mv.pre_rewrite.limit.query_with_limit
nested_materialized_view
mv_tpch_test

Related PR: 
#55066 
#55674
github-actions bot pushed a commit that referenced this pull request Nov 26, 2025
@seawinde
[fix](mtmv) Fix materialized view regression test err (#58018)
70bdf68 
This PR aims to fix several failing regression tests related to
Materialized Views (MTMV), ensuring the stability and correctness of the
MTMV feature.

The specific failing test cases addressed are:

nereids_rules_p0.mv.pre_rewrite.limit.query_with_limit
nested_materialized_view
mv_tpch_test

Related PR: 
#55066 
#55674
nagisa-kunhah pushed a commit to nagisa-kunhah/doris that referenced this pull request Dec 14, 2025
@seawinde @nagisa-kunhah
[fix](mtmv) Fix materialized view regression test err (apache#58018)
be30bd5 
This PR aims to fix several failing regression tests related to
Materialized Views (MTMV), ensuring the stability and correctness of the
MTMV feature.

The specific failing test cases addressed are:

nereids_rules_p0.mv.pre_rewrite.limit.query_with_limit
nested_materialized_view
mv_tpch_test

Related PR: 
apache#55066 
apache#55674
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@morrySnow morrySnow morrySnow approved these changes

+1 more reviewer

@zddr zddr zddr approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

approved Indicates a PR has been approved by one committer. dev/4.0.2-merged reviewed

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@seawinde @Thearas @doris-robot @hello-stephen @zddr @morrySnow @yiguolei