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