This is an automated email from the ASF dual-hosted git repository.
zclllyybb 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 43800f3bf51 [Fix](nereids) Fix nullable date literal binding and date
*_diff folding (#64127)
43800f3bf51 is described below
commit 43800f3bf517704e34eceb91d7b794938d84c3ad
Author: linrrarity <[email protected]>
AuthorDate: Tue Jun 9 21:52:00 2026 +0800
[Fix](nereids) Fix nullable date literal binding and date *_diff folding
(#64127)
## Problem Summary
Two issues in the **FE constant folding** of date/time `*_diff`
functions
(`days_diff`, `weeks_diff`, `hours_diff`, `minutes_diff`,
`seconds_diff`,
`milliseconds_diff`, `microseconds_diff`). Both only affect FE folding —
the BE
results were already correct — so `debug_skip_fold_constant` on/off
could
disagree for the same query. (`datediff` / `to_days` already fold via
`calc_daynr` and are **not** changed here; this PR only brings the
remaining
`*_diff` folding in line with them.)
### 1. Nullable string/date literals were resolved to TIMESTAMPTZ
unexpectedly
During signature search an argument is only type-coerced when
`argument.isLiteral()` is true. A literal wrapped in `nullable()` /
`non_nullable()` is a function node, not a `Literal`, so the check
fails: the
datetime coercion **and** the "no time zone → don't prefer TIMESTAMPTZ"
scoring
are both skipped. With the scoring gone every candidate signature ties,
and the
first one — the TIMESTAMPTZ overload — wins. So a plain, no-time-zone
literal
that happens to be wrapped in `nullable()` is cast to `timestamptz`
instead of
`datetimev2`.
The change unwraps `nullable()` / `non_nullable()` so the inner literal
is
coerced exactly like a bare literal. Genuinely timezone-bearing literals
(e.g. `'2021-12-31 00:30:00+08:00'`) still bind to TIMESTAMPTZ.
before:
```
explain select days_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00'));
0:VUNION(11)
constant exprs:
days_diff(CAST(nullable('2021-12-31 12:23:34') AS
timestamptz(6)), CAST(nullable('0000-01-01 00:00:00') AS timestamptz(6)))
```
now:
```
0:VUNION(11)
constant exprs:
days_diff(CAST(nullable('2021-12-31 12:23:34') AS datetimev2(6)),
CAST(nullable('0000-01-01 00:00:00') AS datetimev2(6)))
```
### 2. Wrong day count when the range crosses year `0000`
The folding used Java `ChronoUnit.*.between(...)`. Java's `java.time`
follows the
ISO proleptic Gregorian calendar in which year 0 **is** a leap year
(`0000-02-29` exists). Doris/BE use the MySQL-style `calc_daynr`, in
which year 0
is **not** a leap year. So whenever the interval crosses `0000-02-29`,
FE folding
was exactly one day larger than the BE result.
The change folds these functions from `calc_daynr`
(`DateLiteral.getTotalDays()`)
for the day count, plus the sub-day remainder for the time-aware units —
matching `datediff` / `to_days` and the BE. `TimestampTzLiteral`
overloads are
added so timezone-bearing literals fold on the FE too.
before:
```
set debug_skip_fold_constant=false; -- FE folds
select days_diff('0001-01-01 00:00:00', '0000-01-01 00:00:00');
+---------------------------------------------------------+
| days_diff('0001-01-01 00:00:00', '0000-01-01 00:00:00') |
+---------------------------------------------------------+
| 366 | -- wrong
+---------------------------------------------------------+
set debug_skip_fold_constant=true; -- BE
select days_diff('0001-01-01 00:00:00', '0000-01-01 00:00:00');
+---------------------------------------------------------+
| 365 | -- correct
+---------------------------------------------------------+
```
now: FE folding matches BE, both return `365`.
---
.../expressions/functions/SearchSignature.java | 8 +-
.../executable/DateTimeExtractAndTransform.java | 104 +++++++++++++++----
.../trees/expressions/literal/DateTimeLiteral.java | 4 +
.../expressions/literal/DateTimeV2Literal.java | 30 ++++++
.../trees/expressions/literal/DateV2Literal.java | 4 +
.../apache/doris/nereids/util/ExpressionUtils.java | 9 ++
.../data/nereids_function_p0/scalar_function/D.out | 12 +++
.../nereids_function_p0/scalar_function/D.groovy | 113 +++++++++++++++++++++
8 files changed, 259 insertions(+), 25 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java
index 22735912068..74e9e06f442 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/SearchSignature.java
@@ -29,6 +29,7 @@ import org.apache.doris.nereids.types.DateTimeV2Type;
import org.apache.doris.nereids.types.DateType;
import org.apache.doris.nereids.types.DateV2Type;
import org.apache.doris.nereids.types.DecimalV3Type;
+import org.apache.doris.nereids.util.ExpressionUtils;
import org.apache.doris.nereids.util.TypeCoercionUtils;
import com.google.common.collect.Lists;
@@ -257,14 +258,15 @@ public class SearchSignature {
// we need to try to do string literal coercion when search
signature.
// for example, FUNC_A has two signature FUNC_A(datetime) and
FUNC_A(string)
// if SQL block is `FUNC_A('2020-02-02 00:00:00')`, we should
return signature FUNC_A(datetime).
- if (!argument.isNullLiteral() && argument.isLiteral() &&
realType.isStringLikeType()) {
- realType =
TypeCoercionUtils.characterLiteralTypeCoercion(((Literal)
argument).getStringValue(),
+ Optional<Literal> literalAfterUnwrapNullable =
ExpressionUtils.getLiteralAfterUnwrapNullable(argument);
+ if (!argument.isNullLiteral() &&
literalAfterUnwrapNullable.isPresent() && realType.isStringLikeType()) {
+ String literalValue =
literalAfterUnwrapNullable.get().getStringValue();
+ realType =
TypeCoercionUtils.characterLiteralTypeCoercion(literalValue,
sigArgType).orElse(argument).getDataType();
if (!realType.isStringLikeType()) {
stringLiteralCoersionCount++;
}
- String literalValue = ((Literal) argument).getStringValue();
if (sigArgType.isTimeStampTzType()) {
boolean hasTimeZone =
DateTimeChecker.hasTimeZone(literalValue);
if (hasTimeZone) {
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 3595010c96e..2fc25ff528f 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
@@ -26,6 +26,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.FromMilliseco
import org.apache.doris.nereids.trees.expressions.functions.scalar.FromSecond;
import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
+import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateTimeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal;
@@ -38,6 +39,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.SmallIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLikeLiteral;
import org.apache.doris.nereids.trees.expressions.literal.StringLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TimeV2Literal;
+import org.apache.doris.nereids.trees.expressions.literal.TimestampTzLiteral;
import org.apache.doris.nereids.trees.expressions.literal.TinyIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral;
import org.apache.doris.nereids.types.DateTimeV2Type;
@@ -1109,112 +1111,155 @@ public class DateTimeExtractAndTransform {
@ExecFunction(name = "microseconds_diff")
public static Expression microsecondsDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MICROS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInMicroSeconds(t1, t2));
+ }
+
+ @ExecFunction(name = "microseconds_diff")
+ public static Expression microsecondsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInMicroSeconds(t1, t2));
}
@ExecFunction(name = "milliseconds_diff")
public static Expression millisecondsDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MILLIS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInMicroSeconds(t1, t2) / 1000L);
+ }
+
+ @ExecFunction(name = "milliseconds_diff")
+ public static Expression millisecondsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInMicroSeconds(t1, t2) / 1000L);
}
@ExecFunction(name = "seconds_diff")
public static Expression secondsDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.SECONDS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2));
+ }
+
+ @ExecFunction(name = "seconds_diff")
+ public static Expression secondsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2));
}
@ExecFunction(name = "seconds_diff")
public static Expression secondsDiff(DateTimeV2Literal t1, DateV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.SECONDS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2));
}
@ExecFunction(name = "seconds_diff")
public static Expression secondsDiff(DateV2Literal t1, DateTimeV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.SECONDS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2));
}
@ExecFunction(name = "seconds_diff")
public static Expression secondsDiff(DateV2Literal t1, DateV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.SECONDS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2));
}
@ExecFunction(name = "minutes_diff")
public static Expression minutesDiff(DateTimeV2Literal t1,
DateTimeV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MINUTES.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L);
+ }
+
+ @ExecFunction(name = "minutes_diff")
+ public static Expression minutesDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L);
}
@ExecFunction(name = "minutes_diff")
public static Expression minutesDiff(DateTimeV2Literal t1, DateV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.MINUTES.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L);
}
@ExecFunction(name = "minutes_diff")
public static Expression minutesDiff(DateV2Literal t1, DateTimeV2Literal
t2) {
- return new
BigIntLiteral(ChronoUnit.MINUTES.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L);
}
@ExecFunction(name = "minutes_diff")
public static Expression minutesDiff(DateV2Literal t1, DateV2Literal t2) {
- return new
BigIntLiteral(ChronoUnit.MINUTES.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L);
}
@ExecFunction(name = "hours_diff")
public static Expression hoursDiff(DateTimeV2Literal t1, DateTimeV2Literal
t2) {
- return new BigIntLiteral(ChronoUnit.HOURS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L / 60L);
+ }
+
+ @ExecFunction(name = "hours_diff")
+ public static Expression hoursDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L / 60L);
}
@ExecFunction(name = "hours_diff")
public static Expression hoursDiff(DateTimeV2Literal t1, DateV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.HOURS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L / 60L);
}
@ExecFunction(name = "hours_diff")
public static Expression hoursDiff(DateV2Literal t1, DateTimeV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.HOURS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L / 60L);
}
@ExecFunction(name = "hours_diff")
public static Expression hoursDiff(DateV2Literal t1, DateV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.HOURS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return new
BigIntLiteral(DateTimeV2Literal.datetimeDiffInSecondsRoundToZeroByMicroSecond(t1,
t2) / 60L / 60L);
}
@ExecFunction(name = "days_diff")
public static Expression daysDiff(DateTimeV2Literal t1, DateTimeV2Literal
t2) {
- return new BigIntLiteral(ChronoUnit.DAYS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return daysDiff((DateLiteral) t1, t2);
+ }
+
+ @ExecFunction(name = "days_diff")
+ public static Expression daysDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return daysDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "days_diff")
public static Expression daysDiff(DateTimeV2Literal t1, DateV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.DAYS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return daysDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "days_diff")
public static Expression daysDiff(DateV2Literal t1, DateTimeV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.DAYS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return daysDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "days_diff")
public static Expression daysDiff(DateV2Literal t1, DateV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.DAYS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return daysDiff((DateLiteral) t1, t2);
+ }
+
+ private static Expression daysDiff(DateLiteral t1, DateLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.dateDiffInDaysRoundToZeroByTime(t1, t2));
}
@ExecFunction(name = "weeks_diff")
public static Expression weeksDiff(DateTimeV2Literal t1, DateTimeV2Literal
t2) {
- return new BigIntLiteral(ChronoUnit.WEEKS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return weeksDiff((DateLiteral) t1, t2);
+ }
+
+ @ExecFunction(name = "weeks_diff")
+ public static Expression weeksDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return weeksDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "weeks_diff")
public static Expression weeksDiff(DateTimeV2Literal t1, DateV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.WEEKS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return weeksDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "weeks_diff")
public static Expression weeksDiff(DateV2Literal t1, DateTimeV2Literal t2)
{
- return new BigIntLiteral(ChronoUnit.WEEKS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return weeksDiff((DateLiteral) t1, t2);
}
@ExecFunction(name = "weeks_diff")
public static Expression weeksDiff(DateV2Literal t1, DateV2Literal t2) {
- return new BigIntLiteral(ChronoUnit.WEEKS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ return weeksDiff((DateLiteral) t1, t2);
+ }
+
+ private static Expression weeksDiff(DateLiteral t1, DateLiteral t2) {
+ return new
BigIntLiteral(DateTimeV2Literal.dateDiffInDaysRoundToZeroByTime(t1, t2) / 7L);
}
@ExecFunction(name = "months_diff")
@@ -1222,6 +1267,11 @@ public class DateTimeExtractAndTransform {
return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
}
+ @ExecFunction(name = "months_diff")
+ public static Expression monthsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ }
+
@ExecFunction(name = "months_diff")
public static Expression monthsDiff(DateTimeV2Literal t1, DateV2Literal
t2) {
return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
@@ -1242,6 +1292,11 @@ public class DateTimeExtractAndTransform {
return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
}
+ @ExecFunction(name = "quarters_diff")
+ public static Expression quartersDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
+ }
+
@ExecFunction(name = "quarters_diff")
public static Expression quartersDiff(DateV2Literal t1, DateV2Literal t2) {
return new
BigIntLiteral(ChronoUnit.MONTHS.between(t2.toJavaDateType(),
t1.toJavaDateType()) / 3);
@@ -1252,6 +1307,11 @@ public class DateTimeExtractAndTransform {
return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
}
+ @ExecFunction(name = "years_diff")
+ public static Expression yearsDiff(TimestampTzLiteral t1,
TimestampTzLiteral t2) {
+ return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
+ }
+
@ExecFunction(name = "years_diff")
public static Expression yearsDiff(DateTimeV2Literal t1, DateV2Literal t2)
{
return new BigIntLiteral(ChronoUnit.YEARS.between(t2.toJavaDateType(),
t1.toJavaDateType()));
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
index 019e8d8a40f..4a8ac991168 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
@@ -320,6 +320,10 @@ public class DateTimeLiteral extends DateLiteral {
return (year * 10000 + month * 100 + day) * 1000000L + hour * 10000 +
minute * 100 + second;
}
+ public long timePartToMicroSecond() {
+ return ((hour * 60L + minute) * 60L + second) * 1000L * 1000L +
microSecond;
+ }
+
@Override
public double getDouble() {
return (double) getValue();
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 08f2c982b43..93f221d4d99 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
@@ -64,6 +64,36 @@ public class DateTimeV2Literal extends DateTimeLiteral {
roundMicroSecond(dateType.getScale());
}
+ /** Date difference rounded toward zero by time part. */
+ public static long dateDiffInDaysRoundToZeroByTime(DateLiteral lhs,
DateLiteral rhs) {
+ long days = DateV2Literal.dateDiffInDays(lhs, rhs);
+ long microSecondDiff = timePartToMicroSecond(lhs) -
timePartToMicroSecond(rhs);
+ if (days > 0 && microSecondDiff < 0) {
+ days--;
+ } else if (days < 0 && microSecondDiff > 0) {
+ days++;
+ }
+ return days;
+ }
+
+ /** Datetime difference in seconds rounded toward zero by microsecond
part. */
+ public static long
datetimeDiffInSecondsRoundToZeroByMicroSecond(DateLiteral lhs, DateLiteral rhs)
{
+ return datetimeDiffInMicroSeconds(lhs, rhs) / 1000L / 1000L;
+ }
+
+ /** Datetime difference in microseconds. */
+ public static long datetimeDiffInMicroSeconds(DateLiteral lhs, DateLiteral
rhs) {
+ return DateV2Literal.dateDiffInDays(lhs, rhs) * 24L * 60L * 60L *
1000L * 1000L
+ + timePartToMicroSecond(lhs) - timePartToMicroSecond(rhs);
+ }
+
+ private static long timePartToMicroSecond(DateLiteral date) {
+ if (date instanceof DateTimeLiteral) {
+ return ((DateTimeLiteral) date).timePartToMicroSecond();
+ }
+ return 0;
+ }
+
@Override
public DateTimeV2Type getDataType() throws UnboundException {
return (DateTimeV2Type) super.getDataType();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
index 459131ddc11..1462397bb08 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
@@ -45,6 +45,10 @@ public class DateV2Literal extends DateLiteral {
super(DateV2Type.INSTANCE, year, month, day);
}
+ public static long dateDiffInDays(DateLiteral lhs, DateLiteral rhs) {
+ return lhs.getTotalDays() - rhs.getTotalDays();
+ }
+
@Override
public org.apache.doris.analysis.DateLiteral toLegacyLiteral() {
return legacyLiteral.get();
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
index 5dcd65d69c6..53ccdb2d403 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/ExpressionUtils.java
@@ -71,7 +71,9 @@ import
org.apache.doris.nereids.trees.expressions.functions.generator.PosExplode
import org.apache.doris.nereids.trees.expressions.functions.generator.Unnest;
import org.apache.doris.nereids.trees.expressions.functions.scalar.If;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Length;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NullIf;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.Nullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Nvl;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
import org.apache.doris.nereids.trees.expressions.literal.ComparableLiteral;
@@ -1263,6 +1265,13 @@ public class ExpressionUtils {
return Optional.empty();
}
+ public static Optional<Literal> getLiteralAfterUnwrapNullable(Expression
expr) {
+ while (expr instanceof Nullable || expr instanceof NonNullable) {
+ expr = expr.child(0);
+ }
+ return expr instanceof Literal ? Optional.of((Literal) expr) :
Optional.empty();
+ }
+
/** analyze the unbound expression and fold it to literal */
public static Literal analyzeAndFoldToLiteral(ConnectContext ctx,
Expression expression) throws UserException {
Scope scope = new Scope(new ArrayList<>());
diff --git a/regression-test/data/nereids_function_p0/scalar_function/D.out
b/regression-test/data/nereids_function_p0/scalar_function/D.out
index cc87f9917e2..6c1ee1b0f88 100644
--- a/regression-test/data/nereids_function_p0/scalar_function/D.out
+++ b/regression-test/data/nereids_function_p0/scalar_function/D.out
@@ -492,6 +492,18 @@
0
0
+-- !sql_nullable_date_diff_utc --
+738519 738519 738519 738519 105502 24263 2021 17724468
1063468103 63808086214 63808086214000 63808086214000000
838:59:59.000000
+
+-- !sql_nullable_date_diff_shanghai --
+738519 738519 738519 738519 105502 24263 2021 17724468
1063468103 63808086214 63808086214000 63808086214000000
838:59:59.000000
+
+-- !sql_timestamptz_date_diff_utc --
+0 0 0 0 0 0 0 0 0 0
0 0 1 1 60 60 3600 3600 3600000 3600000
3600000000 3600000000
+
+-- !sql_timestamptz_date_diff_shanghai --
+0 0 0 0 0 0 0 0 0 0
0 0 1 1 60 60 3600 3600 3600000 3600000
3600000000 3600000000
+
-- !sql_datev2_DateTimeV2 --
\N
2012-03-01
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
index fb4025cfccf..009f101e279 100644
--- a/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
+++ b/regression-test/suites/nereids_function_p0/scalar_function/D.groovy
@@ -55,6 +55,119 @@ suite("nereids_scalar_fn_D") {
qt_sql_datediff_DateTimeV2_DateTime_notnull "select datediff(kdtmv2s1,
kdtm) from fn_test_not_nullable order by kdtmv2s1, kdtm"
qt_sql_datediff_DateV2_DateTime "select datediff(kdtv2, kdtm) from
fn_test order by kdtv2, kdtm"
qt_sql_datediff_DateV2_DateTime_notnull "select datediff(kdtv2, kdtm)
from fn_test_not_nullable order by kdtv2, kdtm"
+ def timeZoneOrigValue = sql("select @@time_zone")
+ try {
+ sql "set time_zone = '+00:00'"
+ qt_sql_nullable_date_diff_utc """
+ select
+ datediff('2021-12-31 12:23:34', '0000-01-01
00:00:00'),
+ datediff('2021-12-31 12:23:34',
nullable('0000-01-01 00:00:00')),
+ datediff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ days_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ weeks_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ months_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ years_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ hours_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ minutes_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ seconds_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ milliseconds_diff(nullable('2021-12-31
12:23:34'), nullable('0000-01-01 00:00:00')),
+ microseconds_diff(nullable('2021-12-31
12:23:34'), nullable('0000-01-01 00:00:00')),
+ timediff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00'))
+ """
+ sql "set time_zone = '+08:00'"
+ qt_sql_nullable_date_diff_shanghai """
+ select
+ datediff('2021-12-31 12:23:34', '0000-01-01
00:00:00'),
+ datediff('2021-12-31 12:23:34',
nullable('0000-01-01 00:00:00')),
+ datediff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ days_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ weeks_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ months_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ years_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ hours_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ minutes_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ seconds_diff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00')),
+ milliseconds_diff(nullable('2021-12-31
12:23:34'), nullable('0000-01-01 00:00:00')),
+ microseconds_diff(nullable('2021-12-31
12:23:34'), nullable('0000-01-01 00:00:00')),
+ timediff(nullable('2021-12-31 12:23:34'),
nullable('0000-01-01 00:00:00'))
+ """
+ sql "set time_zone = '+00:00'"
+ qt_sql_timestamptz_date_diff_utc """
+ select
+ datediff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ datediff(nullable('2021-12-31 00:30:00+08:00'),
nullable('2021-12-30 23:30:00+08:00')),
+ days_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ days_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ weeks_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ weeks_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ months_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ months_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ quarters_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ quarters_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ years_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ years_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ hours_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ hours_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ minutes_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ minutes_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ seconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ seconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ milliseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ milliseconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ microseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ microseconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00'))
+ """
+ sql "set time_zone = '+08:00'"
+ qt_sql_timestamptz_date_diff_shanghai """
+ select
+ datediff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ datediff(nullable('2021-12-31 00:30:00+08:00'),
nullable('2021-12-30 23:30:00+08:00')),
+ days_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ days_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ weeks_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ weeks_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ months_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ months_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ quarters_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ quarters_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ years_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ years_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ hours_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ hours_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ minutes_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ minutes_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ seconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ seconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ milliseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ milliseconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00')),
+ microseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ microseconds_diff(nullable('2021-12-31
00:30:00+08:00'), nullable('2021-12-30 23:30:00+08:00'))
+ """
+ testFoldConst """
+ select
+ datediff('2021-12-31 12:23:34', '0000-01-01
00:00:00'),
+ days_diff('2021-12-31 12:23:34', '0000-01-01
00:00:00')
+ """
+ testFoldConst """
+ select month_floor('2023-07-13 22:28:18.4567', 5)
+ """
+ testFoldConst """
+ select
+ datediff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ days_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ weeks_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ months_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ quarters_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ years_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ hours_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ minutes_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ seconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ milliseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00'),
+ microseconds_diff('2021-12-31 00:30:00+08:00',
'2021-12-30 23:30:00+08:00')
+ """
+ } finally {
+ sql "set time_zone = '${timeZoneOrigValue[0][0]}'"
+ }
qt_sql_datev2_DateTimeV2 "select datev2(kdtmv2s1) from fn_test order by
kdtmv2s1"
qt_sql_datev2_DateTimeV2_notnull "select datev2(kdtmv2s1) from
fn_test_not_nullable order by kdtmv2s1"
qt_sql_day_ceil_DateTime "select day_ceil(kdtm) from fn_test order by
kdtm"
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
