doris icon indicating copy to clipboard operation
doris copied to clipboard

Published 20 hours ago • verified publisher icon apache

[feature](functions) impl function previous_day

Open codetyri0n opened this issue 5 months ago • 8 comments

What problem does this PR solve?

This PR brings in the implementation of the previous_day SQL function for Apache Doris. Issue Number: close #44932

Related PR: #xxx

Problem Summary:

Release note

Syntax

previous_day(expr, dayOfWeek)

Arguments

expr: A DATE expression. dayOfWeek: A STRING expression identifying a day of the week.

Returns

A DATE.

dayOfWeek must be one of the following (case insensitive):

'SU', 'SUN', 'SUNDAY'
'MO', 'MON', 'MONDAY'
'TU', 'TUE', 'TUESDAY'
'WE', 'WED', 'WEDNESDAY'
'TH', 'THU', 'THURSDAY'
'FR', 'FRI', 'FRIDAY'
'SA', 'SAT', 'SATURDAY'

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

codetyri0n avatar Jun 17 '25 09:06 codetyri0n

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?

hello-stephen avatar Jun 17 '25 09:06 hello-stephen

@zclllyybb @morningman @morrySnow Please review this, thank you.

codetyri0n avatar Jun 17 '25 09:06 codetyri0n

Hi @codetyri0n , thanks for contributing. Plz run sh build-support/clang-format.sh to format the BE code.

morningman avatar Jun 17 '25 14:06 morningman

Hi @codetyri0n , thanks for contributing. Plz run sh build-support/clang-format.sh to format the BE code. Thanks @morningman, Initial checks pass after BE code formatting

codetyri0n avatar Jun 17 '25 15:06 codetyri0n

Hi @codetyri0n , thanks for contributing. Plz add a doc in https://github.com/apache/doris-website thank you

HappenLee avatar Jun 17 '25 15:06 HappenLee

Hi @codetyri0n , thanks for contributing. Plz add a doc in https://github.com/apache/doris-website thank you

hi @HappenLee I have added the docs and created the PR for the same - https://github.com/apache/doris-website/pull/2504

codetyri0n avatar Jun 18 '25 08:06 codetyri0n

run buildall

codetyri0n avatar Jun 19 '25 05:06 codetyri0n

run buildall

codetyri0n avatar Jun 19 '25 05:06 codetyri0n

run buildall

codetyri0n avatar Jun 19 '25 15:06 codetyri0n

run buildall

codetyri0n avatar Jul 03 '25 05:07 codetyri0n

run buildall

zclllyybb avatar Jul 14 '25 08:07 zclllyybb

run buildall

codetyri0n avatar Jul 20 '25 11:07 codetyri0n

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

github-actions[bot] avatar Jul 23 '25 06:07 github-actions[bot]

PR approved by anyone and no changes requested.

github-actions[bot] avatar Jul 23 '25 06:07 github-actions[bot]

run buildall

codetyri0n avatar Jul 24 '25 07:07 codetyri0n

run buildall

codetyri0n avatar Jul 24 '25 16:07 codetyri0n

BE UT Coverage Report

Increment line coverage 78.16% (68/87) :tada:

Increment coverage report Complete coverage report

Category Coverage
Function Coverage 57.57% (15946/27698)
Line Coverage 46.35% (143329/309202)
Region Coverage 35.79% (107992/301773)
Branch Coverage 38.30% (47649/124411)

hello-stephen avatar Jul 24 '25 18:07 hello-stephen

run buildall

codetyri0n avatar Jul 25 '25 03:07 codetyri0n

run buildall

codetyri0n avatar Jul 25 '25 04:07 codetyri0n

BE UT Coverage Report

Increment line coverage 78.16% (68/87) :tada:

Increment coverage report Complete coverage report

Category Coverage
Function Coverage 57.57% (15946/27698)
Line Coverage 46.36% (143359/309202)
Region Coverage 35.81% (108052/301773)
Branch Coverage 38.33% (47684/124411)

doris-robot avatar Jul 25 '25 06:07 doris-robot

run buildall

codetyri0n avatar Jul 25 '25 07:07 codetyri0n

run buildall

codetyri0n avatar Jul 25 '25 09:07 codetyri0n

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

