Bug #78415 Equivalent SQL like "select cast( varchar as decimal)" returns different results

• Submitted: 12 Sep 2015 6:17
• Modified: 12 Sep 2015 9:50
• Reporter: Su Dylan
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6.22, 5.6.26, 5.7.9
• OS: Any
• CPU Architecture: Any

[12 Sep 2015 6:17] Su Dylan

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));

[12 Sep 2015 7:05] MySQL Verification Team

Hello Su Dylan,

Thank you for the report and test case.

Thanks,
Umesh

[12 Sep 2015 9:50] Su Dylan

Since type for "least(cast('01-01-01' as datetime), '01-01-02')" is ver_string, the expected result for the following SQL should be 2001.00. 
select cast(least(cast('01-01-01' as datetime), '01-01-02') as decimal(16,2));

Is this correct?