This is an automated email from the ASF dual-hosted git repository. morrysnow pushed a commit to branch opt_date_ceil_floor in repository https://gitbox.apache.org/repos/asf/doris.git
commit 91c91d6edd0f56ca1ac8769f9e822cd67ce88c15 Author: morrySnow <[email protected]> AuthorDate: Tue Aug 5 11:41:31 2025 +0800 [opt](Nereids) return narrow type for date_ceil(floor) if the first argument is date type, and unit is not datetime unit, then we return date type other than datetime type --- .../rules/analysis/DatetimeFunctionBinder.java | 35 ++++----- .../functions/executable/TimeRoundSeries.java | 13 ++-- .../trees/expressions/literal/DateV2Literal.java | 4 ++ .../datetime_functions/test_date_floor_ceil.out | Bin 886 -> 2043 bytes .../datetime_functions/test_date_floor_ceil.groovy | 78 ++++++++++++--------- 5 files changed, 74 insertions(+), 56 deletions(-) diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java index 69add2f9caa..6e31d0cdf28 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/DatetimeFunctionBinder.java @@ -68,6 +68,7 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsAdd; import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsDiff; import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsSub; import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal; +import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal; import org.apache.doris.nereids.trees.expressions.literal.Interval; import org.apache.doris.nereids.trees.expressions.literal.Interval.TimeUnit; @@ -324,22 +325,23 @@ public class DatetimeFunctionBinder { } private Expression processDateFloor(TimeUnit unit, Expression timeStamp, Expression amount) { - DateTimeV2Literal e = DateTimeV2Literal.USE_IN_FLOOR_CEIL; + DateTimeV2Literal datetimeBase = DateTimeV2Literal.USE_IN_FLOOR_CEIL; + DateV2Literal dateBase = DateV2Literal.USE_IN_FLOOR_CEIL; switch (unit) { case YEAR: - return new YearFloor(timeStamp, amount, e); + return new YearFloor(timeStamp, amount, dateBase); case MONTH: - return new MonthFloor(timeStamp, amount, e); + return new MonthFloor(timeStamp, amount, dateBase); case WEEK: - return new WeekFloor(timeStamp, amount, e); + return new WeekFloor(timeStamp, amount, dateBase); case DAY: - return new DayFloor(timeStamp, amount, e); + return new DayFloor(timeStamp, amount, dateBase); case HOUR: - return new HourFloor(timeStamp, amount, e); + return new HourFloor(timeStamp, amount, datetimeBase); case MINUTE: - return new MinuteFloor(timeStamp, amount, e); + return new MinuteFloor(timeStamp, amount, datetimeBase); case SECOND: - return new SecondFloor(timeStamp, amount, e); + return new SecondFloor(timeStamp, amount, datetimeBase); default: throw new AnalysisException("Unsupported time stamp floor time unit: " + unit + ", supported time unit: YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND"); @@ -347,22 +349,23 @@ public class DatetimeFunctionBinder { } private Expression processDateCeil(TimeUnit unit, Expression timeStamp, Expression amount) { - DateTimeV2Literal e = DateTimeV2Literal.USE_IN_FLOOR_CEIL; + DateTimeV2Literal datetimeBase = DateTimeV2Literal.USE_IN_FLOOR_CEIL; + DateV2Literal dateBase = DateV2Literal.USE_IN_FLOOR_CEIL; switch (unit) { case YEAR: - return new YearCeil(timeStamp, amount, e); + return new YearCeil(timeStamp, amount, dateBase); case MONTH: - return new MonthCeil(timeStamp, amount, e); + return new MonthCeil(timeStamp, amount, dateBase); case WEEK: - return new WeekCeil(timeStamp, amount, e); + return new WeekCeil(timeStamp, amount, dateBase); case DAY: - return new DayCeil(timeStamp, amount, e); + return new DayCeil(timeStamp, amount, dateBase); case HOUR: - return new HourCeil(timeStamp, amount, e); + return new HourCeil(timeStamp, amount, datetimeBase); case MINUTE: - return new MinuteCeil(timeStamp, amount, e); + return new MinuteCeil(timeStamp, amount, datetimeBase); case SECOND: - return new SecondCeil(timeStamp, amount, e); + return new SecondCeil(timeStamp, amount, datetimeBase); default: throw new AnalysisException("Unsupported time stamp ceil time unit: " + unit + ", supported time unit: YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND"); 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 8dd933a7a1a..eba711eb24d 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 @@ -25,6 +25,7 @@ import org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal; import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal; import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral; +import java.time.Duration; import java.time.LocalDateTime; /** @@ -67,29 +68,25 @@ public class TimeRoundSeries { break; } case DAY: { - diff = dt.getTotalDays() - start.getTotalDays(); + diff = Duration.between(origin, date).toDays(); long part2 = dt.getHour() * 3600 + dt.getMinute() * 60 + dt.getSecond(); long part1 = start.getHour() * 3600 + start.getMinute() * 60 + start.getSecond(); trivialPart = part2 - part1; break; } case HOUR: { - diff = (dt.getTotalDays() - start.getTotalDays()) * 24 + (dt.getHour() - start.getHour()); + diff = Duration.between(origin, date).toHours(); trivialPart = (dt.getMinute() * 60 + dt.getSecond()) - (start.getMinute() * 60 + start.getSecond()); break; } case MINUTE: { - diff = (dt.getTotalDays() - start.getTotalDays()) * 24 * 60 + (dt.getHour() - start.getHour()) * 60 - + (dt.getMinute() - start.getMinute()); + diff = Duration.between(origin, date).toMinutes(); trivialPart = dt.getSecond() - start.getSecond(); break; } case SECOND: { - diff = (dt.getTotalDays() - start.getTotalDays()) * 24 * 60 * 60 - + (dt.getHour() - start.getHour()) * 60 * 60 - + (dt.getMinute() - start.getMinute()) * 60 - + (dt.getSecond() - start.getSecond()); + diff = Duration.between(origin, date).toMinutes() * 60 + (dt.getSecond() - start.getSecond()); trivialPart = dt.getMicroSecond() - start.getMicroSecond(); break; } 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..5c391f4fb77 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 @@ -33,6 +33,10 @@ import java.util.function.Supplier; * date v2 literal for nereids */ public class DateV2Literal extends DateLiteral { + + public static final DateV2Literal USE_IN_FLOOR_CEIL + = new DateV2Literal(0001L, 01L, 01L); + private final Supplier<org.apache.doris.analysis.DateLiteral> legacyLiteral = Suppliers.memoize(() -> new org.apache.doris.analysis.DateLiteral(year, month, day, Type.DATEV2) ); diff --git a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out index 22379a2b71b..9ef3d31a605 100644 Binary files a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out and b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out differ diff --git a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy index ffcf9e5eb63..4038b854bb4 100644 --- a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy +++ b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy @@ -20,43 +20,57 @@ suite("test_date_floor_ceil") { sql "set enable_fallback_to_original_planner=false;" sql "set enable_fold_constant_by_be=false;" - qt_sql1 """select date_floor("2023-07-14 10:51:11",interval 5 second); """ - qt_sql2 """select date_floor("2023-07-14 10:51:00",interval 5 minute); """ - qt_sql3 """select date_floor("2023-07-14 10:51:00",interval 5 hour); """ - qt_sql4 """select date_floor("2023-07-14 10:51:00",interval 5 day); """ - qt_sql5 """select date_floor("2023-07-14 10:51:00",interval 5 month); """ - qt_sql6 """select date_floor("2023-07-14 10:51:00",interval 5 year); """ + qt_date_floor_datetime_1 """select date_floor("2023-07-14 10:51:11",interval 5 second); """ + qt_date_floor_datetime_2 """select date_floor("2023-07-14 10:51:00",interval 5 minute); """ + qt_date_floor_datetime_3 """select date_floor("2023-07-14 10:51:00",interval 5 hour); """ + qt_date_floor_datetime_4 """select date_floor("2023-07-14 10:51:00",interval 5 day); """ + qt_date_floor_datetime_5 """select date_floor("2023-07-14 10:51:00",interval 5 month); """ + qt_date_floor_datetime_6 """select date_floor("2023-07-14 10:51:00",interval 5 year); """ - qt_sql7 """select date_ceil("2023-07-14 10:51:11",interval 5 second); """ - qt_sql8 """select date_ceil("2023-07-14 10:51:00",interval 5 minute); """ - qt_sql9 """select date_ceil("2023-07-14 10:51:00",interval 5 hour); """ - qt_sql10 """select date_ceil("2023-07-14 10:51:00",interval 5 day); """ - qt_sql11 """select date_ceil("2023-07-14 10:51:00",interval 5 month); """ - qt_sql12 """select date_ceil("2023-07-14 10:51:00",interval 5 year); """ + qt_date_ceil_datetime_1 """select date_ceil("2023-07-14 10:51:11",interval 5 second); """ + qt_date_ceil_datetime_2 """select date_ceil("2023-07-14 10:51:00",interval 5 minute); """ + qt_date_ceil_datetime_3 """select date_ceil("2023-07-14 10:51:00",interval 5 hour); """ + qt_date_ceil_datetime_4 """select date_ceil("2023-07-14 10:51:00",interval 5 day); """ + qt_date_ceil_datetime_5 """select date_ceil("2023-07-14 10:51:00",interval 5 month); """ + qt_date_ceil_datetime_6 """select date_ceil("2023-07-14 10:51:00",interval 5 year); """ + + qt_date_floor_date_1 """select date_floor("2023-07-14 10:51:11",interval 5 second); """ + qt_date_floor_date_2 """select date_floor("2023-07-14 10:51:00",interval 5 minute); """ + qt_date_floor_date_3 """select date_floor("2023-07-14 10:51:00",interval 5 hour); """ + qt_date_floor_date_4 """select date_floor("2023-07-14 10:51:00",interval 5 day); """ + qt_date_floor_date_5 """select date_floor("2023-07-14 10:51:00",interval 5 month); """ + qt_date_floor_date_6 """select date_floor("2023-07-14 10:51:00",interval 5 year); """ + + qt_date_ceil_date_1 """select date_ceil("2023-07-14 10:51:11",interval 5 second); """ + qt_date_ceil_date_2 """select date_ceil("2023-07-14 10:51:00",interval 5 minute); """ + qt_date_ceil_date_3 """select date_ceil("2023-07-14 10:51:00",interval 5 hour); """ + qt_date_ceil_date_4 """select date_ceil("2023-07-14 10:51:00",interval 5 day); """ + qt_date_ceil_date_5 """select date_ceil("2023-07-14 10:51:00",interval 5 month); """ + qt_date_ceil_date_6 """select date_ceil("2023-07-14 10:51:00",interval 5 year); """ // test hour_floor - qt_sql1 """select hour_floor("2023-07-14 10:51:00", 5, "0001-01-01 00:00:00");""" - qt_sql2 """select hour_floor("2023-07-14 10:51:00", 5, "1970-01-01 00:00:00");""" - qt_sql3 """select hour_floor("2023-07-14 10:51:00", 5);""" + qt_hour_floor_fold_by_fe_1 """select hour_floor("2023-07-14 10:51:00", 5, "0001-01-01 00:00:00");""" + qt_hour_floor_fold_by_fe_2 """select hour_floor("2023-07-14 10:51:00", 5, "1970-01-01 00:00:00");""" + qt_hour_floor_fold_by_fe_3 """select hour_floor("2023-07-14 10:51:00", 5);""" sql """set debug_skip_fold_constant = true""" - qt_sql1 """select hour_floor("2023-07-14 10:51:00", 5, "0001-01-01 00:00:00");""" - qt_sql2 """select hour_floor("2023-07-14 10:51:00", 5, "1970-01-01 00:00:00");""" - qt_sql3 """select hour_floor("2023-07-14 10:51:00", 5);""" + qt_hour_floor_not_fold_1 """select hour_floor("2023-07-14 10:51:00", 5, "0001-01-01 00:00:00");""" + qt_hour_floor_not_fold_2 """select hour_floor("2023-07-14 10:51:00", 5, "1970-01-01 00:00:00");""" + qt_hour_floor_not_fold_3 """select hour_floor("2023-07-14 10:51:00", 5);""" sql """set debug_skip_fold_constant = false""" - qt_x1 """ select date_floor('9999-12-31 23:59:59.999999', interval 5 minute); """ - qt_x2 """ select date_floor('9999-12-31 23:59:59.999999', interval 33333 year); """ - qt_x3 """ select date_floor('9999-12-31 23:59:59.999999', interval -10 year); """ - qt_x4 """ select date_floor('1923-12-31 23:59:59.999999', interval -10 year); """ - // qt_x5 """ select date_floor('0000-01-01 00:00:00', interval 7 minute); """//wrong - qt_x6 """ select date_floor('0001-01-01 00:00:00', interval 7 minute); """ - qt_x7 """ select date_ceil('9999-12-31 23:59:59.999999', interval 5 minute); """ - qt_x8 """ select date_ceil('9999-12-31 23:59:59.999999', interval 1 second); """ - qt_x9 """ select date_ceil('9999-12-31 23:59:59.999999', interval 100 year); """ - // qt_x10 """ select date_ceil('0000-01-01 23:59:59.999999', interval 7 month); """//wrong - qt_x11 """ select date_ceil('0001-01-01 23:59:59.999999', interval 7 month); """ - qt_x12 """ select date_ceil('0001-09-01 23:59:59.999999', interval -7 month); """ - qt_x13 """ select date_ceil('0002-02-01 23:59:59.999999', interval -7 month); """ - qt_x14 """ select date_ceil('9999-12-31 23:54:59.999999', interval 5 minute); """ + qt_date_floor_corner_case_1 """ select date_floor('9999-12-31 23:59:59.999999', interval 5 minute); """ + qt_date_floor_corner_case_2 """ select date_floor('9999-12-31 23:59:59.999999', interval 33333 year); """ + qt_date_floor_corner_case_3 """ select date_floor('9999-12-31 23:59:59.999999', interval -10 year); """ + qt_date_floor_corner_case_4 """ select date_floor('1923-12-31 23:59:59.999999', interval -10 year); """ + qt_date_floor_corner_case_5 """ select date_floor('0000-01-01 00:00:00', interval 7 minute); """ // be's result is wrong + qt_date_floor_corner_case_6 """ select date_floor('0001-01-01 00:00:00', interval 7 minute); """ + qt_date_ceil_corner_case_1 """ select date_ceil('9999-12-31 23:59:59.999999', interval 5 minute); """ + qt_date_ceil_corner_case_2 """ select date_ceil('9999-12-31 23:59:59.999999', interval 1 second); """ + qt_date_ceil_corner_case_3 """ select date_ceil('9999-12-31 23:59:59.999999', interval 100 year); """ + qt_date_ceil_corner_case_4 """ select date_ceil('0000-01-01 23:59:59.999999', interval 7 month); """ // be's result is wrong + qt_date_ceil_corner_case_5 """ select date_ceil('0001-01-01 23:59:59.999999', interval 7 month); """ + qt_date_ceil_corner_case_6 """ select date_ceil('0001-09-01 23:59:59.999999', interval -7 month); """ + qt_date_ceil_corner_case_7 """ select date_ceil('0002-02-01 23:59:59.999999', interval -7 month); """ + qt_date_ceil_corner_case_8 """ select date_ceil('9999-12-31 23:54:59.999999', interval 5 minute); """ } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
