Skip to content

[Fix-3.0](function) Fix wrong calculation of timestampdiff of datetimev1 #54967

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
Closed
zclllyybb wants to merge 1 commit into from
Closed

[Fix-3.0](function) Fix wrong calculation of timestampdiff of datetimev1 #54967

zclllyybb wants to merge 1 commit into from

Conversation

@zclllyybb
Copy link
Contributor

@zclllyybb zclllyybb commented Aug 19, 2025 •
edited
Loading

What problem does this PR solve?

Issue Number: close #xxx

Related PR: #49429

Problem Summary:

before:

mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
|                                                    103079215078 |
+-----------------------------------------------------------------+

now:

mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
|                                                             -24 |
+-----------------------------------------------------------------+

and make fe fold of now() return v2 type

Release note

Fix wrong calculation of timestampdiff of datetimev1

Check List (For Author)

  • Test

    • 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

  • Behavior changed:

    • No.
    • Yes.

  • Does this need documentation?

    • No.
    • Yes.

Check List (For Reviewer who merge this PR)

  • Confirm the release note
  • Confirm test cases
  • Confirm document
  • Add branch pick label

@zclllyybb zclllyybb requested a review from dataroaring as a code owner August 19, 2025 01:21
@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 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?

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 19, 2025

run buildall

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

FE UT Coverage Report

Increment line coverage 25.00% (1/4) 🎉
Increment coverage report
Complete coverage report

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

------ Round 1 ----------------------------------
q1	17941	6926	6809	6809
q2	2038	198	179	179
q3	10632	1202	1256	1202
q4	10377	782	785	782
q5	7788	2979	2882	2882
q6	228	131	132	131
q7	1004	615	614	614
q8	9378	2015	2128	2015
q9	6719	6476	6478	6476
q10	7000	2275	2366	2275
q11	461	262	259	259
q12	424	212	209	209
q13	17786	2989	2965	2965
q14	243	208	217	208
q15	521	467	462	462
q16	464	381	377	377
q17	1023	571	613	571
q18	7400	6720	6798	6720
q19	1411	1179	1051	1051
q20	508	205	207	205
q21	3935	3234	3234	3234
q22	1128	991	1005	991
Total cold run time: 108409 ms
Total hot run time: 40617 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6752	6644	6660	6644
q2	337	232	237	232
q3	3144	2921	2934	2921
q4	2133	1825	1809	1809
q5	5769	5831	5817	5817
q6	221	130	128	128
q7	2256	1785	1792	1785
q8	3416	3639	3541	3541
q9	8927	9060	8982	8982
q10	3571	3555	3562	3555
q11	595	501	492	492
q12	801	604	575	575
q13	9090	3144	3201	3144
q14	309	266	273	266
q15	504	467	471	467
q16	506	464	444	444
q17	1906	1705	1652	1652
q18	8272	7913	7822	7822
q19	1822	1606	1769	1606
q20	2133	1878	1859	1859
q21	5285	5031	5032	5031
q22	1148	1056	1048	1048
Total cold run time: 68897 ms
Total hot run time: 59820 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

TPC-DS: Total hot run time: 192186 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 98a5bad3cbeb244b8788ca4609cd8e42a5561b07, data reload: false

query1	941	411	427	411
query2	6220	1889	1854	1854
query3	8685	199	200	199
query4	33964	23886	23346	23346
query5	3622	463	459	459
query6	293	190	181	181
query7	4204	316	315	315
query8	297	233	224	224
query9	9127	2549	2556	2549
query10	483	262	254	254
query11	18306	15246	15249	15246
query12	154	103	100	100
query13	1580	422	413	413
query14	9413	7118	6985	6985
query15	263	175	175	175
query16	8028	491	514	491
query17	1640	599	607	599
query18	2164	316	327	316
query19	365	162	176	162
query20	126	120	119	119
query21	206	108	107	107
query22	4732	4554	4381	4381
query23	35026	34121	34255	34121
query24	11166	3016	2929	2929
query25	606	398	389	389
query26	799	165	167	165
query27	2329	355	362	355
query28	6597	2165	2100	2100
query29	791	460	443	443
query30	281	161	166	161
query31	1025	842	824	824
query32	102	54	54	54
query33	798	298	305	298
query34	989	526	559	526
query35	865	729	751	729
query36	1112	963	983	963
query37	130	71	76	71
query38	4062	3972	3919	3919
query39	1512	1498	1441	1441
query40	214	99	105	99
query41	50	48	47	47
query42	118	102	100	100
query43	506	482	469	469
query44	1344	818	793	793
query45	184	174	173	173
query46	1176	762	734	734
query47	2003	1929	1900	1900
query48	489	388	395	388
query49	884	386	389	386
query50	842	434	420	420
query51	7333	7256	7265	7256
query52	105	94	89	89
query53	264	200	188	188
query54	1396	478	466	466
query55	82	74	78	74
query56	260	254	249	249
query57	1356	1241	1204	1204
query58	217	214	226	214
query59	3170	2992	2998	2992
query60	278	274	257	257
query61	106	108	112	108
query62	850	710	696	696
query63	231	203	200	200
query64	4113	664	653	653
query65	3399	3324	3306	3306
query66	792	291	297	291
query67	16542	15617	15592	15592
query68	4577	570	569	569
query69	445	268	258	258
query70	1155	1089	1109	1089
query71	341	262	258	258
query72	6413	4281	4018	4018
query73	753	343	350	343
query74	10570	9056	9043	9043
query75	3380	2629	2624	2624
query76	2593	1074	1024	1024
query77	364	265	271	265
query78	10413	9621	9610	9610
query79	2477	591	607	591
query80	1207	438	427	427
query81	565	219	219	219
query82	584	89	84	84
query83	235	144	149	144
query84	235	77	80	77
query85	1505	294	296	294
query86	488	304	303	303
query87	4392	4193	4259	4193
query88	4274	2364	2348	2348
query89	407	293	285	285
query90	2003	181	181	181
query91	183	148	146	146
query92	62	48	52	48
query93	2268	550	545	545
query94	864	302	292	292
query95	366	255	260	255
query96	630	295	283	283
query97	3287	3167	3174	3167
query98	214	204	206	204
query99	1527	1326	1281	1281
Total cold run time: 300084 ms
Total hot run time: 192186 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

query1	0.03	0.03	0.03
query2	0.07	0.03	0.04
query3	0.23	0.07	0.06
query4	1.61	0.10	0.10
query5	0.52	0.52	0.50
query6	1.14	0.74	0.73
query7	0.03	0.02	0.02
query8	0.04	0.04	0.04
query9	0.57	0.50	0.50
query10	0.56	0.56	0.56
query11	0.16	0.10	0.10
query12	0.14	0.11	0.11
query13	0.61	0.61	0.61
query14	0.77	0.82	0.79
query15	0.84	0.82	0.83
query16	0.39	0.40	0.39
query17	1.04	1.06	1.07
query18	0.24	0.23	0.23
query19	1.96	1.78	1.82
query20	0.02	0.02	0.01
query21	15.36	0.59	0.59
query22	2.23	2.57	1.45
query23	16.89	0.93	0.84
query24	3.42	1.68	0.66
query25	0.18	0.06	0.13
query26	0.45	0.14	0.14
query27	0.05	0.04	0.04
query28	9.85	0.51	0.50
query29	12.57	3.18	3.23
query30	0.24	0.06	0.06
query31	2.84	0.40	0.39
query32	3.22	0.46	0.45
query33	2.98	3.06	3.04
query34	17.35	4.50	4.52
query35	4.62	4.57	4.54
query36	0.66	0.49	0.48
query37	0.10	0.07	0.05
query38	0.05	0.03	0.03
query39	0.04	0.02	0.02
query40	0.16	0.12	0.12
query41	0.09	0.02	0.02
query42	0.03	0.02	0.02
query43	0.03	0.03	0.03
Total cold run time: 104.38 s
Total hot run time: 29.68 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

BE UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 42.07% (11237/26710)
Line Coverage 32.59% (96174/295112)
Region Coverage 30.52% (55216/180895)
Branch Coverage 26.85% (27330/101790)

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

------ Round 1 ----------------------------------
q1	17582	6875	6668	6668
q2	2047	189	170	170
q3	10677	1118	1138	1118
q4	10513	722	695	695
q5	7749	2824	2812	2812
q6	215	132	136	132
q7	979	629	621	621
q8	9353	1940	2032	1940
q9	6627	6356	6404	6356
q10	7042	2238	2334	2238
q11	466	270	274	270
q12	408	220	221	220
q13	17801	3000	3000	3000
q14	242	209	207	207
q15	522	459	453	453
q16	495	382	382	382
q17	992	587	574	574
q18	7191	6622	6647	6622
q19	1416	1074	1099	1074
q20	483	198	210	198
q21	4085	3260	3298	3260
q22	1093	978	999	978
Total cold run time: 107978 ms
Total hot run time: 39988 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6685	6615	6550	6550
q2	330	226	239	226
q3	2917	2973	2906	2906
q4	2038	1830	1815	1815
q5	5725	5743	5729	5729
q6	217	131	131	131
q7	2264	1784	1835	1784
q8	3440	3539	3562	3539
q9	8767	8965	8885	8885
q10	3569	3515	3542	3515
q11	601	497	510	497
q12	831	585	602	585
q13	7988	3196	3193	3193
q14	311	264	283	264
q15	514	474	473	473
q16	483	441	449	441
q17	1866	1667	1693	1667
q18	8544	7999	7929	7929
q19	1674	1495	1578	1495
q20	2068	1884	1899	1884
q21	5295	5126	5137	5126
q22	1150	1130	1056	1056
Total cold run time: 67277 ms
Total hot run time: 59690 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

