Re: [PR] [nereids] string literal coercion of in predicate [doris]
morrySnow commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126356831 run p0 -- 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
github-actions[bot] commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126291323 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
github-actions[bot] commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126291357 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
doris-robot commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126259739 ClickBench: Total hot run time: 30.51 s ``` machine: 'aliyun_ecs.c7a.8xlarge_32C64G' scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools ClickBench test result on commit 570607367f6ddd1b81c6cf9f5d267f8e299608f0, data reload: false query1 0.040.030.04 query2 0.080.030.04 query3 0.220.050.05 query4 1.680.080.09 query5 0.550.480.54 query6 1.130.720.72 query7 0.020.010.01 query8 0.050.030.04 query9 0.540.490.48 query10 0.530.550.55 query11 0.150.110.12 query12 0.150.120.13 query13 0.600.590.59 query14 0.740.810.78 query15 0.820.820.80 query16 0.370.370.37 query17 0.951.021.03 query18 0.210.220.26 query19 1.761.681.64 query20 0.010.010.01 query21 15.43 0.720.70 query22 4.646.901.95 query23 18.28 1.341.33 query24 1.440.340.20 query25 0.160.080.08 query26 0.250.160.16 query27 0.070.080.08 query28 13.45 1.040.99 query29 12.73 3.313.21 query30 0.240.060.06 query31 2.880.400.38 query32 3.290.470.45 query33 2.912.922.86 query34 17.14 4.414.44 query35 4.504.514.48 query36 0.670.490.46 query37 0.180.160.15 query38 0.150.150.14 query39 0.040.040.04 query40 0.160.140.15 query41 0.090.050.04 query42 0.050.050.05 query43 0.040.030.03 Total cold run time: 109.39 s Total hot run time: 30.51 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
doris-robot commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126254800 TPC-DS: Total hot run time: 168073 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 570607367f6ddd1b81c6cf9f5d267f8e299608f0, data reload: false query1 899 378 371 371 query2 6432250224092409 query3 6647207 211 207 query4 19687 17258 17324 17258 query5 4114410 428 410 query6 239 159 147 147 query7 4579296 295 295 query8 240 194 180 180 query9 8609236423492349 query10 455 281 264 264 query11 10758 10069 10127 10069 query12 129 94 86 86 query13 1640352 362 352 query14 10110 594175235941 query15 207 167 171 167 query16 7768263 268 263 query17 1713516 516 516 query18 1947269 265 265 query19 201 152 154 152 query20 88 86 99 86 query21 197 137 127 127 query22 4113416939313931 query23 33668 33099 33144 33099 query24 9476289527542754 query25 565 354 361 354 query26 725 163 159 159 query27 2205329 332 329 query28 5609207020542054 query29 849 599 600 599 query30 251 171 176 171 query31 967 758 751 751 query32 84 55 52 52 query33 633 254 264 254 query34 849 474 479 474 query35 711 624 618 618 query36 1053938 891 891 query37 108 72 72 72 query38 2953274127712741 query39 847 787 797 787 query40 206 127 128 127 query41 50 46 47 46 query42 102 96 105 96 query43 594 553 561 553 query44 1058727 730 727 query45 183 170 167 167 query46 1072746 729 729 query47 1840178417711771 query48 364 291 298 291 query49 830 369 390 369 query50 765 382 394 382 query51 6829682167056705 query52 101 91 91 91 query53 349 291 276 276 query54 726 414 419 414 query55 74 71 76 71 query56 257 237 235 235 query57 1106106410551055 query58 231 207 206 206 query59 3545312131833121 query60 284 253 252 252 query61 87 86 85 85 query62 591 439 438 438 query63 306 289 285 285 query64 8447222717691769 query65 3165307731673077 query66 784 321 325 321 query67 15200 14679 14775 14679 query68 4525539 525 525 query69 439 262 269 262 query70 1141114911671149 query71 359 268 270 268 query72 7259267825312531 query73 705 321 321 321 query74 6039558456265584 query75 3256263426332633 query76 2270994 956 956 query77 372 263 261 261 query78 10452 980396419641 query79 2403523 516 516 query80 1133456 455 455 query81 542 243 247 243 query82 952 97 100 97 query83 257 186 179 179 query84 247 90 87 87 query85 1326275 259 259 query86 461 330 331 330 query87 3449311331503113 query88 4006233923262326 query89 470 389 388 388 query90 1960190 189 189 query91 123 100 95 95 query92 60 47 46 46 query93 1511513 503 503 query94 1274186 186 186 query95 405 314 302 302 query96 576 267 266 266 query97 3118298330102983 query98 239 225 214 214 query99 1159862 823 823 Total cold run time: 263959 ms Total hot run time: 168073 ms ``` -- 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 sp
Re: [PR] [nereids] string literal coercion of in predicate [doris]
mongo360 commented on code in PR #35200:
URL: https://github.com/apache/doris/pull/35200#discussion_r1610985815
##
fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java:
##
@@ -975,8 +976,21 @@ public static Expression processInPredicate(InPredicate
inPredicate) {
}
return inPredicate;
}
+// process string literal with numeric
+AtomicReference atomicInPredicate = new
AtomicReference<>(inPredicate);
+if (!(inPredicate.getCompareExpr().getDataType().isStringLikeType())) {
+List newOptions =
inPredicate.getOptions().stream().map(option -> {
Review Comment:
test case added
--
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-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
-
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
doris-robot commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126243871 TPC-H: Total hot run time: 41943 ms ``` machine: 'aliyun_ecs.c7a.8xlarge_32C64G' scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools Tpch sf100 test result on commit 570607367f6ddd1b81c6cf9f5d267f8e299608f0, data reload: false -- Round 1 -- q1 17604 435142344234 q2 2014186 192 186 q3 10543 120012561200 q4 10201 791 758 758 q5 7493275526692669 q6 219 139 139 139 q7 973 622 637 622 q8 9219216221102110 q9 9449671467476714 q10 9079387838723872 q11 448 242 236 236 q12 527 237 227 227 q13 17442 320832073207 q14 261 207 216 207 q15 524 460 464 460 q16 508 395 392 392 q17 1001666 785 666 q18 8336792779597927 q19 7234159115751575 q20 637 319 313 313 q21 5237400439533953 q22 364 294 276 276 Total cold run time: 119313 ms Total hot run time: 41943 ms - Round 2, with runtime_filter_mode=off - q1 4537438144674381 q2 384 274 259 259 q3 3170296227282728 q4 1859164216561642 q5 5478547954965479 q6 219 127 127 127 q7 2145182518341825 q8 3231338633883386 q9 8685868886368636 q10 3942387338573857 q11 573 492 491 491 q12 793 638 663 638 q13 16222 314531883145 q14 285 272 250 250 q15 523 464 474 464 q16 475 412 417 412 q17 1759150915141509 q18 7645775075607560 q19 1674152815181518 q20 1995180017951795 q21 5032469846684668 q22 551 490 502 490 Total cold run time: 71177 ms Total hot run time: 55260 ms ``` -- 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
morrySnow commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2126203685 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
morrySnow commented on code in PR #35200:
URL: https://github.com/apache/doris/pull/35200#discussion_r1609442824
##
fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java:
##
@@ -975,8 +976,21 @@ public static Expression processInPredicate(InPredicate
inPredicate) {
}
return inPredicate;
}
+// process string literal with numeric
+AtomicReference atomicInPredicate = new
AtomicReference<>(inPredicate);
+if (!(inPredicate.getCompareExpr().getDataType().isStringLikeType())) {
+List newOptions =
inPredicate.getOptions().stream().map(option -> {
Review Comment:
do not use stream api, stream api hurt performance
--
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-unsubscr...@doris.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
-
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
Re: [PR] [nereids] string literal coercion of in predicate [doris]
doris-robot commented on PR #35200: URL: https://github.com/apache/doris/pull/35200#issuecomment-2124064301 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-unsubscr...@doris.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
[PR] [nereids] string literal coercion of in predicate [doris]
mongo360 opened a new pull request, #35200:
URL: https://github.com/apache/doris/pull/35200
Description:
The sql execute much slow when the literal value with string format in
`in predicate`; and the real data is integral type。
```
mysql> set enable_nereids_planner = false;
Query OK, 0 rows affected (0.03 sec)
mysql> select id,sum(clicks) from a_table where id in ('787934713',
'306960695') group by id limit 10;
++---+
| id | sum(`clicks`) |
++---+
| 787934713 | 2838 |
| 306960695 | 339 |
++---+
2 rows in set (1.81 sec)
mysql> set enable_nereids_planner = true;
Query OK, 0 rows affected (0.02 sec)
mysql> select id,sum(clicks) from a_table where id in ('787934713',
'306960695') group by id limit 10;
++-+
| id | sum(clicks) |
++-+
| 787934713 |2838 |
| 306960695 | 339 |
++-+
2 rows in set (28.14 sec)
```
Reason:
In legacy planner, the string literal with convert to integral value, but in
the nereids planner do not do this convert and with do string matching in BE。
Solved:
do process string literal with numeric in `in predicate` like in `comparison
predicate`;
test table:
```
create table a_table(
k1 BIGINT NOT NULL,
k2 VARCHAR(100) NOT NULL,
v1 INT SUM NULL DEFAULT "0"
) ENGINE=OLAP
AGGREGATE KEY(k1,k2)
distributed BY hash(k1) buckets 2
properties("replication_num" = "1");
insert into a_table values (10, 'name1', 10),(20, 'name2', 10);
explain plan select * from a_table where k1 in ('10', '20001');
```
before optimize:
```
+--+
| Explain String(Nereids Planner)
|
+--+
| == PARSED PLAN (time: 1ms) ==
|
| UnboundResultSink[4] ( )
|
| +--LogicalProject[3] ( distinct=false, projects=[*], excepts=[] )
|
|+--LogicalFilter[2] ( predicates='k1 IN ('10001', '20001') )
|
| +--LogicalCheckPolicy ( )
|
| +--UnboundRelation ( id=RelationId#0, nameParts=a_table )
|
|
|
| == ANALYZED PLAN (time: 2ms) ==
|
| LogicalResultSink[15] ( outputExprs=[k1#0, k2#1, v1#2] )
|
| +--LogicalProject[13] ( distinct=false, projects=[k1#0, k2#1, v1#2],
excepts=[] )|
|+--LogicalFilter[11] ( predicates=cast(k1#0 as TEXT) IN ('10001',
'20001') ) |
| +--LogicalOlapScan ( qualified=internal.db.a_table,
indexName=, selectedIndexId=12003, preAgg=UNSET ) |
|
|
| == REWRITTEN PLAN (time: 6ms) ==
|
| LogicalResultSink[45] ( outputExprs=[k1#0, k2#1, v1#2] )
|
| +--LogicalFilter[43] ( predicates=cast(k1#0 as TEXT) IN ('10001', '20001')
) |
|+--LogicalOlapScan ( qualified=internal.db.a_table, indexName=a_table,
selectedIndexId=12003, preAgg=OFF, No aggregate on scan. ) |
|
|
| == OPTIMIZED PLAN (time: 6ms) ==
|
| PhysicalResultSink[90] ( outputExprs=[k1#0, k2#1, v1#2] )
