Description:
Output:
=======
mysql> select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
+------------------------------------------------------------------------+
| cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2)) |
+------------------------------------------------------------------------+
| 20010101000000.00 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select least(cast('01-01-01' as datetime), '01-01-02');
+-------------------------------------------------+
| least(cast('01-01-01' as datetime), '01-01-02') |
+-------------------------------------------------+
| 2001-01-01 00:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select cast( '2001-01-01 00:00:00' as decimal(16,2));
+-----------------------------------------------+
| cast( '2001-01-01 00:00:00' as decimal(16,2)) |
+-----------------------------------------------+
| 2001.00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
By displaying --column-type-info, we can see that type of "least(cast('01-01-01' as datetime), '01-01-02')" is "VAR_STRING".
=====
mysql> select least(cast('01-01-01' as datetime), '01-01-02');
Field 1: `least(cast('01-01-01' as datetime), '01-01-02')`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: VAR_STRING
Collation: ? (45)
Length: 32
Max_length: 19
Decimals: 31
Flags:
+-------------------------------------------------+
| least(cast('01-01-01' as datetime), '01-01-02') |
+-------------------------------------------------+
| 2001-01-01 00:00:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
Problem:
========
Since the inputs of cast( x as decimal(16,2)) in the following two SQLs are both varchar, they get inconsistent result.
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
select cast( '2001-01-01 00:00:00' as decimal(16,2));
How to repeat:
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
select least(cast('01-01-01' as datetime), '01-01-02');
select cast( '2001-01-01 00:00:00' as decimal(16,2));
Suggested fix:
The following two SQLs get the same result:
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));
select cast( '2001-01-01 00:00:00' as decimal(16,2));