Bug #79890 Function GREATEST(bigint, date) is not working as expected

• Submitted: 8 Jan 2016 12:10
• Modified: 8 Jan 2016 14:36
• Reporter: Su Dylan
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.8
• OS: Any
• CPU Architecture: Any

[8 Jan 2016 12:10] Su Dylan

Description:
Output:
=====
mysql> SELECT GREATEST( 106168320, date '2006-11-14' );
+------------------------------------------+
| GREATEST( 106168320, date '2006-11-14' ) |
+------------------------------------------+
| 106168320                                |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(106168320);
+--------------------------+
| from_unixtime(106168320) |
+--------------------------+
| 1973-05-14 03:12:00      |
+--------------------------+
1 row in set (0.00 sec)

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

Problem:
=====
'2006-11-14' is expected to be returned.

How to repeat:
SELECT GREATEST( 106168320, date '2006-11-14' );
select from_unixtime(106168320);

Suggested fix:
'2006-11-14' is returned.

[8 Jan 2016 14:36] MySQL Verification Team

Hello Su Dylan,

Thank you for the report.
Imho this is expected behavior i.e if the arguments comprise a mix of numbers and strings(as in case of date '2006-11-14' which returns string), they are compared as numbers.

Thanks,
Umesh

[8 Jan 2016 14:42] Peter Laursen

To compare as TIME: 

SELECT GREATEST (FROM_UNIXTIME(106168320), DATE '2006-11-14' );
-- returns 2006-11-14 00:00:00