doris
doris copied to clipboard
apache
[fix](multi-catalog) Disable string dictionary filtering when predicate express is not slot
Proposed changes
follow up https://github.com/apache/doris/pull/35335/
When the "case when ... then ... when ... then ... else" occurs, function_expr may not exist in the pushed down predicate, but the handling of null values is still problematic.
table data:
mysql> select o_orderpriority from test_string_dict_filter_orc;
+-----------------+
| o_orderpriority |
+-----------------+
| 5-LOW |
| 1-URGENT |
| 5-LOW |
| NULL |
| 5-LOW |
+-----------------+
before:
mysql> select count(o_orderpriority) from ( select (case when o_orderpriority = 'x' then '1' when o_orderpriority = 'y' then '2' else '0' end) as o_orderpriority from test_string_dict_filter_orc ) as A where o_orderpriority = '0';
+------------------------+
| count(o_orderpriority) |
+------------------------+
| 4 |
+------------------------+
after:
mysql> select count(o_orderpriority) from ( select (case when o_orderpriority = 'x' then '1' when o_orderpriority = 'y' then '2' else '0' end) as o_orderpriority from test_string_dict_filter_orc ) as A where o_orderpriority = '0';
+------------------------+
| count(o_orderpriority) |
+------------------------+
| 5 |
+------------------------+
Thank you for your contribution to Apache Doris. Don't know what should be done next? See How to process your PR
Since 2024-03-18, the Document has been moved to doris-website. See Doris Document.
![github-actions[bot] avatar](https://avatars.githubusercontent.com/in/15368?v=4 "Published by a pub.dev verified publisher"){kind=small}
TeamCity be ut coverage result: Function Coverage: 37.47% (9711/25916) Line Coverage: 28.73% (80614/280546) Region Coverage: 28.18% (41717/148048) Branch Coverage: 24.76% (21211/85660) Coverage Report: http://coverage.selectdb-in.cc/coverage/510a031741644e8c88eaf7a27008a2f6dc451373_510a031741644e8c88eaf7a27008a2f6dc451373/report/index.html
TPC-H: Total hot run time: 41065 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 510a031741644e8c88eaf7a27008a2f6dc451373, data reload: false
------ Round 1 ----------------------------------
q1 17571 7466 7291 7291
q2 2021 285 289 285
q3 12019 1065 1139 1065
q4 10595 854 852 852
q5 7708 3074 3056 3056
q6 238 155 147 147
q7 1018 605 611 605
q8 9351 1956 1905 1905
q9 6592 6420 6443 6420
q10 7065 2411 2408 2408
q11 457 240 243 240
q12 399 215 217 215
q13 17763 3004 3031 3004
q14 233 204 225 204
q15 569 513 508 508
q16 646 599 592 592
q17 961 507 486 486
q18 7188 6783 6673 6673
q19 1346 916 916 916
q20 486 187 185 185
q21 3966 3130 3025 3025
q22 1107 983 1018 983
Total cold run time: 109299 ms
Total hot run time: 41065 ms
----- Round 2, with runtime_filter_mode=off -----
q1 7319 7260 7193 7193
q2 336 230 227 227
q3 3014 2944 2967 2944
q4 2044 1809 1747 1747
q5 5740 5771 5788 5771
q6 239 146 146 146
q7 2276 1874 1787 1787
q8 3364 3519 3442 3442
q9 8888 8924 8847 8847
q10 3570 3580 3534 3534
q11 576 480 496 480
q12 803 634 678 634
q13 9535 3175 3198 3175
q14 301 285 270 270
q15 565 522 517 517
q16 662 640 639 639
q17 1848 1615 1582 1582
q18 8277 7948 7809 7809
q19 1713 1394 1508 1394
q20 2149 1911 1892 1892
q21 5532 5372 5442 5372
q22 1101 1054 1067 1054
Total cold run time: 69852 ms
Total hot run time: 60456 ms
TPC-DS: Total hot run time: 191765 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 510a031741644e8c88eaf7a27008a2f6dc451373, data reload: false
query1 936 389 398 389
query2 6260 2081 2017 2017
query3 8681 194 200 194
query4 33981 23627 23737 23627
query5 3498 487 469 469
query6 271 169 168 168
query7 4202 304 283 283
query8 288 224 251 224
query9 9666 2751 2733 2733
query10 466 292 276 276
query11 17849 15291 15355 15291
query12 154 106 108 106
query13 1560 439 424 424
query14 9775 7341 7035 7035
query15 244 170 173 170
query16 7872 466 459 459
query17 1628 592 592 592
query18 2146 316 321 316
query19 339 164 158 158
query20 126 115 116 115
query21 220 109 110 109
query22 4901 4539 4397 4397
query23 34990 34226 34128 34128
query24 10954 2750 2766 2750
query25 627 420 396 396
query26 1460 156 162 156
query27 2539 286 285 285
query28 7517 2459 2443 2443
query29 937 430 430 430
query30 268 157 154 154
query31 1038 809 796 796
query32 99 56 56 56
query33 783 301 312 301
query34 928 505 519 505
query35 906 743 749 743
query36 1088 939 963 939
query37 157 92 91 91
query38 4003 3942 3969 3942
query39 1475 1423 1428 1423
query40 265 100 98 98
query41 50 48 46 46
query42 130 102 98 98
query43 509 486 489 486
query44 1189 801 804 801
query45 200 164 167 164
query46 1142 702 707 702
query47 1905 1857 1854 1854
query48 433 313 333 313
query49 1044 435 416 416
query50 819 395 403 395
query51 7429 6938 7005 6938
query52 102 93 93 93
query53 257 183 179 179
query54 1307 451 444 444
query55 80 79 79 79
query56 287 279 284 279
query57 1303 1171 1160 1160
query58 228 238 245 238
query59 3272 2938 3179 2938
query60 328 283 280 280
query61 109 109 98 98
query62 834 676 671 671
query63 220 190 183 183
query64 4918 633 616 616
query65 3279 3202 3203 3202
query66 1433 309 303 303
query67 16061 15493 15509 15493
query68 4285 570 585 570
query69 546 283 290 283
query70 1215 1104 1147 1104
query71 345 282 282 282
query72 7381 3989 3771 3771
query73 784 372 370 370
query74 10189 9017 8984 8984
query75 3387 2680 2654 2654
query76 3241 1009 908 908
query77 588 299 285 285
query78 10409 9623 9589 9589
query79 1358 605 596 596
query80 1420 460 454 454
query81 578 240 243 240
query82 741 142 139 139
query83 286 141 137 137
query84 279 73 73 73
query85 1493 313 276 276
query86 394 298 299 298
query87 4543 4323 4342 4323
query88 3207 2203 2183 2183
query89 411 286 285 285
query90 2234 189 188 188
query91 137 99 101 99
query92 67 48 48 48
query93 1075 535 552 535
query94 1150 295 299 295
query95 345 251 250 250
query96 610 280 284 280
query97 3300 3107 3134 3107
query98 208 199 197 197
query99 1620 1305 1307 1305
Total cold run time: 301791 ms
Total hot run time: 191765 ms
ClickBench: Total hot run time: 32.39 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 510a031741644e8c88eaf7a27008a2f6dc451373, data reload: false
query1 0.03 0.03 0.03
query2 0.07 0.02 0.03
query3 0.23 0.06 0.06
query4 1.65 0.10 0.10
query5 0.53 0.50 0.52
query6 1.12 0.75 0.72
query7 0.02 0.01 0.01
query8 0.04 0.04 0.03
query9 0.56 0.50 0.50
query10 0.56 0.55 0.56
query11 0.14 0.10 0.10
query12 0.13 0.11 0.11
query13 0.62 0.58 0.60
query14 2.85 2.70 2.69
query15 0.88 0.82 0.87
query16 0.38 0.40 0.38
query17 1.07 1.08 0.99
query18 0.19 0.20 0.20
query19 1.95 1.87 2.02
query20 0.02 0.01 0.02
query21 15.35 0.62 0.59
query22 2.58 2.13 2.64
query23 17.03 1.06 0.87
query24 2.83 1.79 0.54
query25 0.24 0.19 0.04
query26 0.45 0.15 0.13
query27 0.06 0.04 0.04
query28 10.74 1.10 1.07
query29 12.52 3.25 3.18
query30 0.24 0.07 0.06
query31 2.86 0.38 0.38
query32 3.29 0.46 0.45
query33 3.01 3.00 3.04
query34 17.17 4.42 4.46
query35 4.49 4.51 4.46
query36 0.69 0.47 0.51
query37 0.08 0.06 0.06
query38 0.05 0.03 0.04
query39 0.03 0.03 0.02
query40 0.17 0.12 0.12
query41 0.07 0.02 0.02
query42 0.04 0.02 0.02
query43 0.03 0.02 0.02
Total cold run time: 107.06 s
Total hot run time: 32.39 s
TeamCity be ut coverage result: Function Coverage: 37.47% (9711/25916) Line Coverage: 28.75% (80655/280546) Region Coverage: 28.18% (41724/148048) Branch Coverage: 24.76% (21213/85660) Coverage Report: http://coverage.selectdb-in.cc/coverage/1a564aa0ad4867b184b9ef477e4cb18a8c4dbf1e_1a564aa0ad4867b184b9ef477e4cb18a8c4dbf1e/report/index.html
TPC-H: Total hot run time: 41237 ms
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 1a564aa0ad4867b184b9ef477e4cb18a8c4dbf1e, data reload: false
------ Round 1 ----------------------------------
q1 17560 7633 7266 7266
q2 2019 301 278 278
q3 12118 1065 1121 1065
q4 10567 858 810 810
q5 7759 3083 3054 3054
q6 236 153 145 145
q7 1010 612 627 612
q8 9361 1943 1992 1943
q9 7479 6456 6435 6435
q10 7049 2390 2367 2367
q11 447 246 251 246
q12 411 227 215 215
q13 17764 3008 3013 3008
q14 254 224 215 215
q15 569 531 514 514
q16 640 581 597 581
q17 966 520 570 520
q18 7316 6779 6637 6637
q19 1365 940 1061 940
q20 477 186 185 185
q21 4024 3303 3205 3205
q22 1114 1013 996 996
Total cold run time: 110505 ms
Total hot run time: 41237 ms
----- Round 2, with runtime_filter_mode=off -----
q1 7238 7212 7216 7212
q2 329 228 232 228
q3 3031 2912 3006 2912
q4 2101 1831 1820 1820
q5 5705 5780 5770 5770
q6 230 144 143 143
q7 2293 1792 1823 1792
q8 3412 3418 3497 3418
q9 8913 8846 8843 8843
q10 3593 3586 3580 3580
q11 588 488 484 484
q12 887 634 634 634
q13 10994 3152 3185 3152
q14 322 298 276 276
q15 562 538 525 525
q16 704 651 651 651
q17 1833 1620 1614 1614
q18 8369 7962 7617 7617
q19 1714 1495 1490 1490
q20 2131 1887 1886 1886
q21 5528 5433 5334 5334
q22 1149 1023 1039 1023
Total cold run time: 71626 ms
Total hot run time: 60404 ms
TPC-DS: Total hot run time: 191599 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 1a564aa0ad4867b184b9ef477e4cb18a8c4dbf1e, data reload: false
query1 942 405 403 403
query2 6253 2073 2023 2023
query3 8672 187 204 187
query4 33729 23720 23550 23550
query5 3382 458 463 458
query6 264 166 168 166
query7 4197 286 292 286
query8 293 229 233 229
query9 9328 2730 2707 2707
query10 463 275 270 270
query11 17949 15208 15191 15191
query12 159 101 99 99
query13 1586 432 419 419
query14 9577 7401 7050 7050
query15 245 174 181 174
query16 7791 449 462 449
query17 1622 616 590 590
query18 1614 312 310 310
query19 355 163 154 154
query20 123 112 120 112
query21 222 110 113 110
query22 4934 4615 4440 4440
query23 35363 34234 34429 34234
query24 10951 2774 2733 2733
query25 644 405 396 396
query26 1121 160 161 160
query27 2246 286 295 286
query28 7284 2432 2406 2406
query29 776 420 422 420
query30 260 153 155 153
query31 1039 810 799 799
query32 104 57 57 57
query33 769 294 292 292
query34 940 515 519 515
query35 917 786 750 750
query36 1107 958 963 958
query37 160 93 91 91
query38 4164 3902 3923 3902
query39 1486 1431 1416 1416
query40 207 97 97 97
query41 49 45 48 45
query42 118 100 97 97
query43 526 487 484 484
query44 1238 811 795 795
query45 199 167 167 167
query46 1119 684 697 684
query47 1947 1852 1877 1852
query48 421 325 330 325
query49 963 429 410 410
query50 810 386 401 386
query51 7138 7036 6993 6993
query52 105 87 87 87
query53 252 180 177 177
query54 1210 424 440 424
query55 84 82 76 76
query56 286 277 288 277
query57 1272 1204 1163 1163
query58 243 231 226 226
query59 3114 3042 2838 2838
query60 288 262 260 260
query61 106 96 106 96
query62 905 666 681 666
query63 221 193 189 189
query64 3980 631 610 610
query65 3264 3176 3218 3176
query66 815 301 315 301
query67 16150 15758 15680 15680
query68 4378 575 602 575
query69 497 282 288 282
query70 1220 1086 1157 1086
query71 365 275 268 268
query72 7240 3984 3909 3909
query73 777 365 366 365
query74 10359 9046 9083 9046
query75 3470 2671 2636 2636
query76 2823 930 898 898
query77 619 296 297 296
query78 10445 9677 9542 9542
query79 1352 611 585 585
query80 1370 441 444 441
query81 581 239 239 239
query82 426 142 144 142
query83 293 139 135 135
query84 283 69 73 69
query85 1396 293 283 283
query86 406 303 297 297
query87 4583 4320 4284 4284
query88 3227 2229 2154 2154
query89 397 290 279 279
query90 2018 184 182 182
query91 142 96 103 96
query92 65 49 49 49
query93 1638 545 550 545
query94 1054 275 268 268
query95 344 247 240 240
query96 620 274 279 274
query97 3255 3124 3157 3124
query98 218 205 193 193
query99 1593 1326 1291 1291
Total cold run time: 297515 ms
Total hot run time: 191599 ms
ClickBench: Total hot run time: 32.34 s
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 1a564aa0ad4867b184b9ef477e4cb18a8c4dbf1e, data reload: false
query1 0.03 0.03 0.03
query2 0.07 0.02 0.03
query3 0.23 0.07 0.07
query4 1.64 0.11 0.10
query5 0.51 0.50 0.50
query6 1.14 0.75 0.72
query7 0.02 0.02 0.04
query8 0.03 0.03 0.03
query9 0.57 0.50 0.51
query10 0.55 0.55 0.56
query11 0.14 0.10 0.10
query12 0.13 0.10 0.11
query13 0.62 0.59 0.60
query14 2.72 2.88 2.75
query15 0.90 0.83 0.84
query16 0.40 0.38 0.37
query17 1.06 1.08 1.06
query18 0.21 0.20 0.20
query19 1.89 1.78 2.03
query20 0.01 0.00 0.01
query21 15.37 0.62 0.60
query22 2.67 2.17 2.36
query23 16.81 1.20 0.81
query24 3.19 0.33 0.89
query25 0.26 0.17 0.09
query26 0.37 0.14 0.14
query27 0.04 0.05 0.04
query28 11.46 1.09 1.06
query29 12.59 3.21 3.20
query30 0.24 0.06 0.06
query31 2.89 0.39 0.38
query32 3.25 0.46 0.45
query33 2.99 3.03 3.02
query34 16.96 4.43 4.48
query35 4.50 4.47 4.53
query36 0.67 0.48 0.48
query37 0.08 0.06 0.05
query38 0.05 0.03 0.04
query39 0.04 0.02 0.02
query40 0.17 0.13 0.12
query41 0.08 0.02 0.03
query42 0.04 0.03 0.02
query43 0.04 0.03 0.03
Total cold run time: 107.63 s
Total hot run time: 32.34 s
PR approved by at least one committer and no changes requested.