This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new a1ba0a1983c [fix](function) MicroSecondsSub without scale (#38945)
a1ba0a1983c is described below
commit a1ba0a1983c180ed0959c613f69965a597244f73
Author: Mryange <59914473+mrya...@users.noreply.github.com>
AuthorDate: Sun Aug 11 06:30:30 2024 +0800
[fix](function) MicroSecondsSub without scale (#38945)
## Proposed changes
Added the computeSignature function for millisecond/microsecond
calculation functions to generate parameters and return values with the
appropriate precision.
Modified the microSecondsAdd function, which was used for constant
folding, because constant folding uses the precision of the parameters
for calculation. However, for millisecond/microsecond calculations, it
is necessary to set the precision to the maximum to ensure correct
display.
before
```
mysql> SELECT MICROSECONDS_SUB('2010-11-30 23:50:50', 2);
+-------------------------------------------------------------------+
| microseconds_sub(cast('2010-11-30 23:50:50' as DATETIMEV2(0)), 2) |
+-------------------------------------------------------------------+
| 2010-11-30 23:50:49 |
+-------------------------------------------------------------------+
```
now
```
mysql> SELECT MICROSECONDS_SUB('2010-11-30 23:50:50', 2);
+-------------------------------------------------------------------+
| microseconds_sub(cast('2010-11-30 23:50:50' as DATETIMEV2(0)), 2) |
+-------------------------------------------------------------------+
| 2010-11-30 23:50:49.999998 |
+-------------------------------------------------------------------+
```
<!--Describe your changes.-->
---
.../functions/executable/DateTimeArithmetic.java | 24 +++++++++++++++++
.../functions/scalar/MicroSecondsAdd.java | 11 +++++---
.../functions/scalar/MicroSecondsSub.java | 11 +++++---
.../functions/scalar/MilliSecondsAdd.java | 11 +++++---
.../functions/scalar/MilliSecondsSub.java | 11 +++++---
.../expressions/literal/DateTimeV2Literal.java | 8 +++++-
.../nereids/rules/expression/FoldConstantTest.java | 19 ++++++++++---
.../test_from_millisecond_microsecond.out | 16 +++++++++++
.../test_from_millisecond_microsecond.groovy | 31 ++++++++++++++++++++++
9 files changed, 125 insertions(+), 17 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
index 033bff2afd3..c10181a1040 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeArithmetic.java
@@ -218,6 +218,30 @@ public class DateTimeArithmetic {
return date.plusMicroSeconds(microSecond.getValue());
}
+ /**
+ * datetime arithmetic function microseconds_sub.
+ */
+ @ExecFunction(name = "microseconds_sub", argTypes = { "DATETIMEV2", "INT"
}, returnType = "DATETIMEV2")
+ public static Expression microSecondsSub(DateTimeV2Literal date,
IntegerLiteral microSecond) {
+ return date.plusMicroSeconds(-microSecond.getValue());
+ }
+
+ /**
+ * datetime arithmetic function milliseconds_add.
+ */
+ @ExecFunction(name = "milliseconds_add", argTypes = { "DATETIMEV2", "INT"
}, returnType = "DATETIMEV2")
+ public static Expression milliSecondsAdd(DateTimeV2Literal date,
IntegerLiteral milliSecond) {
+ return date.plusMilliSeconds(milliSecond.getValue());
+ }
+
+ /**
+ * datetime arithmetic function milliseconds_sub.
+ */
+ @ExecFunction(name = "milliseconds_sub", argTypes = { "DATETIMEV2", "INT"
}, returnType = "DATETIMEV2")
+ public static Expression milliSecondsSub(DateTimeV2Literal date,
IntegerLiteral milliSecond) {
+ return date.plusMilliSeconds(-milliSecond.getValue());
+ }
+
/**
* datetime arithmetic function years-sub.
*/
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsAdd.java
index 059577143ef..8d792259440 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsAdd.java
@@ -38,9 +38,8 @@ public class MicroSecondsAdd extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
PropagateNullableOnDateLikeV2Args {
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
- FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
- .args(DateTimeV2Type.SYSTEM_DEFAULT, IntegerType.INSTANCE)
- );
+ FunctionSignature.ret(DateTimeV2Type.MAX)
+ .args(DateTimeV2Type.MAX, IntegerType.INSTANCE));
public MicroSecondsAdd(Expression arg0, Expression arg1) {
super("microseconds_add", arg0, arg1);
@@ -57,6 +56,12 @@ public class MicroSecondsAdd extends ScalarFunction
return SIGNATURES;
}
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ signature = super.computeSignature(signature);
+ return signature.withArgumentType(0,
DateTimeV2Type.MAX).withReturnType(DateTimeV2Type.MAX);
+ }
+
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitMicroSecondsAdd(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsSub.java
index c0b99eade79..2894d1fffc9 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondsSub.java
@@ -38,9 +38,8 @@ public class MicroSecondsSub extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
PropagateNullableOnDateLikeV2Args {
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
- FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
- .args(DateTimeV2Type.SYSTEM_DEFAULT, IntegerType.INSTANCE)
- );
+ FunctionSignature.ret(DateTimeV2Type.MAX)
+ .args(DateTimeV2Type.MAX, IntegerType.INSTANCE));
public MicroSecondsSub(Expression arg0, Expression arg1) {
super("microseconds_sub", arg0, arg1);
@@ -57,6 +56,12 @@ public class MicroSecondsSub extends ScalarFunction
return SIGNATURES;
}
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ signature = super.computeSignature(signature);
+ return signature.withArgumentType(0,
DateTimeV2Type.MAX).withReturnType(DateTimeV2Type.MAX);
+ }
+
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitMicroSecondsSub(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsAdd.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsAdd.java
index ddf58907e88..1cb56b13f84 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsAdd.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsAdd.java
@@ -38,9 +38,8 @@ public class MilliSecondsAdd extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
PropagateNullableOnDateLikeV2Args {
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
- FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
- .args(DateTimeV2Type.SYSTEM_DEFAULT, IntegerType.INSTANCE)
- );
+ FunctionSignature.ret(DateTimeV2Type.MAX)
+ .args(DateTimeV2Type.MAX, IntegerType.INSTANCE));
public MilliSecondsAdd(Expression arg0, Expression arg1) {
super("milliseconds_add", arg0, arg1);
@@ -57,6 +56,12 @@ public class MilliSecondsAdd extends ScalarFunction
return SIGNATURES;
}
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ signature = super.computeSignature(signature);
+ return signature.withArgumentType(0,
DateTimeV2Type.MAX).withReturnType(DateTimeV2Type.MAX);
+ }
+
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitMilliSecondsAdd(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsSub.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsSub.java
index eb96aa59ac2..42891b7e7e0 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsSub.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondsSub.java
@@ -38,9 +38,8 @@ public class MilliSecondsSub extends ScalarFunction
implements BinaryExpression, ExplicitlyCastableSignature,
PropagateNullableOnDateLikeV2Args {
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
- FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT)
- .args(DateTimeV2Type.SYSTEM_DEFAULT, IntegerType.INSTANCE)
- );
+ FunctionSignature.ret(DateTimeV2Type.MAX)
+ .args(DateTimeV2Type.MAX, IntegerType.INSTANCE));
public MilliSecondsSub(Expression arg0, Expression arg1) {
super("milliseconds_sub", arg0, arg1);
@@ -57,6 +56,12 @@ public class MilliSecondsSub extends ScalarFunction
return SIGNATURES;
}
+ @Override
+ public FunctionSignature computeSignature(FunctionSignature signature) {
+ signature = super.computeSignature(signature);
+ return signature.withArgumentType(0,
DateTimeV2Type.MAX).withReturnType(DateTimeV2Type.MAX);
+ }
+
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitMilliSecondsSub(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
index a769bd03717..a3457f2463d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
@@ -215,8 +215,14 @@ public class DateTimeV2Literal extends DateTimeLiteral {
return fromJavaDateType(toJavaDateType().plusSeconds(seconds),
getDataType().getScale());
}
+ // When performing addition or subtraction with MicroSeconds, the
precision must
+ // be set to 6 to display it completely.
public Expression plusMicroSeconds(long microSeconds) {
- return fromJavaDateType(toJavaDateType().plusNanos(microSeconds *
1000L), getDataType().getScale());
+ return fromJavaDateType(toJavaDateType().plusNanos(microSeconds *
1000L), 6);
+ }
+
+ public Expression plusMilliSeconds(long microSeconds) {
+ return plusMicroSeconds(microSeconds * 1000L);
}
/**
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
index 6e4febe0ea6..4a5a5e9065c 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/expression/FoldConstantTest.java
@@ -549,12 +549,17 @@ class FoldConstantTest extends
ExpressionRewriteTestHelper {
VarcharLiteral format = new VarcharLiteral("%Y-%m-%d");
String[] answer = {
- "'2000-01-30 23:59:59'", "'1999-12-01 23:59:59'", "'2029-12-31
23:59:59'", "'1969-12-31 23:59:59'",
- "'2002-06-30 23:59:59'", "'1997-06-30 23:59:59'", "'2000-01-30
23:59:59'", "'1999-12-01 23:59:59'",
+ "'2000-01-30 23:59:59'", "'1999-12-01 23:59:59'", "'2029-12-31
23:59:59'",
+ "'1969-12-31 23:59:59'",
+ "'2002-06-30 23:59:59'", "'1997-06-30 23:59:59'", "'2000-01-30
23:59:59'",
+ "'1999-12-01 23:59:59'",
"'2000-01-02 05:59:59'", "'1999-12-30 17:59:59'", "'2000-01-01
00:29:59'",
- "'1999-12-31 23:29:59'", "'2000-01-01 00:00:29'", "'1999-12-31
23:59:29'", "'1999-12-31 23:59:59'",
+ "'1999-12-31 23:29:59'", "'2000-01-01 00:00:29'", "'1999-12-31
23:59:29'",
+ "'1999-12-31 23:59:59.000030'", "'1999-12-31
23:59:58.999970'", "'1999-12-31 23:59:59.030000'",
+ "'1999-12-31 23:59:58.970000'",
"1999", "4", "12", "6", "31", "365", "31", "23", "59", "59",
- "'1999-12-31'", "'1999-12-27'", "'1999-12-31'",
"'1999-12-31'", "730484", "'1999-12-31'", "'1999-12-31'"
+ "'1999-12-31'", "'1999-12-27'", "'1999-12-31'",
"'1999-12-31'", "730484", "'1999-12-31'",
+ "'1999-12-31'"
};
int answerIdx = 0;
@@ -578,6 +583,12 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
answer[answerIdx++]);
Assertions.assertEquals(DateTimeArithmetic.microSecondsAdd(dateLiteral,
integerLiteral).toSql(),
answer[answerIdx++]);
+
Assertions.assertEquals(DateTimeArithmetic.microSecondsSub(dateLiteral,
integerLiteral).toSql(),
+ answer[answerIdx++]);
+
Assertions.assertEquals(DateTimeArithmetic.milliSecondsAdd(dateLiteral,
integerLiteral).toSql(),
+ answer[answerIdx++]);
+
Assertions.assertEquals(DateTimeArithmetic.milliSecondsSub(dateLiteral,
integerLiteral).toSql(),
+ answer[answerIdx++]);
Assertions.assertEquals(DateTimeExtractAndTransform.year(dateLiteral).toSql(),
answer[answerIdx++]);
Assertions.assertEquals(DateTimeExtractAndTransform.quarter(dateLiteral).toSql(),
answer[answerIdx++]);
diff --git
a/regression-test/data/correctness/test_from_millisecond_microsecond.out
b/regression-test/data/correctness/test_from_millisecond_microsecond.out
index 50cdb55766d..84b1f2d5359 100644
--- a/regression-test/data/correctness/test_from_millisecond_microsecond.out
+++ b/regression-test/data/correctness/test_from_millisecond_microsecond.out
@@ -159,3 +159,19 @@
-- !sql --
\N
+-- !sql_all_constent --
+2010-11-30T23:50:50.000002 2010-11-30T23:50:49.999998
2010-11-30T23:50:50.002 2010-11-30T23:50:49.998
+
+-- !sql_all_constent --
+2010-11-30T23:50:50.000002 2010-11-30T23:50:49.999998
2010-11-30T23:50:50.002 2010-11-30T23:50:49.998
+
+-- !select_null_datetime --
+1 2023-01-01T00:00:00.000002 2022-12-31T23:59:59.999998
2023-01-01T00:00:00.002 2022-12-31T23:59:59.998
+2 2023-01-01T00:00:00.123002 2023-01-01T00:00:00.122998
2023-01-01T00:00:00.125 2023-01-01T00:00:00.121
+3 2023-01-01T00:00:00.123458 2023-01-01T00:00:00.123454
2023-01-01T00:00:00.125456 2023-01-01T00:00:00.121456
+
+-- !select_null_datetime --
+1 2023-01-01T00:00:00.000002 2022-12-31T23:59:59.999998
2023-01-01T00:00:00.002 2022-12-31T23:59:59.998
+2 2023-01-01T00:00:00.123002 2023-01-01T00:00:00.122998
2023-01-01T00:00:00.125 2023-01-01T00:00:00.121
+3 2023-01-01T00:00:00.123458 2023-01-01T00:00:00.123454
2023-01-01T00:00:00.125456 2023-01-01T00:00:00.121456
+
diff --git
a/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
index 48df29472f6..afbb386931b 100644
---
a/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
+++
b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy
@@ -316,4 +316,35 @@ suite("test_from_millisecond_microsecond") {
qt_sql " select from_second(-1) "
qt_sql " select from_microsecond(253402271999999999) "
qt_sql " select from_microsecond(253402272000000000) "
+
+
+ qt_sql_all_constent """
+ select microseconds_add('2010-11-30 23:50:50', 2) ,
microseconds_sub('2010-11-30 23:50:50', 2) , milliseconds_add('2010-11-30
23:50:50', 2) , milliseconds_sub('2010-11-30 23:50:50', 2);
+ """
+
+ qt_sql_all_constent """
+ select microseconds_add(cast('2010-11-30 23:50:50' as DATETIME(3)), 2)
, microseconds_sub(cast('2010-11-30 23:50:50' as DATETIME(3)), 2) ,
milliseconds_add(cast('2010-11-30 23:50:50' as DATETIME(3)), 2) ,
milliseconds_sub(cast('2010-11-30 23:50:50' as DATETIME(3)), 2);
+ """
+
+ qt_select_null_datetime """
+ select
+ id,
+ microseconds_add(t,2),
+ microseconds_sub(t,2),
+ milliseconds_add(t,2),
+ milliseconds_sub(t,2)
+ from millimicro
+ order by id;
+ """
+
+ qt_select_null_datetime """
+ select
+ id,
+ microseconds_add(cast(t as DATETIME(3)),2),
+ microseconds_sub(cast(t as DATETIME(3)),2),
+ milliseconds_add(cast(t as DATETIME(3)),2),
+ milliseconds_sub(cast(t as DATETIME(3)),2)
+ from millimicro
+ order by id;
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
