MySQL Bugs: #60408: Datatime difference returns wrong answer

Bug #60408	Datatime difference returns wrong answer
Submitted:	9 Mar 2011 22:38	Modified:	10 Apr 2011 4:10
Reporter:	Allen Morris	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.1.49	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	datetime, difference, Subtraction

Description:
cast('2011-03-09 13:53:00' as datetime) - cast('2011-03-09 13:52:59' as datetime) = 41 rather than the expected 1

<pre>
+-----------------------------------------------------------------------------------+
| cast('2011-03-09 13:53:00' as datetime) - cast('2011-03-09 13:52:59' as datetime) |
+-----------------------------------------------------------------------------------+
|                                                                         41.000000 |
+-----------------------------------------------------------------------------------+
</pre>

How to repeat:
CREATE TABLE t1 (f1 datetime, f2 datetime);
insert into t1 values ('2011-03-09 13:53:00', '2011-03-09 13:52:59');
select cast('2011-03-09 13:53:00' as datetime) - cast('2011-03-09 13:52:59' as datetime), f1 - f2 from t1

Bad tags.

Please, check if the same problem happens with a recent version, 5.1.56. 

Look:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t1 (f1 datetime, f2 datetime);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values ('2011-03-09 13:53:00', '2011-03-09 13:52:59');
Query OK, 1 row affected (0.04 sec)

mysql> select cast('2011-03-09 13:53:00' as datetime) - cast('2011-03-09
13:52:59' as datetime) as a, f1 - f2 as b from t1;
+-----------+-----------+
| a         | b         |
+-----------+-----------+
| 41.000000 | 41.000000 |
+-----------+-----------+
1 row in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".