Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Fix](sql function)Fix split_part function behavior #36231

Closed
wants to merge 4 commits into from
Closed

[Fix](sql function)Fix split_part function behavior #36231

wants to merge 4 commits into from

Conversation

liujiwen-up
Copy link
Contributor

@liujiwen-up liujiwen-up commented Jun 13, 2024 •
edited
Loading

Proposed changes

Issue Number: close #xxx

If the delimiter does not exist in the string, it is returned unchanged, changed to the same behavior as pg/trino

  • Before behavior change
    mysql> select split_part('abc', '|', 1);
    +---------------------------+
    | split_part('abc', '|', 1) |
    +---------------------------+
    | NULL |
    +---------------------------+
    1 row in set (0.02 sec)

  • After behavior change
    `mysql> select split_part('abc', '|', 1);
    +---------------------------+
    | split_part('abc', '|', 1) |
    +---------------------------+
    | abc |
    +---------------------------+
    1 row in set (0.01 sec)

    mysql> select split_part('abc', '||', 1);
    +----------------------------+
    | split_part('abc', '||', 1) |
    +----------------------------+
    | abc |
    +----------------------------+
    1 row in set (0.01 sec)

    mysql> select split_part('abc', '|', -1);
    +----------------------------+
    | split_part('abc', '|', -1) |
    +----------------------------+
    | abc |
    +----------------------------+
    1 row in set (0.00 sec)

    mysql> select split_part('abc', '||', -1);
    +-----------------------------+
    | split_part('abc', '||', -1) |
    +-----------------------------+
    | abc |
    +-----------------------------+
    1 row in set (0.00 sec)`

@doris-robot
Copy link

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 GitHub Actions
Copy link
Contributor

clang-tidy review says "All clean, LGTM! 👍"

3 similar comments
@github-actions GitHub Actions
Copy link
Contributor

clang-tidy review says "All clean, LGTM! 👍"

@github-actions GitHub Actions
Copy link
Contributor

clang-tidy review says "All clean, LGTM! 👍"

@github-actions GitHub Actions
Copy link
Contributor

clang-tidy review says "All clean, LGTM! 👍"

morningman added a commit to morningman/doris that referenced this pull request Jun 13, 2024
@morningman
[Fix](sql function)Fix split_part function behavior apache#36231
7de0107
@zclllyybb
Copy link
Contributor

please add description about what's the behavior before

@zclllyybb
Copy link
Contributor

run buildall

@doris-robot
Copy link

TPC-H: Total hot run time: 39916 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 4decd9f2e69f6b228de4b2e683250866f31d165b, data reload: false

------ Round 1 ----------------------------------
q1	17619	4344	4264	4264
q2	2017	204	183	183
q3	10454	1149	1061	1061
q4	10180	845	840	840
q5	7465	2704	2646	2646
q6	220	137	133	133
q7	970	616	587	587
q8	9214	2059	2050	2050
q9	9057	6495	6505	6495
q10	8909	3735	3735	3735
q11	481	232	238	232
q12	449	227	229	227
q13	17777	2973	2992	2973
q14	275	227	223	223
q15	534	484	471	471
q16	547	374	371	371
q17	961	718	681	681
q18	8089	7415	7543	7415
q19	6590	1510	1486	1486
q20	661	322	330	322
q21	4900	3180	3227	3180
q22	396	347	341	341
Total cold run time: 117765 ms
Total hot run time: 39916 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4406	4253	4212	4212
q2	369	283	260	260
q3	2931	2969	2894	2894
q4	1984	1723	1750	1723
q5	5575	5511	5472	5472
q6	230	130	127	127
q7	2256	1851	1839	1839
q8	3291	3489	3402	3402
q9	8691	8732	8734	8732
q10	4096	3750	3778	3750
q11	587	514	510	510
q12	818	651	654	651
q13	16199	3215	3174	3174
q14	305	279	266	266
q15	545	489	478	478
q16	509	430	421	421
q17	1847	1509	1496	1496
q18	8130	8118	7732	7732
q19	1853	1578	1659	1578
q20	3056	1897	1856	1856
q21	5173	4906	4785	4785
q22	673	519	558	519
Total cold run time: 73524 ms
Total hot run time: 55877 ms

@doris-robot
Copy link

TeamCity be ut coverage result:
Function Coverage: 36.47% (9000/24677)
Line Coverage: 28.02% (73718/263134)
Region Coverage: 27.49% (38295/139317)
Branch Coverage: 24.18% (19517/80710)
Coverage Report: http://coverage.selectdb-in.cc/coverage/4decd9f2e69f6b228de4b2e683250866f31d165b_4decd9f2e69f6b228de4b2e683250866f31d165b/report/index.html

@doris-robot
Copy link

