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 15:45	Modified:	6 Jul 2006 3:45
Reporter:	Martijn van Dongen
Status:	Not a Bug
Category:	Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.22, 4.1.21-BK	OS:	Any (All)
Assigned to:	Bugs System	Target Version:

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)