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

branch-3.0: [fix](date_function) fix str_to_date function return wrong microsecond issue #47129 #47428

Closed
wants to merge 1 commit into from
Closed

branch-3.0: [fix](date_function) fix str_to_date function return wrong microsecond issue #47129 #47428

wants to merge 1 commit into from
+70 −2

Conversation

github-actions[bot]
Copy link
Contributor

Cherry-picked from #47129

@Yulei-Yang
[fix](date_function) fix str_to_date function return wrong microsecon…
0b12f48 
…d issue (#47129)

### What problem does this PR solve?

Issue Number: close #47105

Related PR: #24932

Problem Summary:

### Release note

str_to_date always return microsecond part for datetime even if user
does not specfic %f in date format string. This is wrong.
mysql> select id,str_to_date(dt, '%Y-%m-%d %H:%i:%s') from test1 limit
1;
+------+--------------------------------------+
| id   | str_to_date(dt, '%Y-%m-%d %H:%i:%s') |
+------+--------------------------------------+
|    2 | 2024-12-28 10:11:12.000000           |
+------+--------------------------------------+

and constant fold scenario is wrong too:
mysql> select cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d
%H:%i:%s') as string);

+--------------------------------------------------------------------------+
| cast(str_to_date('2025-01-17 11:59:30', '%Y-%m-%d %H:%i:%s') as TEXT)
|

+--------------------------------------------------------------------------+
| 2025-01-17 11:59:30.000000 |

+--------------------------------------------------------------------------+


### Check List (For Author)

- Test <!-- At least one of them must be included. -->
    - [x] Regression test
    - [ ] Unit Test
    - [ ] Manual test (add detailed scripts or steps below)
    - [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
        - [ ] Previous test can cover this change.
        - [ ] No code files have been changed.
        - [ ] Other reason <!-- Add your reason?  -->

- Behavior changed:
    - [x] No.
    - [ ] Yes. <!-- Explain the behavior change -->

- Does this need documentation?
    - [x] No.
- [ ] Yes. <!-- Add document PR link here. eg:
apache/doris-website#1214 -->

### Check List (For Reviewer who merge this PR)

- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
@github-actions github-actions bot requested a review from dataroaring as a code owner January 24, 2025 10:16
@Thearas
Copy link
Contributor

Thearas commented Jan 24, 2025

Thank you for your contribution to Apache Doris.
Don't know what should be done next? See How to process your PR.

Please clearly describe your PR:

  1. What problem was fixed (it's best to include specific error reporting information). How it was fixed.
  2. Which behaviors were modified. What was the previous behavior, what is it now, why was it modified, and what possible impacts might there be.
  3. What features were added. Why was this function added?
  4. Which code was refactored and why was this part of the code refactored?
  5. Which functions were optimized and what is the difference before and after the optimization?

@dataroaring dataroaring reopened this Jan 24, 2025
@Thearas
Copy link
Contributor

Thearas commented Jan 24, 2025

run buildall

@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17924	7624	7424	7424
q2	2125	177	190	177
q3	11527	1160	1221	1160
q4	10760	741	710	710
q5	8024	2973	2958	2958
q6	235	148	149	148
q7	1008	625	625	625
q8	9519	2087	2098	2087
q9	6890	6559	6524	6524
q10	7062	2344	2338	2338
q11	475	276	251	251
q12	418	215	212	212
q13	17783	3008	3033	3008
q14	250	205	214	205
q15	563	528	540	528
q16	680	604	591	591
q17	1041	638	620	620
q18	7412	6820	6779	6779
q19	1424	1133	1145	1133
q20	485	205	198	198
q21	4374	3267	3244	3244
q22	1141	1021	1025	1021
Total cold run time: 111120 ms
Total hot run time: 41941 ms

----- Round 2, with runtime_filter_mode=off -----
q1	7369	7335	7214	7214
q2	372	255	249	249
q3	3283	3027	2943	2943
q4	2054	1854	1804	1804
q5	5760	5742	5780	5742
q6	220	137	135	135
q7	2202	1812	1829	1812
q8	3383	3586	3508	3508
q9	8996	9004	8927	8927
q10	3663	3529	3618	3529
q11	597	489	496	489
q12	793	604	604	604
q13	6452	3157	3144	3144
q14	294	268	269	268
q15	568	513	510	510
q16	690	658	658	658
q17	1846	1638	1628	1628
q18	8373	7884	7635	7635
q19	1766	1640	1517	1517
q20	2046	1786	1817	1786
q21	5380	5271	5052	5052
q22	1106	996	1019	996
Total cold run time: 67213 ms
Total hot run time: 60150 ms

@doris-robot
Copy link

TPC-DS: Total hot run time: 192172 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 0b12f488a34d329d1d5ea890b81a3ca72586e943, data reload: false

query1	968	368	369	368
query2	6533	2211	2175	2175
query3	6704	213	223	213
query4	34020	23525	23360	23360
query5	4352	456	451	451
query6	284	180	179	179
query7	4651	308	307	307
query8	287	231	221	221
query9	9622	2691	2678	2678
query10	491	260	261	260
query11	18037	15158	15182	15158
query12	156	110	99	99
query13	1638	434	422	422
query14	9783	7552	7238	7238
query15	301	172	173	172
query16	8214	466	478	466
query17	1740	558	539	539
query18	2134	300	301	300
query19	365	154	152	152
query20	114	106	101	101
query21	208	100	100	100
query22	4496	4357	4400	4357
query23	34618	33954	34065	33954
query24	11241	2802	2871	2802
query25	697	409	422	409
query26	1209	171	171	171
query27	2788	352	356	352
query28	7519	2408	2393	2393
query29	884	455	432	432
query30	325	168	162	162
query31	1005	800	813	800
query32	102	60	58	58
query33	790	319	308	308
query34	912	486	517	486
query35	979	717	725	717
query36	1082	950	935	935
query37	143	76	76	76
query38	3948	3867	3978	3867
query39	1486	1430	1456	1430
query40	207	102	102	102
query41	54	53	51	51
query42	114	105	105	105
query43	559	501	490	490
query44	1274	804	800	800
query45	192	170	173	170
query46	1143	720	709	709
query47	1917	1837	1859	1837
query48	470	380	383	380
query49	1196	399	404	399
query50	809	423	416	416
query51	7379	7055	7218	7055
query52	104	92	97	92
query53	258	192	191	191
query54	1316	477	467	467
query55	80	78	81	78
query56	271	255	277	255
query57	1238	1117	1132	1117
query58	249	222	212	212
query59	3303	3071	3135	3071
query60	303	269	271	269
query61	129	134	133	133
query62	855	664	673	664
query63	233	196	194	194
query64	5416	728	628	628
query65	3304	3218	3180	3180
query66	1485	313	329	313
query67	16004	15748	15648	15648
query68	4719	603	573	573
query69	449	269	269	269
query70	1196	1125	1097	1097
query71	405	255	269	255
query72	6442	3782	4046	3782
query73	767	350	349	349
query74	10244	8968	9009	8968
query75	3366	2634	2640	2634
query76	2995	1096	1070	1070
query77	464	290	278	278
query78	10651	9944	9600	9600
query79	1144	595	591	591
query80	726	429	456	429
query81	530	236	237	236
query82	1096	115	123	115
query83	249	144	139	139
query84	239	85	78	78
query85	1134	309	294	294
query86	314	298	283	283
query87	4526	4337	4312	4312
query88	3691	2377	2363	2363
query89	413	291	294	291
query90	2104	190	187	187
query91	184	149	150	149
query92	64	51	52	51
query93	1055	563	562	562
query94	794	290	293	290
query95	354	257	260	257
query96	616	281	284	281
query97	3390	3189	3216	3189
query98	212	204	203	203
query99	1525	1326	1303	1303
Total cold run time: 300746 ms
Total hot run time: 192172 ms

@doris-robot
Copy link

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

query1	0.03	0.03	0.03
query2	0.07	0.03	0.03
query3	0.23	0.06	0.06
query4	1.63	0.10	0.10
query5	0.51	0.50	0.50
query6	1.13	0.72	0.73
query7	0.02	0.01	0.02
query8	0.06	0.03	0.03
query9	0.56	0.49	0.50
query10	0.55	0.55	0.58
query11	0.15	0.11	0.11
query12	0.14	0.11	0.12
query13	0.62	0.59	0.58
query14	2.73	2.85	2.73
query15	0.89	0.82	0.82
query16	0.39	0.38	0.40
query17	0.98	1.01	1.06
query18	0.23	0.22	0.21
query19	1.90	1.79	1.98
query20	0.01	0.01	0.01
query21	15.36	0.59	0.58
query22	2.71	2.72	1.66
query23	16.89	0.91	0.86
query24	3.02	1.86	1.44
query25	0.23	0.19	0.14
query26	0.41	0.14	0.14
query27	0.05	0.05	0.04
query28	9.99	1.11	1.06
query29	12.62	3.31	3.27
query30	0.26	0.07	0.06
query31	2.86	0.38	0.38
query32	3.25	0.46	0.46
query33	2.99	3.00	3.06
query34	17.13	4.51	4.54
query35	4.55	4.56	4.58
query36	0.68	0.47	0.48
query37	0.10	0.06	0.06
query38	0.05	0.03	0.03
query39	0.02	0.02	0.02
query40	0.16	0.13	0.13
query41	0.07	0.02	0.03
query42	0.04	0.02	0.02
query43	0.03	0.03	0.04
Total cold run time: 106.3 s
Total hot run time: 33.21 s

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@dataroaring dataroaring Awaiting requested review from dataroaring dataroaring is a code owner

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
4 participants
@Thearas @doris-robot @dataroaring @Yulei-Yang