Skip to content

branch-3.1: [improvement](nereids)Support GROUP BY ... WITH ROLLUP syntax #51948 #51979

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 1 commit into from
Jun 20, 2025
Merged

branch-3.1: [improvement](nereids)Support GROUP BY ... WITH ROLLUP syntax #51948 #51979

morrySnow merged 1 commit into from
Jun 20, 2025

Conversation

@github-actions
Copy link
Contributor

@github-actions github-actions bot commented Jun 19, 2025

Cherry-picked from #51948

@Jibing-Li
[improvement](nereids)Support GROUP BY ... WITH ROLLUP syntax (#51948)
9314103 
### What problem does this PR solve?

Support GROUP BY ... WITH ROLLUP syntax

mysql> SELECT    year,   month,  SUM(amount) AS total_amount FROM sales GROUP BY year, month WITH ROLLUP order by year desc, month desc;
+------+-------+--------------+
| year | month | total_amount |
+------+-------+--------------+
| 2024 |     1 |      3400.00 |
| 2024 |  NULL |      3400.00 |
| 2023 |     2 |      4000.00 |
| 2023 |     1 |      3000.00 |
| 2023 |  NULL |      7000.00 |
| NULL |  NULL |     10400.00 |
+------+-------+--------------+
6 rows in set (0.02 sec)
@github-actions github-actions bot requested a review from morrySnow as a code owner June 19, 2025 12:24
@Thearas
Copy link
Contributor

Thearas commented Jun 19, 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?

@dataroaring dataroaring reopened this Jun 19, 2025
@Thearas
Copy link
Contributor

Thearas commented Jun 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

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

------ Round 1 ----------------------------------
q1	17606	6714	6579	6579
q2	2073	170	164	164
q3	10552	1098	1175	1098
q4	10537	730	743	730
q5	7742	2846	2863	2846
q6	214	130	130	130
q7	955	626	605	605
q8	9350	1943	2020	1943
q9	6571	6402	6402	6402
q10	7030	2259	2262	2259
q11	486	271	265	265
q12	389	211	205	205
q13	17784	2973	2988	2973
q14	227	209	211	209
q15	510	473	462	462
q16	502	382	378	378
q17	975	573	571	571
q18	7218	6639	6786	6639
q19	1389	1110	1032	1032
q20	470	205	215	205
q21	4027	3124	3127	3124
q22	1097	999	986	986
Total cold run time: 107704 ms
Total hot run time: 39805 ms

----- Round 2, with runtime_filter_mode=off -----
q1	7343	6563	6536	6536
q2	323	232	239	232
q3	2916	2751	2863	2751
q4	2056	1772	1734	1734
q5	5762	5721	5755	5721
q6	208	127	131	127
q7	2205	1863	1791	1791
q8	3343	3533	3531	3531
q9	8993	8787	8914	8787
q10	3554	3531	3560	3531
q11	588	486	508	486
q12	790	606	600	600
q13	8232	3118	3199	3118
q14	289	280	277	277
q15	503	459	457	457
q16	496	434	437	434
q17	1830	1625	1635	1625
q18	8148	7923	7813	7813
q19	1672	1553	1550	1550
q20	2096	1860	1813	1813
q21	5090	5052	4896	4896
q22	1145	1066	1037	1037
Total cold run time: 67582 ms
Total hot run time: 58847 ms

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

TPC-DS: Total hot run time: 196158 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 9314103d5a3838a5fc19321f24b6541ad23ed149, data reload: false

query1	1265	908	903	903
query2	6226	1927	1818	1818
query3	10820	4197	4381	4197
query4	61510	28401	23582	23582
query5	5221	456	453	453
query6	414	183	200	183
query7	5500	311	304	304
query8	306	211	216	211
query9	8729	2560	2553	2553
query10	449	264	268	264
query11	17680	15497	15669	15497
query12	173	101	105	101
query13	1448	461	435	435
query14	10523	6949	6689	6689
query15	191	183	183	183
query16	7136	416	477	416
query17	1180	588	592	588
query18	1939	325	317	317
query19	216	166	155	155
query20	121	111	111	111
query21	198	106	103	103
query22	4747	4426	4447	4426
query23	34252	34201	33841	33841
query24	6114	2941	2975	2941
query25	535	443	429	429
query26	657	167	167	167
query27	1855	360	350	350
query28	4082	2161	2140	2140
query29	710	467	475	467
query30	229	156	146	146
query31	958	818	815	815
query32	70	54	55	54
query33	419	294	279	279
query34	883	517	521	517
query35	831	706	742	706
query36	1069	928	954	928
query37	116	64	68	64
query38	4019	3958	3962	3958
query39	1555	1453	1465	1453
query40	201	99	95	95
query41	50	48	49	48
query42	116	102	99	99
query43	525	480	484	480
query44	1151	803	791	791
query45	188	171	169	169
query46	1166	755	743	743
query47	2008	1917	1923	1917
query48	486	388	409	388
query49	730	399	386	386
query50	867	430	441	430
query51	7471	7320	7314	7314
query52	99	93	95	93
query53	266	189	184	184
query54	603	474	454	454
query55	77	84	81	81
query56	258	265	252	252
query57	1349	1197	1196	1196
query58	238	213	216	213
query59	3243	2949	2971	2949
query60	297	258	271	258
query61	110	155	128	128
query62	795	691	694	691
query63	222	194	195	194
query64	1369	696	615	615
query65	3248	3199	3166	3166
query66	705	301	294	294
query67	15811	15544	15634	15544
query68	4246	574	561	561
query69	456	260	263	260
query70	1137	1065	1123	1065
query71	343	246	258	246
query72	6337	4025	4105	4025
query73	757	348	357	348
query74	10557	9324	8938	8938
query75	3360	2638	2661	2638
query76	2000	1050	1129	1050
query77	498	278	261	261
query78	10415	9587	9552	9552
query79	1407	587	583	583
query80	863	425	429	425
query81	496	218	218	218
query82	1194	89	83	83
query83	161	146	144	144
query84	281	81	78	78
query85	828	307	293	293
query86	328	306	300	300
query87	4357	4211	4200	4200
query88	3794	2405	2384	2384
query89	417	290	295	290
query90	2016	182	180	180
query91	177	149	154	149
query92	60	49	48	48
query93	1843	543	556	543
query94	718	288	270	270
query95	354	258	259	258
query96	602	285	276	276
query97	3322	3103	3129	3103
query98	205	205	193	193
query99	1789	1290	1280	1280
Total cold run time: 313313 ms
Total hot run time: 196158 ms

@doris-robot
Copy link

doris-robot commented Jun 19, 2025

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

query1	0.03	0.03	0.03
query2	0.07	0.03	0.03
query3	0.23	0.07	0.07
query4	1.61	0.10	0.10
query5	0.52	0.50	0.52
query6	1.13	0.73	0.72
query7	0.02	0.03	0.02
query8	0.04	0.04	0.03
query9	0.56	0.50	0.52
query10	0.55	0.56	0.56
query11	0.15	0.10	0.10
query12	0.14	0.11	0.11
query13	0.60	0.60	0.59
query14	0.78	0.77	0.81
query15	0.85	0.82	0.82
query16	0.36	0.38	0.36
query17	0.99	1.01	1.00
query18	0.24	0.21	0.20
query19	1.92	1.88	1.83
query20	0.01	0.01	0.02
query21	15.39	0.57	0.57
query22	2.22	2.16	1.62
query23	16.90	0.84	0.96
query24	3.56	1.19	1.59
query25	0.32	0.14	0.19
query26	0.39	0.14	0.13
query27	0.04	0.04	0.03
query28	9.37	0.54	0.47
query29	12.58	3.24	3.25
query30	0.25	0.06	0.07
query31	2.84	0.40	0.38
query32	3.24	0.45	0.46
query33	2.97	2.98	3.06
query34	16.98	4.42	4.45
query35	4.53	4.51	4.51
query36	0.67	0.47	0.50
query37	0.10	0.06	0.06
query38	0.05	0.03	0.03
query39	0.03	0.03	0.02
query40	0.15	0.13	0.13
query41	0.07	0.03	0.03
query42	0.04	0.03	0.02
query43	0.04	0.03	0.02
Total cold run time: 103.53 s
Total hot run time: 30.16 s

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

github-actions bot commented Jun 20, 2025

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

@github-actions
Copy link
Contributor Author

github-actions bot commented Jun 20, 2025

PR approved by anyone and no changes requested.

@morrySnow morrySnow merged commit 2e8972d into branch-3.1 Jun 20, 2025
22 of 24 checks passed
@morrySnow morrySnow deleted the auto-pick-51948-branch-3.1 branch June 20, 2025 03:12
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

Assignees

No one assigned

Labels

approved Indicates a PR has been approved by one committer. reviewed

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

6 participants

@Thearas @doris-robot @morrySnow @dataroaring @Jibing-Li