Skip to content

Commit 6e5a892

Browse files
authored
[fix](nereids) Fold UTC_DATE and UTC_TIME in FE (apache#69393)
### What problem does this PR solve? Issue Number: close apache#69368 Related PR: apache#57347 Problem Summary: `UTC_DATE()` and `UTC_TIME()` lack FE constant folding support, preventing partition pruning for predicates such as `ts >= UTC_DATE()`. ### Release note Add FE constant folding for `UTC_DATE()` and both overloads of `UTC_TIME()`, using the statement start time in UTC.
1 parent 0d777cd commit 6e5a892

3 files changed

Lines changed: 143 additions & 3 deletions

File tree

‎fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeAcquire.java‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -162,4 +162,19 @@ public static Expression utcTimestamp() {
162162
public static Expression utcTimestamp(IntegerLiteral precision) {
163163
return currentTimestamp(precision.getValue(), ZoneId.of("UTC+0"));
164164
}
165+
166+
@ExecFunction(name = "utc_date")
167+
public static Expression utcDate() {
168+
return DateV2Literal.fromJavaDateType(currentDateTime(ZoneId.of("UTC+0")));
169+
}
170+
171+
@ExecFunction(name = "utc_time")
172+
public static Expression utcTime() {
173+
return TimeV2Literal.fromJavaDateType(currentDateTime(ZoneId.of("UTC+0")));
174+
}
175+
176+
@ExecFunction(name = "utc_time")
177+
public static Expression utcTime(IntegerLiteral precision) {
178+
return TimeV2Literal.fromJavaDateType(currentDateTime(ZoneId.of("UTC+0")), precision.getValue());
179+
}
165180
}
Lines changed: 57 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,64 @@
11
-- This file is automatically generated. You should know what you did if you want to edit this
22
-- !sql --
3-
2019-03-21 15:10:55 1196389819
3+
2019-03-21 15:10:55 1196389819.000000
44

55
-- !sql --
6-
2019-03-21 07:10:55 1196418619
6+
2019-03-21 07:10:55 1196418619.000000
77

88
-- !sql --
9-
2019-03-21 11:10:55 1196404219
9+
2019-03-21 11:10:55 1196404219.000000
10+
11+
-- !utc_today --
12+
3
13+
14+
-- !utc_yesterday --
15+
3
16+
17+
-- !utc_values --
18+
true true true true true true true true true
19+
20+
-- !utc_today --
21+
3
22+
23+
-- !utc_yesterday --
24+
3
25+
26+
-- !utc_values --
27+
true true true true true true true true true
28+
29+
-- !utc_today --
30+
3
31+
32+
-- !utc_yesterday --
33+
3
34+
35+
-- !utc_values --
36+
true true true true true true true true true
37+
38+
-- !utc_today --
39+
3
40+
41+
-- !utc_yesterday --
42+
3
43+
44+
-- !utc_values --
45+
true true true true true true true true true
46+
47+
-- !utc_today --
48+
3
49+
50+
-- !utc_yesterday --
51+
3
52+
53+
-- !utc_values --
54+
true true true true true true true true true
55+
56+
-- !utc_today --
57+
3
58+
59+
-- !utc_yesterday --
60+
3
61+
62+
-- !utc_values --
63+
true true true true true true true true true
1064

‎regression-test/suites/query_p0/datatype/test_date_acquire.groovy‎

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -32,4 +32,75 @@ suite("test_date_acquire") {
3232

3333
sql "set time_zone='+04:00'"
3434
qt_sql "select from_unixtime(1553152255), unix_timestamp('2007-11-30 10:30%3A19', '%Y-%m-%d %H:%i%%3A%s')"
35+
36+
sql "DROP TABLE IF EXISTS utc_function_folding"
37+
sql """
38+
CREATE TABLE utc_function_folding (
39+
ts DATETIME NOT NULL,
40+
v INT
41+
)
42+
DUPLICATE KEY(ts)
43+
PARTITION BY RANGE(ts) (
44+
PARTITION p_before_1970 VALUES LESS THAN ('1970-01-01'),
45+
PARTITION p_before_2000 VALUES LESS THAN ('2000-01-01'),
46+
PARTITION p_current VALUES LESS THAN MAXVALUE
47+
)
48+
DISTRIBUTED BY HASH(v) BUCKETS 1
49+
PROPERTIES ("replication_num" = "1")
50+
"""
51+
sql """INSERT INTO utc_function_folding VALUES
52+
('1960-01-01', 1), ('1990-01-01', 2), ('9999-01-01', 3)
53+
"""
54+
55+
def utcValues = """
56+
SELECT UTC_DATE() = CAST(UTC_TIMESTAMP() AS DATE),
57+
UTC_TIME() = CAST(UTC_TIMESTAMP() AS TIME),
58+
UTC_TIME(0) = CAST(UTC_TIMESTAMP(0) AS TIME(0)),
59+
UTC_TIME(1) = CAST(UTC_TIMESTAMP(1) AS TIME(1)),
60+
UTC_TIME(2) = CAST(UTC_TIMESTAMP(2) AS TIME(2)),
61+
UTC_TIME(3) = CAST(UTC_TIMESTAMP(3) AS TIME(3)),
62+
UTC_TIME(4) = CAST(UTC_TIMESTAMP(4) AS TIME(4)),
63+
UTC_TIME(5) = CAST(UTC_TIMESTAMP(5) AS TIME(5)),
64+
UTC_TIME(6) = CAST(UTC_TIMESTAMP(6) AS TIME(6))
65+
"""
66+
def today = "SELECT v FROM utc_function_folding WHERE ts >= UTC_DATE() ORDER BY v"
67+
def yesterday = """SELECT v FROM utc_function_folding
68+
WHERE ts >= UTC_DATE() - INTERVAL 1 DAY ORDER BY v"""
69+
def previous = sql "SELECT @@time_zone, @@enable_fold_constant_by_be, @@debug_skip_fold_constant"
70+
try {
71+
for (def foldByBe : [false, true]) {
72+
sql "SET enable_fold_constant_by_be=${foldByBe}"
73+
for (def timeZone : ["+00:00", "+08:00", "-08:00"]) {
74+
sql "SET time_zone='${timeZone}'"
75+
sql "SET debug_skip_fold_constant=false"
76+
explain {
77+
sql today
78+
contains "partitions=1/3 (p_current)"
79+
notContains "utc_date()"
80+
}
81+
explain {
82+
sql yesterday
83+
contains "partitions=1/3 (p_current)"
84+
notContains "utc_date()"
85+
}
86+
explain {
87+
sql "SELECT UTC_DATE(), UTC_TIME(), UTC_TIME(0), UTC_TIME(1), UTC_TIME(2), " +
88+
"UTC_TIME(3), UTC_TIME(4), UTC_TIME(5), UTC_TIME(6)"
89+
notContains "utc_date("
90+
notContains "utc_time("
91+
}
92+
qt_utc_today today
93+
qt_utc_yesterday yesterday
94+
qt_utc_values utcValues
95+
testFoldConst(today)
96+
testFoldConst(yesterday)
97+
testFoldConst(utcValues)
98+
}
99+
}
100+
} finally {
101+
sql "SET time_zone='${previous[0][0]}'"
102+
sql "SET enable_fold_constant_by_be=${previous[0][1]}"
103+
sql "SET debug_skip_fold_constant=${previous[0][2]}"
104+
sql "DROP TABLE IF EXISTS utc_function_folding"
105+
}
35106
}

0 commit comments

Comments
 (0)