Bug #7521 TIME_FORMAT fails in VIEWS
Submitted: 24 Dec 2004 1:13 Modified: 22 Jun 2005 7:43
Reporter: Harald Gölles Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.2 OS:Linux (linux)
Assigned to: Oleksandr Byelkin CPU Architecture:Any

[24 Dec 2004 1:13] Harald Gölles
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;
[20 Jun 2005 11:56] Bugs System
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
[22 Jun 2005 6:31] Oleksandr Byelkin
pushed to 5.0.8
[22 Jun 2005 7:43] Jon Stephens
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.