MySQL Bugs: #31014: NOW()+0 and CURTIME()+0 casts to a DOUBLE with 6 decimals

Bug #31014	NOW()+0 and CURTIME()+0 casts to a DOUBLE with 6 decimals
Submitted:	14 Sep 2007 4:12	Modified:	30 Nov 2007 16:46
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	MacOS
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
Temporal values in the form of datetime and time casts different when cast implicitly vs. explicitly.

How to repeat:
/* Copy and paste if you want... */

SELECT NOW()+0, CAST(NOW() AS SIGNED);
/*
+-----------------------+-----------------------+
| NOW()+0               | CAST(NOW() AS SIGNED) |
+-----------------------+-----------------------+
| 20070914060943.000000 |        20070914060943 | 
+-----------------------+-----------------------+
1 row in set (0.00 sec)
*/

SELECT CURTIME()+0, CAST(CURTIME() AS SIGNED);
/*
+--------------+---------------------------+
| CURTIME()+0  | CAST(CURTIME() AS SIGNED) |
+--------------+---------------------------+
| 61023.000000 |                     61023 | 
+--------------+---------------------------+
*/

-- This works fine, however:
SELECT CURDATE()+0, CAST(CURDATE() AS SIGNED);
/*
+-------------+---------------------------+
| CURDATE()+0 | CAST(CURDATE() AS SIGNED) |
+-------------+---------------------------+
|    20070914 |                  20070914 | 
+-------------+---------------------------+
1 row in set (0.00 sec)*/

Suggested fix:
I guess the side effect comes from them being returned as DOUBLE data-types.
The strange thing is that it works without problems for DATE, but not DATETIME and TIME.

Thank you for a bug report. Verified just as described:

mysql> create view cd as select curdate()+0 a, now()+0 as b;
Query OK, 0 rows affected (0.03 sec)

mysql> desc cd;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | double(17,0) | NO   |     | 0       |       |
| b     | double(23,6) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select now()+0;
+-----------------------+
| now()+0               |
+-----------------------+
| 20070914074732.000000 |
+-----------------------+
1 row in set (0.00 sec)

Note that above is not only inconsistent, but also clearly contradictory to the manual (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now):

"Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

mysql> SELECT NOW();
        -> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 19971215235026"

According to Joro this is not a bug because the manual says that
arithmetic operations between non-integer types convert their
arguments to double.

I only found this:
"If you are using a string in an arithmetic operation, this is converted
to a floating-point number."
here:
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

After discussion with Tobias Asplund, I believe that this is not a bug but the manual needs updating.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.