[MDEV-5041] Inserting a TIME with hour>24 into a DATETIME column produces a wrong value Created: 2013-09-20  Updated: 2014-04-18  Resolved: 2014-04-18

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.4, 5.5.33, 5.3.13
Fix Version/s: 5.5.38, 10.0.11, 5.3.13

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


 Description    

mysql> drop table if exists t1;
 
mysql> create table t1 (a datetime);
 
mysql> insert into t1 values (time('58:00:00'));
 
mysql> select * from t1;
 
+---------------------+
 
| a                   |
 
+---------------------+
 
| 0000-00-00 58:00:00 |
 
+---------------------+
 
1 row in set (0.00 sec)

This is a wrong result.
The expected result is ' 0000-00-02 10:00:00',
which is what the explicit CAST returns:

mysql> select cast(time('58:00:00') as datetime);
 
+------------------------------------+
 
| cast(time('58:00:00') as datetime) |
 
+------------------------------------+
 
| 0000-00-02 10:00:00                |
 
+------------------------------------+
 
1 row in set (0.00 sec)

There is also a difference in 5.3 and 5.5 in how invariants of the same TIME value
are inserted into a DATE column:

mysql> drop table if exists t1; create table t1 (a date);  insert into t1 values (time('2 10:00:00')),(time('58:20:30')); select * from t1;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
Query OK, 0 rows affected (0.07 sec)
 
 
 
Query OK, 2 rows affected, 2 warnings (0.00 sec)
 
Records: 2  Duplicates: 0  Warnings: 2
 
 
 
+------------+
 
| a          |
 
+------------+
 
| 0000-00-02 |
 
| 0000-00-00 |
 
+------------+
 
2 rows in set (0.00 sec)

The expected result is to have the same values in the table in the two records.
MariaDB-10.0 returns '0000-00-00' for both records.
The expected result should probably be '0000-00-02'.

 Comments   
Comment by Alexander Barkov [ 2014-04-18 ]

Pushed into 5.3 and 5.5

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