TPC-DS: Total hot run time: 192579 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 49fceb5c5b3f16a1548aafe304db2ff9379260dd, data reload: false

query1	936	396	395	395
query2	6223	1896	1925	1896
query3	8702	198	199	198
query4	33721	23826	23861	23826
query5	4315	475	445	445
query6	271	180	173	173
query7	4213	313	319	313
query8	289	223	228	223
query9	9458	2558	2547	2547
query10	480	273	265	265
query11	18333	15008	15195	15008
query12	158	104	100	100
query13	1570	446	423	423
query14	9500	7148	7117	7117
query15	267	175	188	175
query16	8101	501	525	501
query17	1570	602	590	590
query18	2168	339	332	332
query19	369	164	173	164
query20	124	123	118	118
query21	214	110	110	110
query22	4718	4617	4397	4397
query23	35261	33832	34448	33832
query24	11182	2897	2992	2897
query25	659	441	427	427
query26	1194	178	180	178
query27	2374	364	381	364
query28	7297	2133	2149	2133
query29	872	477	451	451
query30	253	163	165	163
query31	1049	873	856	856
query32	99	62	59	59
query33	790	318	301	301
query34	937	515	534	515
query35	877	751	738	738
query36	1144	961	929	929
query37	130	67	69	67
query38	4100	3995	4049	3995
query39	1518	1473	1473	1473
query40	203	103	104	103
query41	49	49	48	48
query42	116	99	101	99
query43	522	463	483	463
query44	1278	821	823	821
query45	194	172	168	168
query46	1171	760	753	753
query47	2019	1915	1896	1896
query48	486	379	393	379
query49	977	426	433	426
query50	866	429	437	429
query51	7245	7133	7286	7133
query52	103	91	93	91
query53	266	192	196	192
query54	1239	481	477	477
query55	80	77	80	77
query56	272	261	261	261
query57	1310	1196	1179	1179
query58	220	213	207	207
query59	3203	3080	3112	3080
query60	292	255	271	255
query61	111	107	113	107
query62	885	735	717	717
query63	242	206	199	199
query64	5273	709	661	661
query65	3428	3295	3369	3295
query66	1081	309	309	309
query67	16084	15589	15354	15354
query68	4795	574	571	571
query69	411	257	265	257
query70	1120	1110	1118	1110
query71	336	262	254	254
query72	6300	4213	4068	4068
query73	773	349	356	349
query74	10481	9211	9162	9162
query75	3381	2663	2634	2634
query76	2824	1098	1053	1053
query77	410	273	270	270
query78	10620	9525	9600	9525
query79	2553	611	621	611
query80	1026	458	428	428
query81	551	217	213	213
query82	609	89	90	89
query83	237	144	145	144
query84	243	80	83	80
query85	1288	318	290	290
query86	431	304	295	295
query87	4374	4244	4254	4244
query88	4344	2363	2354	2354
query89	409	294	293	293
query90	2005	184	185	184
query91	185	151	153	151
query92	63	52	53	52
query93	2463	555	555	555
query94	795	294	302	294
query95	364	263	261	261
query96	618	286	277	277
query97	3329	3135	3158	3135
query98	228	200	205	200
query99	1494	1303	1327	1303
Total cold run time: 303711 ms
Total hot run time: 192579 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

query1	0.03	0.03	0.02
query2	0.06	0.03	0.03
query3	0.24	0.06	0.06
query4	1.62	0.11	0.10
query5	0.51	0.52	0.51
query6	1.13	0.73	0.73
query7	0.02	0.02	0.02
query8	0.05	0.04	0.03
query9	0.57	0.52	0.49
query10	0.56	0.55	0.55
query11	0.15	0.11	0.11
query12	0.15	0.11	0.10
query13	0.63	0.60	0.60
query14	0.78	0.80	0.79
query15	0.84	0.82	0.83
query16	0.38	0.39	0.41
query17	1.02	1.07	1.10
query18	0.24	0.21	0.22
query19	1.92	1.88	1.87
query20	0.01	0.01	0.01
query21	15.37	0.58	0.56
query22	2.32	1.62	1.31
query23	16.95	0.94	0.83
query24	3.38	1.69	0.66
query25	0.33	0.21	0.07
query26	0.32	0.15	0.14
query27	0.04	0.04	0.06
query28	9.82	0.53	0.52
query29	12.56	3.18	3.20
query30	0.25	0.06	0.06
query31	2.86	0.39	0.39
query32	3.23	0.46	0.46
query33	3.01	3.02	3.09
query34	16.88	4.54	4.55
query35	4.56	4.51	4.52
query36	0.68	0.47	0.47
query37	0.08	0.06	0.06
query38	0.05	0.04	0.04
query39	0.03	0.02	0.02
query40	0.15	0.12	0.13
query41	0.08	0.02	0.02
query42	0.03	0.02	0.02
query43	0.04	0.03	0.03
Total cold run time: 103.93 s
Total hot run time: 29.58 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