TPC-DS: Total hot run time: 174398 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 4decd9f2e69f6b228de4b2e683250866f31d165b, data reload: false

query1	933	375	380	375
query2	6431	2541	2534	2534
query3	6639	207	213	207
query4	20487	17620	17319	17319
query5	3565	514	463	463
query6	244	162	168	162
query7	4584	294	303	294
query8	353	306	301	301
query9	8565	2374	2374	2374
query10	591	307	280	280
query11	10892	10016	10092	10016
query12	135	87	85	85
query13	1653	367	368	367
query14	10014	7493	7166	7166
query15	229	193	193	193
query16	7803	273	260	260
query17	1915	529	502	502
query18	1999	300	268	268
query19	195	155	152	152
query20	94	83	82	82
query21	212	126	124	124
query22	4242	4154	4008	4008
query23	34053	33867	33708	33708
query24	10926	2870	2871	2870
query25	602	389	370	370
query26	732	163	167	163
query27	2270	337	333	333
query28	6136	2110	2122	2110
query29	947	655	672	655
query30	265	155	159	155
query31	958	780	787	780
query32	95	55	60	55
query33	728	305	302	302
query34	911	496	490	490
query35	763	674	648	648
query36	1136	983	1002	983
query37	146	77	79	77
query38	2981	2835	2869	2835
query39	877	850	856	850
query40	216	134	144	134
query41	58	61	56	56
query42	123	105	104	104
query43	610	548	579	548
query44	1190	721	736	721
query45	195	170	166	166
query46	1092	754	732	732
query47	1864	1765	1792	1765
query48	382	302	299	299
query49	848	414	425	414
query50	766	399	394	394
query51	6805	6738	6660	6660
query52	105	94	96	94
query53	358	297	293	293
query54	912	458	455	455
query55	75	76	75	75
query56	306	277	282	277
query57	1146	1074	1116	1074
query58	255	278	241	241
query59	3662	3342	3351	3342
query60	293	287	285	285
query61	99	92	94	92
query62	608	450	426	426
query63	318	292	296	292
query64	8937	2279	1734	1734
query65	3154	3090	3099	3090
query66	731	325	332	325
query67	15576	14952	14821	14821
query68	6160	546	546	546
query69	687	472	411	411
query70	1212	1068	1140	1068
query71	461	281	275	275
query72	8458	5372	5529	5372
query73	766	325	326	325
query74	5842	5414	5482	5414
query75	4224	2643	2696	2643
query76	3475	912	972	912
query77	716	299	308	299
query78	10315	9694	9789	9694
query79	6986	522	520	520
query80	772	486	477	477
query81	542	219	226	219
query82	222	104	108	104
query83	299	170	165	165
query84	273	84	90	84
query85	848	281	272	272
query86	349	341	319	319
query87	3279	3100	3076	3076
query88	4033	2347	2315	2315
query89	522	381	370	370
query90	1906	194	256	194
query91	129	101	100	100
query92	62	53	50	50
query93	5049	513	509	509
query94	1278	188	189	188
query95	416	321	311	311
query96	603	273	268	268
query97	3191	3048	3073	3048
query98	210	198	197	197
query99	1066	837	828	828
Total cold run time: 281336 ms
Total hot run time: 174398 ms

@doris-robot
Copy link

ClickBench: Total hot run time: 30.8 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 4decd9f2e69f6b228de4b2e683250866f31d165b, data reload: false

query1	0.04	0.04	0.04
query2	0.08	0.04	0.05
query3	0.23	0.04	0.05
query4	1.68	0.07	0.07
query5	0.51	0.49	0.48
query6	1.13	0.72	0.72
query7	0.02	0.02	0.02
query8	0.05	0.05	0.05
query9	0.54	0.50	0.49
query10	0.55	0.54	0.54
query11	0.15	0.12	0.11
query12	0.15	0.12	0.12
query13	0.60	0.59	0.60
query14	0.81	0.77	0.79
query15	0.85	0.80	0.82
query16	0.37	0.36	0.36
query17	0.97	1.00	1.04
query18	0.23	0.24	0.25
query19	1.75	1.75	1.72
query20	0.01	0.01	0.02
query21	15.40	0.65	0.65
query22	4.40	7.02	2.08
query23	18.35	1.42	1.32
query24	2.13	0.21	0.22
query25	0.15	0.08	0.09
query26	0.27	0.18	0.18
query27	0.07	0.08	0.08
query28	13.28	1.03	1.01
query29	12.63	3.25	3.23
query30	0.25	0.06	0.06
query31	2.87	0.39	0.38
query32	3.29	0.47	0.47
query33	2.85	2.90	2.93
query34	17.17	4.38	4.44
query35	4.48	4.53	4.54
query36	0.65	0.47	0.45
query37	0.18	0.15	0.15
query38	0.15	0.14	0.14
query39	0.04	0.03	0.04
query40	0.17	0.14	0.14
query41	0.09	0.04	0.04
query42	0.05	0.05	0.04
query43	0.04	0.04	0.04
Total cold run time: 109.68 s
Total hot run time: 30.8 s

