MySQL Bugs: #20292: inconsistent range for time data type

Bug #20292	inconsistent range for time data type
Submitted:	6 Jun 2006 12:43	Modified:	6 Jun 2006 14:43
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.21-BK, 4.1.20 and 5.0.22	OS:	Any (*)
Assigned to:		CPU Architecture:	Any

Description:
This may be similiar to bug #20203, but is happening in 4.1 too.

cast and set_to_time both return results that are outside the range of the time data type.
  http://dev.mysql.com/doc/refman/4.1/en/time.html
  http://dev.mysql.com/doc/refman/5.0/en/time.html
TIME values may range from '-838:59:59' to '838:59:59'.

However sec_to_time(), maketime() and even cast(x as time) return values outside this range. 
If used in a sub-query, the outer query cuts down the result.

Queries used:
 select cast("1000:01:01" as time) as t;
 select * from ( select cast("1000:01:01" as time) as t) x; show warnings;
 select sec_to_time(4000000) as t; 
 select * from (select sec_to_time(4000000) as t) x; show warnings;

Note: the 1st and 3rd query do not issue a warning, even so "show warnings" might show it. This is unrelated to the problem and reported as bug #20291

How to repeat:
Mysql 4.1

select cast("1000:01:01" as time) as t;
+------------+
| t          |
+------------+
| 1000:01:01 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from ( select cast("1000:01:01" as time) as t) x; show warnings;
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.03 sec)

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '1000:01:01' |
+---------+------+----------------------------------------------+
1 row in set (0.01 sec)

mysql>  select sec_to_time(4000000) as t;
+------------+
| t          |
+------------+
| 1111:06:40 |
+------------+
1 row in set (0.01 sec)

mysql>  select * from (select sec_to_time(4000000) as t) x; show warnings;
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.00 sec)

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '1111:06:40' |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)

### Mysql 5.0

 select cast("1000:01:01" as time) as t;
+------------+
| t          |
+------------+
| 1000:01:01 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from ( select cast("1000:01:01" as time) as t) x; show warnings;
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect time value: '1000:01:01' for column 't' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  select sec_to_time(4000000) as t;
+------------+
| t          |
+------------+
| 1111:06:40 |
+------------+
1 row in set (0.00 sec)

mysql>  select * from (select sec_to_time(4000000) as t) x; show warnings;
+-----------+
| t         |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.01 sec)

+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Incorrect time value: '1111:06:40' for column 't' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
The correct fix would be to always truncate the Value.

I am not sure, if it is the most desirable. As bug #20203 suggests, the missing truncation, is a very usefull "feature" for the sum function.

This means fixing this, by applying truncation, is likely to break a lot of applications.

A "group by" does also force the value into its limit

select sec_to_time(a) from (select 3020399 as a UNION select 3020400 UNION select 4000000 UNION select 4000001) x;
select sec_to_time(a) from (select 3020399 as a UNION select 3020400 UNION select 4000000 UNION select 4000001) x group by 1;

select sec_to_time(a) from (select 3020399 as a UNION select 3020400 UNION select 4000000 UNION select 4000001) x;
+----------------+
| sec_to_time(a) |
+----------------+
| 838:59:59      |
| 839:00:00      |
| 1111:06:40     |
| 1111:06:41     |
+----------------+
4 rows in set (0.00 sec)

mysql> select sec_to_time(a) from (select 3020399 as a UNION select 3020400 UNION select 4000000 UNION select 4000001) x group by 1;
+----------------+
| sec_to_time(a) |
+----------------+
| 838:59:59      |
+----------------+
1 row in set, 3 warnings (0.00 sec)

This is different as the subquery returns an integer, however I need to get the value from the subqgery as it does not work on a constant item

 select sec_to_time(3020399+1) from (select 1) x; show warnings;
+------------------------+
| sec_to_time(3020399+1) |
+------------------------+
| 839:00:00              |
+------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

mysql> select sec_to_time(3020399+1) from (select 1) x group by 1; show warnings;
+------------------------+
| sec_to_time(3020399+1) |
+------------------------+
| 839:00:00              |
+------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Thank you for a problem report. Verified just as described with 4.1.21-BK build on Linux. As bug #20203 has assigned developer already, I'll mark this report as a duplicate of it.