BE UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 42.08% (11239/26710)
Line Coverage 32.58% (96158/295112)
Region Coverage 30.53% (55220/180895)
Branch Coverage 26.85% (27330/101790)

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

FE UT Coverage Report

Increment line coverage 40.00% (2/5) 🎉
Increment coverage report
Complete coverage report

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

------ Round 1 ----------------------------------
q1	17606	6917	6713	6713
q2	2065	215	175	175
q3	10634	1132	1122	1122
q4	10499	686	697	686
q5	7728	2965	2868	2868
q6	217	140	141	140
q7	969	631	602	602
q8	9603	1981	2077	1981
q9	8156	6464	6435	6435
q10	7040	2237	2322	2237
q11	454	268	265	265
q12	418	215	211	211
q13	17784	3015	2946	2946
q14	248	208	206	206
q15	504	449	455	449
q16	481	371	376	371
q17	965	594	670	594
q18	7290	6601	6653	6601
q19	1404	1101	1066	1066
q20	470	198	200	198
q21	4167	3285	3257	3257
q22	1098	1019	999	999
Total cold run time: 109800 ms
Total hot run time: 40122 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6615	6526	6615	6526
q2	340	231	235	231
q3	3071	2951	2909	2909
q4	2093	1817	1861	1817
q5	5722	5714	5732	5714
q6	219	131	134	131
q7	2255	1840	1804	1804
q8	3433	3532	3419	3419
q9	8877	9000	8863	8863
q10	3580	3525	3549	3525
q11	583	495	489	489
q12	822	612	608	608
q13	6652	3193	3118	3118
q14	298	284	285	284
q15	521	452	459	452
q16	504	463	436	436
q17	1892	1629	1625	1625
q18	8278	7696	7712	7696
q19	1675	1584	1545	1545
q20	2114	1878	1868	1868
q21	5176	4981	5017	4981
q22	1125	1023	1048	1023
Total cold run time: 65845 ms
Total hot run time: 59064 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

TPC-DS: Total hot run time: 192821 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 df3c55f761818089dd4469437ce869b99ed7e557, data reload: false

query1	940	401	406	401
query2	6254	1868	1857	1857
query3	8692	197	196	196
query4	33740	23955	23445	23445
query5	4036	454	437	437
query6	279	176	178	176
query7	4201	305	310	305
query8	281	228	224	224
query9	9459	2535	2543	2535
query10	484	264	266	264
query11	17870	15177	15204	15177
query12	159	101	100	100
query13	1547	430	440	430
query14	8881	7374	7208	7208
query15	248	175	185	175
query16	8019	492	503	492
query17	1654	612	582	582
query18	2139	343	329	329
query19	239	155	154	154
query20	121	125	129	125
query21	206	108	106	106
query22	4952	4623	4594	4594
query23	35095	34320	36060	34320
query24	12475	2943	2997	2943
query25	567	440	417	417
query26	1071	173	172	172
query27	2920	375	375	375
query28	7569	2143	2139	2139
query29	744	479	462	462
query30	261	174	166	166
query31	1068	799	823	799
query32	106	57	58	57
query33	770	324	316	316
query34	968	522	533	522
query35	851	752	764	752
query36	1139	972	952	952
query37	190	68	70	68
query38	4065	3978	3913	3913
query39	1507	1466	1443	1443
query40	256	105	103	103
query41	50	52	50	50
query42	122	101	103	101
query43	528	490	470	470
query44	1286	810	832	810
query45	187	175	169	169
query46	1165	749	746	746
query47	1999	1880	1923	1880
query48	482	397	387	387
query49	1042	413	405	405
query50	833	423	438	423
query51	7500	7282	7100	7100
query52	99	90	93	90
query53	278	193	184	184
query54	1077	460	461	460
query55	82	81	81	81
query56	277	257	268	257
query57	1321	1215	1186	1186
query58	231	207	227	207
query59	3140	3098	2976	2976
query60	304	252	277	252
query61	111	104	104	104
query62	847	703	710	703
query63	227	203	196	196
query64	4792	672	630	630
query65	3349	3306	3335	3306
query66	1021	294	291	291
query67	15989	15868	15735	15735
query68	4874	581	567	567
query69	434	258	268	258
query70	1153	1127	1086	1086
query71	322	249	263	249
query72	5892	4170	4037	4037
query73	768	348	349	348
query74	10125	9021	8915	8915
query75	3372	2655	2621	2621
query76	2771	1068	1165	1068
query77	373	273	275	273
query78	10692	9597	9721	9597
query79	2474	605	607	605
query80	1009	426	434	426
query81	549	219	229	219
query82	577	89	86	86
query83	214	142	139	139
query84	235	83	77	77
query85	1486	295	292	292
query86	465	293	292	292
query87	4403	4213	4292	4213
query88	4276	2389	2429	2389
query89	409	294	297	294
query90	1830	187	186	186
query91	187	151	146	146
query92	67	50	49	49
query93	2372	546	549	546
query94	713	291	258	258
query95	361	255	249	249
query96	613	282	284	282
query97	3314	3156	3136	3136
query98	225	201	194	194
query99	1526	1305	1294	1294
Total cold run time: 302443 ms
Total hot run time: 192821 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

