Bug #18984 CASTing datetime to decimal without decimals gives unexpected output

• Submitted: 10 Apr 2006 19:44
• Modified: 18 Nov 2007 11:34
• Reporter: Tobias Asplund
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.19/5.1.7
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[10 Apr 2006 19:44] Tobias Asplund

Description:
mysql> SELECT CAST(NOW() AS DECIMAL(14,3));
+------------------------------+
| CAST(NOW() AS DECIMAL(14,3)) |
+------------------------------+
| 20060410214208.000           |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(NOW() AS DECIMAL(14,2));
+------------------------------+
| CAST(NOW() AS DECIMAL(14,2)) |
+------------------------------+
| 20060410214210.00            |
+------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CAST(NOW() AS DECIMAL(14,1));
+------------------------------+
| CAST(NOW() AS DECIMAL(14,1)) |
+------------------------------+
| 20060410214212.0             |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(NOW() AS DECIMAL(14,0));
+------------------------------+
| CAST(NOW() AS DECIMAL(14,0)) |
+------------------------------+
| 20060410214214.00            |
+------------------------------+
1 row in set (0.00 sec)

I would have expected:

mysql> SELECT CAST(NOW() AS DECIMAL(14,0));
+------------------------------+
| CAST(NOW() AS DECIMAL(14,0)) |
+------------------------------+
| 20060410214214               |
+------------------------------+
1 row in set (0.00 sec)

How to repeat:
SELECT CAST(NOW() AS DECIMAL(14,0));

[3 Aug 2006 9:34] Magnus Blåudd

DECIMAL(14,0) interprets the zero as "use default" number of decimals. Will need to investigate how this should work.

[27 Jun 2007 12:23] Tobias Asplund

It seems like the default is not 2 in this case, though:

mysql> CREATE TABLE t1 ( a DECIMAL );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t1 VALUES (2.555);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM t1;
+------+
| a    |
+------+
|    3 | 
+------+
1 row in set (0.00 sec)

[9 Oct 2007 8:50] MySQL Verification Team

see related Bug #31471

[18 Nov 2007 11:34] Gleb Shchepa

This bug is a duplicate and already has been fixed (see bug #27921 and bug #27957).