This is an automated email from the ASF dual-hosted git repository.
lihaopeng 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 8840daab84c [Fix](function) fix FE impl of some time functions (#37746)
8840daab84c is described below
commit 8840daab84c4276c50f42c869ccdf7430035b644
Author: zclllyybb <[email protected]>
AuthorDate: Thu Jul 18 14:08:00 2024 +0800
[Fix](function) fix FE impl of some time functions (#37746)
before:
```sql
mysql> select date_ceil("2020-12-12 12:12:12.123", interval 2 second);
+-----------------------+
| '2020-12-12 12:12:12' |
+-----------------------+
| 2020-12-12 12:12:12 |
+-----------------------+
1 row in set (0.10 sec)
mysql> select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/Galapagos');
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.09 sec)
mysql [(none)]>select CONVERT_TZ('9999-12-31 23:59:59.999999',
'Pacific/Galapagos', 'Pacific/GalapaGoS');
+-----------------------------------------------------------------------------------------------------------+
| convert_tz(cast('9999-12-31 23:59:59.999999' as DATETIMEV2(6)),
'Pacific/Galapagos', 'Pacific/GalapaGoS') |
+-----------------------------------------------------------------------------------------------------------+
| 9999-12-31 23:59:59.999999
|
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.08 sec) --- gone to BE
```
after:
```sql
mysql> select date_ceil("2020-12-12 12:12:12.123", interval 2 second);
+------------------------------+
| '2020-12-12 12:12:14.000000' |
+------------------------------+
| 2020-12-12 12:12:14 |
+------------------------------+
1 row in set (0.11 sec)
mysql> select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/Galapagos');
+-----------------------------------------------------------------------------------------------------------+
| convert_tz(cast('9999-12-31 23:59:59.999999' as DATETIMEV2(6)),
'Pacific/Galapagos', 'Pacific/Galapagos') |
+-----------------------------------------------------------------------------------------------------------+
| 9999-12-31 23:59:59.999999
|
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.23 sec)
mysql> select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/GalapaGoS');
+------------------------------+
| '9999-12-31 23:59:59.999999' |
+------------------------------+
| 9999-12-31 23:59:59.999999 |
+------------------------------+
1 row in set (0.11 sec) --- finished in FE
```
---
.../executable/DateTimeExtractAndTransform.java | 18 ++++++++--
.../functions/executable/TimeRoundSeries.java | 2 +-
.../trees/expressions/literal/DateLiteral.java | 2 +-
.../expressions/literal/DateTimeV2Literal.java | 2 +-
.../nereids/rules/expression/FoldConstantTest.java | 21 +++++++++---
.../data/correctness/test_timev2_fold.out | 34 +++++++++++++++---
.../suites/correctness/test_timev2_fold.groovy | 40 ++++++++++++++++++----
.../nereids_p0/javaudf/test_alias_function.groovy | 2 +-
8 files changed, 97 insertions(+), 24 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index f719eea44b3..c14b372f201 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -51,7 +51,9 @@ import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;
+import java.time.format.DateTimeFormatterBuilder;
import java.time.format.DateTimeParseException;
+import java.time.format.ResolverStyle;
import java.time.format.TextStyle;
import java.time.temporal.ChronoUnit;
import java.time.temporal.WeekFields;
@@ -645,12 +647,22 @@ public class DateTimeExtractAndTransform {
return datetime;
}
+ /**
+ * convert_tz
+ */
@ExecFunction(name = "convert_tz", argTypes = {"DATETIMEV2", "VARCHAR",
"VARCHAR"}, returnType = "DATETIMEV2")
public static Expression convertTz(DateTimeV2Literal datetime,
StringLikeLiteral fromTz, StringLikeLiteral toTz) {
+ DateTimeFormatter zoneFormatter = new DateTimeFormatterBuilder()
+ .parseCaseInsensitive()
+ .appendZoneOrOffsetId()
+ .toFormatter()
+ .withResolverStyle(ResolverStyle.STRICT);
+ ZoneId fromZone =
ZoneId.from(zoneFormatter.parse(fromTz.getStringValue()));
+ ZoneId toZone =
ZoneId.from(zoneFormatter.parse(toTz.getStringValue()));
+
LocalDateTime localDateTime = datetime.toJavaDateType();
- ZonedDateTime fromDateTime =
localDateTime.atZone(ZoneId.of(fromTz.getStringValue()));
- ZonedDateTime toDateTime =
fromDateTime.withZoneSameInstant(ZoneId.of(toTz.getStringValue()));
- return
DateTimeV2Literal.fromJavaDateType(toDateTime.toLocalDateTime(),
datetime.getDataType().getScale());
+ ZonedDateTime resultDateTime =
localDateTime.atZone(fromZone).withZoneSameInstant(toZone);
+ return
DateTimeV2Literal.fromJavaDateType(resultDateTime.toLocalDateTime(),
datetime.getDataType().getScale());
}
@ExecFunction(name = "weekday", argTypes = {"DATE"}, returnType =
"TINYINT")
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
index cde59d85686..a9337f05370 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
@@ -87,7 +87,7 @@ public class TimeRoundSeries {
+ (dt.getHour() - start.getHour()) * 60 * 60
+ (dt.getMinute() - start.getMinute()) * 60
+ (dt.getSecond() - start.getSecond());
- trivialPart = 0;
+ trivialPart = dt.getMicroSecond() - start.getMicroSecond();
break;
}
default: {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
index ebf7d225e3a..46345445b2f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
@@ -46,7 +46,7 @@ public class DateLiteral extends Literal {
// for cast datetime type to date type.
private static final LocalDateTime START_OF_A_DAY = LocalDateTime.of(0, 1,
1, 0, 0, 0);
- private static final LocalDateTime END_OF_A_DAY = LocalDateTime.of(9999,
12, 31, 23, 59, 59);
+ private static final LocalDateTime END_OF_A_DAY = LocalDateTime.of(9999,
12, 31, 23, 59, 59, 999999000);
private static final DateLiteral MIN_DATE = new DateLiteral(0, 1, 1);
private static final DateLiteral MAX_DATE = new DateLiteral(9999, 12, 31);
private static final int[] DAYS_IN_MONTH = new int[] {0, 31, 28, 31, 30,
31, 30, 31, 31, 30, 31, 30, 31};
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 1ddd0cfcc5c..a769bd03717 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
@@ -257,7 +257,7 @@ public class DateTimeV2Literal extends DateTimeLiteral {
}
public static Expression fromJavaDateType(LocalDateTime dateTime) {
- return fromJavaDateType(dateTime, 0);
+ return fromJavaDateType(dateTime, 6);
}
/**
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 e3276522844..6e4febe0ea6 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
@@ -32,6 +32,7 @@ import
org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.TimestampArithmetic;
import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeArithmetic;
import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeExtractAndTransform;
+import
org.apache.doris.nereids.trees.expressions.functions.executable.TimeRoundSeries;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.AppendTrailingCharIfAbsent;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ConvertTz;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DateFormat;
@@ -186,7 +187,11 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
ConvertTz c = new
ConvertTz(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1,
1)),
StringLiteral.of("Asia/Shanghai"), StringLiteral.of("GMT"));
Expression rewritten = executor.rewrite(c, context);
- Assertions.assertEquals(new DateTimeV2Literal("0000-12-31 16:55:18"),
rewritten);
+ Assertions.assertTrue(new DateTimeV2Literal("0000-12-31
16:55:18.000000").compareTo((Literal) rewritten) == 0);
+ c = new
ConvertTz(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(9999, 12, 31, 23,
59, 59, 999999000)),
+ StringLiteral.of("Pacific/Galapagos"),
StringLiteral.of("Pacific/Galapagos"));
+ rewritten = executor.rewrite(c, context);
+ Assertions.assertTrue(new DateTimeV2Literal("9999-12-31
23:59:59.999999").compareTo((Literal) rewritten) == 0);
DateFormat d = new
DateFormat(DateTimeLiteral.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1, 1)),
StringLiteral.of("%y %m %d"));
@@ -212,7 +217,7 @@ class FoldConstantTest extends ExpressionRewriteTestHelper {
t = new
DateTrunc(DateTimeV2Literal.fromJavaDateType(LocalDateTime.of(1, 1, 1, 1, 1,
1)),
StringLiteral.of("week"));
rewritten = executor.rewrite(t, context);
- Assertions.assertEquals(new DateTimeV2Literal("0001-01-01 00:00:00"),
rewritten);
+ Assertions.assertTrue(((Literal) rewritten).compareTo(new
DateTimeV2Literal("0001-01-01 00:00:00.000000")) == 0);
t = new DateTrunc(DateLiteral.fromJavaDateType(LocalDateTime.of(1, 1,
1, 1, 1, 1)),
StringLiteral.of("week"));
rewritten = executor.rewrite(t, context);
@@ -600,6 +605,11 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
Assertions.assertEquals("'2023 18 2023 19'",
DateTimeExtractAndTransform.dateFormat(
new DateTimeLiteral("2023-05-07 02:41:42"),
new VarcharLiteral("%x %v %X %V")).toSql());
+
+ Assertions.assertTrue(new DateTimeV2Literal("2021-01-01
12:12:14.000000").compareTo((Literal) TimeRoundSeries
+ .secondCeil(new DateTimeV2Literal("2021-01-01 12:12:12.123"),
new IntegerLiteral(2))) == 0);
+ Assertions.assertTrue(new DateTimeV2Literal("2021-01-01
12:12:12.000000").compareTo((Literal) TimeRoundSeries
+ .secondFloor(new DateTimeV2Literal("2021-01-01 12:12:12.123"),
new IntegerLiteral(2))) == 0);
}
@Test
@@ -632,9 +642,10 @@ class FoldConstantTest extends ExpressionRewriteTestHelper
{
String[] tags = {"year", "month", "day", "hour", "minute", "second"};
String[] answer = {
- "'2001-01-01 00:00:00'", "'2001-01-01 00:00:00'", "'2001-12-01
00:00:00'", "'2001-12-01 00:00:00'",
- "'2001-12-31 00:00:00'", "'2001-12-31 00:00:00'", "'2001-12-31
01:00:00'", "'2001-12-31 01:00:00'",
- "'2001-12-31 01:01:00'", "'2001-12-31 01:01:00'", "'2001-12-31
01:01:01'", "'2001-12-31 01:01:01'",
+ "'2001-01-01 00:00:00'", "'2001-01-01 00:00:00.000000'",
"'2001-12-01 00:00:00'",
+ "'2001-12-01 00:00:00.000000'", "'2001-12-31 00:00:00'",
"'2001-12-31 00:00:00.000000'",
+ "'2001-12-31 01:00:00'", "'2001-12-31 01:00:00.000000'",
"'2001-12-31 01:01:00'",
+ "'2001-12-31 01:01:00.000000'", "'2001-12-31 01:01:01'",
"'2001-12-31 01:01:01.000000'",
"'2001-01-01 00:00:00'", "'2001-01-01 00:00:00'", "'2001-01-01
00:00:00'",
"'2001-04-01 00:00:00'", "'2001-04-01 00:00:00'", "'2001-04-01
00:00:00'",
"'2001-07-01 00:00:00'", "'2001-07-01 00:00:00'", "'2001-07-01
00:00:00'",
diff --git a/regression-test/data/correctness/test_timev2_fold.out
b/regression-test/data/correctness/test_timev2_fold.out
index b070f10507f..756f9df5b19 100644
--- a/regression-test/data/correctness/test_timev2_fold.out
+++ b/regression-test/data/correctness/test_timev2_fold.out
@@ -1,13 +1,37 @@
-- This file is automatically generated. You should know what you did if you
want to edit this
--- !select1 --
+-- !select10 --
-07:00:00
--- !select2 --
--07:00:00
+-- !select11 --
+2020-02-02T04:00
+
+-- !select12 --
+2020-05-02T03:00
+
+-- !select13 --
+9999-12-31T23:59:59.999999
--- !select3 --
+-- !select20 --
-07:00:00
--- !select4 --
+-- !select21 --
+2020-02-02T04:00
+
+-- !select22 --
+2020-05-02T03:00
+
+-- !select23 --
+9999-12-31T23:59:59.999999
+
+-- !select20 --
-07:00:00
+-- !select21 --
+2020-02-02T04:00
+
+-- !select22 --
+2020-05-02T03:00
+
+-- !select23 --
+9999-12-31T23:59:59.999999
+
diff --git a/regression-test/suites/correctness/test_timev2_fold.groovy
b/regression-test/suites/correctness/test_timev2_fold.groovy
index dcc65539196..ac7994922c0 100644
--- a/regression-test/suites/correctness/test_timev2_fold.groovy
+++ b/regression-test/suites/correctness/test_timev2_fold.groovy
@@ -16,20 +16,46 @@
// under the License.
suite("test_timev2_fold") {
+ // FE
sql """ set enable_fold_constant_by_be=false """
- qt_select1 """
+ qt_select10 """
select timediff( convert_tz("2020-05-05 00:00:00", 'UTC',
'America/Los_Angeles'), "2020-05-05 00:00:00");
"""
- sql """ set enable_nereids_planner=true,enable_fold_constant_by_be=false
"""
- qt_select2 """
- select timediff( convert_tz("2020-05-05 00:00:00", 'UTC',
'America/Los_Angeles'), "2020-05-05 00:00:00");
+ qt_select11 """
+ select convert_tz('2020-02-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select12 """
+ select convert_tz('2020-05-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select13 """
+ select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/GalapaGoS');
"""
+ // FE + BE
sql """ set enable_fold_constant_by_be=true """
- qt_select3 """
+ qt_select20 """
select timediff( convert_tz("2020-05-05 00:00:00", 'UTC',
'America/Los_Angeles'), "2020-05-05 00:00:00");
"""
- sql """ set enable_nereids_planner=true,enable_fold_constant_by_be=true """
- qt_select4 """
+ qt_select21 """
+ select convert_tz('2020-02-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select22 """
+ select convert_tz('2020-05-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select23 """
+ select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/GalapaGoS');
+ """
+ // BE
+ sql """ set debug_skip_fold_constant=true """
+ qt_select20 """
select timediff( convert_tz("2020-05-05 00:00:00", 'UTC',
'America/Los_Angeles'), "2020-05-05 00:00:00");
"""
+ qt_select21 """
+ select convert_tz('2020-02-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select22 """
+ select convert_tz('2020-05-01 12:00:00', 'America/Los_Angeles',
'+08:00');
+ """
+ qt_select23 """
+ select CONVERT_TZ('9999-12-31 23:59:59.999999', 'Pacific/Galapagos',
'Pacific/GalapaGoS');
+ """
}
diff --git
a/regression-test/suites/nereids_p0/javaudf/test_alias_function.groovy
b/regression-test/suites/nereids_p0/javaudf/test_alias_function.groovy
index 7aa5077600c..579724a0139 100644
--- a/regression-test/suites/nereids_p0/javaudf/test_alias_function.groovy
+++ b/regression-test/suites/nereids_p0/javaudf/test_alias_function.groovy
@@ -46,7 +46,7 @@ suite("nereids_test_alias_function") {
test {
sql 'select cast(f1(\'2023-06-01\', 3) as string);'
- result([['2023-05-29 00:00:00']])
+ result([['2023-05-29 00:00:00.000000']])
}
test {
sql 'select f2(f1(\'2023-05-20\', 2), 3)'
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
