MySQL Bugs: #20203: SEC_TO_TIME(SUM(TIME_TO_SEC( doesn't work correctly with a VIEW

Bug #20203	SEC_TO_TIME(SUM(TIME_TO_SEC( doesn't work correctly with a VIEW
Submitted:	1 Jun 2006 13:45	Modified:	6 Jul 2006 1:45
Reporter:	Martijn van Dongen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.22, 4.1.21-BK	OS:	Any (All)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
Command line MySQL tool:

I create a table:

CREATE TABLE `projectfasen` (
  `projectFaseID` int(11) NOT NULL auto_increment,
  `projectID` int(11) NOT NULL,
  `richtwaarde` time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert the next data: 

INSERT INTO `projectfasen` VALUES 
(1,1,'60:00:00'), 
(2,1,'120:00:00'),
(3,1,'480:00:00'),
(4,1,'240:00:00'),
(5,1,'240:00:00');

Execute the following query:

SELECT 
  SEC_TO_TIME(SUM(TIME_TO_SEC(richtwaarde))) AS totaal
FROM
  projectfasen
GROUP BY 
  projectID

Get the next result:

+------------+
| totaal     |
+------------+
| 1140:00:00 |
+------------+
1 row in set (0.00 sec)

But if i create the earlier query as a view (for example 'test'):

CREATE OR REPLACE VIEW test AS
SELECT 
  SEC_TO_TIME(SUM(TIME_TO_SEC(richtwaarde))) AS totaal
FROM
  projectfasen
GROUP BY 
  projectID

And execute the query, i get the following result, that should be 1140:00:00:

SELECT * FROM test;

+-----------+
| totaal    |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.41 sec)

What am i or is mysql doing wrong??

How to repeat:
Please send me a temporarily solution or a explanation to solve this problem

Suggested fix:
If it's a bug, i hope it would be updated soon...

Hi!

The problem you're describing is indeed a bug.  I was able to reproduce it using 5.0.23bk under FC5:

mysql> CREATE TABLE `projectfasen` (   `projectFaseID` int(11) NOT NULL auto_increment primary key,   `projectID` int(11) NOT NULL,   `richtwaarde` time NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `projectfasen` VALUES 
    -> (1,1,'60:00:00'), 
    -> (2,1,'120:00:00'),
    -> (3,1,'480:00:00'),
    -> (4,1,'240:00:00'),
    -> (5,1,'240:00:00');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT 
    ->   SEC_TO_TIME(SUM(TIME_TO_SEC(richtwaarde))) AS totaal
    -> FROM
    ->   projectfasen
    -> GROUP BY 
    ->   projectID
    -> ;
+------------+
| totaal     |
+------------+
| 1140:00:00 | 
+------------+
1 row in set (0.01 sec)

mysql> CREATE OR REPLACE VIEW test AS
    -> SELECT 
    ->   SEC_TO_TIME(SUM(TIME_TO_SEC(richtwaarde))) AS totaal
    -> FROM
    ->   projectfasen
    -> GROUP BY 
    ->   projectID
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+-----------+
| totaal    |
+-----------+
| 838:59:59 | 
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql>

Bug #20292 was marked as a duplicate of this one. But should be fixed in 4.1 as well. Bug #20292 contains some simple test cases, that can be useful.

This is not a bug.
View test is defined by a select statement with GROUP BY. Such views are always materialized when used in queries. The type of the only column of this view is TIME. The maximal value for this type is '838:59:59'. 
When the only row of the view is written to a temporary table (that's how
any view is materialized) the value of the column expression '1140:00:00' is clipped to this maximal value.
The warning message we can get after running the query 'SELECT * FROM test' says it explicitly:
 
mysql> SELECT * FROM test;
+-----------+
| totaal    |
+-----------+
| 838:59:59 |
+-----------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1292 | Incorrect time value: '1140:00:00' for column 'totaal' at row 1 |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

Why is the result of the time expression not clipped in the first query?
This is a different problem and it is reported in bug #11655 which has to be fixed somehow.

please see note on bug #11655

from the documentation at
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes, and seconds, as a
value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used
in a string or numeric context. 

string or numeric context. this function returns either a string or a number,
not a date. So it does not need to be clipped.

Not treating the value as time-type, has however another drawback.
If evaluated in string context, it will change the ordering (in order or group by) of negative values.
It wil be fine in numeric context.

Also it needs definition as what it will be treaded if there is neither a nummeric nor string context (as in a subquery).

I believe mysql should in some way keep the ability of handling values, biger than the time-type. I would think that many applications are using this.

I can thing of the following scenarios:

- treat it as string or integer, default to string if no context.
(for ordering people can cast the value, to int or time; int would work for bigger values too)

- tread it as string or integer, if a *direct context* of either type is given; treat it as time otherwise
this would cut the value, in the given example. but would keep it if the result was directly used in a cast( as string or int), concat() or numeric function

- treat it as time, if the result is smaller than max time, otherwise as string (or int, if context requires)
(I dont really thing this one is a good option)

I believe the 2nd solution will break more existing applications (even where no sub-query is used.)
The first solution on the otherhand, will break order by. (but imho is closer to the current documentation)