Skip to content

Commit b5336a1

Browse files
penghuopenghuo
authored
Merge pull request #1262 from opensearch-project/backport/backport-1227-to-2.x
[Backport 2.x] Add Day_Of_Month Function As An Alias Of DayOfMonth
2 parents 761194e + a08fc6d commit b5336a1

8 files changed

Lines changed: 189 additions & 19 deletions

File tree

‎core/src/main/java/org/opensearch/sql/expression/DSL.java‎

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -322,8 +322,10 @@ public static FunctionExpression dayname(Expression... expressions) {
322322
return compile(FunctionProperties.None, BuiltinFunctionName.DAYNAME, expressions);
323323
}
324324

325-
public static FunctionExpression dayofmonth(Expression... expressions) {
326-
return compile(FunctionProperties.None, BuiltinFunctionName.DAYOFMONTH, expressions);
325+
public static FunctionExpression dayofmonth(
326+
FunctionProperties functionProperties,
327+
Expression... expressions) {
328+
return compile(functionProperties, BuiltinFunctionName.DAYOFMONTH, expressions);
327329
}
328330

329331
public static FunctionExpression dayofweek(
@@ -335,6 +337,12 @@ public static FunctionExpression dayofyear(Expression... expressions) {
335337
return compile(FunctionProperties.None, BuiltinFunctionName.DAYOFYEAR, expressions);
336338
}
337339

340+
public static FunctionExpression day_of_month(
341+
FunctionProperties functionProperties,
342+
Expression... expressions) {
343+
return compile(functionProperties, BuiltinFunctionName.DAY_OF_MONTH, expressions);
344+
}
345+
338346
public static FunctionExpression day_of_year(
339347
FunctionProperties functionProperties, Expression... expressions) {
340348
return compile(functionProperties, BuiltinFunctionName.DAY_OF_YEAR, expressions);

‎core/src/main/java/org/opensearch/sql/expression/datetime/DateTimeFunction.java‎

Lines changed: 15 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -64,7 +64,6 @@
6464
import org.opensearch.sql.data.model.ExprValue;
6565
import org.opensearch.sql.data.type.ExprCoreType;
6666
import org.opensearch.sql.exception.ExpressionEvaluationException;
67-
import org.opensearch.sql.exception.SemanticCheckException;
6867
import org.opensearch.sql.expression.function.BuiltinFunctionName;
6968
import org.opensearch.sql.expression.function.BuiltinFunctionRepository;
7069
import org.opensearch.sql.expression.function.DefaultFunctionResolver;
@@ -113,7 +112,8 @@ public void register(BuiltinFunctionRepository repository) {
113112
repository.register(date_sub());
114113
repository.register(day());
115114
repository.register(dayName());
116-
repository.register(dayOfMonth());
115+
repository.register(dayOfMonth(BuiltinFunctionName.DAYOFMONTH));
116+
repository.register(dayOfMonth(BuiltinFunctionName.DAY_OF_MONTH));
117117
repository.register(dayOfWeek(BuiltinFunctionName.DAYOFWEEK.getName()));
118118
repository.register(dayOfWeek(BuiltinFunctionName.DAY_OF_WEEK.getName()));
119119
repository.register(dayOfYear(BuiltinFunctionName.DAYOFYEAR));
@@ -444,12 +444,15 @@ private DefaultFunctionResolver dayName() {
444444
/**
445445
* DAYOFMONTH(STRING/DATE/DATETIME/TIMESTAMP). return the day of the month (1-31).
446446
*/
447-
private DefaultFunctionResolver dayOfMonth() {
448-
return define(BuiltinFunctionName.DAYOFMONTH.getName(),
447+
private DefaultFunctionResolver dayOfMonth(BuiltinFunctionName name) {
448+
return define(name.getName(),
449+
implWithProperties(nullMissingHandlingWithProperties(
450+
(functionProperties, arg) -> DateTimeFunction.dayOfMonthToday(
451+
functionProperties.getQueryStartClock())), INTEGER, TIME),
449452
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, DATE),
450453
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, DATETIME),
451-
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, TIMESTAMP),
452-
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, STRING)
454+
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, STRING),
455+
impl(nullMissingHandling(DateTimeFunction::exprDayOfMonth), INTEGER, TIMESTAMP)
453456
);
454457
}
455458

@@ -844,6 +847,11 @@ private DefaultFunctionResolver date_format() {
844847
);
845848
}
846849

850+
851+
private ExprValue dayOfMonthToday(Clock clock) {
852+
return new ExprIntegerValue(LocalDateTime.now(clock).getDayOfMonth());
853+
}
854+
847855
private ExprValue dayOfYearToday(Clock clock) {
848856
return new ExprIntegerValue(LocalDateTime.now(clock).getDayOfYear());
849857
}
@@ -1058,7 +1066,7 @@ private ExprValue exprDayName(ExprValue date) {
10581066
/**
10591067
* Day of Month implementation for ExprValue.
10601068
*
1061-
* @param date ExprValue of Date/String type.
1069+
* @param date ExprValue of Date/Datetime/String/Time/Timestamp type.
10621070
* @return ExprValue.
10631071
*/
10641072
private ExprValue exprDayOfMonth(ExprValue date) {

‎core/src/main/java/org/opensearch/sql/expression/function/BuiltinFunctionName.java‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -69,6 +69,7 @@ public enum BuiltinFunctionName {
6969
DAY(FunctionName.of("day")),
7070
DAYNAME(FunctionName.of("dayname")),
7171
DAYOFMONTH(FunctionName.of("dayofmonth")),
72+
DAY_OF_MONTH(FunctionName.of("day_of_month")),
7273
DAYOFWEEK(FunctionName.of("dayofweek")),
7374
DAYOFYEAR(FunctionName.of("dayofyear")),
7475
DAY_OF_WEEK(FunctionName.of("day_of_week")),

‎core/src/test/java/org/opensearch/sql/expression/datetime/DateTimeFunctionTest.java‎

Lines changed: 85 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -422,20 +422,101 @@ public void dayName() {
422422
public void dayOfMonth() {
423423
when(nullRef.type()).thenReturn(DATE);
424424
when(missingRef.type()).thenReturn(DATE);
425-
assertEquals(nullValue(), eval(DSL.dayofmonth(nullRef)));
426-
assertEquals(missingValue(), eval(DSL.dayofmonth(missingRef)));
425+
assertEquals(nullValue(), eval(DSL.dayofmonth(functionProperties, nullRef)));
426+
assertEquals(missingValue(), eval(DSL.dayofmonth(functionProperties, missingRef)));
427427

428-
FunctionExpression expression = DSL.dayofmonth(DSL.literal(new ExprDateValue("2020-08-07")));
428+
FunctionExpression expression = DSL.dayofmonth(
429+
functionProperties, DSL.literal(new ExprDateValue("2020-08-07")));
429430
assertEquals(INTEGER, expression.type());
430431
assertEquals("dayofmonth(DATE '2020-08-07')", expression.toString());
431432
assertEquals(integerValue(7), eval(expression));
432433

433-
expression = DSL.dayofmonth(DSL.literal("2020-07-08"));
434+
expression = DSL.dayofmonth(functionProperties, DSL.literal("2020-07-08"));
434435
assertEquals(INTEGER, expression.type());
435436
assertEquals("dayofmonth(\"2020-07-08\")", expression.toString());
436437
assertEquals(integerValue(8), eval(expression));
437438
}
438439

440+
private void testDayOfMonthWithUnderscores(FunctionExpression dateExpression, int dayOfMonth) {
441+
assertEquals(INTEGER, dateExpression.type());
442+
assertEquals(integerValue(dayOfMonth), eval(dateExpression));
443+
}
444+
445+
@Test
446+
public void dayOfMonthWithUnderscores() {
447+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
448+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
449+
450+
451+
FunctionExpression expression1 = DSL.dayofmonth(
452+
functionProperties, DSL.literal(new ExprDateValue("2020-08-07")));
453+
FunctionExpression expression2 = DSL.dayofmonth(functionProperties, DSL.literal("2020-07-08"));
454+
455+
assertAll(
456+
() -> testDayOfMonthWithUnderscores(expression1, 7),
457+
() -> assertEquals("dayofmonth(DATE '2020-08-07')", expression1.toString()),
458+
459+
() -> testDayOfMonthWithUnderscores(expression2, 8),
460+
() -> assertEquals("dayofmonth(\"2020-07-08\")", expression2.toString())
461+
462+
);
463+
}
464+
465+
@Test
466+
public void testDayOfMonthWithTimeType() {
467+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
468+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
469+
FunctionExpression expression = DSL.day_of_month(
470+
functionProperties, DSL.literal(new ExprTimeValue("12:23:34")));
471+
472+
assertEquals(INTEGER, eval(expression).type());
473+
assertEquals(
474+
LocalDate.now(functionProperties.getQueryStartClock()).getDayOfMonth(),
475+
eval(expression).integerValue());
476+
assertEquals("day_of_month(TIME '12:23:34')", expression.toString());
477+
}
478+
479+
private void testInvalidDayOfMonth(String date) {
480+
FunctionExpression expression = DSL.day_of_month(
481+
functionProperties, DSL.literal(new ExprDateValue(date)));
482+
eval(expression);
483+
}
484+
485+
@Test
486+
public void dayOfMonthWithUnderscoresLeapYear() {
487+
lenient().when(nullRef.valueOf(env)).thenReturn(nullValue());
488+
lenient().when(missingRef.valueOf(env)).thenReturn(missingValue());
489+
490+
//Feb. 29 of a leap year
491+
testDayOfMonthWithUnderscores(DSL.day_of_month(
492+
functionProperties, DSL.literal("2020-02-29")), 29);
493+
494+
//Feb. 29 of a non-leap year
495+
assertThrows(SemanticCheckException.class, () -> testInvalidDayOfMonth("2021-02-29"));
496+
}
497+
498+
@Test
499+
public void dayOfMonthWithUnderscoresInvalidArguments() {
500+
lenient().when(nullRef.type()).thenReturn(DATE);
501+
lenient().when(missingRef.type()).thenReturn(DATE);
502+
503+
assertAll(
504+
() -> assertEquals(nullValue(), eval(DSL.day_of_month(functionProperties, nullRef))),
505+
() -> assertEquals(
506+
missingValue(), eval(DSL.day_of_month(functionProperties, missingRef))),
507+
508+
//40th day of the month
509+
() -> assertThrows(
510+
SemanticCheckException.class, () -> testInvalidDayOfMonth("2021-02-40")),
511+
//13th month of the year
512+
() -> assertThrows(
513+
SemanticCheckException.class, () -> testInvalidDayOfMonth("2021-13-40")),
514+
//incorrect format
515+
() -> assertThrows(
516+
SemanticCheckException.class, () -> testInvalidDayOfMonth("asdfasdfasdf"))
517+
);
518+
}
519+
439520
private void dayOfWeekQuery(
440521
FunctionExpression dateExpression,
441522
int dayOfWeek,

‎docs/user/dql/functions.rst‎

Lines changed: 29 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1499,13 +1499,13 @@ DAY
14991499
Description
15001500
>>>>>>>>>>>
15011501

1502-
Usage: day(date) extracts the day of the month for date, in the range 1 to 31. The dates with value 0 such as '0000-00-00' or '2008-00-00' are invalid.
1502+
Usage: day(date) extracts the day of the month for date, in the range 1 to 31.
15031503

1504-
Argument type: STRING/DATE/DATETIME/TIMESTAMP
1504+
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
15051505

15061506
Return type: INTEGER
15071507

1508-
Synonyms: DAYOFMONTH
1508+
Synonyms: `DAYOFMONTH`_, `DAY_OF_MONTH`_
15091509

15101510
Example::
15111511

@@ -1547,13 +1547,13 @@ DAYOFMONTH
15471547
Description
15481548
>>>>>>>>>>>
15491549

1550-
Usage: dayofmonth(date) extracts the day of the month for date, in the range 1 to 31. The dates with value 0 such as '0000-00-00' or '2008-00-00' are invalid.
1550+
Usage: dayofmonth(date) extracts the day of the month for date, in the range 1 to 31.
15511551

1552-
Argument type: STRING/DATE/DATETIME/TIMESTAMP
1552+
Argument type: STRING/DATE/DATETIME/TIME/TIMESTAMP
15531553

15541554
Return type: INTEGER
15551555

1556-
Synonyms: DAY
1556+
Synonyms: `DAY`_, `DAY_OF_MONTH`_
15571557

15581558
Example::
15591559

@@ -1565,6 +1565,29 @@ Example::
15651565
| 26 |
15661566
+----------------------------------+
15671567

1568+
DAY_OF_MONTH
1569+
------------
1570+
1571+
Description
1572+
>>>>>>>>>>>
1573+
1574+
Usage: day_of_month(date) extracts the day of the month for date, in the range 1 to 31.
1575+
1576+
Argument type: STRING/DATE/TIME/DATETIME/TIMESTAMP
1577+
1578+
Return type: INTEGER
1579+
1580+
Synonyms: `DAY`_, `DAYOFMONTH`_
1581+
1582+
Example::
1583+
1584+
os> SELECT DAY_OF_MONTH('2020-08-26')
1585+
fetched rows / total rows = 1/1
1586+
+------------------------------+
1587+
| DAY_OF_MONTH('2020-08-26') |
1588+
|------------------------------|
1589+
| 26 |
1590+
+------------------------------+
15681591

15691592
DAYOFWEEK
15701593
---------

‎integ-test/src/test/java/org/opensearch/sql/sql/DateTimeFunctionIT.java‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -208,6 +208,48 @@ public void testDayOfMonth() throws IOException {
208208
verifyDataRows(result, rows(16));
209209
}
210210

211+
@Test
212+
public void testDayOfMonthWithUnderscores() throws IOException {
213+
JSONObject result = executeQuery("select day_of_month(date('2020-09-16'))");
214+
verifySchema(result, schema("day_of_month(date('2020-09-16'))", null, "integer"));
215+
verifyDataRows(result, rows(16));
216+
217+
result = executeQuery("select day_of_month('2020-09-16')");
218+
verifySchema(result, schema("day_of_month('2020-09-16')", null, "integer"));
219+
verifyDataRows(result, rows(16));
220+
}
221+
222+
@Test
223+
public void testDayOfMonthAliasesReturnTheSameResults() throws IOException {
224+
JSONObject result1 = executeQuery("SELECT dayofmonth(date('2022-11-22'))");
225+
JSONObject result2 = executeQuery("SELECT day_of_month(date('2022-11-22'))");
226+
verifyDataRows(result1, rows(22));
227+
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
228+
229+
result1 = executeQuery(String.format(
230+
"SELECT dayofmonth(CAST(date0 AS date)) FROM %s", TEST_INDEX_CALCS));
231+
result2 = executeQuery(String.format(
232+
"SELECT day_of_month(CAST(date0 AS date)) FROM %s", TEST_INDEX_CALCS));
233+
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
234+
235+
result1 = executeQuery(String.format(
236+
"SELECT dayofmonth(datetime(CAST(time0 AS STRING))) FROM %s", TEST_INDEX_CALCS));
237+
result2 = executeQuery(String.format(
238+
"SELECT day_of_month(datetime(CAST(time0 AS STRING))) FROM %s", TEST_INDEX_CALCS));
239+
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
240+
241+
result1 = executeQuery(String.format(
242+
"SELECT dayofmonth(CAST(time0 AS STRING)) FROM %s", TEST_INDEX_CALCS));
243+
result2 = executeQuery(String.format(
244+
"SELECT day_of_month(CAST(time0 AS STRING)) FROM %s", TEST_INDEX_CALCS));
245+
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
246+
247+
result1 = executeQuery(String.format(
248+
"SELECT dayofmonth(CAST(datetime0 AS timestamp)) FROM %s", TEST_INDEX_CALCS));
249+
result2 = executeQuery(String.format(
250+
"SELECT day_of_month(CAST(datetime0 AS timestamp)) FROM %s", TEST_INDEX_CALCS));
251+
result1.getJSONArray("datarows").similar(result2.getJSONArray("datarows"));
252+
}
211253
@Test
212254
public void testDayOfWeek() throws IOException {
213255
JSONObject result = executeQuery("select dayofweek(date('2020-09-16'))");

‎sql/src/main/antlr/OpenSearchSQLParser.g4‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -436,6 +436,7 @@ dateTimeFunctionName
436436
| DAY
437437
| DAYNAME
438438
| DAYOFMONTH
439+
| DAY_OF_MONTH
439440
| DAYOFWEEK
440441
| DAYOFYEAR
441442
| DAY_OF_YEAR

‎sql/src/test/java/org/opensearch/sql/sql/antlr/SQLSyntaxParserTest.java‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,12 @@ public void can_parse_week_of_year_functions() {
200200
assertNotNull(parser.parse("SELECT week_of_year('2022-11-18')"));
201201
}
202202

203+
@Test
204+
public void can_parse_dayofmonth_functions() {
205+
assertNotNull(parser.parse("SELECT dayofmonth('2022-11-18')"));
206+
assertNotNull(parser.parse("SELECT day_of_month('2022-11-18')"));
207+
}
208+
203209
@Test
204210
public void can_parse_day_of_week_functions() {
205211
assertNotNull(parser.parse("SELECT dayofweek('2022-11-18')"));

0 commit comments

Comments
 (0)