Bug #79917 Func COALESCE( date, timestamp) and datetime are not working consistently

Submitted: 11 Jan 2016 12:41 Modified: 13 Jan 2016 8:09
Reporter: Su Dylan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.8, 5.5.48, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[11 Jan 2016 12:41] Su Dylan
Description:
Output:
=====
mysql> select cast( coalesce( date '2015-01-01', timestamp '2016-01-01 01:01:01') as decimal ), cast( cast( date '2015-01-01' as datetime) as decimal );
+----------------------------------------------------------------------------------+---------------------------------------------------------+
| cast( coalesce( date '2015-01-01', timestamp '2016-01-01 01:01:01') as decimal ) | cast( cast( date '2015-01-01' as datetime) as decimal ) |
+----------------------------------------------------------------------------------+---------------------------------------------------------+
|                                                                         20150101 |                                              9999999999 |
+----------------------------------------------------------------------------------+---------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
=====
"cast( coalesce( date '2015-01-01', timestamp '2016-01-01 01:01:01') as decimal )" is expected to return 9999999999.

How to repeat:
select cast( coalesce( date '2015-01-01', timestamp '2016-01-01 01:01:01') as decimal ), cast( cast( date '2015-01-01' as datetime) as decimal );

Suggested fix:
"cast( coalesce( date '2015-01-01', timestamp '2016-01-01 01:01:01') as decimal )" returns 9999999999.
[13 Jan 2016 8:09] MySQL Verification Team
Hello Su Dylan,

Thank you for the report and test case.
Observed that 5.6.28/5.7.10 are affected.

Thanks,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.