Description:
Hello MySQL team:
I found a case that MySQL servers does not detect some incorrect time.
How to repeat:
Can be repeated in MySQL 9.0.1
create table t1(c1 datetime);
explain select * from c1 where c1 = 271;
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '271' for column 'c1' at row 1 |
| Note | 1003 | /* select#1 */ select `sqlengine`.`t1`.`c1` AS `c1` from `sqlengine`.`t1` where (`sqlengine`.`t1`.`c1` = 271) |
+---------+------+---------------------------------------------------------------------------------------------------------------+
from warnings, we can see that server detected that 271 is incorrect.
but if execute:
explain select * from t1 where c1 = 231;
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `sqlengine`.`t1`.`c1` AS `c1` from `sqlengine`.`t1` where (`sqlengine`.`t1`.`c1` = TIMESTAMP'2000-02-31 00:00:00') |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------+
there is no warning.
Actually, 231 was treated as TIMESTAMP'2000-02-31 00:00:00' internally.
Suggested fix:
It is about func `check_datetime_range`, it does not conside leap year and corresponding max day for every month.
Here is my fix(based on 9.0.1):
diff --git a/mysys/my_time.cc b/mysys/my_time.cc
index ca8fdd30a63..0c377d4abc8 100644
--- a/mysys/my_time.cc
+++ b/mysys/my_time.cc
@@ -244,6 +244,23 @@ bool check_time_range_quick(const MYSQL_TIME &my_time) {
return true;
}
+/**
+ Check Date range according year, month, day value.
+ @param year year value.
+ @param month month value.
+ @param day day value.
+ @retval false on success
+ @retval true on error
+*/
+static bool check_date_range(uint year, uint month, uint day) {
+ if (year > 9999U || month > 12U || month == 0) return true;
+
+ bool is_leap_year = (year % 4 == 0 && year % 100 != 0) || year % 400 == 0;
+ uint days_in_month[] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31};
+ if (is_leap_year) days_in_month[1] = 29;
+ return day > days_in_month[month - 1];
+}
+
/**
Check datetime, date, or normalized time (i.e. time without days) range.
@param my_time Datetime value.
@@ -255,7 +272,7 @@ bool check_datetime_range(const MYSQL_TIME &my_time) {
In case of MYSQL_TIMESTAMP_TIME hour value can be up to TIME_MAX_HOUR.
In case of MYSQL_TIMESTAMP_DATETIME it cannot be bigger than 23.
*/
- return my_time.year > 9999U || my_time.month > 12U || my_time.day > 31U ||
+ return check_date_range(my_time.year, my_time.month, my_time.day) ||
my_time.minute > 59U || my_time.second > 59U ||
my_time.second_part > 999999U ||
(my_time.hour >
Description: Hello MySQL team: I found a case that MySQL servers does not detect some incorrect time. How to repeat: Can be repeated in MySQL 9.0.1 create table t1(c1 datetime); explain select * from c1 where c1 = 271; mysql> show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '271' for column 'c1' at row 1 | | Note | 1003 | /* select#1 */ select `sqlengine`.`t1`.`c1` AS `c1` from `sqlengine`.`t1` where (`sqlengine`.`t1`.`c1` = 271) | +---------+------+---------------------------------------------------------------------------------------------------------------+ from warnings, we can see that server detected that 271 is incorrect. but if execute: explain select * from t1 where c1 = 231; mysql> show warnings; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `sqlengine`.`t1`.`c1` AS `c1` from `sqlengine`.`t1` where (`sqlengine`.`t1`.`c1` = TIMESTAMP'2000-02-31 00:00:00') | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------+ there is no warning. Actually, 231 was treated as TIMESTAMP'2000-02-31 00:00:00' internally. Suggested fix: It is about func `check_datetime_range`, it does not conside leap year and corresponding max day for every month. Here is my fix(based on 9.0.1): diff --git a/mysys/my_time.cc b/mysys/my_time.cc index ca8fdd30a63..0c377d4abc8 100644 --- a/mysys/my_time.cc +++ b/mysys/my_time.cc @@ -244,6 +244,23 @@ bool check_time_range_quick(const MYSQL_TIME &my_time) { return true; } +/** + Check Date range according year, month, day value. + @param year year value. + @param month month value. + @param day day value. + @retval false on success + @retval true on error +*/ +static bool check_date_range(uint year, uint month, uint day) { + if (year > 9999U || month > 12U || month == 0) return true; + + bool is_leap_year = (year % 4 == 0 && year % 100 != 0) || year % 400 == 0; + uint days_in_month[] = {31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31}; + if (is_leap_year) days_in_month[1] = 29; + return day > days_in_month[month - 1]; +} + /** Check datetime, date, or normalized time (i.e. time without days) range. @param my_time Datetime value. @@ -255,7 +272,7 @@ bool check_datetime_range(const MYSQL_TIME &my_time) { In case of MYSQL_TIMESTAMP_TIME hour value can be up to TIME_MAX_HOUR. In case of MYSQL_TIMESTAMP_DATETIME it cannot be bigger than 23. */ - return my_time.year > 9999U || my_time.month > 12U || my_time.day > 31U || + return check_date_range(my_time.year, my_time.month, my_time.day) || my_time.minute > 59U || my_time.second > 59U || my_time.second_part > 999999U || (my_time.hour >