MySQL Bugs: #25620: Error in ADDTIME function when update more 35 days

Bug #25620	Error in ADDTIME function when update more 35 days
Submitted:	15 Jan 2007 6:54	Modified:	5 Dec 2007 18:55
Reporter:	Luis A S Junior Camargo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.0.33	OS:	Windows (Windows Server 2003 Web Edition)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:

I have download the source and success compiled by the Visual Studio 2005, working fine but when use the ADDTIME function with parameter 35 or more days with the UPDATE i get error "ERROR 1292 (22007): Truncated incorrect time value: '35 00:00:00'"

When using SELECT command working fine, only in the UPDATE command are the problem, see:

> select addtime(now(),'35 00:00:00');
+------------------------------+
| addtime(now(),'35 00:00:00') |
+------------------------------+
| 2007-02-19 03:31:37          |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

> update sometable set somedatetime=addtime(now(),'35 00:00:00') where id=2;
ERROR 1292 (22007): Truncated incorrect time value: '35 00:00:00'

In the previous version 5.0.27 are no problem with the same commands.

=================================
The full log test case are below:
=================================

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21847
Server version: 5.0.33-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mydatabase;
Database changed

mysql> drop table if exists `sometable`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `sometable` (
    ->   `id` int(11) NOT NULL default '0',
    ->   `somedatetime` datetime default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into sometable (id,somedatetime) values (1,addtime(now(),'1 01:01:01'));
Query OK, 1 row affected (0.02 sec)

mysql> insert into sometable (id,somedatetime) values (2,addtime(now(),'2 02:02:02'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from sometable;
+----+---------------------+
| id | somedatetime        |
+----+---------------------+
|  1 | 2007-01-16 05:32:39 |
|  2 | 2007-01-17 06:33:40 |
+----+---------------------+
2 rows in set (0.01 sec)

mysql> select addtime(now(),'34 00:00:00');
+------------------------------+
| addtime(now(),'34 00:00:00') |
+------------------------------+
| 2007-02-18 04:31:38          |
+------------------------------+
1 row in set (0.02 sec)

mysql> update sometable set somedatetime=addtime(now(),'34 00:00:00') where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sometable;
+----+---------------------+
| id | somedatetime        |
+----+---------------------+
|  1 | 2007-02-18 04:31:38 |
|  2 | 2007-01-17 06:33:40 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql> select addtime(now(),'35 00:00:00');
+------------------------------+
| addtime(now(),'35 00:00:00') |
+------------------------------+
| 2007-02-19 03:31:37          |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> update sometable set somedatetime=addtime(now(),'35 00:00:00') where id=2;
ERROR 1292 (22007): Truncated incorrect time value: '35 00:00:00'

mysql> select * from sometable;
+----+---------------------+
| id | somedatetime        |
+----+---------------------+
|  1 | 2007-02-18 04:31:38 |
|  2 | 2007-01-17 06:33:40 |
+----+---------------------+
2 rows in set (0.00 sec)

mysql>

How to repeat:
drop table if exists `sometable`;
CREATE TABLE `sometable` (
  `id` int(11) NOT NULL default '0',
  `somedatetime` datetime default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
insert into sometable (id,somedatetime) values (1,addtime(now(),'1 01:01:01'));
insert into sometable (id,somedatetime) values (2,addtime(now(),'2 02:02:02'));
select * from sometable;
select addtime(now(),'34 00:00:00');
update sometable set somedatetime=addtime(now(),'34 00:00:00') where id=1;
select * from sometable;
select addtime(now(),'35 00:00:00');
update sometable set somedatetime=addtime(now(),'35 00:00:00') where id=2;
select * from sometable;

Suggested fix:
I unknow, to solve my problem i have replaced the my application commands, because the final results is the same:

update sometable set somedatetime=addtime(now(),'35 00:00:00') where id=2;

by

update sometable set somedatetime=adddate(now(),interval 35 day) where id=2;

I'm getting a warning instead of an error on linux, probably due to different SQL_MODE settings. The cutoff value seems to be "34 23:00:00", "34 22:59:59" is still accepted as valid input ...

I think this is the new expected behaviour.

FRom the docs:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

ADDTIME(expr1,expr2)
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime  expression, and expr2 is a time expression. 

"expr2 is a TIME VALUE"

From the Changelog of 5.0.30:

Lack of validation for input and output TIME values resulted in several problems: SEC_TO_TIME() within subqueries incorrectly clipped large values; SEC_TO_TIME() treated BIGINT UNSIGNED values as signed; only truncation warnings were produced when both truncation and out-of-range TIME values occurred. (Bug#11655, Bug#20927)

From the odc at http://dev.mysql.com/doc/refman/4.1/en/time.html

 The TIME Type

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'.

So rejecting values outside this range should be expected.

Not sure if this fix should have made it into a GA version. (It made it into 5.0 and 4.1)