Skip to content

Commit 1087320

Browse files
engleflypull[bot]
authored andcommitted
[feature](nereids) Date add and Date sub related functions (#14753)
## date_add series - DATE_ADD - DAYS_ADD - ADDDATE - TIMESTAMPADD ## date_sub series - DATE_SUB - DAYS_SUB - SUBDATE ## NOTE 1. For DAYS_XXX, time unit is omissible, by default the time unit is DAY 2. no TIMESTAMPSUB
1 parent 8452eaa commit 1087320

22 files changed

Lines changed: 1178 additions & 7 deletions

File tree

‎fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -90,6 +90,7 @@ RIGHT_BRACKET: ']';
9090
//============================
9191
//--DORIS-KEYWORD-LIST-START
9292
ADD: 'ADD';
93+
ADDDATE:'ADDDATE';
9394
AFTER: 'AFTER';
9495
ALL: 'ALL';
9596
ALTER: 'ALTER';
@@ -143,13 +144,16 @@ CURRENT_TIME: 'CURRENT_TIME';
143144
CURRENT_TIMESTAMP: 'CURRENT_TIMESTAMP';
144145
CURRENT_USER: 'CURRENT_USER';
145146
DAY: 'DAY';
147+
DAYS_ADD: 'DAYS_ADD';
148+
DAYS_SUB: 'DAYS_SUB';
146149
DATA: 'DATA';
147150
DATABASE: 'DATABASE';
148151
DATABASES: 'DATABASES';
149152
DATE: 'DATE';
150153
DATEADD: 'DATEADD';
151-
DATE_ADD: 'DATE_ADD';
152154
DATEDIFF: 'DATEDIFF';
155+
DATE_ADD: 'DATE_ADD';
156+
DATE_SUB: 'DATE_SUB';
153157
DATE_DIFF: 'DATE_DIFF';
154158
DBPROPERTIES: 'DBPROPERTIES';
155159
DEFINED: 'DEFINED';
@@ -328,6 +332,7 @@ STATISTICS: 'STATISTICS';
328332
STORED: 'STORED';
329333
STRATIFY: 'STRATIFY';
330334
STRUCT: 'STRUCT';
335+
SUBDATE: 'SUBDATE';
331336
SUBSTR: 'SUBSTR';
332337
SUBSTRING: 'SUBSTRING';
333338
SUM: 'SUM';

‎fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4‎

Lines changed: 18 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -275,6 +275,18 @@ primaryExpression
275275
startTimestamp=valueExpression COMMA
276276
endTimestamp=valueExpression
277277
RIGHT_PAREN #timestampdiff
278+
| name=(TIMESTAMPADD | ADDDATE | DAYS_ADD | DATE_ADD)
279+
LEFT_PAREN
280+
timestamp=valueExpression COMMA
281+
(INTERVAL unitsAmount=valueExpression unit=datetimeUnit
282+
| unitsAmount=valueExpression)
283+
RIGHT_PAREN #date_add
284+
| name=(SUBDATE | DAYS_SUB | DATE_SUB)
285+
LEFT_PAREN
286+
timestamp=valueExpression COMMA
287+
(INTERVAL unitsAmount=valueExpression unit=datetimeUnit
288+
| unitsAmount=valueExpression)
289+
RIGHT_PAREN #date_sub
278290
| CASE whenClause+ (ELSE elseExpression=expression)? END #searchedCase
279291
| CASE value=expression whenClause+ (ELSE elseExpression=expression)? END #simpleCase
280292
| name=CAST LEFT_PAREN expression AS identifier RIGHT_PAREN #cast
@@ -371,6 +383,7 @@ number
371383
ansiNonReserved
372384
//--ANSI-NON-RESERVED-START
373385
: ADD
386+
| ADDDATE
374387
| AFTER
375388
| ALTER
376389
| ANALYZE
@@ -409,11 +422,14 @@ ansiNonReserved
409422
| DATABASE
410423
| DATABASES
411424
| DATE
412-
| DATEADD
413425
| DATE_ADD
414426
| DATEDIFF
415427
| DATE_DIFF
416428
| DAY
429+
| DAYS_ADD
430+
| DAYS_SUB
431+
| DATE_ADD
432+
| DATE_SUB
417433
| DBPROPERTIES
418434
| DEFINED
419435
| DELETE
@@ -545,6 +561,7 @@ ansiNonReserved
545561
| STORED
546562
| STRATIFY
547563
| STRUCT
564+
| SUBDATE
548565
| SUBSTR
549566
| SUBSTRING
550567
| SUM
@@ -670,7 +687,6 @@ nonReserved
670687
| DATABASE
671688
| DATABASES
672689
| DATE
673-
| DATEADD
674690
| DATE_ADD
675691
| DATEDIFF
676692
| DATE_DIFF

‎fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java‎

Lines changed: 92 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -31,10 +31,13 @@
3131
import org.apache.doris.nereids.DorisParser.ComparisonContext;
3232
import org.apache.doris.nereids.DorisParser.CreateRowPolicyContext;
3333
import org.apache.doris.nereids.DorisParser.CteContext;
34+
import org.apache.doris.nereids.DorisParser.Date_addContext;
35+
import org.apache.doris.nereids.DorisParser.Date_subContext;
3436
import org.apache.doris.nereids.DorisParser.DecimalLiteralContext;
3537
import org.apache.doris.nereids.DorisParser.DereferenceContext;
3638
import org.apache.doris.nereids.DorisParser.ExistContext;
3739
import org.apache.doris.nereids.DorisParser.ExplainContext;
40+
import org.apache.doris.nereids.DorisParser.ExpressionContext;
3841
import org.apache.doris.nereids.DorisParser.FromClauseContext;
3942
import org.apache.doris.nereids.DorisParser.GroupingElementContext;
4043
import org.apache.doris.nereids.DorisParser.GroupingSetContext;
@@ -126,12 +129,24 @@
126129
import org.apache.doris.nereids.trees.expressions.TVFProperties;
127130
import org.apache.doris.nereids.trees.expressions.TimestampArithmetic;
128131
import org.apache.doris.nereids.trees.expressions.WhenClause;
132+
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
129133
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysDiff;
134+
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysSub;
135+
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
130136
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursDiff;
137+
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursSub;
138+
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
131139
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesDiff;
140+
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesSub;
141+
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsAdd;
132142
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsDiff;
143+
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsSub;
144+
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd;
133145
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsDiff;
146+
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub;
147+
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsAdd;
134148
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsDiff;
149+
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsSub;
135150
import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
136151
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
137152
import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
@@ -167,6 +182,8 @@
167182
import org.apache.doris.nereids.trees.plans.logical.LogicalSubQueryAlias;
168183
import org.apache.doris.nereids.trees.plans.logical.RelationUtil;
169184
import org.apache.doris.nereids.types.DataType;
185+
import org.apache.doris.nereids.types.IntegerType;
186+
import org.apache.doris.nereids.types.TinyIntType;
170187
import org.apache.doris.nereids.util.ExpressionUtils;
171188
import org.apache.doris.policy.PolicyTypeEnum;
172189
import org.apache.doris.qe.ConnectContext;
@@ -577,10 +594,82 @@ public Expression visitTimestampdiff(TimestampdiffContext ctx) {
577594
} else if ("SECOND".equalsIgnoreCase(unit)) {
578595
return new SecondsDiff(end, start);
579596
}
580-
throw new ParseException("Unsupported time stamp diff time unit: " + unit, ctx);
597+
throw new ParseException("Unsupported time stamp diff time unit: " + unit
598+
+ ", supported time unit: YEAR/MONTH/DAY/HOUR/MINUTE/SECOND", ctx);
581599

582600
}
583601

