You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "feiniaofeiafei (via GitHub)" <gi...@apache.org> on 2024/04/17 13:36:56 UTC
[PR] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
feiniaofeiafei opened a new pull request, #33803:
URL: https://github.com/apache/doris/pull/33803
like expressions without fuzzy matching are rewritten into equivalent expressions
--
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061360956
<details>
<summary>TPC-DS: <b>Total hot run time: 184446 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 35b015411e03b4fd3dfe3b075f71634c709f25f7, data reload: false
query1 897 389 375 375
query2 6842 2584 2374 2374
query3 6651 207 210 207
query4 23365 21565 21221 21221
query5 4150 408 413 408
query6 269 174 172 172
query7 4579 289 286 286
query8 236 172 169 169
query9 8478 2354 2346 2346
query10 577 242 248 242
query11 14860 14238 14244 14238
query12 142 89 89 89
query13 1646 367 364 364
query14 9043 7975 7424 7424
query15 260 187 190 187
query16 8178 264 264 264
query17 1926 602 572 572
query18 2139 299 279 279
query19 295 155 155 155
query20 95 84 88 84
query21 198 123 122 122
query22 5005 4893 4854 4854
query23 33928 33071 33001 33001
query24 11791 2978 2981 2978
query25 670 368 376 368
query26 1768 153 154 153
query27 2973 314 313 313
query28 7594 2035 2008 2008
query29 1022 610 587 587
query30 295 174 170 170
query31 959 727 732 727
query32 87 51 54 51
query33 747 249 241 241
query34 1039 470 482 470
query35 842 721 675 675
query36 1048 889 914 889
query37 187 71 72 71
query38 3403 3170 3160 3160
query39 1557 1670 1526 1526
query40 273 121 125 121
query41 47 46 46 46
query42 103 99 97 97
query43 580 533 557 533
query44 1228 721 713 713
query45 271 272 258 258
query46 1082 690 699 690
query47 1919 1828 1829 1828
query48 378 307 297 297
query49 1171 365 365 365
query50 758 376 387 376
query51 6674 6640 6559 6559
query52 104 86 90 86
query53 348 270 274 270
query54 301 262 231 231
query55 77 73 70 70
query56 243 218 211 211
query57 1183 1122 1129 1122
query58 219 191 198 191
query59 3402 3324 3173 3173
query60 264 252 239 239
query61 90 87 87 87
query62 655 443 433 433
query63 297 271 281 271
query64 6293 4117 3929 3929
query65 3113 3070 3018 3018
query66 1383 338 333 333
query67 15247 15047 14976 14976
query68 5256 532 549 532
query69 484 291 296 291
query70 1185 1182 1202 1182
query71 1405 1257 1258 1257
query72 6644 2590 2406 2406
query73 699 323 324 323
query74 6941 6444 6480 6444
query75 3406 2698 2569 2569
query76 3417 965 1009 965
query77 419 259 271 259
query78 10939 10250 10184 10184
query79 4266 533 530 530
query80 1802 429 428 428
query81 544 246 241 241
query82 801 98 100 98
query83 283 174 169 169
query84 268 90 86 86
query85 2105 273 255 255
query86 521 295 308 295
query87 3460 3250 3273 3250
query88 5058 2413 2399 2399
query89 481 372 376 372
query90 2074 184 185 184
query91 129 98 100 98
query92 62 48 47 47
query93 5199 515 508 508
query94 1236 180 180 180
query95 385 304 302 302
query96 598 273 269 269
query97 3150 2922 2909 2909
query98 240 217 221 217
query99 1281 862 854 854
Total cold run time: 292182 ms
Total hot run time: 184446 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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow merged PR #33803:
URL: https://github.com/apache/doris/pull/33803
--
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2074290436
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061373098
<details>
<summary>ClickBench: <b>Total hot run time: 30.74 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 35b015411e03b4fd3dfe3b075f71634c709f25f7, data reload: false
query1 0.04 0.03 0.03
query2 0.09 0.04 0.04
query3 0.23 0.05 0.05
query4 1.68 0.08 0.08
query5 0.50 0.50 0.50
query6 1.47 0.72 0.72
query7 0.02 0.01 0.02
query8 0.05 0.04 0.05
query9 0.55 0.51 0.52
query10 0.56 0.55 0.55
query11 0.16 0.11 0.12
query12 0.14 0.12 0.12
query13 0.60 0.59 0.59
query14 0.76 0.77 0.79
query15 0.82 0.81 0.80
query16 0.38 0.37 0.36
query17 0.98 0.95 0.96
query18 0.19 0.26 0.25
query19 1.79 1.66 1.81
query20 0.02 0.01 0.01
query21 15.42 0.67 0.65
query22 3.78 7.62 2.29
query23 18.30 1.43 1.29
query24 1.83 0.22 0.20
query25 0.15 0.08 0.09
query26 0.27 0.17 0.17
query27 0.07 0.08 0.08
query28 13.39 1.00 1.00
query29 12.62 3.28 3.26
query30 0.27 0.06 0.06
query31 2.88 0.38 0.38
query32 3.25 0.47 0.47
query33 2.85 2.77 2.81
query34 17.09 4.39 4.41
query35 4.50 4.51 4.47
query36 0.66 0.47 0.46
query37 0.18 0.15 0.15
query38 0.14 0.14 0.14
query39 0.04 0.04 0.03
query40 0.17 0.14 0.13
query41 0.09 0.05 0.04
query42 0.05 0.05 0.06
query43 0.04 0.04 0.03
Total cold run time: 109.07 s
Total hot run time: 30.74 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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2073938147
<details>
<summary>ClickBench: <b>Total hot run time: 31.32 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 af27123b7cae998e765912ed9e37a739be058b71, data reload: false
query1 0.04 0.04 0.03
query2 0.08 0.04 0.04
query3 0.23 0.05 0.05
query4 1.68 0.07 0.06
query5 0.51 0.50 0.52
query6 1.34 0.88 0.82
query7 0.02 0.02 0.01
query8 0.05 0.04 0.04
query9 0.50 0.45 0.44
query10 0.50 0.52 0.51
query11 0.15 0.11 0.11
query12 0.14 0.11 0.11
query13 0.65 0.66 0.65
query14 1.04 0.93 0.92
query15 0.89 0.85 0.85
query16 0.36 0.36 0.38
query17 1.04 1.04 0.99
query18 0.22 0.24 0.23
query19 1.87 1.82 1.81
query20 0.01 0.01 0.01
query21 15.40 0.64 0.64
query22 4.23 7.10 1.68
query23 18.28 1.32 1.28
query24 1.73 0.31 0.28
query25 0.16 0.10 0.10
query26 0.27 0.17 0.17
query27 0.10 0.09 0.09
query28 13.25 1.04 1.03
query29 12.69 3.47 3.39
query30 0.25 0.08 0.06
query31 2.84 0.40 0.40
query32 3.27 0.51 0.49
query33 2.70 2.88 2.75
query34 17.17 4.53 4.65
query35 4.52 4.70 4.75
query36 0.65 0.47 0.47
query37 0.20 0.17 0.18
query38 0.20 0.18 0.19
query39 0.05 0.05 0.05
query40 0.20 0.15 0.15
query41 0.11 0.06 0.06
query42 0.07 0.06 0.06
query43 0.05 0.05 0.06
Total cold run time: 109.71 s
Total hot run time: 31.32 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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061379908
Load test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
```
Load test result on commit 35b015411e03b4fd3dfe3b075f71634c709f25f7 with default session variables
Stream load json: 18 seconds loaded 2358488459 Bytes, about 124 MB/s
Stream load orc: 58 seconds loaded 1101869774 Bytes, about 18 MB/s
Stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
Insert into select: 13.4 seconds inserted 10000000 Rows, about 746K ops/s
```
--
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "feiniaofeiafei (via GitHub)" <gi...@apache.org>.
feiniaofeiafei commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2073902288
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "feiniaofeiafei (via GitHub)" <gi...@apache.org>.
feiniaofeiafei commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061282425
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061335794
<details>
<summary>TPC-H: <b>Total hot run time: 38388 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 35b015411e03b4fd3dfe3b075f71634c709f25f7, data reload: false
------ Round 1 ----------------------------------
q1 17634 4430 4254 4254
q2 2009 190 188 188
q3 10460 1166 1190 1166
q4 10184 752 832 752
q5 7512 2707 2658 2658
q6 214 133 132 132
q7 1010 623 575 575
q8 9235 2079 2042 2042
q9 7423 6571 6534 6534
q10 8492 3482 3493 3482
q11 464 241 232 232
q12 453 228 215 215
q13 17762 2903 2968 2903
q14 277 225 231 225
q15 517 487 472 472
q16 543 388 382 382
q17 965 753 689 689
q18 7285 6692 6699 6692
q19 1624 1527 1496 1496
q20 658 331 310 310
q21 3487 2690 2758 2690
q22 356 299 302 299
Total cold run time: 108564 ms
Total hot run time: 38388 ms
----- Round 2, with runtime_filter_mode=off -----
q1 4214 4284 4167 4167
q2 368 254 272 254
q3 2998 2720 2718 2718
q4 1850 1565 1570 1565
q5 5330 5287 5290 5287
q6 205 123 122 122
q7 2240 1863 1894 1863
q8 3202 3292 3350 3292
q9 8485 8535 8504 8504
q10 3893 3674 3684 3674
q11 581 492 463 463
q12 739 615 599 599
q13 16663 2926 2950 2926
q14 297 278 276 276
q15 511 468 481 468
q16 456 425 426 425
q17 1768 1458 1440 1440
q18 7444 7526 7503 7503
q19 1621 1479 1607 1479
q20 1934 1770 1734 1734
q21 4967 4711 4658 4658
q22 531 459 447 447
Total cold run time: 70297 ms
Total hot run time: 53864 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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "feiniaofeiafei (via GitHub)" <gi...@apache.org>.
feiniaofeiafei commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2065634399
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2074290368
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "feiniaofeiafei (via GitHub)" <gi...@apache.org>.
feiniaofeiafei commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2074001265
run external
--
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow commented on code in PR #33803:
URL: https://github.com/apache/doris/pull/33803#discussion_r1569858624
##########
fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/LikeToEqualRewrite.java:
##########
@@ -0,0 +1,69 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.rules.expression.rules;
+
+import org.apache.doris.nereids.rules.expression.ExpressionPatternMatcher;
+import org.apache.doris.nereids.rules.expression.ExpressionPatternRuleFactory;
+import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.Like;
+import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
+
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * LikeToEqualRewrite
+ */
+public class LikeToEqualRewrite implements ExpressionPatternRuleFactory {
+ public static LikeToEqualRewrite INSTANCE = new LikeToEqualRewrite();
+
+ @Override
+ public List<ExpressionPatternMatcher<? extends Expression>> buildRules() {
+ return ImmutableList.of(
+ matchesType(Like.class).then(LikeToEqualRewrite::rewriteLikeToEqual)
+ );
+ }
+
+ private static Expression rewriteLikeToEqual(Like like) {
+ Expression left = like.child(0);
+ Expression right = like.child(1);
+ if (!(right instanceof VarcharLiteral)) {
+ return like;
+ }
+ String str = ((VarcharLiteral) right).value;
+ StringBuilder sb = new StringBuilder();
+ int len = str.length();
+ for (int i = 0; i < len;) {
+ char c = str.charAt(i);
+ if (c == '\\' && (i + 1) < len
+ && (str.charAt(i + 1) == '%' || str.charAt(i + 1) == '_' || str.charAt(i + 1) == '\\')) {
+ sb.append(str.charAt(i + 1));
+ i += 2;
+ } else {
+ if (c == '%' || c == '_') {
+ return like;
+ }
+ sb.append(c);
+ i++;
+ }
+ }
Review Comment:
use a function to judge whether contain wildcard character to ensure we could easy support escape character other than '\\'. because like could use any character as escape character in future. the sql will like
```sql
c1 LIKE 'ab_%c' ESCAPE '_'
```
--
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] [Feat](nereids) add rewrite rule LikeToEqualRewrite [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #33803:
URL: https://github.com/apache/doris/pull/33803#issuecomment-2061282183
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