You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "starocean999 (via GitHub)" <gi...@apache.org> on 2023/11/15 12:33:31 UTC
[PR] [fix](nereids)count in correlated subquery shoud not output null value [doris]
starocean999 opened a new pull request, #27064:
URL: https://github.com/apache/doris/pull/27064
consider sql: SELECT * FROM t1 WHERE t1.a <= (SELECT COUNT(t2.a) FROM t2 WHERE (t1.b = t2.b));
when unnest correlated subquery, we create a left join node. Assume outer query is left table and subquery is right one.
If there is no match, the row from right table is filled with nulls.
But COUNT function is always not nullable. So wrap COUNT with Nvl to ensure it's result is 0 instead of null to get the correct result
<!--Describe your changes.-->
## Further comments
If this is a relatively large or complex change, kick off the discussion at [dev@doris.apache.org](mailto:dev@doris.apache.org) by explaining why you chose the solution you did and what alternatives you considered, etc...
--
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)count in correlated subquery shoud not output null value [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #27064:
URL: https://github.com/apache/doris/pull/27064#issuecomment-1813739786
(From new machine)TeamCity pipeline, clickbench performance test result:
the sum of best hot time: 44.85 seconds
stream load tsv: 578 seconds loaded 74807831229 Bytes, about 123 MB/s
stream load json: 18 seconds loaded 2358488459 Bytes, about 124 MB/s
stream load orc: 65 seconds loaded 1101869774 Bytes, about 16 MB/s
stream load parquet: 32 seconds loaded 861443392 Bytes, about 25 MB/s
insert into select: 28.6 seconds inserted 10000000 Rows, about 349K ops/s
storage size: 17099424389 Bytes
--
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)count in correlated subquery shoud not output null value [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #27064:
URL: https://github.com/apache/doris/pull/27064#issuecomment-1815725177
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)count in correlated subquery shoud not output null value [doris]
Posted by "doris-robot (via GitHub)" <gi...@apache.org>.
doris-robot commented on PR #27064:
URL: https://github.com/apache/doris/pull/27064#issuecomment-1813737304
<details>
<summary>TPC-H test result on machine: 'aliyun_ecs.c7a.8xlarge_32C64G'</summary>
```
Tpch sf100 test result on commit 9355abe256d3e389d343fad52a802ca94d8ee6cd, data reload: false
run tpch-sf100 query with default conf and session variables
q1 4931 4670 4652 4652
q2 357 173 165 165
q3 2024 1879 1888 1879
q4 1391 1244 1202 1202
q5 3975 3952 3992 3952
q6 247 126 127 126
q7 1405 873 889 873
q8 2752 2802 2765 2765
q9 9986 9744 9539 9539
q10 3436 3517 3515 3515
q11 383 243 246 243
q12 447 290 296 290
q13 4554 3784 3813 3784
q14 320 292 281 281
q15 584 536 522 522
q16 660 576 584 576
q17 1122 955 921 921
q18 7798 7212 7363 7212
q19 1668 1684 1672 1672
q20 573 299 292 292
q21 4364 3970 3951 3951
q22 471 367 370 367
Total cold run time: 53448 ms
Total hot run time: 48779 ms
run tpch-sf100 query with default conf and set session variable runtime_filter_mode=off
q1 4589 4572 4572 4572
q2 337 233 255 233
q3 4014 4004 3969 3969
q4 2687 2682 2685 2682
q5 9666 9640 9788 9640
q6 247 124 125 124
q7 2598 2276 2318 2276
q8 4421 4438 4439 4438
q9 13279 13084 13151 13084
q10 4076 4158 4184 4158
q11 783 659 639 639
q12 986 804 817 804
q13 4295 3611 3554 3554
q14 383 354 351 351
q15 574 525 521 521
q16 734 676 691 676
q17 3824 3816 3827 3816
q18 9594 8809 8994 8809
q19 1790 1779 1786 1779
q20 2375 2068 2043 2043
q21 8794 8569 8643 8569
q22 875 821 762 762
Total cold run time: 80921 ms
Total hot run time: 77499 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)count in correlated subquery shoud not output null value [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow merged PR #27064:
URL: https://github.com/apache/doris/pull/27064
--
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)count in correlated subquery shoud not output null value [doris]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #27064:
URL: https://github.com/apache/doris/pull/27064#issuecomment-1815725150
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)count in correlated subquery shoud not output null value [doris]
Posted by "starocean999 (via GitHub)" <gi...@apache.org>.
starocean999 commented on PR #27064:
URL: https://github.com/apache/doris/pull/27064#issuecomment-1812458725
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