Description:
When I test data of type TIME, i found that time type and datatime type behave differently when truncate occurs.
How to repeat:
mysql> create table t1 (a int , b time, c datetime, d date);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t1 values (1, now(),now(),now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t1 values (2, now(),now(),now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into t1 values (3, 0,0,0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+----------+---------------------+------------+
| a | b | c | d |
+------+----------+---------------------+------------+
| 1 | 21:01:38 | 2022-02-28 21:01:38 | 2022-02-28 |
| 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 |
| 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 |
+------+----------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> select * from t1 where b in ('21:01:41aa',11,'a');
+------+----------+---------------------+------------+
| a | b | c | d |
+------+----------+---------------------+------------+
| 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 |
+------+----------+---------------------+------------+
1 row in set, 5 warnings (0.00 sec)
mysql> select * from t1 where c in ('2022-02-28 21:01:41aa',11,'a');
+------+----------+---------------------+------------+
| a | b | c | d |
+------+----------+---------------------+------------+
| 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 |
| 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 |
+------+----------+---------------------+------------+
2 rows in set, 5 warnings (0.00 sec)
mysql> select * from t1 where d in ('2022-02-28aa',11,'a');
+------+----------+---------------------+------------+
| a | b | c | d |
+------+----------+---------------------+------------+
| 1 | 21:01:38 | 2022-02-28 21:01:38 | 2022-02-28 |
| 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 |
| 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 |
+------+----------+---------------------+------------+
3 rows in set, 4 warnings (0.00 sec)
Suggested fix:
We can find that the datetime type and date type have been truncated to 0 , but the time type did not.
By reading the code, Date type and Datetime type been truncated to 0, but time type been truncated to -1:
if (str) {
¦ MYSQL_TIME l_time;
¦ if (str_to_time_with_warn(str, &l_time)) {
¦ ¦ *is_null = true;
¦ ¦ return ~(ulonglong)0; --> return -1 here
¦ }
¦ value = TIME_to_longlong_datetime_packed(l_time);
}
Why the time type has a special truncate value? Is this a bug?
Thanks for any advice!
Description: When I test data of type TIME, i found that time type and datatime type behave differently when truncate occurs. How to repeat: mysql> create table t1 (a int , b time, c datetime, d date); Query OK, 0 rows affected (0.02 sec) mysql> insert into t1 values (1, now(),now(),now()); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> insert into t1 values (2, now(),now(),now()); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> insert into t1 values (3, 0,0,0); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +------+----------+---------------------+------------+ | a | b | c | d | +------+----------+---------------------+------------+ | 1 | 21:01:38 | 2022-02-28 21:01:38 | 2022-02-28 | | 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 | | 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 | +------+----------+---------------------+------------+ 3 rows in set (0.00 sec) mysql> select * from t1 where b in ('21:01:41aa',11,'a'); +------+----------+---------------------+------------+ | a | b | c | d | +------+----------+---------------------+------------+ | 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 | +------+----------+---------------------+------------+ 1 row in set, 5 warnings (0.00 sec) mysql> select * from t1 where c in ('2022-02-28 21:01:41aa',11,'a'); +------+----------+---------------------+------------+ | a | b | c | d | +------+----------+---------------------+------------+ | 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 | | 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 | +------+----------+---------------------+------------+ 2 rows in set, 5 warnings (0.00 sec) mysql> select * from t1 where d in ('2022-02-28aa',11,'a'); +------+----------+---------------------+------------+ | a | b | c | d | +------+----------+---------------------+------------+ | 1 | 21:01:38 | 2022-02-28 21:01:38 | 2022-02-28 | | 2 | 21:01:41 | 2022-02-28 21:01:41 | 2022-02-28 | | 3 | 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 | +------+----------+---------------------+------------+ 3 rows in set, 4 warnings (0.00 sec) Suggested fix: We can find that the datetime type and date type have been truncated to 0 , but the time type did not. By reading the code, Date type and Datetime type been truncated to 0, but time type been truncated to -1: if (str) { ¦ MYSQL_TIME l_time; ¦ if (str_to_time_with_warn(str, &l_time)) { ¦ ¦ *is_null = true; ¦ ¦ return ~(ulonglong)0; --> return -1 here ¦ } ¦ value = TIME_to_longlong_datetime_packed(l_time); } Why the time type has a special truncate value? Is this a bug? Thanks for any advice!