------ Round 1 ----------------------------------
q1	17576	5296	5117	5117
q2	1961	275	186	186
q3	10500	1320	725	725
q4	10285	1037	513	513
q5	8488	2409	2345	2345
q6	185	159	131	131
q7	895	761	603	603
q8	9321	1295	1104	1104
q9	6911	5119	5154	5119
q10	6942	2368	1950	1950
q11	497	300	278	278
q12	357	362	213	213
q13	17783	3740	3112	3112
q14	232	238	225	225
q15	565	479	480	479
q16	430	445	370	370
q17	593	876	348	348
q18	7613	7132	7131	7131
q19	1289	963	550	550
q20	339	347	220	220
q21	3951	3154	2340	2340
q22	1040	1010	971	971
Total cold run time: 107753 ms
Total hot run time: 34030 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5193	5106	5214	5106
q2	239	319	214	214
q3	2207	2675	2282	2282
q4	1365	1809	1345	1345
q5	4458	4444	4577	4444
q6	216	167	122	122
q7	2063	1926	1831	1831
q8	2661	2635	2641	2635
q9	7360	7294	7339	7294
q10	3116	3553	2862	2862
q11	574	532	491	491
q12	679	788	644	644
q13	3624	4007	3296	3296
q14	275	310	282	282
q15	503	482	473	473
q16	458	560	450	450
q17	1214	1549	1490	1490
q18	8011	7948	7382	7382
q19	826	789	849	789
q20	1927	1978	1828	1828
q21	4756	4293	4287	4287
q22	1033	1021	1011	1011
Total cold run time: 52758 ms
Total hot run time: 50558 ms

doris-robot avatar Jul 25 '25 10:07 doris-robot

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

query1	997	386	422	386
query2	6504	1745	1660	1660
query3	6754	224	219	219
query4	26582	23618	22959	22959
query5	4399	620	502	502
query6	312	233	219	219
query7	4629	505	304	304
query8	272	228	213	213
query9	8597	2922	2896	2896
query10	492	339	284	284
query11	15974	15070	14829	14829
query12	163	115	119	115
query13	1661	540	418	418
query14	9402	5896	5928	5896
query15	211	194	171	171
query16	7436	643	489	489
query17	1231	733	620	620
query18	2012	419	327	327
query19	205	195	168	168
query20	120	119	116	116
query21	216	123	108	108
query22	4214	4270	3988	3988
query23	34454	32951	33063	32951
query24	8093	2381	2405	2381
query25	526	482	413	413
query26	933	277	179	179
query27	2682	530	347	347
query28	4312	2213	2205	2205
query29	661	564	450	450
query30	281	220	193	193
query31	936	824	762	762
query32	88	77	79	77
query33	547	400	330	330
query34	792	846	515	515
query35	782	858	759	759
query36	979	1011	912	912
query37	123	100	88	88
query38	4096	4158	4078	4078
query39	1462	1409	1410	1409
query40	220	135	125	125
query41	60	59	57	57
query42	123	113	115	113
query43	518	511	472	472
query44	1392	867	870	867
query45	175	166	165	165
query46	893	1006	648	648
query47	1775	1823	1735	1735
query48	391	424	319	319
query49	666	474	404	404
query50	682	709	398	398
query51	5564	5624	5374	5374
query52	115	110	106	106
query53	237	267	190	190
query54	603	595	536	536
query55	91	85	87	85
query56	324	334	361	334
query57	1180	1177	1129	1129
query58	285	265	276	265
query59	2557	2649	2527	2527
query60	348	348	327	327
query61	129	122	129	122
query62	842	721	628	628
query63	231	192	194	192
query64	3563	1018	694	694
query65	4256	4180	4192	4180
query66	981	409	337	337
query67	15853	15413	15468	15413
query68	8507	925	569	569
query69	479	323	284	284
query70	1241	1165	1185	1165
query71	512	338	331	331
query72	5314	4740	4625	4625
query73	718	589	363	363
query74	9173	8890	8638	8638
query75	3939	3062	2692	2692
query76	3603	1142	737	737
query77	808	406	316	316
query78	9827	10078	9224	9224
query79	2638	827	587	587
query80	650	596	469	469
query81	456	274	226	226
query82	471	139	108	108
query83	293	266	243	243
query84	296	111	90	90
query85	770	358	332	332
query86	350	331	297	297
query87	4498	4457	4276	4276
query88	2926	2268	2290	2268
query89	424	317	289	289
query90	1947	239	229	229
query91	139	190	119	119
query92	87	71	73	71
query93	1444	978	652	652
query94	680	398	298	298
query95	392	324	321	321
query96	486	607	283	283
query97	2711	2763	2616	2616
query98	232	214	208	208
query99	1441	1393	1295	1295
Total cold run time: 275636 ms
Total hot run time: 186899 ms

doris-robot avatar Jul 25 '25 10:07 doris-robot

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

