Skip to content

branch-3.1: [fix](nereids) fix rewrite avg to sum / count change nullable #53787 #53875

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 2 commits into from
Jul 25, 2025
Merged

branch-3.1: [fix](nereids) fix rewrite avg to sum / count change nullable #53787 #53875

morrySnow merged 2 commits into from
Jul 25, 2025

Conversation

@yujun777
Copy link
Contributor

@yujun777 yujun777 commented Jul 24, 2025

cherry pick from #53787

@yujun777
[fix](nereids) fix rewrite avg to sum / count change nullable (apache…
c7af1c9 
…#53787)

### What problem does this PR solve?

for sql 
select avg(distinct a), sum(distinct b) from t group by c
if "a" is not nullable, then "avg" is not nullable,

AvgDistinctToSumDivCount will rewrite "avg" to
"sum(distinct a) / count(distinct a)", but the divide "/" is nullable,

then AdjustNullable will throw exception for this changed nullable
(introduced by apache#52748):
"AdjustNullable convert slot avg(..)apache#10 from not-nullable to nullable. You can disable check by set fe_debug = false."

need add a nonNullable function to make the rewritten expression not-nullable.
@yujun777 yujun777 requested a review from morrySnow as a code owner July 24, 2025 22:39
@yujun777
Copy link
Contributor Author

yujun777 commented Jul 24, 2025

run buildall

@Thearas
Copy link
Contributor

Thearas commented Jul 24, 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?

@yujun777
Copy link
Contributor Author

yujun777 commented Jul 24, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Jul 24, 2025

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

------ Round 1 ----------------------------------
q1	17592	5437	5576	5437
q2	2046	283	174	174
q3	10562	1246	765	765
q4	10216	867	450	450
q5	8331	2358	2141	2141
q6	178	160	132	132
q7	891	757	619	619
q8	9328	1410	1189	1189
q9	5310	4898	4868	4868
q10	6777	2242	1815	1815
q11	485	272	260	260
q12	338	356	212	212
q13	17794	3599	3002	3002
q14	234	218	207	207
q15	509	467	469	467
q16	439	445	372	372
q17	613	848	364	364
q18	6928	6237	6315	6237
q19	1728	942	564	564
q20	330	339	197	197
q21	2951	2211	2039	2039
q22	1041	1007	968	968
Total cold run time: 104621 ms
Total hot run time: 32479 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5562	5469	5477	5469
q2	233	325	233	233
q3	2212	2655	2272	2272
q4	1374	1823	1357	1357
q5	4379	4841	4946	4841
q6	171	169	129	129
q7	2065	1954	1841	1841
q8	2556	2776	2662	2662
q9	7184	7174	7193	7174
q10	3007	3340	2772	2772
q11	560	500	490	490
q12	664	730	563	563
q13	3416	3815	3181	3181
q14	270	286	270	270
q15	512	455	464	455
q16	457	488	437	437
q17	1223	1729	1241	1241
q18	7527	7394	7298	7298
q19	877	1153	1082	1082
q20	2011	2053	1892	1892
q21	5353	4874	4616	4616
q22	1118	1037	980	980
Total cold run time: 52731 ms
Total hot run time: 51255 ms

@doris-robot
Copy link

doris-robot commented Jul 24, 2025

TPC-DS: Total hot run time: 196599 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 0dcd9d8a6771066b76964204a6c95e6b20331b30, data reload: false

query1	1285	929	890	890
query2	6217	1832	1874	1832
query3	10929	4500	4478	4478
query4	33026	23836	23975	23836
query5	4258	627	458	458
query6	293	214	182	182
query7	4009	492	327	327
query8	294	250	239	239
query9	9546	2613	2609	2609
query10	486	324	258	258
query11	18303	15358	15020	15020
query12	167	106	99	99
query13	1551	541	398	398
query14	9425	7253	6829	6829
query15	232	198	189	189
query16	8033	656	456	456
query17	1573	771	593	593
query18	2118	405	322	322
query19	235	200	156	156
query20	119	115	119	115
query21	206	128	106	106
query22	4685	4658	4421	4421
query23	35108	34182	34115	34115
query24	7224	2698	2674	2674
query25	506	486	430	430
query26	781	274	168	168
query27	2085	472	360	360
query28	5082	2190	2183	2183
query29	588	572	488	488
query30	246	201	164	164
query31	1023	903	839	839
query32	81	61	61	61
query33	490	363	321	321
query34	781	850	524	524
query35	777	827	752	752
query36	1036	1086	978	978
query37	113	96	77	77
query38	4031	3988	3978	3978
query39	1523	1509	1464	1464
query40	211	117	105	105
query41	51	52	54	52
query42	125	105	105	105
query43	516	532	496	496
query44	1323	836	840	836
query45	187	180	175	175
query46	875	1059	688	688
query47	1953	1997	1864	1864
query48	411	444	362	362
query49	782	527	430	430
query50	697	719	436	436
query51	7322	7356	7217	7217
query52	107	105	94	94
query53	227	264	189	189
query54	542	557	484	484
query55	81	78	82	78
query56	274	299	264	264
query57	1248	1263	1195	1195
query58	244	223	224	223
query59	3115	3229	3059	3059
query60	292	276	269	269
query61	144	120	121	120
query62	814	760	737	737
query63	230	195	197	195
query64	3598	986	637	637
query65	3346	3289	3301	3289
query66	825	414	299	299
query67	16203	15888	15409	15409
query68	8110	834	532	532
query69	498	302	262	262
query70	1143	1120	1125	1120
query71	391	288	263	263
query72	5821	3749	3810	3749
query73	633	748	350	350
query74	10500	9084	9025	9025
query75	3189	3117	2673	2673
query76	3072	1173	767	767
query77	499	360	276	276
query78	10332	10518	9513	9513
query79	3045	880	604	604
query80	720	518	427	427
query81	493	256	219	219
query82	943	119	88	88
query83	168	156	143	143
query84	243	98	81	81
query85	795	351	305	305
query86	400	312	299	299
query87	4263	4332	4192	4192
query88	4905	2378	2371	2371
query89	414	334	294	294
query90	1779	188	198	188
query91	130	141	110	110
query92	60	52	50	50
query93	2869	874	546	546
query94	714	403	308	308
query95	338	270	262	262
query96	485	600	282	282
query97	3181	3281	3123	3123
query98	241	203	195	195
query99	1333	1364	1286	1286
Total cold run time: 295915 ms
Total hot run time: 196599 ms

@doris-robot
Copy link

doris-robot commented Jul 24, 2025

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

query1	0.04	0.04	0.03
query2	0.07	0.03	0.03
query3	0.24	0.07	0.07
query4	1.61	0.11	0.11
query5	0.53	0.52	0.50
query6	1.12	0.73	0.72
query7	0.02	0.02	0.02
query8	0.04	0.04	0.03
query9	0.56	0.51	0.51
query10	0.57	0.55	0.54
query11	0.14	0.10	0.09
query12	0.13	0.11	0.10
query13	0.60	0.59	0.59
query14	0.78	0.80	0.78
query15	0.85	0.83	0.83
query16	0.37	0.37	0.39
query17	1.03	1.08	1.08
query18	0.24	0.22	0.22
query19	1.84	1.87	1.86
query20	0.02	0.01	0.01
query21	15.40	0.86	0.60
query22	0.74	0.82	0.67
query23	15.12	1.43	0.53
query24	3.10	0.98	1.13
query25	0.25	0.23	0.08
query26	0.23	0.14	0.13
query27	0.07	0.05	0.04
query28	14.18	0.96	0.43
query29	12.62	3.94	3.26
query30	0.24	0.09	0.06
query31	2.83	0.57	0.38
query32	3.22	0.54	0.45
query33	2.97	2.98	3.02
query34	16.43	5.16	4.58
query35	4.52	4.49	4.61
query36	0.64	0.51	0.51
query37	0.09	0.06	0.05
query38	0.05	0.03	0.03
query39	0.03	0.03	0.02
query40	0.16	0.13	0.13
query41	0.08	0.03	0.02
query42	0.03	0.02	0.02
query43	0.03	0.03	0.03
Total cold run time: 103.83 s
Total hot run time: 28.96 s

@morrySnow morrySnow merged commit 21585b1 into apache:branch-3.1 Jul 25, 2025
22 checks passed
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

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

4 participants

@yujun777 @Thearas @doris-robot @morrySnow