[Bug] [INVALID_ARGUMENT]Create Expr failed because [E33] Invalid value: 2024-03-11 24:00:00 for type DateTime

[ziyanTOP]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.0.5-rc02
2.1.0-rc11

What's Wrong?

With nereids planner enabled, date() function will be rewritten by the DateFunctionRewrite class.

But when a datetimev1 column is processed by date() function then compares to a date literal, typically equal to and less than equal, it goes wrong due to padding the date literal with time '24:00:00', this datetime literal cannot pass range check when it comes to BE, because hour in a datetime literal must not be greater than 23

Code location:

org.apache.doris.nereids.trees.expressions.literal.DateLiteral

 public DateTimeLiteral toEndOfTheDay() {
 return new DateTimeLiteral(year, month, day, 24, 0, 0);
 }

be/src/vec/runtime/vdatetime_value.cpp

bool VecDateTimeValue::check_range(uint32_t year, uint32_t month, uint32_t day, uint32_t hour,
 uint32_t minute, uint32_t second, uint16_t type) {
 bool time = hour > (type == TIME_TIME ? TIME_MAX_HOUR : 23) || minute > 59 || second > 59;
 if (type == TIME_TIME) {
 return time;
 } else {
 return time || check_date(year, month, day);
 }
}

What You Expected?

datetimev1 column works well in nereids planner

How to Reproduce?

Create a table with a datetimev1 column, use date() function to process it, then compare to a date literal.
Only equal to and less than equal can reproduce the bug.

-- add "disable_datev1 = false" to fe.conf first
create table table_for_bug_test (
 id int not null comment 'ID',
 datetimev1_column datetimev1 null comment '日期时间v1列'
) ENGINE=OLAP
DUPLICATE KEY (`id`)
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES (
 "replication_num" = "1"
)
;

select * from table_for_bug_test where date(datetimev1_column) = '2024-03-08';

select * from table_for_bug_test where date(datetimev1_column) <= '2024-03-08';

Anything Else?

if datetimev1 and decimalv2 are gonna be deleted in the future, there should be a statement or tool for us to migrate those outdated column type, or we can only execute ALTER TABLE MODIFY COLUMN one by one.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct