MySQL Bugs: #16546: DATETIME+0 not always coerced the same way

Bug #16546	DATETIME+0 not always coerced the same way
Submitted:	16 Jan 2006 18:55	Modified:	23 Apr 2007 19:46
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.x, 5.0.x	OS:	Any
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
I raised this issue on dev-*, and Serg's response to my questions
was tthere there is a bug:

Serg: A bug, caused by incomplete fix for BUG#11385.

So, please see Bug#11385, and evaluate with respect to the
problem described below:

I'm trying to figure out how to document the issue in Bug#12268,
which is that, apparently, the behavior of applying +0 to DATETIME
columns now results in a float rather than an int.  Example:

Input:

drop table if exists t;
create table t (d datetime);
insert into t set d = now();
select d, d+0 from t;

Result for MySQL 4.1.12, 5.0.7:

+---------------------+----------------+
| d                   | d+0            |
+---------------------+----------------+
| 2006-01-11 21:21:54 | 20060111212154 |
+---------------------+----------------+

Result for MySQL 4.1.13, 5.0.8:

+---------------------+-----------------------+
| d                   | d+0                   |
+---------------------+-----------------------+
| 2006-01-11 21:22:18 | 20060111212218.000000 |
+---------------------+-----------------------+

But this principle of DATETIME conversion seems to be inconsistently
applied.  I investigated conversion of DATETIME values in various
contexts, and get various results.

Input:

drop table if exists t;
create table t (d datetime);
insert into t set d = now();
select d, d+0 from t;
select now(), now()+0;
select cast(d as datetime), cast(d as datetime)+0 from t;
select cast(now() as datetime), cast(now() as datetime)+0;

Result (for versions 4.1.13 and up, 5.0.8 and up):

+---------------------+-----------------------+
| d                   | d+0                   |
+---------------------+-----------------------+
| 2006-01-11 21:24:50 | 20060111212450.000000 |
+---------------------+-----------------------+
+---------------------+----------------+
| now()               | now()+0        |
+---------------------+----------------+
| 2006-01-11 21:24:50 | 20060111212450 |
+---------------------+----------------+
+---------------------+-----------------------+
| cast(d as datetime) | cast(d as datetime)+0 |
+---------------------+-----------------------+
| 2006-01-11 21:24:50 |                  2006 |
+---------------------+-----------------------+
+-------------------------+---------------------------+
| cast(now() as datetime) | cast(now() as datetime)+0 |
+-------------------------+---------------------------+
| 2006-01-11 21:24:50     |                      2006 |
+-------------------------+---------------------------+

Why the different results?  Does the conversion of DATETIME+0
to float apply only to DATETIME _columns_ and not to other
DATETIME values?  If so, why?

Or are the results I observe now what I should observe? (That
is, is there a bug here?)

Or are the values in the last three SELECT statements not "really"
DATETIME values?

How to repeat:
See above.

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/5801

It is possible to describe here how it will function after the patch?

fixed in 4.1.21

I tried my second input test again and the results are
a bit better, but the CAST(... AS DATETIME)+0 expressions
still don't convert properly:

+---------------------+-----------------------+
| d                   | d+0                   |
+---------------------+-----------------------+
| 2006-06-06 14:55:47 | 20060606145547.000000 |
+---------------------+-----------------------+
+---------------------+-----------------------+
| now()               | now()+0               |
+---------------------+-----------------------+
| 2006-06-06 14:55:47 | 20060606145547.000000 |
+---------------------+-----------------------+
+---------------------+-----------------------+
| cast(d as datetime) | cast(d as datetime)+0 |
+---------------------+-----------------------+
| 2006-06-06 14:55:47 |                  2006 |
+---------------------+-----------------------+
+-------------------------+---------------------------+
| cast(now() as datetime) | cast(now() as datetime)+0 |
+-------------------------+---------------------------+
| 2006-06-06 14:55:47     |                      2006 |
+-------------------------+---------------------------+

Shouldn't the second two lines convert the same way as
the first two lines?

Re-opening. Still doesn't seem completely fixed.
See previous comment.

There is duplicate bug #21791

http://lists.mysql.com/commits/15268

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/16490

ChangeSet@1.2563, 2006-12-06 11:03:54+04:00, holyfoot@mysql.com +3 -0
  bug #16546 (DATETIME + 0 not always coerced in the same way)
  fix for cast( AS DATETIME) + 0 operation.
  I just implemented Item_datetime_typecast::val() method
  as it is usually done in other classes.
  Should be fixed more radically in 5.0

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/16492

ChangeSet@1.2326, 2006-12-06 12:06:04+04:00, holyfoot@mysql.com +1 -0
  bug #16546 (DATETIME+0 not always coerced the same way)
  fix for cast( AS DATETIME)+0 in 5.0 and above versions.
  val_real now works using val_decimal for DATETIME Items

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/16493

ChangeSet@1.2327, 2006-12-06 12:39:14+04:00, holyfoot@mysql.com +1 -0
  bug #16546 (DATATIME+0 problems)
  sove val_real() implementations became superfluous, so removed

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/22570

ChangeSet@1.2624, 2007-03-22 12:24:56+04:00, holyfoot@mysql.com +3 -0
   bug #16546 (DATETIME + 0 not always coerced in the same way)
    fix for cast( AS DATETIME) + 0 operation.
    I just implemented Item_datetime_typecast::val() method
    as it is usually done in other classes.
    Should be fixed more radically in 5.0

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/22573

ChangeSet@1.2490, 2007-03-22 12:44:38+04:00, holyfoot@mysql.com +1 -0
  bug #16546 (DATETIME+0 not always coerced the same way)
    fix for cast( AS DATETIME)+0 in 5.0 and above versions.
    val_real now works using val_decimal for DATETIME Items
    Superfluous val_real() methods deleted

Pushed in 4.1.23, 5.0.40 and 5.1.18

Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

Conversion of DATETIME values in numeric contexts sometimes did not
produce a double (YYYYMMDDHHMMSS.uuuuuu) value.

Bug#Bug #19765 CAST(), DATE() and TIME() returning wrong data type for dates?
was marked a duplicate of this bug.