MySQL Bugs: #7521: TIME_FORMAT fails in VIEWS

Bug #7521	TIME_FORMAT fails in VIEWS
Submitted:	24 Dec 2004 2:13	Modified:	22 Jun 2005 9:43
Reporter:	Harald Gölles
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.2	OS:	Linux (linux)
Assigned to:	Bugs System	Target Version:

Description:
TIME_FORMAT fails with calculated value
TIME_FORMAT(t,'%H:%i') = ok
TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') = wrong

the error only occurs inside views.

How to repeat:
USE test;
DROP TABLE t1;
CREATE TABLE t1 (id INT NOT NULL auto_increment, i INT NOT NULL, t TIME, PRIMARY KEY
(id));

INSERT INTO t1 (i, t) VALUES (2, '13:00');
INSERT INTO t1 (i, t) VALUES (2, '14:00');
INSERT INTO t1 (i, t) VALUES (3, '15:00');
INSERT INTO t1 (i, t) VALUES (3, '16:00');
INSERT INTO t1 (i, t) VALUES (4, '17:00');
INSERT INTO t1 (i, t) VALUES (4, '18:00');
INSERT INTO t1 (i, t) VALUES (4, '19:00');
INSERT INTO t1 (i, t) VALUES (5, '20:00');

// wrong result, NULL + 00:00
DROP VIEW tv;
CREATE VIEW tv AS 
SELECT TIME_FORMAT(SEC_TO_TIME(AVG(TIME_TO_SEC(t))),'%H:%i') AS t FROM t1 GROUP BY i;
SELECT * FROM tv;

// correct result
DROP VIEW tv2;
CREATE VIEW tv2 AS 
SELECT i, TIME_FORMAT(t,'%H:%i') AS t FROM t1 GROUP BY i;
SELECT * FROM tv2;

// wrong result (all 00:00)
DROP VIEW tv3;
CREATE VIEW tv3 AS 
SELECT TIME_FORMAT(SEC_TO_TIME(TIME_TO_SEC(t)),'%H:%i') AS t FROM t1;
SELECT * FROM tv3;

// wrong result (all 00:00)
DROP VIEW tv4;
CREATE VIEW tv4 AS 
SELECT TIME_FORMAT(SEC_TO_TIME(3600),'%H:%i') AS t FROM t1;
SELECT * FROM tv4;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26193

pushed to 5.0.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Bugfix documented in 5.0.8 changelog; bug closed.