[MDEV-5467] TO_DAYS(DATE(TIME('32 00:00:00'))) returns a wrong result Created: 2013-12-18  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 10.1, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

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


 Description    

mysql> select to_days('0000-01-01'), date(time('32 00:00:00')),to_days(date(time('32 00:00:00')));
 
+-----------------------+---------------------------+------------------------------------+
 
| to_days('0000-01-01') | date(time('32 00:00:00')) | to_days(date(time('32 00:00:00'))) |
 
+-----------------------+---------------------------+------------------------------------+
 
|                     1 | 0000-01-01                |                               NULL |
 
+-----------------------+---------------------------+------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
mysql> show warnings;
 
+---------+------+---------------------------------------+
 
| Level   | Code | Message                               |
 
+---------+------+---------------------------------------+
 
| Warning | 1292 | Incorrect datetime value: '768:00:00' |
 
+---------+------+---------------------------------------+
 
1 row in set (0.00 sec)

The expected result for the third column is 1, without warnings.

Or the other way around, date(time('32 00:00:00')) should return NULL.

 Comments   
Comment by Ian Gilfillan [ 2018-11-19 ]

This seems to have been fixed since MariaDB 10.0.9 (with 32 days ahead being the expected result, rather than 1)

select to_days('0000-01-01'), date(time('32 00:00:00')),time('32 00:00:00'),to_days(date(time('32 00:00:00')));
 
+-----------------------+---------------------------+---------------------+------------------------------------+
 
| to_days('0000-01-01') | date(time('32 00:00:00')) | time('32 00:00:00') | to_days(date(time('32 00:00:00'))) |
 
+-----------------------+---------------------------+---------------------+------------------------------------+
 
|                     1 | 2018-12-21                | 768:00:00           |                             737414 |
 
+-----------------------+---------------------------+---------------------+------------------------------------+

Comment by Elena Stepanova [ 2020-06-28 ]

10.1-10.5 return results similar to what Ian described above, but that's not what the description says to be the expected result.

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