[MDEV-5354] Inserting a huge time value into a datetime column produces a wrong result. Created: 2013-11-27  Updated: 2022-12-09  Resolved: 2022-12-09

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 5.3.12, 5.5.34, 10.0.6
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Cannot Reproduce Votes: 0
Labels: None


 Description    

mysql> create table t1 (a datetime);
 
Query OK, 0 rows affected (0.07 sec)
 
 
 
mysql> insert into t1 values (time('800:20:30'));
 
Query OK, 1 row affected (0.03 sec)
 
 
 
mysql> select * from t1;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 0000-00-08 00:20:30 |
 
+---------------------+
 
1 row in set (0.00 sec)

The above result looks wrong.

Explicit conversion from time to datetime seems to return a much better result:

mysql> select cast(time('800:20:30') as datetime);
 
+-------------------------------------+
 
| cast(time('800:20:30') as datetime) |
 
+-------------------------------------+
 
| 0000-01-02 08:20:30                 |
 
+-------------------------------------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Alice Sherepa [ 2022-12-09 ]

current 10.3-10.11 return correct results

MariaDB [test]> create table t1 (a datetime);
 
Query OK, 0 rows affected (0,047 sec)
 
 
 
MariaDB [test]>  insert into t1 values (time('800:20:30'));
 
Query OK, 1 row affected (0,012 sec)
 
 
 
MariaDB [test]> select * from t1;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 2023-01-11 08:20:30 |
 
+---------------------+
 
1 row in set (0,000 sec)

Generated at Thu Feb 08 07:03:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.