query1	0.04	0.03	0.02
query2	0.06	0.03	0.03
query3	0.23	0.06	0.06
query4	1.64	0.11	0.10
query5	0.55	0.51	0.52
query6	1.13	0.72	0.76
query7	0.02	0.01	0.01
query8	0.05	0.04	0.04
query9	0.56	0.51	0.49
query10	0.56	0.55	0.56
query11	0.14	0.10	0.10
query12	0.13	0.11	0.11
query13	0.60	0.59	0.59
query14	0.77	0.81	0.79
query15	0.85	0.83	0.83
query16	0.40	0.42	0.41
query17	1.08	1.07	1.04
query18	0.24	0.22	0.21
query19	1.82	1.86	1.83
query20	0.02	0.01	0.01
query21	15.38	0.60	0.56
query22	2.11	1.64	1.72
query23	16.90	1.04	0.78
query24	3.31	0.65	1.33
query25	0.18	0.08	0.09
query26	0.55	0.15	0.15
query27	0.06	0.03	0.04
query28	10.42	0.48	0.46
query29	12.58	3.19	3.15
query30	0.24	0.06	0.06
query31	2.87	0.39	0.38
query32	3.25	0.47	0.46
query33	3.02	3.03	3.03
query34	17.30	4.51	4.56
query35	4.62	4.56	4.57
query36	0.67	0.48	0.47
query37	0.09	0.06	0.06
query38	0.05	0.04	0.03
query39	0.04	0.02	0.03
query40	0.15	0.12	0.13
query41	0.08	0.02	0.03
query42	0.04	0.02	0.02
query43	0.03	0.03	0.03
Total cold run time: 104.83 s
Total hot run time: 29.72 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

BE UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 42.07% (11237/26710)
Line Coverage 32.59% (96184/295112)
Region Coverage 30.53% (55220/180895)
Branch Coverage 26.85% (27334/101790)

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

BE Regression && UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 75.07% (19707/26251)
Line Coverage 68.33% (201110/294333)
Region Coverage 66.47% (120416/181167)
Branch Coverage 59.84% (61144/102186)

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

------ Round 1 ----------------------------------
q1	17647	6916	6662	6662
q2	2063	187	171	171
q3	10680	1142	1180	1142
q4	10539	821	743	743
q5	7705	2850	2872	2850
q6	213	128	128	128
q7	959	606	592	592
q8	9351	1984	2044	1984
q9	6709	6470	6465	6465
q10	6988	2274	2293	2274
q11	461	260	258	258
q12	399	212	211	211
q13	17781	2962	2982	2962
q14	242	202	205	202
q15	502	464	462	462
q16	475	373	380	373
q17	977	577	640	577
q18	7294	6601	6701	6601
q19	1400	1098	1065	1065
q20	484	196	205	196
q21	3858	3184	3261	3184
q22	1080	997	998	997
Total cold run time: 107807 ms
Total hot run time: 40099 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6559	6566	6555	6555
q2	342	230	233	230
q3	2916	2974	2927	2927
q4	2040	1808	1853	1808
q5	5710	5737	5703	5703
q6	207	124	124	124
q7	2222	1750	1796	1750
q8	3395	3543	3558	3543
q9	8749	8938	8850	8850
q10	3567	3573	3550	3550
q11	599	492	493	492
q12	790	606	599	599
q13	6445	3155	3194	3155
q14	307	272	268	268
q15	525	468	457	457
q16	480	436	434	434
q17	1860	1640	1623	1623
q18	8285	7920	7825	7825
q19	1670	1590	1602	1590
q20	2033	1861	1869	1861
q21	5160	5080	5051	5051
q22	1146	1081	1062	1062
Total cold run time: 65007 ms
Total hot run time: 59457 ms

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 19, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

------ Round 1 ----------------------------------
q1	17602	6776	6725	6725
q2	2074	172	173	172
q3	10524	1158	1172	1158
q4	10216	741	736	736
q5	7711	2929	2873	2873
q6	216	135	135	135
q7	967	622	599	599
q8	9593	2018	1991	1991
q9	8250	6460	6438	6438
q10	7053	2308	2311	2308
q11	460	259	264	259
q12	504	213	215	213
q13	17794	2992	3028	2992
q14	241	216	205	205
q15	503	466	458	458
q16	491	373	385	373
q17	997	611	541	541
q18	7341	6741	6736	6736
q19	1386	1042	1078	1042
q20	479	199	205	199
q21	3914	3229	3187	3187
q22	1115	1016	1001	1001
Total cold run time: 109431 ms
Total hot run time: 40341 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6696	6560	6613	6560
q2	327	226	225	225
q3	2969	2812	2780	2780
q4	1976	1737	1687	1687
q5	5459	5475	5471	5471
q6	211	128	128	128
q7	2124	1689	1724	1689
q8	3300	3491	3472	3472
q9	8569	8559	8595	8559
q10	3493	3444	3455	3444
q11	577	484	473	473
q12	777	560	575	560
q13	3724	2989	2974	2974
q14	295	263	257	257
q15	502	464	463	463
q16	478	421	412	412
q17	1818	1610	1580	1580
q18	7644	7400	7372	7372
q19	1670	1600	1536	1536
q20	2030	1810	1830	1810
q21	5140	4919	4981	4919
q22	1097	1001	955	955
Total cold run time: 60876 ms
Total hot run time: 57326 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

