MySQL Bugs: #23656: NOW() and CURDATE() doesn't convert correctly when used by CAST() function

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

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)

Thank you for the report.

Verified as described on Linux using last BK sources.

There is Bug #23093 with mirror-like problem.

4.1 and 5.1 versions are affected too

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.

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.

Pushed into 5.1.18-beta

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?).

Pushed into 4.1.23

Pushed into 5.0.42

Noted in 5.0.42, 4.1.23 changelogs.