doris
doris copied to clipboard
apache
[feature](function) support sequence function(alias of array_range), enhance both to handle datetimev2
Proposed changes
Issue Number: #30487
Doris：
mysql> select sequence(cast('2022-05-18 12:00:10' as datetimev2(0)), cast('2022-05-18 22:00:30' as datetimev2(0)), interval 4 hour);
+----------------------------------------------------------------------------------------------------------------------+
| array_range_hour_unit(cast('2022-05-18 12:00:10' as DATETIMEV2(0)), cast('2022-05-18 22:00:30' as DATETIMEV2(0)), 4) |
+----------------------------------------------------------------------------------------------------------------------+
| ["2022-05-18 12:00:10", "2022-05-18 16:00:10", "2022-05-18 20:00:10"] |
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select array_range(cast('2022-05-18 12:00:10' as datetimev2(0)), cast('2022-05-18 12:16:30' as datetimev2(0)), interval 5 minute);
+------------------------------------------------------------------------------------------------------------------------+
| array_range_minute_unit(cast('2022-05-18 12:00:10' as DATETIMEV2(0)), cast('2022-05-18 12:16:30' as DATETIMEV2(0)), 5) |
+------------------------------------------------------------------------------------------------------------------------+
| ["2022-05-18 12:00:10", "2022-05-18 12:05:10", "2022-05-18 12:10:10", "2022-05-18 12:15:10"] |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select sequence(cast('2022-35-38 12:00:10' as datetimev2(0)), cast('2022-05-18 22:00:30' as datetimev2(0)), interval 4 hour);
+----------------------------------------------------------------------------------------------------------------------+
| array_range_hour_unit(cast('2022-35-38 12:00:10' as DATETIMEV2(0)), cast('2022-05-18 22:00:30' as DATETIMEV2(0)), 4) |
+----------------------------------------------------------------------------------------------------------------------+
| NULL |
+----------------------------------------------------------------------------------------------------------------------+
Further comments
If this is a relatively large or complex change, kick off the discussion at [email protected] by explaining why you chose the solution you did and what alternatives you considered, etc...
Thank you for your contribution to Apache Doris. Don't know what should be done next? See How to process your PR
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
TPC-H: Total hot run time: 36636 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 02c51dff94a42609be1c0a3df49d074144187542, data reload: false
------ Round 1 ----------------------------------
q1 17645 4360 4408 4360
q2 2038 143 136 136
q3 10590 925 928 925
q4 4653 753 676 676
q5 7666 2729 2791 2729
q6 183 119 120 119
q7 1145 724 701 701
q8 9305 1972 1994 1972
q9 7199 6379 6313 6313
q10 8108 2395 2431 2395
q11 419 209 203 203
q12 764 275 269 269
q13 18016 3293 3307 3293
q14 270 251 245 245
q15 525 505 494 494
q16 500 420 403 403
q17 959 573 529 529
q18 6616 5876 6059 5876
q19 1549 1360 1345 1345
q20 583 339 347 339
q21 6701 3043 3018 3018
q22 802 296 302 296
Total cold run time: 106236 ms
Total hot run time: 36636 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4482 4445 4360 4360
q2 321 231 242 231
q3 2995 2883 2783 2783
q4 1841 1628 1661 1628
q5 5241 5242 5247 5242
q6 190 113 115 113
q7 2146 1769 1796 1769
q8 3111 3220 3204 3204
q9 8408 8326 8196 8196
q10 5860 3520 3540 3520
q11 533 468 469 468
q12 733 561 562 561
q13 13220 3095 3038 3038
q14 279 246 262 246
q15 523 493 487 487
q16 519 490 494 490
q17 1835 1685 1655 1655
q18 7986 7726 7544 7544
q19 7748 1484 1494 1484
q20 2126 1918 1892 1892
q21 4754 4603 4659 4603
q22 564 454 473 454
Total cold run time: 75415 ms
Total hot run time: 53968 ms
TeamCity be ut coverage result: Function Coverage: 36.16% (8628/23860) Line Coverage: 28.22% (70567/250095) Region Coverage: 27.24% (36410/133682) Branch Coverage: 24.02% (18646/77620) Coverage Report: http://coverage.selectdb-in.cc/coverage/02c51dff94a42609be1c0a3df49d074144187542_02c51dff94a42609be1c0a3df49d074144187542/report/index.html
TPC-DS: Total hot run time: 173707 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 02c51dff94a42609be1c0a3df49d074144187542, data reload: false
query1 936 344 334 334
query2 6536 2046 1889 1889
query3 6687 207 204 204
query4 31833 21992 21810 21810
query5 4209 433 359 359
query6 256 175 172 172
query7 4603 281 273 273
query8 241 175 174 174
query9 9008 2334 2320 2320
query10 412 214 227 214
query11 19068 15413 15202 15202
query12 129 77 79 77
query13 1624 432 415 415
query14 9099 6717 6826 6717
query15 233 178 181 178
query16 8060 261 242 242
query17 1873 542 503 503
query18 2110 264 269 264
query19 188 139 142 139
query20 81 78 76 76
query21 200 131 120 120
query22 4843 4613 4510 4510
query23 30760 29979 30053 29979
query24 9512 2748 2757 2748
query25 550 358 342 342
query26 713 145 158 145
query27 2218 299 294 294
query28 5823 1863 1842 1842
query29 872 622 606 606
query30 280 134 139 134
query31 924 714 707 707
query32 89 56 54 54
query33 543 230 225 225
query34 836 468 463 463
query35 832 785 748 748
query36 1054 967 932 932
query37 96 57 57 57
query38 3158 3132 3142 3132
query39 1307 1251 1256 1251
query40 186 99 92 92
query41 41 37 34 34
query42 106 96 97 96
query43 550 476 481 476
query44 1057 693 697 693
query45 195 182 174 174
query46 1051 633 651 633
query47 1582 1454 1508 1454
query48 431 357 360 357
query49 1073 288 289 288
query50 769 393 367 367
query51 5239 5164 5151 5151
query52 98 90 91 90
query53 339 283 270 270
query54 253 216 223 216
query55 79 75 77 75
query56 226 200 212 200
query57 968 903 894 894
query58 193 180 182 180
query59 2650 2418 2395 2395
query60 234 223 218 218
query61 87 93 84 84
query62 616 370 358 358
query63 297 267 261 261
query64 4843 3740 3552 3552
query65 3274 3217 3220 3217
query66 812 313 309 309
query67 14417 13883 14174 13883
query68 4412 517 538 517
query69 486 330 324 324
query70 1263 1256 1265 1256
query71 329 252 250 250
query72 6046 2844 2679 2679
query73 690 314 317 314
query74 6591 6206 6211 6206
query75 3048 2339 2294 2294
query76 2873 999 884 884
query77 381 232 227 227
query78 9029 8779 8503 8503
query79 4547 492 493 492
query80 1782 355 346 346
query81 537 195 197 195
query82 1401 80 83 80
query83 291 127 126 126
query84 280 84 82 82
query85 2026 347 332 332
query86 476 310 295 295
query87 3355 3220 3230 3220
query88 4553 2318 2321 2318
query89 463 357 353 353
query90 1979 166 168 166
query91 152 122 122 122
query92 53 44 44 44
query93 5523 498 457 457
query94 1290 173 175 173
query95 495 360 7963 360
query96 601 272 265 265
query97 4241 4099 4106 4099
query98 218 197 190 190
query99 1093 676 670 670
Total cold run time: 283966 ms
Total hot run time: 173707 ms
ClickBench: Total hot run time: 30.84 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 02c51dff94a42609be1c0a3df49d074144187542, data reload: false
query1 0.04 0.03 0.03
query2 0.05 0.03 0.03
query3 0.23 0.06 0.06
query4 1.68 0.11 0.11
query5 0.52 0.51 0.51
query6 1.19 0.64 0.66
query7 0.01 0.01 0.01
query8 0.04 0.02 0.02
query9 0.56 0.50 0.48
query10 0.55 0.55 0.54
query11 0.12 0.08 0.08
query12 0.10 0.09 0.10
query13 0.60 0.61 0.61
query14 0.81 0.78 0.80
query15 0.81 0.78 0.77
query16 0.38 0.38 0.40
query17 0.98 0.98 1.03
query18 0.22 0.22 0.27
query19 1.90 1.76 1.79
query20 0.01 0.01 0.01
query21 15.40 0.56 0.60
query22 2.82 2.75 1.80
query23 17.25 0.81 0.80
query24 2.46 1.11 0.92
query25 0.34 0.11 0.20
query26 0.58 0.14 0.15
query27 0.04 0.04 0.05
query28 12.22 0.85 0.84
query29 12.52 3.16 3.12
query30 0.63 0.55 0.53
query31 2.78 0.35 0.35
query32 3.36 0.48 0.48
query33 3.21 3.21 3.21
query34 15.95 4.30 4.28
query35 4.27 4.30 4.24
query36 1.13 1.04 1.05
query37 0.07 0.05 0.05
query38 0.04 0.03 0.03
query39 0.02 0.02 0.02
query40 0.16 0.12 0.12
query41 0.07 0.02 0.02
query42 0.03 0.02 0.01
query43 0.02 0.02 0.02
Total cold run time: 106.17 s
Total hot run time: 30.84 s
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
Load test result on commit 02c51dff94a42609be1c0a3df49d074144187542 with default session variables
Stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc: 59 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select: 13.6 seconds inserted 10000000 Rows, about 735K ops/s
TeamCity be ut coverage result: Function Coverage: 36.16% (8627/23860) Line Coverage: 28.21% (70573/250163) Region Coverage: 27.21% (36405/133784) Branch Coverage: 24.00% (18648/77688) Coverage Report: http://coverage.selectdb-in.cc/coverage/02e9b6359bf73591302374839b083da103e656d0_02e9b6359bf73591302374839b083da103e656d0/report/index.html
please replace picture with text in Desc for easy adding to git commit msg
TeamCity be ut coverage result: Function Coverage: 36.16% (8627/23860) Line Coverage: 28.21% (70564/250166) Region Coverage: 27.21% (36407/133792) Branch Coverage: 24.00% (18649/77688) Coverage Report: http://coverage.selectdb-in.cc/coverage/ac21d93841bc94540c1ff46d729329df876edd8e_ac21d93841bc94540c1ff46d729329df876edd8e/report/index.html
TeamCity be ut coverage result: Function Coverage: 35.71% (8556/23960) Line Coverage: 27.60% (69457/251672) Region Coverage: 26.72% (36015/134766) Branch Coverage: 23.54% (18422/78248) Coverage Report: http://coverage.selectdb-in.cc/coverage/f807adf69fce433bd4b1c429d5d64682515eb204_f807adf69fce433bd4b1c429d5d64682515eb204/report/index.html
TeamCity be ut coverage result: Function Coverage: 35.71% (8556/23960) Line Coverage: 27.60% (69463/251672) Region Coverage: 26.73% (36024/134766) Branch Coverage: 23.55% (18430/78248) Coverage Report: http://coverage.selectdb-in.cc/coverage/ed3026175df1910b6b1c378c1d429749205f344b_ed3026175df1910b6b1c378c1d429749205f344b/report/index.html
TPC-H: Total hot run time: 41674 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit ed3026175df1910b6b1c378c1d429749205f344b, data reload: false
------ Round 1 ----------------------------------
q1 18874 5072 4924 4924
q2 2041 148 135 135
q3 12085 1028 1022 1022
q4 4864 970 976 970
q5 7740 3211 3325 3211
q6 208 151 148 148
q7 1302 824 800 800
q8 9679 2116 2085 2085
q9 9147 6947 6801 6801
q10 8349 2668 2691 2668
q11 426 226 224 224
q12 773 346 348 346
q13 21499 3715 3708 3708
q14 317 260 256 256
q15 613 512 500 500
q16 477 401 412 401
q17 919 863 835 835
q18 7413 6535 6773 6535
q19 1526 1477 1481 1477
q20 627 342 348 342
q21 6857 3966 3947 3947
q22 867 350 339 339
Total cold run time: 116603 ms
Total hot run time: 41674 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4883 4842 4844 4842
q2 289 187 185 185
q3 3603 3574 3586 3574
q4 2568 2534 2536 2534
q5 5752 5775 5745 5745
q6 213 130 131 130
q7 2237 1661 1661 1661
q8 2995 3071 3105 3071
q9 8723 8690 8738 8690
q10 6784 4267 4233 4233
q11 533 378 366 366
q12 772 550 549 549
q13 4287 3429 3425 3425
q14 262 241 224 224
q15 592 509 505 505
q16 496 469 436 436
q17 1934 1611 1596 1596
q18 8264 7591 7710 7591
q19 1617 1618 1634 1618
q20 2117 1830 1831 1830
q21 6599 6190 6167 6167
q22 571 528 514 514
Total cold run time: 66091 ms
Total hot run time: 59486 ms
TPC-DS: Total hot run time: 176973 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 ed3026175df1910b6b1c378c1d429749205f344b, data reload: false
query1 945 352 341 341
query2 6541 1697 1731 1697
query3 6700 203 202 202
query4 23107 21171 21092 21092
query5 4307 383 380 380
query6 259 184 176 176
query7 4615 303 306 303
query8 259 193 197 193
query9 8412 2853 2857 2853
query10 424 231 236 231
query11 14945 14556 14676 14556
query12 137 87 85 85
query13 1723 422 424 422
query14 9077 7661 7710 7661
query15 210 189 188 188
query16 7412 260 253 253
query17 1390 559 548 548
query18 1964 277 259 259
query19 194 150 150 150
query20 89 84 87 84
query21 196 118 119 118
query22 4937 4712 4696 4696
query23 32457 31582 31440 31440
query24 12821 3484 3436 3436
query25 648 372 373 372
query26 1902 160 158 158
query27 3048 327 309 309
query28 6655 1830 1809 1809
query29 1182 625 619 619
query30 281 138 143 138
query31 952 752 756 752
query32 98 61 58 58
query33 725 233 226 226
query34 1069 493 499 493
query35 941 831 821 821
query36 976 918 897 897
query37 180 59 66 59
query38 3239 3176 3149 3149
query39 1372 1333 1310 1310
query40 283 106 105 105
query41 38 35 35 35
query42 107 98 100 98
query43 471 425 435 425
query44 1094 702 724 702
query45 197 187 172 172
query46 1050 789 779 779
query47 1674 1558 1522 1522
query48 420 349 356 349
query49 1271 316 307 307
query50 781 379 382 379
query51 4400 4335 4397 4335
query52 104 97 99 97
query53 397 302 315 302
query54 287 220 240 220
query55 90 90 83 83
query56 218 213 201 201
query57 1051 949 979 949
query58 219 197 196 196
query59 2243 2004 2133 2004
query60 237 219 217 217
query61 92 87 84 84
query62 600 381 373 373
query63 328 285 288 285
query64 6315 3110 3183 3110
query65 3291 3248 3259 3248
query66 1338 328 325 325
query67 14541 14295 14241 14241
query68 5083 553 539 539
query69 516 373 369 369
query70 1234 1215 1205 1205
query71 367 257 260 257
query72 6451 2805 2635 2635
query73 706 319 311 311
query74 6868 6500 6471 6471
query75 3178 2579 2559 2559
query76 3290 1174 1203 1174
query77 340 243 233 233
query78 9563 8864 8833 8833
query79 982 513 496 496
query80 540 364 342 342
query81 439 208 209 208
query82 157 83 85 83
query83 145 122 121 121
query84 223 80 81 80
query85 1041 384 342 342
query86 298 306 306 306
query87 3545 3260 3293 3260
query88 2797 2306 2305 2305
query89 433 350 379 350
query90 1968 166 166 166
query91 165 129 128 128
query92 55 52 53 52
query93 1019 534 506 506
query94 1097 179 181 179
query95 487 375 398 375
query96 586 266 265 265
query97 4434 4262 4259 4259
query98 228 211 206 206
query99 1101 681 684 681
Total cold run time: 268862 ms
Total hot run time: 176973 ms
ClickBench: Total hot run time: 30.64 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit ed3026175df1910b6b1c378c1d429749205f344b, data reload: false
query1 0.03 0.02 0.02
query2 0.08 0.03 0.02
query3 0.23 0.07 0.07
query4 1.63 0.07 0.11
query5 0.48 0.49 0.49
query6 1.34 0.61 0.60
query7 0.02 0.02 0.02
query8 0.04 0.02 0.02
query9 0.52 0.47 0.47
query10 0.49 0.49 0.49
query11 0.13 0.09 0.09
query12 0.12 0.10 0.10
query13 0.58 0.58 0.59
query14 0.76 0.78 0.79
query15 0.83 0.78 0.79
query16 0.33 0.33 0.34
query17 0.90 0.90 0.88
query18 0.20 0.16 0.16
query19 1.82 1.65 1.67
query20 0.01 0.00 0.01
query21 15.43 0.64 0.57
query22 3.11 3.28 2.13
query23 17.59 0.99 1.03
query24 2.23 0.34 0.22
query25 0.55 0.07 0.06
query26 0.16 0.13 0.14
query27 0.08 0.05 0.06
query28 12.09 0.85 0.86
query29 12.58 3.27 3.33
query30 0.55 0.49 0.49
query31 2.78 0.37 0.37
query32 3.29 0.48 0.48
query33 3.14 3.16 3.15
query34 15.35 4.52 4.53
query35 4.54 4.53 4.52
query36 1.08 0.95 0.95
query37 0.07 0.04 0.05
query38 0.04 0.03 0.03
query39 0.02 0.01 0.02
query40 0.17 0.15 0.15
query41 0.07 0.02 0.01
query42 0.02 0.01 0.02
query43 0.02 0.02 0.02
Total cold run time: 105.5 s
Total hot run time: 30.64 s
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
Load test result on commit ed3026175df1910b6b1c378c1d429749205f344b with default session variables
Stream load json: 19 seconds loaded 2358488459 Bytes, about 118 MB/s
Stream load orc: 60 seconds loaded 1101869774 Bytes, about 17 MB/s
Stream load parquet: 31 seconds loaded 861443392 Bytes, about 26 MB/s
Insert into select: 14.3 seconds inserted 10000000 Rows, about 699K ops/s
TeamCity be ut coverage result: Function Coverage: 35.73% (8542/23909) Line Coverage: 27.54% (69322/251720) Region Coverage: 26.68% (35939/134681) Branch Coverage: 23.50% (18377/78214) Coverage Report: http://coverage.selectdb-in.cc/coverage/bff8c7e20c08d93ee0764c1c3c82df42e1ece219_bff8c7e20c08d93ee0764c1c3c82df42e1ece219/report/index.html