@liujiwen-up
Copy link
Contributor Author

please add description about what's the behavior before

  • Before behavior change
    mysql> select split_part('abc', '|', 1);
    +---------------------------+
    | split_part('abc', '|', 1) |
    +---------------------------+
    | NULL |
    +---------------------------+
    1 row in set (0.02 sec)
  • After behavior change
    `mysql> select split_part('abc', '|', 1);
    +---------------------------+
    | split_part('abc', '|', 1) |
    +---------------------------+
    | abc |
    +---------------------------+
    1 row in set (0.01 sec)

@github-actions github-actions bot added the approved Indicates a PR has been approved by one committer. label Jun 21, 2024
@github-actions GitHub Actions
Copy link
Contributor

PR approved by at least one committer and no changes requested.

@github-actions GitHub Actions
Copy link
Contributor

PR approved by anyone and no changes requested.

@morningman morningman mentioned this pull request Jun 21, 2024
Closed
zclllyybb
zclllyybb approved these changes Jun 21, 2024
View reviewed changes
Copy link
Contributor

@zclllyybb zclllyybb left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@yiguolei yiguolei force-pushed the fix_split_part_function branch from 4decd9f to 00bae9e Compare July 15, 2024 07:03
@yiguolei
Copy link
Contributor

run buildall

@github-actions GitHub Actions
Copy link
Contributor

clang-tidy review says "All clean, LGTM! 👍"

@doris-robot
Copy link

TPC-H: Total hot run time: 39352 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 00bae9e79cf88b8e350fafc5196195c5b8f49226, data reload: false

------ Round 1 ----------------------------------
q1	17606	4322	4271	4271
q2	2013	187	180	180
q3	10451	1254	1039	1039
q4	10184	710	819	710
q5	7550	2701	2656	2656
q6	223	142	138	138
q7	957	596	595	595
q8	9207	2053	2066	2053
q9	8733	6563	6537	6537
q10	8796	3724	3803	3724
q11	486	228	230	228
q12	434	223	219	219
q13	17766	2934	2948	2934
q14	277	219	240	219
q15	521	480	484	480
q16	481	389	386	386
q17	963	639	604	604
q18	8134	7457	7295	7295
q19	7452	1392	1326	1326
q20	657	317	326	317
q21	4868	3163	3165	3163
q22	334	281	278	278
Total cold run time: 118093 ms
Total hot run time: 39352 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4387	4212	4222	4212
q2	374	275	256	256
q3	2967	2850	2887	2850
q4	1974	1655	1691	1655
q5	5563	5504	5446	5446
q6	232	132	137	132
q7	2171	1896	1894	1894
q8	3232	3391	3391	3391
q9	8735	8739	8909	8739
q10	4044	3907	3707	3707
q11	586	475	486	475
q12	830	627	651	627
q13	16313	3149	3161	3149
q14	331	302	291	291
q15	528	490	475	475
q16	486	458	438	438
q17	1807	1536	1507	1507
q18	8036	8100	7883	7883
q19	1753	1646	1482	1482
q20	2125	1878	1869	1869
q21	9528	4842	4665	4665
q22	589	507	526	507
Total cold run time: 76591 ms
Total hot run time: 55650 ms

@doris-robot
Copy link

TPC-DS: Total hot run time: 172840 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 00bae9e79cf88b8e350fafc5196195c5b8f49226, data reload: false

query1	931	377	368	368
query2	6446	1774	1791	1774
query3	6625	205	215	205
query4	28314	17535	17328	17328
query5	3654	476	498	476
query6	256	174	171	171
query7	4584	285	281	281
query8	238	206	200	200
query9	8481	2383	2378	2378
query10	432	283	269	269
query11	11518	9950	10016	9950
query12	113	90	83	83
query13	1652	383	367	367
query14	10219	7750	7883	7750
query15	221	169	163	163
query16	7070	315	310	310
query17	1824	550	520	520
query18	1772	275	272	272
query19	189	145	170	145
query20	93	79	85	79
query21	204	128	134	128
query22	4467	3974	4028	3974
query23	33876	33461	33711	33461
query24	10600	2960	2950	2950
query25	613	387	367	367
query26	697	154	156	154
query27	2178	265	273	265
query28	5994	2063	2048	2048
query29	877	663	618	618
query30	259	147	159	147
query31	1000	764	745	745
query32	96	51	57	51
query33	675	300	295	295
query34	890	474	497	474
query35	697	572	585	572
query36	1143	974	963	963
query37	145	98	94	94
query38	2950	2930	2882	2882
query39	896	852	859	852
query40	208	122	131	122
query41	50	44	45	44
query42	113	98	97	97
query43	504	464	455	455
query44	1084	721	731	721
query45	189	160	156	156
query46	1097	689	702	689
query47	1851	1757	1784	1757
query48	370	290	288	288
query49	825	401	412	401
query50	767	386	383	383
query51	7030	6661	6697	6661
query52	108	88	93	88
query53	367	285	290	285
query54	910	449	438	438
query55	73	73	72	72
query56	287	263	265	263
query57	1122	1087	1039	1039
query58	236	246	261	246
query59	2769	2632	2507	2507
query60	302	272	265	265
query61	95	95	94	94
query62	784	645	642	642
query63	316	296	302	296
query64	9150	2174	1611	1611
query65	3146	3089	3083	3083
query66	733	332	320	320
query67	15262	15148	14908	14908
query68	4715	533	521	521
query69	612	470	354	354
query70	1114	1162	1147	1147
query71	432	277	269	269
query72	7753	5905	5515	5515
query73	763	318	317	317
query74	6212	5685	5726	5685
query75	3508	2716	2697	2697
query76	3471	970	876	876
query77	617	305	302	302
query78	9753	8898	9070	8898
query79	3122	509	516	509
query80	2506	495	464	464
query81	588	217	222	217
query82	1186	139	131	131
query83	291	168	171	168
query84	278	92	86	86
query85	1421	298	291	291
query86	464	320	333	320
query87	3365	3112	3156	3112
query88	4086	2511	2439	2439
query89	470	382	390	382
query90	1788	196	190	190
query91	129	97	100	97
query92	62	49	47	47
query93	3702	491	490	490
query94	1113	212	213	212
query95	413	313	311	311
query96	604	288	274	274
query97	3184	3056	3019	3019
query98	211	200	203	200
query99	1595	1240	1255	1240
Total cold run time: 282165 ms
Total hot run time: 172840 ms

@doris-robot
Copy link

ClickBench: Total hot run time: 30.87 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 00bae9e79cf88b8e350fafc5196195c5b8f49226, data reload: false

query1	0.04	0.03	0.04
query2	0.07	0.03	0.04
query3	0.22	0.05	0.05
query4	1.68	0.09	0.08
query5	0.48	0.52	0.49
query6	1.14	0.73	0.72
query7	0.02	0.01	0.02
query8	0.05	0.05	0.04
query9	0.56	0.49	0.47
query10	0.54	0.53	0.55
query11	0.15	0.11	0.11
query12	0.14	0.12	0.13
query13	0.59	0.59	0.60
query14	0.75	0.80	0.77
query15	0.84	0.82	0.82
query16	0.37	0.37	0.37
query17	1.01	1.05	1.03
query18	0.22	0.22	0.21
query19	1.77	1.78	1.77
query20	0.01	0.01	0.02
query21	15.40	0.73	0.66
query22	4.46	6.94	2.09
query23	18.27	1.37	1.22
query24	2.11	0.22	0.24
query25	0.15	0.09	0.09
query26	0.28	0.21	0.21
query27	0.45	0.23	0.23
query28	13.19	1.03	0.99
query29	12.62	3.34	3.33
query30	0.25	0.06	0.06
query31	2.86	0.39	0.38
query32	3.29	0.47	0.48
query33	2.87	2.89	2.92
query34	17.20	4.34	4.36
query35	4.43	4.38	4.38
query36	0.66	0.51	0.49
query37	0.19	0.16	0.15
query38	0.15	0.16	0.14
query39	0.04	0.03	0.04
query40	0.16	0.12	0.11
query41	0.09	0.05	0.05
query42	0.06	0.05	0.05
query43	0.04	0.04	0.04
Total cold run time: 109.87 s
Total hot run time: 30.87 s

@github-actions GitHub Actions
Copy link
Contributor

We're closing this PR because it hasn't been updated in a while.
This isn't a judgement on the merit of the PR in any way. It's just a way of keeping the PR queue manageable.
If you'd like to revive this PR, please reopen it and feel free a maintainer to remove the Stale tag!

@github-actions github-actions bot added the Stale label Jan 12, 2025
@github-actions github-actions bot closed this Jan 13, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@zclllyybb zclllyybb zclllyybb approved these changes

@wuwenchi wuwenchi wuwenchi approved these changes

@morningman morningman morningman approved these changes

Assignees
No one assigned
Labels
approved Indicates a PR has been approved by one committer. dev/2.1.x kind/behavior-changed reviewed Stale
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
6 participants
@liujiwen-up @doris-robot @zclllyybb @yiguolei @morningman @wuwenchi