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 14:20	Modified:	21 Jan 2006 19:41
Reporter:	Richard Sonnen
Status:	Closed
Category:	Server: Docs	Severity:	S2 (Serious)
Version:	4.1.13, 4.1.14-nt	OS:	FreeBSD (FreeBSD 4.11p8, Windows)
Assigned to:	Bugs System	Target Version:
Triage:	D4 (Minor)

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