TPC-DS: Total hot run time: 190230 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 b67f718c1d5ba1eb1e8337a620c712a4905ea6be, data reload: false

query1	971	396	401	396
query2	6301	1875	1825	1825
query3	6573	214	214	214
query4	33612	23934	23549	23549
query5	4366	452	463	452
query6	280	183	181	181
query7	4505	310	307	307
query8	293	223	229	223
query9	9226	2561	2551	2551
query10	484	270	266	266
query11	18100	15278	15270	15270
query12	154	101	104	101
query13	1426	425	427	425
query14	9673	6657	7105	6657
query15	226	173	177	173
query16	7352	467	465	465
query17	1334	551	547	547
query18	1889	296	304	296
query19	351	162	161	161
query20	120	110	113	110
query21	178	109	105	105
query22	4520	4477	4275	4275
query23	34185	33137	33461	33137
query24	10096	2827	2900	2827
query25	644	427	402	402
query26	1368	170	175	170
query27	2680	349	343	343
query28	6726	2123	2078	2078
query29	833	443	447	443
query30	324	165	161	161
query31	1034	811	815	811
query32	101	62	60	60
query33	703	302	313	302
query34	933	494	512	494
query35	838	726	722	722
query36	1090	970	926	926
query37	141	69	71	69
query38	3971	3822	3812	3812
query39	1510	1438	1442	1438
query40	272	108	104	104
query41	56	54	52	52
query42	119	110	104	104
query43	502	487	465	465
query44	1152	809	815	809
query45	191	171	182	171
query46	1171	708	726	708
query47	1953	1815	1836	1815
query48	465	374	369	369
query49	1243	412	415	412
query50	814	410	411	410
query51	7230	7079	7088	7079
query52	100	93	93	93
query53	268	194	190	190
query54	1268	481	469	469
query55	81	81	81	81
query56	283	272	268	268
query57	1293	1167	1162	1162
query58	232	218	224	218
query59	3018	2930	2834	2834
query60	297	274	267	267
query61	137	133	135	133
query62	865	690	664	664
query63	222	200	195	195
query64	4824	759	633	633
query65	3318	3225	3190	3190
query66	1345	319	306	306
query67	16159	15800	15773	15773
query68	3995	592	597	592
query69	423	269	271	269
query70	1176	1105	1066	1066
query71	375	258	259	258
query72	5577	4083	4019	4019
query73	762	349	354	349
query74	10109	9282	9100	9100
query75	3360	2657	2618	2618
query76	2671	1165	1036	1036
query77	437	272	271	271
query78	10517	9750	9563	9563
query79	1098	590	587	587
query80	750	459	431	431
query81	509	225	220	220
query82	1106	90	89	89
query83	241	153	147	147
query84	262	85	78	78
query85	1134	330	285	285
query86	326	307	291	291
query87	4364	4149	4360	4149
query88	3593	2399	2366	2366
query89	405	307	290	290
query90	2090	185	188	185
query91	187	153	153	153
query92	62	51	53	51
query93	1078	547	542	542
query94	814	301	302	301
query95	346	259	249	249
query96	609	282	287	282
query97	3290	3183	3159	3159
query98	217	213	200	200
query99	1640	1328	1316	1316
Total cold run time: 291512 ms
Total hot run time: 190230 ms

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

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

query1	0.03	0.02	0.03
query2	0.07	0.03	0.04
query3	0.23	0.07	0.07
query4	1.63	0.10	0.11
query5	0.51	0.50	0.50
query6	1.16	0.72	0.72
query7	0.03	0.03	0.02
query8	0.04	0.03	0.03
query9	0.56	0.51	0.49
query10	0.58	0.54	0.56
query11	0.14	0.11	0.11
query12	0.14	0.10	0.10
query13	0.61	0.60	0.60
query14	0.76	0.82	0.78
query15	0.82	0.81	0.82
query16	0.38	0.39	0.38
query17	1.01	1.03	1.07
query18	0.25	0.22	0.22
query19	1.96	1.91	1.82
query20	0.02	0.01	0.02
query21	15.41	0.59	0.58
query22	2.33	2.95	1.32
query23	16.96	1.04	0.78
query24	3.41	1.35	0.28
query25	0.28	0.15	0.04
query26	0.33	0.15	0.13
query27	0.05	0.04	0.04
query28	10.58	0.52	0.49
query29	12.61	3.20	3.20
query30	0.24	0.06	0.06
query31	2.85	0.39	0.38
query32	3.24	0.46	0.45
query33	3.00	3.04	3.02
query34	17.26	4.54	4.56
query35	4.51	4.55	4.57
query36	0.66	0.48	0.47
query37	0.09	0.06	0.06
query38	0.05	0.03	0.03
query39	0.04	0.03	0.02
query40	0.17	0.13	0.13
query41	0.08	0.02	0.03
query42	0.03	0.02	0.02
query43	0.04	0.04	0.03
Total cold run time: 105.15 s
Total hot run time: 29.02 s

