You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/06/19 15:44:40 UTC
[doris] branch master updated: [fix](datetime) Fix the error in date calculation that includes constants (#20863)
This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 5a28b6f9fc [fix](datetime) Fix the error in date calculation that includes constants (#20863)
5a28b6f9fc is described below
commit 5a28b6f9fc29b697861919f370dc03cf7c0539ee
Author: Mryange <59...@users.noreply.github.com>
AuthorDate: Mon Jun 19 23:44:30 2023 +0800
[fix](datetime) Fix the error in date calculation that includes constants (#20863)
before
```
mysql> select hours_add('2023-03-30 22:23:45.23452',8);
+-------------------------------------+
| hours_add('2023-03-30 22:23:45', 8) |
+-------------------------------------+
| 2023-03-31 06:23:45 |
+-------------------------------------+
mysql> select date_add('2023-03-30 22:23:45.23452',8);
+------------------------------------+
| date_add('2023-03-30 22:23:45', 8) |
+------------------------------------+
| 2023-04-07 22:23:45 |
+------------------------------------+
mysql [test]>select hours_add('2023-03-30 22:23:45.23452',8);
+-------------------------------------------+
| hours_add('2023-03-30 22:23:45.23452', 8) |
+-------------------------------------------+
| 2023-03-31 06:23:45.000234 |
+-------------------------------------------+
```
after
```
mysql [test]>select hours_add('2023-03-30 22:23:45.23452',8);
+-------------------------------------------+
| hours_add('2023-03-30 22:23:45.23452', 8) |
+-------------------------------------------+
| 2023-03-31 06:23:45.23452 |
+-------------------------------------------+
1 row in set (0.01 sec)
mysql [test]>select date_add('2023-03-30 22:23:45.23452',8);
+------------------------------------------+
| date_add('2023-03-30 22:23:45.23452', 8) |
+------------------------------------------+
| 2023-04-07 22:23:45.23452 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql [test]>set enable_nereids_planner=true;
Query OK, 0 rows affected (0.00 sec)
mysql [test]>set enable_fallback_to_original_planner=false;
Query OK, 0 rows affected (0.00 sec)
mysql [test]>select hours_add('2023-03-30 22:23:45.23452',8);
+-------------------------------------------+
| hours_add('2023-03-30 22:23:45.23452', 8) |
+-------------------------------------------+
| 2023-03-31 06:23:45.23452 |
+-------------------------------------------+
1 row in set (0.03 sec)
mysql [test]>select date_add('2023-03-30 22:23:45.23452',8);
+------------------------------------------+
| days_add('2023-03-30 22:23:45.23452', 8) |
+------------------------------------------+
| 2023-04-07 22:23:45.23452 |
+------------------------------------------+
1 row in set (0.00 sec)
```
---
.../org/apache/doris/analysis/DateLiteral.java | 11 ++--
.../functions/ComputeSignatureHelper.java | 9 +++-
.../expressions/literal/DateTimeV2Literal.java | 35 ++++++++++---
.../apache/doris/nereids/types/DateTimeV2Type.java | 11 ++++
.../org/apache/doris/nereids/util/DateUtils.java | 5 +-
.../data/correctness/test_date_function_const.out | 31 ++++++++++++
.../suites/correctness/test_cast_as_time.groovy | 3 +-
.../correctness/test_date_function_const.groovy | 59 ++++++++++++++++++++++
.../suites/nereids_syntax_p0/type_cast.groovy | 5 +-
9 files changed, 152 insertions(+), 17 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
index 5299fb3f78..f9a4cd15ca 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
@@ -323,7 +323,7 @@ public class DateLiteral extends LiteralExpr {
this.month = dateTime.getMonthValue();
this.day = dateTime.getDayOfMonth();
this.type = type;
- if (type.equals(Type.DATETIME) || type.equals(Type.DATETIMEV2)) {
+ if (type.isDatetime() || type.isDatetimeV2()) {
this.hour = dateTime.getHour();
this.minute = dateTime.getMinute();
this.second = dateTime.getSecond();
@@ -444,8 +444,9 @@ public class DateLiteral extends LiteralExpr {
second = getOrDefault(dateTime, ChronoField.SECOND_OF_MINUTE, 0);
microsecond = getOrDefault(dateTime, ChronoField.MICRO_OF_SECOND, 0);
if (microsecond != 0 && type.isDatetime()) {
- LOG.warn("Microseconds is not supported by Datetime type and hence is ignored."
- + "Please change to Datetimev2 to use it.");
+ int dotIndex = s.lastIndexOf(".");
+ int scale = s.length() - dotIndex - 1;
+ type = ScalarType.createDatetimeV2Type(scale);
}
this.type = type;
if (checkRange() || checkDate()) {
@@ -1000,7 +1001,9 @@ public class DateLiteral extends LiteralExpr {
final int second = getOrDefault(accessor, ChronoField.SECOND_OF_MINUTE, 0);
final int microSeconds = getOrDefault(accessor, ChronoField.MICRO_OF_SECOND, 0);
- return LocalDateTime.of(year, month, dayOfMonth, hour, minute, second, microSeconds);
+ // LocalDateTime of(int year, int month, int dayOfMonth, int hour, int minute,
+ // int second, int nanoOfSecond)
+ return LocalDateTime.of(year, month, dayOfMonth, hour, minute, second, microSeconds * 1000);
}
public DateLiteral plusYears(int year) throws AnalysisException {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputeSignatureHelper.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputeSignatureHelper.java
index fba33d0fe0..e31a8ab6aa 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputeSignatureHelper.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/ComputeSignatureHelper.java
@@ -22,6 +22,7 @@ import org.apache.doris.catalog.FunctionSignature.TripleFunction;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
+import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.types.ArrayType;
import org.apache.doris.nereids.types.DataType;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -123,7 +124,13 @@ public class ComputeSignatureHelper {
continue;
}
if (finalType == null) {
- finalType = DateTimeV2Type.forType(arguments.get(i).getDataType());
+ if (arguments.get(i) instanceof StringLikeLiteral) {
+ // We need to determine the scale based on the string literal.
+ StringLikeLiteral str = (StringLikeLiteral) arguments.get(i);
+ finalType = DateTimeV2Type.forTypeFromString(str.getStringValue());
+ } else {
+ finalType = DateTimeV2Type.forType(arguments.get(i).getDataType());
+ }
} else {
finalType = DateTimeV2Type.getWiderDatetimeV2Type(finalType,
DateTimeV2Type.forType(arguments.get(i).getDataType()));
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 50b909f6df..e180c667e1 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
@@ -32,7 +32,7 @@ import java.time.LocalDateTime;
public class DateTimeV2Literal extends DateTimeLiteral {
public DateTimeV2Literal(String s) {
- this(DateTimeV2Type.MAX, s);
+ this(DateTimeV2Type.forTypeFromString(s), s);
}
public DateTimeV2Literal(DateTimeV2Type dateType, String s) {
@@ -62,9 +62,27 @@ public class DateTimeV2Literal extends DateTimeLiteral {
return visitor.visitDateTimeV2Literal(this, context);
}
+ private long formatMicroSecond(long microSecond) {
+ if (microSecond == 0) {
+ return microSecond;
+ }
+ while (10 * microSecond <= 999999) {
+ microSecond = 10 * microSecond;
+ }
+ return microSecond;
+ }
+
+ /*
+ * Legacy and Nereids have different approaches to handling accuracy in scale.
+ * For example, with a scale of 5, Legacy would store 123450, while Nereids
+ * would store it as 12345.
+ * Therefore, we need to perform a conversion.
+ * Alternatively, can we standardize the format?
+ */
@Override
public LiteralExpr toLegacyLiteral() {
- return new org.apache.doris.analysis.DateLiteral(year, month, day, hour, minute, second, microSecond,
+ return new org.apache.doris.analysis.DateLiteral(year, month, day, hour, minute, second,
+ formatMicroSecond(microSecond),
getDataType().toCatalogDataType());
}
@@ -75,10 +93,15 @@ public class DateTimeV2Literal extends DateTimeLiteral {
@Override
public String getStringValue() {
- return String.format("%04d-%02d-%02d %02d:%02d:%02d"
- + (getDataType().getScale() > 0 ? ".%0" + getDataType().getScale() + "d" : ""),
- year, month, day, hour, minute, second,
- (int) (microSecond / Math.pow(10, DateTimeV2Type.MAX_SCALE - getDataType().getScale())));
+ int scale = getDataType().getScale();
+ if (scale == 0) {
+ return String.format("%04d-%02d-%02d %02d:%02d:%02d", year, month, day, hour, minute, second);
+ } else {
+ int microsecond = (int) (microSecond / Math.pow(10, DateTimeV2Type.MAX_SCALE - scale));
+ return String.format("%04d-%02d-%02d %02d:%02d:%02d"
+ + ".%0" + scale + "d",
+ year, month, day, hour, minute, second, microsecond);
+ }
}
@Override
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
index 71a12e2410..6a57c7a218 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/types/DateTimeV2Type.java
@@ -78,6 +78,17 @@ public class DateTimeV2Type extends DateLikeType {
return MAX;
}
+ /**
+ * return proper type of datetimev2 for String
+ * may be we need to check for validity?
+ */
+ public static DateTimeV2Type forTypeFromString(String s) {
+ if (!s.contains(String.valueOf("."))) {
+ return DateTimeV2Type.SYSTEM_DEFAULT;
+ }
+ return DateTimeV2Type.of(s.length() - s.lastIndexOf(".") - 1);
+ }
+
@Override
public String toSql() {
return super.toSql() + "(" + scale + ")";
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
index cb65a9bca6..56201460e7 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/DateUtils.java
@@ -158,12 +158,11 @@ public class DateUtils {
getOrDefault(accessor, ChronoField.HOUR_OF_DAY),
getOrDefault(accessor, ChronoField.MINUTE_OF_HOUR),
getOrDefault(accessor, ChronoField.SECOND_OF_MINUTE),
- getOrDefault(accessor, ChronoField.MICRO_OF_SECOND)
- );
+ 1000 * getOrDefault(accessor, ChronoField.MICRO_OF_SECOND));
}
public static int getOrDefault(final TemporalAccessor accessor, final ChronoField field) {
- return accessor.isSupported(field) ? accessor.get(field) : /*default value*/ 0;
+ return accessor.isSupported(field) ? accessor.get(field) : /* default value */ 0;
}
public static ZoneId getTimeZone() {
diff --git a/regression-test/data/correctness/test_date_function_const.out b/regression-test/data/correctness/test_date_function_const.out
new file mode 100644
index 0000000000..29c884118c
--- /dev/null
+++ b/regression-test/data/correctness/test_date_function_const.out
@@ -0,0 +1,31 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select1 --
+2023-03-31T06:23:45.234520
+
+-- !select2 --
+2023-04-07T22:23:45.234520
+
+-- !select3 --
+2023-03-30T22:31:45.234520
+
+-- !select4 --
+2023-03-31T06:23:45.234500
+
+-- !select5 --
+2023-03-31T06:23:45.234520
+
+-- !select6 --
+2023-03-31T06:23:45.234520
+
+-- !select7 --
+2023-04-07T22:23:45.234520
+
+-- !select8 --
+2023-03-30T22:31:45.234520
+
+-- !select9 --
+2023-03-31T06:23:45.234500
+
+-- !select10 --
+2023-03-31T06:23:45.234520
+
diff --git a/regression-test/suites/correctness/test_cast_as_time.groovy b/regression-test/suites/correctness/test_cast_as_time.groovy
index b15440f791..892d4937e4 100644
--- a/regression-test/suites/correctness/test_cast_as_time.groovy
+++ b/regression-test/suites/correctness/test_cast_as_time.groovy
@@ -30,7 +30,8 @@ suite("test_cast_as_time") {
"storage_format" = "V2"
);
"""
- sql """
+ sql 'set enable_nereids_planner=true'
+ sql """
insert into tbl_cast_as_time values(300,'19:18:17')
"""
sql """
diff --git a/regression-test/suites/correctness/test_date_function_const.groovy b/regression-test/suites/correctness/test_date_function_const.groovy
new file mode 100644
index 0000000000..fd5607d519
--- /dev/null
+++ b/regression-test/suites/correctness/test_date_function_const.groovy
@@ -0,0 +1,59 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_date_function_const") {
+ sql 'set enable_nereids_planner=false'
+
+ qt_select1 """
+ select hours_add('2023-03-30 22:23:45.23452',8)
+ """
+ qt_select2 """
+ select date_add('2023-03-30 22:23:45.23452',8)
+ """
+ qt_select3 """
+ select minutes_add('2023-03-30 22:23:45.23452',8)
+ """
+ // using cast
+ qt_select4 """
+ select hours_add(cast('2023-03-30 22:23:45.23452' as datetimev2(4)),8)
+ """
+ qt_select5 """
+ select hours_add(cast('2023-03-30 22:23:45.23452' as datetimev2(6)),8)
+ """
+
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+
+
+ qt_select6 """
+ select hours_add('2023-03-30 22:23:45.23452',8)
+ """
+ qt_select7 """
+ select date_add('2023-03-30 22:23:45.23452',8)
+ """
+ qt_select8 """
+ select minutes_add('2023-03-30 22:23:45.23452',8)
+ """
+ // using cast
+ qt_select9 """
+ select hours_add(cast('2023-03-30 22:23:45.23452' as datetimev2(4)),8)
+ """
+ qt_select10 """
+ select hours_add(cast('2023-03-30 22:23:45.23452' as datetimev2(6)),8)
+ """
+
+}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_syntax_p0/type_cast.groovy b/regression-test/suites/nereids_syntax_p0/type_cast.groovy
index 1fa087a196..26300b6201 100644
--- a/regression-test/suites/nereids_syntax_p0/type_cast.groovy
+++ b/regression-test/suites/nereids_syntax_p0/type_cast.groovy
@@ -34,8 +34,9 @@ suite("type_cast") {
sql """insert into test_table2 values('2020-05-25');"""
def ret = sql"""explain verbose select * from test_table2 where day > CONVERT_tz('2020-05-25 00:00:00', 'Asia/Shanghai', 'Asia/Shanghai');"""
- assertTrue(ret.toString().contains("CAST(day[#0] AS DATETIMEV2(6))"))
-
+ // assertTrue(ret.toString().contains("CAST(day[#0] AS DATETIMEV2(6))"))
+ // https://github.com/apache/doris/pull/20863
+ // Since we have handled datetime string literals, we don't need to use cast for conversion.
qt_sql """select count(*) from test_table2 where 'a' = 'a';"""
qt_sql """select count(*) from test_table2 where cast('2020-01-01' as date) = cast('2020-01-01' as date);"""
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org