602+
@Override
603+
public Expression visitDate_add(Date_addContext ctx) {
604+
Expression timeStamp = (Expression) visit(ctx.timestamp);
605+
Expression amount = (Expression) visit(ctx.unitsAmount);
606+
if (ctx.unit == null) {
607+
if ("days_add".equalsIgnoreCase(ctx.name.getText())) {
608+
return new DaysAdd(timeStamp, amount);
609+
}
610+
throw new ParseException("Unsupported signature: " + ctx.name
611+
+ " needs time unit (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)", ctx);
612+
}
613+
614+
if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
615+
return new DaysAdd(timeStamp, amount);
616+
}
617+
if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
618+
return new MonthsAdd(timeStamp, amount);
619+
}
620+
if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
621+
return new YearsAdd(timeStamp, amount);
622+
}
623+
if ("Hour".equalsIgnoreCase(ctx.unit.getText())) {
624+
return new HoursAdd(timeStamp, amount);
625+
}
626+
if ("Minute".equalsIgnoreCase(ctx.unit.getText())) {
627+
return new MinutesAdd(timeStamp, amount);
628+
}
629+
if ("Second".equalsIgnoreCase(ctx.unit.getText())) {
630+
return new SecondsAdd(timeStamp, amount);
631+
}
632+
throw new ParseException("Unsupported time unit: " + ctx.unit
633+
+ ", supported time unit: YEAR/MONTH/DAY/HOUR/MINUTE/SECOND", ctx);
634+
}
635+
636+
@Override
637+
public Expression visitDate_sub(Date_subContext ctx) {
638+
Expression timeStamp = (Expression) visit(ctx.timestamp);
639+
Expression amount = (Expression) visit(ctx.unitsAmount);
640+
if (! (amount.getDataType() instanceof TinyIntType)) {
641+
amount = new Cast(amount, IntegerType.INSTANCE);
642+
}
643+
if (ctx.unit == null) {
644+
if ("days_sub".equalsIgnoreCase(ctx.name.getText())) {
645+
return new DaysSub(timeStamp, amount);
646+
}
647+
throw new ParseException("Unsupported signature: " + ctx.name
648+
+ " needs time unit (YEAR/MONTH/DAY/HOUR/MINUTE/SECOND)", ctx);
649+
}
650+
651+
if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
652+
return new DaysSub(timeStamp, amount);
653+
}
654+
if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
655+
return new MonthsSub(timeStamp, amount);
656+
}
657+
if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
658+
return new YearsSub(timeStamp, amount);
659+
}
660+
if ("Hour".equalsIgnoreCase(ctx.unit.getText())) {
661+
return new HoursSub(timeStamp, amount);
662+
}
663+
if ("Minute".equalsIgnoreCase(ctx.unit.getText())) {
664+
return new MinutesSub(timeStamp, amount);
665+
}
666+
if ("Second".equalsIgnoreCase(ctx.unit.getText())) {
667+
return new SecondsSub(timeStamp, amount);
668+
}
669+
throw new ParseException("Unsupported time unit: " + ctx.unit
670+
+ ", supported time unit: YEAR/MONTH/DAY/HOUR/MINUTE/SECOND", ctx);
671+
}
672+
584673
/**
585674
* Create a value based [[CaseWhen]] expression. This has the following SQL form:
586675
* {{{
@@ -648,7 +737,8 @@ public UnboundFunction visitFunctionCall(DorisParser.FunctionCallContext ctx) {
648737
// the function information is obtained by parsing the catalog. This method is more scalable.
649738
String functionName = ctx.identifier().getText();
650739
boolean isDistinct = ctx.DISTINCT() != null;
651-
List<Expression> params = visit(ctx.expression(), Expression.class);
740+
List<ExpressionContext> expressionContexts = ctx.expression();
741+
List<Expression> params = visit(expressionContexts, Expression.class);
652742
for (Expression expression : params) {
653743
if (expression instanceof UnboundStar && functionName.equalsIgnoreCase("count") && !isDistinct) {
654744
return new UnboundFunction(functionName, false, true, new ArrayList<>());

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/TimestampArithmetic.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,10 @@
4848
public class TimestampArithmetic extends Expression implements BinaryExpression, ImplicitCastInputTypes,
4949
PropagateNullable {
5050

51+
//the size and order of EXPECTED_INPUT_TYPES must follow the function signature parameters
52+
//For example: days_sub('2000-01-01', interval 5 days),
53+
// '2000-01-01'->DateTimeType.INSTANCE
54+
// 5 -> IntegerType
5155
private static final List<AbstractDataType> EXPECTED_INPUT_TYPES = ImmutableList.of(
5256
DateTimeType.INSTANCE,
5357
IntegerType.INSTANCE
Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
package org.apache.doris.nereids.trees.expressions.functions.scalar;
19+
20+
import org.apache.doris.catalog.FunctionSignature;
21+
import org.apache.doris.nereids.trees.expressions.Expression;
22+
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
23+
import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
24+
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
25+
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
26+
import org.apache.doris.nereids.types.DateTimeType;
27+
import org.apache.doris.nereids.types.DateTimeV2Type;
28+
import org.apache.doris.nereids.types.DateType;
29+
import org.apache.doris.nereids.types.DateV2Type;
30+
import org.apache.doris.nereids.types.IntegerType;
31+
32+
import com.google.common.base.Preconditions;
33+
import com.google.common.collect.ImmutableList;
34+
35+
import java.util.List;
36+
37+
/**
38+
* ScalarFunction 'days_add'.
39+
*/
40+
public class DaysAdd extends ScalarFunction implements BinaryExpression, ExplicitlyCastableSignature, AlwaysNullable {
41+
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
42+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE, IntegerType.INSTANCE),
43+
FunctionSignature.ret(DateTimeV2Type.INSTANCE).args(DateTimeV2Type.INSTANCE, IntegerType.INSTANCE),
44+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE, IntegerType.INSTANCE),
45+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE, IntegerType.INSTANCE)
46+
);
47+
48+
public DaysAdd(Expression arg0, Expression arg1) {
49+
super("days_add", arg0, arg1);
50+
}
51+
52+
@Override
53+
public DaysAdd withChildren(List<Expression> children) {
54+
Preconditions.checkArgument(children.size() == 2);
55+
return new DaysAdd(children.get(0), children.get(1));
56+
}
57+
58+
@Override
59+
public List<FunctionSignature> getSignatures() {
60+
return SIGNATURES;
61+
}
62+
63+
@Override
64+
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
65+
return visitor.visitDaysAdd(this, context);
66+
}
67+
}
Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
// Licensed to the Apache Software Foundation (ASF) under one
2+
// or more contributor license agreements. See the NOTICE file
3+
// distributed with this work for additional information
4+
// regarding copyright ownership. The ASF licenses this file
5+
// to you under the Apache License, Version 2.0 (the
6+
// "License"); you may not use this file except in compliance
7+
// with the License. You may obtain a copy of the License at
8+
//
9+
// http://www.apache.org/licenses/LICENSE-2.0
10+
//
11+
// Unless required by applicable law or agreed to in writing,
12+
// software distributed under the License is distributed on an
13+
// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14+
// KIND, either express or implied. See the License for the
15+
// specific language governing permissions and limitations
16+
// under the License.
17+
18+
package org.apache.doris.nereids.trees.expressions.functions.scalar;
19+
20+
import org.apache.doris.catalog.FunctionSignature;
21+
import org.apache.doris.nereids.trees.expressions.Expression;
22+
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
23+
import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
24+
import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression;
25+
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
26+
import org.apache.doris.nereids.types.DateTimeType;
27+
import org.apache.doris.nereids.types.DateTimeV2Type;
28+
import org.apache.doris.nereids.types.DateType;
29+
import org.apache.doris.nereids.types.DateV2Type;
30+
import org.apache.doris.nereids.types.IntegerType;
31+
32+
import com.google.common.base.Preconditions;
33+
import com.google.common.collect.ImmutableList;
34+
35+
import java.util.List;
36+
37+
/**
38+
* ScalarFunction 'days_add'.
39+
*/
40+
public class DaysSub extends ScalarFunction implements BinaryExpression, ExplicitlyCastableSignature, AlwaysNullable {
41+
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
42+
FunctionSignature.ret(DateTimeType.INSTANCE).args(DateTimeType.INSTANCE, IntegerType.INSTANCE),
43+
FunctionSignature.ret(DateTimeV2Type.INSTANCE).args(DateTimeV2Type.INSTANCE, IntegerType.INSTANCE),
44+
FunctionSignature.ret(DateType.INSTANCE).args(DateType.INSTANCE, IntegerType.INSTANCE),
45+
FunctionSignature.ret(DateV2Type.INSTANCE).args(DateV2Type.INSTANCE, IntegerType.INSTANCE)
46+
);
47+
48+
public DaysSub(Expression arg0, Expression arg1) {
49+
super("days_sub", arg0, arg1);
50+
}
51+
52+
@Override
53+
public DaysSub withChildren(List<Expression> children) {
54+
Preconditions.checkArgument(children.size() == 2);
55+
return new DaysSub(children.get(0), children.get(1));
56+
}
57+
58+
@Override
59+
public List<FunctionSignature> getSignatures() {
60+
return SIGNATURES;
61+
}
62+
63+
@Override
64+
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
65+
return visitor.visitDaysSub(this, context);
66+
}
67+
}

0 commit comments

Comments
 (0)