Bug #19491 CASTing a DATETIME to DECIMAL truncates microseconds
Submitted: 2 May 2006 20:27 Modified: 27 Nov 2006 19:46
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.7 OS:Any (*)
Assigned to: Alexey Botchkov CPU Architecture:Any

[2 May 2006 20:27] Tobias Asplund
Description:
When a datetime value with microseconds gets casted to a decimal, the microseconds disappear (gets truncated without warnings)

How to repeat:
eeyore> SELECT NOW() + INTERVAL 14 MICROSECOND;
+---------------------------------+
| NOW() + INTERVAL 14 MICROSECOND |
+---------------------------------+
| 2006-05-02 22:08:47.000014      |
+---------------------------------+
1 row in set (0.00 sec)

eeyore> SELECT CAST(NOW() + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+--------------------------------------------------------+
| CAST(NOW() + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) |
+--------------------------------------------------------+
|                                  20060502220848.000000 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Note that this works:
eeyore> SELECT CAST(NOW() + INTERVAL 14 MICROSECOND AS CHAR);
+-----------------------------------------------+
| CAST(NOW() + INTERVAL 14 MICROSECOND AS CHAR) |
+-----------------------------------------------+
| 2006-05-02 22:10:27.000014                    |
+-----------------------------------------------+
1 row in set (0.00 sec)
[31 May 2006 21:31] Jorge del Conde
jorge-/my/mysql-5.0> mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT NOW() + INTERVAL 14 MICROSECOND;
+---------------------------------+
| NOW() + INTERVAL 14 MICROSECOND |
+---------------------------------+
| 2006-05-31 16:27:29.000014      | 
+---------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CAST(NOW() + INTERVAL 14 MICROSECOND AS DECIMAL(20,6));
+--------------------------------------------------------+
| CAST(NOW() + INTERVAL 14 MICROSECOND AS DECIMAL(20,6)) |
+--------------------------------------------------------+
|                                            2006.000000 | 
+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(NOW() + INTERVAL 14 MICROSECOND AS CHAR);
+-----------------------------------------------+
| CAST(NOW() + INTERVAL 14 MICROSECOND AS CHAR) |
+-----------------------------------------------+
| 2006-05-31 16:27:46.000014                    | 
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql>
[31 May 2006 21:32] Jorge del Conde
Tested under FC5
[10 Aug 2006 9:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10243

ChangeSet@1.2233, 2006-08-10 14:46:06+05:00, holyfoot@mysql.com +5 -0
  Bug #19491 (CASTing a DATETIME to DECIMAL truncates microsecond)
  
  Problem was that we tried to get DECIMAL from string DATETIME representation
  using string2my_decimal conversion.
  I added the code to produce the DECIMAL from the TIME struct directly
  for DATETIME family items
[11 Aug 2006 8:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10299

ChangeSet@1.2234, 2006-08-11 14:08:40+05:00, holyfoot@mysql.com +5 -0
  bug #19491 (CASTing a DATETIME to DECIMAL)
  additional fix to solve the same problem with TIME type
[14 Aug 2006 10:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10352

ChangeSet@1.2233, 2006-08-14 15:53:55+05:00, holyfoot@mysql.com +12 -0
  bug #19491 (CAST DATETIME as DECIMAL fails)
  the problem was that string DATATIME representations is converted
  into DECIMAL with the weird result.
  I decided just to implement direct TIME->DECIMAL conversion
[15 Aug 2006 10:46] Sergey Glukhov
ok to push
[2 Sep 2006 16:28] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11308

ChangeSet@1.2255, 2006-09-02 21:54:29+05:00, holyfoot@mysql.com +12 -0
  bug #19491 (CAST do DATETIME wrong result)
[25 Oct 2006 15:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14368

ChangeSet@1.2255, 2006-10-25 20:14:39+05:00, holyfoot@mysql.com +12 -0
  bug #19491 (CAST do DATETIME wrong result)
[5 Nov 2006 18:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14859

ChangeSet@1.2324, 2006-11-05 22:44:37+04:00, holyfoot@mysql.com +4 -0
  bug #19491 5.1-related fixes
[6 Nov 2006 18:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14904

ChangeSet@1.2297, 2006-11-06 22:33:18+04:00, holyfoot@mysql.com +4 -0
  bug #19491 (5.0-related additional fixes)
[27 Nov 2006 17:13] Georgi Kodinov
Pushed in 5.0.32/5.1.14-beta
[27 Nov 2006 19:46] Paul Dubois
Noted in 5.0.32, 5.1.14 changelogs.

For a cast of a DATETIME value containing microseconds to DECIMAL,
the microseconds part was truncated without generating a warning. Now
the microseconds part is preserved.