query1	0.05	0.04	0.04
query2	0.09	0.04	0.04
query3	0.25	0.08	0.07
query4	1.67	0.11	0.11
query5	0.41	0.42	0.41
query6	1.18	0.64	0.67
query7	0.03	0.02	0.01
query8	0.05	0.04	0.04
query9	0.60	0.52	0.52
query10	0.59	0.56	0.57
query11	0.15	0.11	0.11
query12	0.15	0.12	0.13
query13	0.64	0.61	0.61
query14	0.81	0.83	0.83
query15	0.92	0.86	0.88
query16	0.42	0.39	0.39
query17	1.09	1.06	1.07
query18	0.21	0.20	0.19
query19	1.94	1.93	1.86
query20	0.01	0.02	0.01
query21	15.38	0.89	0.54
query22	0.80	1.23	0.79
query23	14.76	1.41	0.64
query24	6.44	1.47	0.95
query25	0.48	0.15	0.09
query26	0.62	0.17	0.15
query27	0.07	0.05	0.05
query28	9.53	0.91	0.42
query29	12.59	3.92	3.35
query30	3.12	3.04	2.96
query31	2.82	0.58	0.38
query32	3.25	0.54	0.48
query33	3.12	3.19	3.11
query34	16.16	5.44	4.80
query35	4.89	4.86	4.88
query36	0.70	0.51	0.49
query37	0.10	0.07	0.06
query38	0.05	0.05	0.04
query39	0.04	0.02	0.02
query40	0.17	0.14	0.13
query41	0.08	0.03	0.03
query42	0.04	0.02	0.03
query43	0.04	0.03	0.04
Total cold run time: 106.51 s
Total hot run time: 32.87 s

doris-robot avatar Jul 25 '25 10:07 doris-robot

BE UT Coverage Report

Increment line coverage 78.16% (68/87) :tada:

Increment coverage report Complete coverage report

Category Coverage
Function Coverage 57.57% (15948/27702)
Line Coverage 46.36% (143384/309291)
Region Coverage 35.78% (107995/301800)
Branch Coverage 38.31% (47671/124429)

hello-stephen avatar Jul 25 '25 11:07 hello-stephen

run buildall

codetyri0n avatar Jul 25 '25 12:07 codetyri0n

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

------ Round 1 ----------------------------------
q1	17609	5234	5072	5072
q2	1928	292	174	174
q3	10314	1327	698	698
q4	10220	993	528	528
q5	7520	2500	2279	2279
q6	182	157	128	128
q7	895	739	592	592
q8	9310	1294	1056	1056
q9	7162	5082	5066	5066
q10	6903	2353	1975	1975
q11	490	289	271	271
q12	341	343	222	222
q13	17756	3696	3056	3056
q14	217	242	210	210
q15	549	483	486	483
q16	424	420	375	375
q17	614	871	350	350
q18	7718	7169	7124	7124
q19	1227	965	552	552
q20	346	356	218	218
q21	3858	3147	2422	2422
q22	1066	1037	966	966
Total cold run time: 106649 ms
Total hot run time: 33817 ms

----- Round 2, with runtime_filter_mode=off -----
q1	5191	5132	5199	5132
q2	236	320	219	219
q3	2148	2641	2321	2321
q4	1366	1787	1338	1338
q5	4189	4611	4562	4562
q6	214	172	130	130
q7	1999	1942	1796	1796
q8	2630	2638	2656	2638
q9	7332	7278	7270	7270
q10	3090	3256	2873	2873
q11	579	499	515	499
q12	686	855	643	643
q13	3686	3970	3444	3444
q14	280	327	273	273
q15	523	493	478	478
q16	460	507	452	452
q17	1185	1516	1397	1397
q18	7975	7809	7643	7643
q19	857	846	976	846
q20	2001	2002	1872	1872
q21	4647	4414	4251	4251
q22	1049	1020	974	974
Total cold run time: 52323 ms
Total hot run time: 51051 ms

doris-robot avatar Jul 25 '25 12:07 doris-robot

TPC-DS: Total hot run time: 186302 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 2e961c75e72afb90999d49593cb48f748405d1ed, data reload: false

