You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "924060929 (via GitHub)" <gi...@apache.org> on 2024/06/11 09:35:55 UTC

[PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

924060929 opened a new pull request, #36128:
URL: https://github.com/apache/doris/pull/36128

   ## Proposed changes
   
   Backend can process distinct aggregate by two method:
   1. use group by key to process distinct, I call it: external distinct of aggregate function
   2. use `multi_distinct_xxx` to process distinct, in the corresponding aggregate function, I call it: internal distinct of aggregate function
   
   
   This sql should process distinct by the multi distinct function when use four phase aggregation:
   The first two phase use group by key: id,  field1 and field2.
   The last two phase use group by key: id. the group by key can not process distinct in the aggregate function, so we should use internal distinct method in four phase aggregate function, if contains multi distinct column(e.g. field1 and field2) 
   
   ```sql
   select /*+SET_VAR(disable_nereids_rules='TWO_PHASE_AGGREGATE_SINGLE_DISTINCT_TO_MULTI,TWO_PHASE_AGGREGATE_WITH_MULTI_DISTINCT,THREE_PHASE_AGGREGATE_WITH_DISTINCT,THREE_PHASE_AGGREGATE_WITH_COUNT_DISTINCT_MULTI')*/
     id,
     count(distinct field1),
     count(distinct field2)
   from agg_4_phase_tbl2
   group by id
   order by id
   ```
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160263885

   Thank you for your contribution to Apache Doris.
   Don't know what should be done next? See [How to process your PR](https://cwiki.apache.org/confluence/display/DORIS/How+to+process+your+PR)
   
   Since 2024-03-18, the Document has been moved to [doris-website](https://github.com/apache/doris-website).
   See [Doris Document](https://cwiki.apache.org/confluence/display/DORIS/Doris+Document).


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "924060929 (via GitHub)" <gi...@apache.org>.
924060929 commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160268923

   run buildall


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160397546

   
   <details>
   <summary>ClickBench: <b>Total hot run time: 30.91 s</b></summary>
   
   ```
   machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
   scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
   ClickBench test result on commit b8910fa0b0eb7043d2bed221332d54f0ac7bdfb9, data reload: false
   
   query1	0.04	0.03	0.04
   query2	0.08	0.04	0.04
   query3	0.22	0.05	0.05
   query4	1.68	0.07	0.08
   query5	0.51	0.48	0.49
   query6	1.13	0.73	0.73
   query7	0.02	0.02	0.02
   query8	0.04	0.04	0.04
   query9	0.54	0.50	0.49
   query10	0.55	0.57	0.54
   query11	0.15	0.12	0.11
   query12	0.14	0.12	0.12
   query13	0.60	0.59	0.58
   query14	0.76	0.79	0.79
   query15	0.83	0.81	0.81
   query16	0.34	0.36	0.37
   query17	1.02	1.02	0.99
   query18	0.21	0.29	0.23
   query19	1.76	1.67	1.83
   query20	0.01	0.01	0.01
   query21	15.41	0.66	0.65
   query22	3.63	7.25	2.36
   query23	18.32	1.34	1.25
   query24	2.06	0.24	0.21
   query25	0.14	0.08	0.08
   query26	0.27	0.18	0.17
   query27	0.08	0.08	0.08
   query28	13.29	1.01	0.99
   query29	12.58	3.34	3.31
   query30	0.26	0.06	0.05
   query31	2.86	0.39	0.39
   query32	3.28	0.47	0.47
   query33	2.88	2.93	2.84
   query34	17.32	4.38	4.39
   query35	4.43	4.48	4.50
   query36	0.65	0.45	0.47
   query37	0.19	0.15	0.15
   query38	0.16	0.16	0.15
   query39	0.05	0.04	0.03
   query40	0.17	0.14	0.14
   query41	0.10	0.04	0.05
   query42	0.06	0.04	0.05
   query43	0.04	0.04	0.04
   Total cold run time: 108.86 s
   Total hot run time: 30.91 s
   ```
   </details>
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160342518

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160342622

   PR approved by anyone and no changes requested.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "924060929 (via GitHub)" <gi...@apache.org>.
924060929 merged PR #36128:
URL: https://github.com/apache/doris/pull/36128


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160367551

   
   <details>
   <summary>TPC-H: <b>Total hot run time: 39472 ms</b></summary>
   
   ```
   machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
   scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
   Tpch sf100 test result on commit b8910fa0b0eb7043d2bed221332d54f0ac7bdfb9, data reload: false
   
   ------ Round 1 ----------------------------------
   q1	17636	4375	4293	4293
   q2	2021	195	193	193
   q3	10473	1063	1130	1063
   q4	10213	832	749	749
   q5	7501	2668	2613	2613
   q6	219	136	134	134
   q7	949	606	587	587
   q8	9205	2061	2038	2038
   q9	8791	6478	6511	6478
   q10	8870	3744	3695	3695
   q11	454	233	236	233
   q12	464	241	226	226
   q13	17777	2969	2966	2966
   q14	277	214	218	214
   q15	523	482	474	474
   q16	524	369	376	369
   q17	965	691	759	691
   q18	7912	7410	7307	7307
   q19	8061	1452	1475	1452
   q20	665	309	311	309
   q21	4941	3064	3079	3064
   q22	376	324	324	324
   Total cold run time: 118817 ms
   Total hot run time: 39472 ms
   
   ----- Round 2, with runtime_filter_mode=off -----
   q1	4413	4271	4452	4271
   q2	362	260	262	260
   q3	2941	2901	2854	2854
   q4	1966	1656	1716	1656
   q5	5558	5456	5401	5401
   q6	237	133	129	129
   q7	2180	1882	1836	1836
   q8	3295	3403	3424	3403
   q9	8600	8602	8827	8602
   q10	4048	3879	3766	3766
   q11	576	501	484	484
   q12	817	656	622	622
   q13	16316	3133	3118	3118
   q14	298	292	265	265
   q15	538	495	481	481
   q16	497	446	437	437
   q17	1807	1503	1520	1503
   q18	8145	7933	7742	7742
   q19	1821	1518	1698	1518
   q20	3077	1935	1846	1846
   q21	8950	4987	4830	4830
   q22	607	530	513	513
   Total cold run time: 77049 ms
   Total hot run time: 55537 ms
   ```
   </details>
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org


Re: [PR] [fix](Nereids) fix four phase aggregation compute wrong result [doris]

Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #36128:
URL: https://github.com/apache/doris/pull/36128#issuecomment-2160387623

   
   <details>
   <summary>TPC-DS: <b>Total hot run time: 173198 ms</b></summary>
   
   ```
   machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
   scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
   TPC-DS sf100 test result on commit b8910fa0b0eb7043d2bed221332d54f0ac7bdfb9, data reload: false
   
   query1	925	384	373	373
   query2	6456	2338	2336	2336
   query3	6632	203	208	203
   query4	19609	17593	17401	17401
   query5	3632	455	449	449
   query6	235	157	153	153
   query7	4576	295	286	286
   query8	309	318	303	303
   query9	8580	2397	2376	2376
   query10	567	308	295	295
   query11	10467	10013	9929	9929
   query12	120	89	81	81
   query13	1644	361	361	361
   query14	9660	7620	6796	6796
   query15	237	184	186	184
   query16	7736	266	264	264
   query17	1672	533	510	510
   query18	1945	264	269	264
   query19	196	151	151	151
   query20	87	82	79	79
   query21	212	130	127	127
   query22	4284	4135	4083	4083
   query23	33747	33715	33875	33715
   query24	11232	2823	2946	2823
   query25	623	363	357	357
   query26	744	152	153	152
   query27	2357	327	315	315
   query28	6320	2136	2126	2126
   query29	864	619	626	619
   query30	237	156	160	156
   query31	974	762	740	740
   query32	87	51	57	51
   query33	771	278	286	278
   query34	1059	491	500	491
   query35	769	665	606	606
   query36	1158	987	1001	987
   query37	144	75	76	75
   query38	2918	2864	2849	2849
   query39	882	827	840	827
   query40	220	123	127	123
   query41	58	51	52	51
   query42	112	99	103	99
   query43	612	543	539	539
   query44	1205	719	715	715
   query45	198	168	170	168
   query46	1098	733	711	711
   query47	1878	1830	1780	1780
   query48	376	287	296	287
   query49	834	403	424	403
   query50	768	388	394	388
   query51	6895	6753	6689	6689
   query52	96	88	89	88
   query53	352	292	290	290
   query54	875	462	425	425
   query55	76	70	70	70
   query56	280	254	252	252
   query57	1115	1030	1073	1030
   query58	259	241	260	241
   query59	3361	3176	3055	3055
   query60	287	256	265	256
   query61	91	87	94	87
   query62	603	439	447	439
   query63	315	292	291	291
   query64	8527	2319	1733	1733
   query65	3203	3081	3078	3078
   query66	740	336	326	326
   query67	15427	14970	14792	14792
   query68	4483	535	555	535
   query69	505	355	358	355
   query70	1171	1130	1138	1130
   query71	412	279	271	271
   query72	7142	5635	5386	5386
   query73	742	324	322	322
   query74	5870	5538	5536	5536
   query75	3310	2669	2650	2650
   query76	2139	946	917	917
   query77	421	288	291	288
   query78	10234	9768	9740	9740
   query79	2269	523	515	515
   query80	2624	455	447	447
   query81	568	217	217	217
   query82	920	114	99	99
   query83	332	155	157	155
   query84	266	89	81	81
   query85	2033	277	263	263
   query86	491	318	313	313
   query87	3253	3117	3058	3058
   query88	3864	2382	2371	2371
   query89	489	399	394	394
   query90	1701	191	187	187
   query91	126	97	154	97
   query92	60	54	48	48
   query93	2438	503	496	496
   query94	1110	178	180	178
   query95	398	309	308	308
   query96	594	270	263	263
   query97	3181	2977	3047	2977
   query98	213	197	192	192
   query99	1363	832	856	832
   Total cold run time: 269250 ms
   Total hot run time: 173198 ms
   ```
   </details>
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org