MySQL Bugs: #12268: Datetime column width wrong with in numeric context

Bug #12268	Datetime column width wrong with in numeric context
Submitted:	29 Jul 2005 12:20	Modified:	21 Jan 2006 18:41
Reporter:	Richard Sonnen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	4.1.13, 4.1.14-nt	OS:	FreeBSD (FreeBSD 4.11p8, Windows)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
In 4.1.12, a DATETIME column in numeric context return in YYYYMMDDHHMMSS format.  As of 4.1.13 the same column will return in YYYYMMDDHHMMSS.FFFFFF where F appears to be a fractional second.

My understanding was that fractional seconds were going to be supported in a future release, but not in this one, as they aren't mentioned in the changelog (http://dev.mysql.com/doc/mysql/en/news-4-1-13.html).

How to repeat:
mysql> create table datetest (start_time datetime);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into datetest values(now());
Query OK, 1 row affected (0.00 sec)

mysql> select start_time from datetest;
+---------------------+
| start_time          |
+---------------------+
| 2005-07-29 06:02:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> select start_time +0 from datetest;
+-----------------------+
| start_time +0         |
+-----------------------+
| 20050729060254.000000 |
+-----------------------+
1 row in set (0.00 sec)

Suggested fix:
Return column width to previous format, or document new behavior in the release notes along with a recommended workaround to mimic the old behavior.  (Casting the value to an unsigned integer will do it, but seems clumsy.)

Verified with 4.1 from BK tree

ChangeSet@1.2354, 2005-07-28 03:22:47+03:00, monty@mysql.com

I'm wondering if then, that this just needs to be documented?

Isolated this to a change from 5.0.7 to 5.0.8

I will be working on this bug 8th and 9th - do not take from me! ;)

The same behaviour in 4.1.14:

mysql> select start_time from datetest;
+---------------------+
| start_time          |
+---------------------+
| 2005-09-20 13:15:22 |
+---------------------+
1 row in set (0.00 sec)

mysql> select start_time +0 from datetest;
+-----------------------+
| start_time +0         |
+-----------------------+
| 20050920131522.000000 |
+-----------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.1.14-nt |
+-----------+
1 row in set (0.00 sec)

In talking to Brian and Monty:

<monty|zzz>	The plan is, as stated in the bug report that when converting a datetime to number, we will also include sub seconds
[04:33] 	<patg>	I'm wondering where I should focus my examination on, codewise. Also, that sounds like a bug to you too?
[04:34] 	<monty|zzz>	we don't yet have sub seconds support, but the question is when to start adding the .000000 data
[04:34] 	<monty|zzz>	I am not 100 % sure it's a bug as this is something we want to do in the future as soon as we have datetime with higher precission
[04:34] 	<monty|zzz>	But I don't feel strongly about this
[04:35] 	<patg>	I see, so the real problem is that it subseconds don't exist, but are being displayed.
[04:35] 	<patg>	ok, antony just came down and everyone is hovering over me because they are hungry for dinner!
[04:35] 	<patg>	monty|zzz: thanks
[04:36] 	<monty|zzz>	yes. In 5.2 or 5.3 we should add sub second support, the question is if it's good to prepare users for that

Brian, says that it's the behaviour (it should be case as a float vs. int) that we want, and that it just needs to be documented.

Additional note: The change occurred in 4.1.13 for 4.1
and 5.0.8 for 5.0.

Changing this to a documentation bug with Verified status as the Documenting status is reserved for bugfixes that need changelog entries.

Documenting this is hung up waiting for Bug#16546 to be resolved.

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
product(s).

Additional info:

I've added incompatibility notes to the 4.1.13 and 5.0.8 changelogs,
and to the 4.1 and 5.0 upgrading sections in their respective manuals.
Bug#16546 still needs to be resolved..