Bug #23656 NOW() and CURDATE() doesn't convert correctly when used by CAST() function
Submitted: 26 Oct 2006 5:43 Modified: 10 May 2007 17:48
Reporter: Tobias Asplund Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.24, 4.1 BK, 5.1 BK OS:Linux (Linux, Max OS X)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[26 Oct 2006 5:43] Tobias Asplund
Description:
Implicit and Explicit conversions of NOW() and CURDATE() works differently.
Implicit conversions works just fine, but when using CAST() it fails.

How to repeat:
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2006-10-26 14:39:12 | 
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW() + 0;
+-----------------------+
| NOW() + 0             |
+-----------------------+
| 20061026143916.000000 | 
+-----------------------+
1 row in set (0.09 sec)

mysql> SELECT CAST(NOW() AS UNSIGNED);
+-------------------------+
| CAST(NOW() AS UNSIGNED) |
+-------------------------+
|                    2006 | 
+-------------------------+
1 row in set, 1 warning (0.00 sec)

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

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2006-10-26 | 
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
|      20061026 | 
+---------------+
1 row in set (0.00 sec)

mysql> SELECT CAST(CURDATE() AS UNSIGNED);
+-----------------------------+
| CAST(CURDATE() AS UNSIGNED) |
+-----------------------------+
|                        2006 | 
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

Suggested fix:
My argument is that the CAST(NOW() AS UNSIGNED) should convert to the same as the implicit conversions.
(Doesn't matter with SIGNED/UNSIGNED)
[26 Oct 2006 7:36] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources.
[26 Oct 2006 7:38] Sveta Smirnova
There is Bug #23093 with mirror-like problem.
[26 Oct 2006 7:57] Sveta Smirnova
4.1 and 5.1 versions are affected too
[3 May 2007 20:12] 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/26052

ChangeSet@1.2470, 2007-05-04 00:10:22+04:00, evgen@moonbone.local +3 -0
  Bug#23656: Wrong conversion result of a DATETIME to integer using CAST function.
  
  The generic string to int conversion was used by the Item_func_signed and
  the Item_func_unsigned classes to convert DATE/DATETIME values to the
  SIGNED/UNSIGNED type. But this conversion produces wrong results for such
  values.
  
  Now if the item which result has to be converted can return its result as
  longlong then the item->val_int() method is used to allow the item to carry
  out the conversion itself and return the correct result.
  This condition is checked in the Item_func_signed::val_int() and the
  Item_func_unsigned::val_int() functions.
[3 May 2007 20:56] 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/26053

ChangeSet@1.2652, 2007-05-04 00:53:37+04:00, evgen@moonbone.local +3 -0
  Bug#23656: Wrong conversion result of a DATETIME to integer using CAST function.
  
  The generic string to int conversion was used by the Item_func_signed and
  the Item_func_unsigned classes to convert DATE/DATETIME values to the
  SIGNED/UNSIGNED type. But this conversion produces wrong results for such
  values.
  
  Now if the item which result has to be converted can return its result as
  longlong then the item->val_int() method is used to allow the item to carry
  out the conversion itself and return the correct result.
  This condition is checked in the Item_func_signed::val_int() and the
  Item_func_unsigned::val_int() functions.
[7 May 2007 18:15] Bugs System
Pushed into 5.1.18-beta
[8 May 2007 1:32] Paul Dubois
Noted in 5.1.18 changelog.

Using CAST() to convert DATETIME values to numeric values did not
work.

Setting to Patch Queued pending push into 5.0.x (and 4.1.x?).
[10 May 2007 6:44] Bugs System
Pushed into 4.1.23
[10 May 2007 6:52] Bugs System
Pushed into 5.0.42
[10 May 2007 17:48] Paul Dubois
Noted in 5.0.42, 4.1.23 changelogs.