query1	997	383	401	383
query2	6541	1683	1621	1621
query3	6740	219	219	219
query4	26200	23167	22828	22828
query5	4344	603	477	477
query6	307	220	201	201
query7	4629	503	285	285
query8	286	226	215	215
query9	8627	2897	2886	2886
query10	462	327	287	287
query11	15292	14973	14764	14764
query12	172	110	109	109
query13	1656	533	421	421
query14	8589	5823	5809	5809
query15	204	188	171	171
query16	7280	622	481	481
query17	1221	707	598	598
query18	1998	414	330	330
query19	193	191	164	164
query20	135	121	119	119
query21	219	124	103	103
query22	4132	4225	4133	4133
query23	33694	33034	32927	32927
query24	8099	2362	2348	2348
query25	565	470	408	408
query26	1224	264	160	160
query27	2757	501	343	343
query28	4353	2214	2186	2186
query29	764	565	466	466
query30	284	222	192	192
query31	883	783	716	716
query32	78	77	73	73
query33	540	371	327	327
query34	810	839	501	501
query35	796	838	732	732
query36	939	1008	918	918
query37	117	104	83	83
query38	4064	4066	4152	4066
query39	1485	1408	1412	1408
query40	213	123	117	117
query41	58	61	52	52
query42	127	109	110	109
query43	485	485	458	458
query44	1376	862	860	860
query45	179	171	160	160
query46	848	984	645	645
query47	1778	1837	1725	1725
query48	383	417	329	329
query49	756	503	395	395
query50	628	689	390	390
query51	5482	5510	5425	5425
query52	118	105	102	102
query53	224	260	188	188
query54	594	596	527	527
query55	88	87	87	87
query56	307	314	311	311
query57	1185	1194	1148	1148
query58	276	264	281	264
query59	2522	2632	2591	2591
query60	360	341	326	326
query61	128	152	121	121
query62	765	718	667	667
query63	214	185	191	185
query64	4302	982	729	729
query65	4255	4156	4170	4156
query66	1160	412	324	324
query67	15774	15518	15558	15518
query68	8410	921	568	568
query69	477	315	286	286
query70	1261	1157	1083	1083
query71	441	326	318	318
query72	5653	4836	4867	4836
query73	771	677	363	363
query74	8851	8823	8778	8778
query75	3952	3063	2609	2609
query76	3738	1137	737	737
query77	805	423	384	384
query78	9653	9707	8819	8819
query79	2033	831	580	580
query80	609	542	469	469
query81	470	263	233	233
query82	424	140	102	102
query83	283	250	238	238
query84	289	102	83	83
query85	791	352	327	327
query86	358	331	302	302
query87	4381	4456	4297	4297
query88	2885	2260	2262	2260
query89	381	311	288	288
query90	1918	226	219	219
query91	135	138	109	109
query92	89	71	63	63
query93	1158	975	646	646
query94	672	392	293	293
query95	396	318	307	307
query96	491	588	279	279
query97	2682	2739	2580	2580
query98	238	214	207	207
query99	1436	1411	1272	1272
Total cold run time: 272392 ms
Total hot run time: 186302 ms

doris-robot avatar Jul 25 '25 12:07 doris-robot

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

query1	0.04	0.04	0.03
query2	0.08	0.04	0.05
query3	0.24	0.07	0.08
query4	1.63	0.11	0.11
query5	0.42	0.43	0.39
query6	1.16	0.65	0.66
query7	0.03	0.02	0.02
query8	0.04	0.03	0.03
query9	0.60	0.51	0.51
query10	0.58	0.57	0.56
query11	0.16	0.11	0.11
query12	0.15	0.12	0.12
query13	0.62	0.61	0.62
query14	0.80	0.82	0.83
query15	0.88	0.89	0.87
query16	0.39	0.38	0.38
query17	1.09	1.08	1.05
query18	0.20	0.20	0.20
query19	1.93	1.80	1.86
query20	0.02	0.01	0.01
query21	15.39	0.91	0.55
query22	0.79	1.13	0.71
query23	14.98	1.38	0.66
query24	7.09	1.09	0.62
query25	0.50	0.20	0.21
query26	0.58	0.16	0.13
query27	0.06	0.05	0.05
query28	10.10	0.87	0.42
query29	12.53	3.93	3.31
query30	3.10	2.99	2.98
query31	2.82	0.59	0.38
query32	3.24	0.55	0.48
query33	3.17	3.14	3.06
query34	15.87	5.40	4.88
query35	4.87	4.80	4.92
query36	0.70	0.51	0.49
query37	0.09	0.07	0.07
query38	0.06	0.04	0.04
query39	0.04	0.02	0.03
query40	0.18	0.13	0.14
query41	0.08	0.02	0.02
query42	0.04	0.03	0.03
query43	0.04	0.04	0.04
Total cold run time: 107.38 s
Total hot run time: 32.45 s

doris-robot avatar Jul 25 '25 13:07 doris-robot