@doris-robot
Copy link

doris-robot commented Aug 19, 2025

BE UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 42.08% (11239/26710)
Line Coverage 32.59% (96176/295112)
Region Coverage 30.53% (55231/180895)
Branch Coverage 26.86% (27338/101790)

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 19, 2025

FE UT Coverage Report

Increment line coverage 50.00% (2/4) 🎉
Increment coverage report
Complete coverage report

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Aug 20, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Aug 20, 2025

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

------ Round 1 ----------------------------------
q1	17584	6773	6716	6716
q2	2081	198	171	171
q3	11228	1178	1178	1178
q4	10731	776	807	776
q5	8553	2984	2911	2911
q6	215	132	132	132
q7	970	631	625	625
q8	9608	1979	1935	1935
q9	7010	6411	6402	6402
q10	6968	2242	2282	2242
q11	461	275	272	272
q12	392	219	228	219
q13	17792	2980	3000	2980
q14	229	201	204	201
q15	507	459	472	459
q16	454	378	379	378
q17	980	583	573	573
q18	7360	6645	6577	6577
q19	1412	1067	977	977
q20	481	202	204	202
q21	4019	3215	3195	3195
q22	1129	985	1001	985
Total cold run time: 110164 ms
Total hot run time: 40106 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6554	6578	6641	6578
q2	324	234	228	228
q3	3054	3025	2996	2996
q4	2009	1836	1810	1810
q5	5757	5770	5676	5676
q6	217	129	127	127
q7	2162	1815	1779	1779
q8	3355	3525	3537	3525
q9	8906	8970	8947	8947
q10	3603	3516	3569	3516
q11	600	498	506	498
q12	848	566	611	566
q13	7497	3154	3137	3137
q14	306	276	262	262
q15	518	470	464	464
q16	483	437	434	434
q17	1846	1600	1604	1600
q18	8324	7550	7694	7550
q19	1688	1665	1609	1609
q20	2115	1910	1825	1825
q21	5331	4918	4983	4918
q22	1118	994	1015	994
Total cold run time: 66615 ms
Total hot run time: 59039 ms

@doris-robot
Copy link

doris-robot commented Aug 20, 2025

TPC-DS: Total hot run time: 190724 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 e7e7dbbca74f38b8c343eac1c1e03c144f261951, data reload: false

query1	944	384	365	365
query2	6517	2020	1935	1935
query3	6702	220	215	215
query4	33950	23865	23475	23475
query5	4320	451	457	451
query6	260	172	182	172
query7	4624	310	305	305
query8	284	226	218	218
query9	9582	2596	2586	2586
query10	488	269	276	269
query11	18250	15152	15122	15122
query12	158	107	106	106
query13	1662	428	423	423
query14	9591	7056	6566	6566
query15	229	170	177	170
query16	8045	492	483	483
query17	1623	584	561	561
query18	2120	318	309	309
query19	232	164	163	163
query20	115	111	106	106
query21	205	105	109	105
query22	4462	4368	4270	4270
query23	34246	33846	33739	33739
query24	11061	2792	2804	2792
query25	701	413	418	413
query26	1433	170	173	170
query27	2947	346	352	346
query28	7768	2108	2094	2094
query29	936	442	441	441
query30	316	172	159	159
query31	1020	783	801	783
query32	100	60	66	60
query33	790	310	303	303
query34	914	496	525	496
query35	845	717	710	710
query36	1079	915	933	915
query37	134	75	69	69
query38	4078	3921	3834	3834
query39	1501	1442	1519	1442
query40	293	103	101	101
query41	55	52	53	52
query42	115	99	105	99
query43	543	491	469	469
query44	1278	807	811	807
query45	189	166	169	166
query46	1148	732	712	712
query47	1940	1838	1854	1838
query48	458	373	392	373
query49	1210	426	407	407
query50	823	421	436	421
query51	7306	7197	7035	7035
query52	101	95	88	88
query53	266	187	191	187
query54	1237	477	482	477
query55	76	83	77	77
query56	286	262	251	251
query57	1267	1150	1165	1150
query58	247	218	237	218
query59	3148	2938	3026	2938
query60	291	264	267	264
query61	140	133	136	133
query62	908	660	694	660
query63	220	196	197	196
query64	5486	703	638	638
query65	3301	3187	3215	3187
query66	1460	309	307	307
query67	15825	15643	15604	15604
query68	4628	587	578	578
query69	428	261	259	259
query70	1157	1121	1124	1121
query71	328	251	254	251
query72	6370	4137	4131	4131
query73	749	348	354	348
query74	10116	9096	9249	9096
query75	3365	2630	2643	2630
query76	2898	1074	1050	1050
query77	476	283	289	283
query78	10410	9630	9620	9620
query79	2263	611	612	611
query80	1158	431	421	421
query81	538	222	223	222
query82	890	87	84	84
query83	219	150	141	141
query84	233	84	80	80
query85	1357	330	280	280
query86	431	271	300	271
query87	4379	4225	4353	4225
query88	3727	2369	2363	2363
query89	406	298	293	293
query90	2037	190	187	187
query91	185	151	150	150
query92	70	51	52	51
query93	1541	574	587	574
query94	964	308	272	272
query95	359	254	255	254
query96	613	288	283	283
query97	3269	3091	3157	3091
query98	214	205	197	197
query99	1574	1320	1311	1311
Total cold run time: 301201 ms
Total hot run time: 190724 ms

@doris-robot
Copy link

doris-robot commented Aug 20, 2025

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

query1	0.03	0.03	0.03
query2	0.06	0.03	0.03
query3	0.23	0.06	0.06
query4	1.63	0.10	0.11
query5	0.53	0.51	0.50
query6	1.14	0.72	0.73
query7	0.02	0.01	0.02
query8	0.04	0.04	0.03
query9	0.57	0.49	0.50
query10	0.57	0.55	0.55
query11	0.14	0.11	0.11
query12	0.14	0.11	0.12
query13	0.61	0.61	0.60
query14	0.77	0.81	0.78
query15	0.84	0.83	0.82
query16	0.38	0.38	0.40
query17	1.06	0.98	0.98
query18	0.24	0.23	0.23
query19	1.94	1.82	1.82
query20	0.02	0.01	0.01
query21	15.39	0.60	0.57
query22	2.24	1.79	1.67
query23	16.89	1.00	0.90
query24	3.35	1.56	0.91
query25	0.11	0.06	0.14
query26	0.45	0.14	0.12
query27	0.05	0.04	0.05
query28	9.75	0.56	0.48
query29	12.58	3.22	3.21
query30	0.24	0.06	0.06
query31	2.86	0.38	0.38
query32	3.23	0.47	0.46
query33	2.97	3.02	3.01
query34	17.15	4.51	4.53
query35	4.55	4.54	4.53
query36	0.69	0.48	0.47
query37	0.09	0.06	0.06
query38	0.04	0.03	0.04
query39	0.03	0.02	0.02
query40	0.16	0.12	0.13
query41	0.08	0.02	0.02
query42	0.03	0.03	0.02
query43	0.04	0.03	0.03
Total cold run time: 103.93 s
Total hot run time: 30.04 s

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 20, 2025

BE UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 42.07% (11237/26711)
Line Coverage 32.58% (96141/295119)
Region Coverage 30.53% (55222/180898)
Branch Coverage 26.84% (27321/101792)

@hello-stephen
Copy link
Contributor

hello-stephen commented Aug 20, 2025

BE Regression && UT Coverage Report

Increment line coverage 100.00% (2/2) 🎉

Increment coverage report
Complete coverage report

Category Coverage
Function Coverage 75.21% (19709/26204)
Line Coverage 68.43% (201049/293804)
Region Coverage 66.52% (120400/181004)
Branch Coverage 59.83% (61098/102116)

@zclllyybb
Copy link
Contributor Author

zclllyybb commented Oct 13, 2025

only fix in 3.1 by #56893

@zclllyybb zclllyybb closed this Oct 13, 2025
@zclllyybb zclllyybb deleted the tsdiff3.0 branch October 21, 2025 10:56
Hastyshell pushed a commit to Hastyshell/doris that referenced this pull request Nov 12, 2025
@zclllyybb
[Fix-3.0](function) Fix wrong calculation of timestampdiff of datetim…
067491d 
…ev1 (apache#5237)

pick apache#54967


Related PR: apache#49429

Problem Summary:

before:
```sql
mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
|                                                    103079215078 |
+-----------------------------------------------------------------+
```
now:
```sql
mysql> select hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) from my_doris_table;
+-----------------------------------------------------------------+
| hours_diff(now(), date_format(event_date, '%Y-%m-%d %H:00:00')) |
+-----------------------------------------------------------------+
|                                                             -24 |
+-----------------------------------------------------------------+
```

and make fe fold of `now()` return v2 type

### Release note

Fix wrong calculation of timestampdiff of datetimev1
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

Assignees

No one assigned

Labels

p0_w

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@zclllyybb @hello-stephen @doris-robot