MySQL Bugs: #25643: SEC_TO_TIME function problem

Bug #25643	SEC_TO_TIME function problem
Submitted:	16 Jan 2007 9:18	Modified:	13 Feb 2007 19:11
Reporter:	Jean Lamy	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.36-BK, 5.0.32, 5.1 BK, 4.1 BK	OS:	Linux (Linux, Debian Sarge)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	sec_to_time time conversion

Description:
Hi,

I'm not sure if it's a bug but I've found something strange with the SEC_TO_TIME function. 

I have a table with 3 colums : id, time1 and time2
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `time1` time default NULL,
  `time2` time default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `test` (`id`, `time1`, `time2`) VALUES 
(1, '10:00:00', NULL),
(2, '11:00:00', '11:15:00');

When i run the following query :

SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 ) )
FROM `test` 

I get this result :

10:00:00 	NULL 	        00:00:00
11:00:00 	11:15:00 	NULL

What is strange here, is that on the second line, instead of NULL I should have '00:15:00'.

If I run the query

SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 ) )
FROM `test` ORDER BY id DESC

this time I get the good result on line 1:

11:00:00  	11:15:00  	00:15:00
10:00:00 	NULL 	        00:00:00

I know I can use SUBTIME() function but anymway the function SEC_TO_TIME seems to have a problem with NULL values.

To conclude, I hope this problem was not already submited.

Sorry for this bad english, I'm french :)

How to repeat:
create tables indicated in Description and execute the query :

SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 ) )
FROM `test` 

Suggested fix:
No ideas

Thank you for the report.

Verified as described on Linux using last BK sources. All versions are affected.

Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.36-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `test25643` (   `id` int(10) unsigned NOT NULL auto_increme
nt,   `time1` time default NULL,   `time2` time default NULL,   PRIMARY KEY  (`
id`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `test25643` (`id`, `time1`, `time2`) VALUES
    -> (1, '10:00:00', NULL),
    -> (2, '11:00:00', '11:15:00');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 ) )
    -> FROM `test25643`;
+----------+----------+---------------------------------------------------------
---+
| time1    | time2    | SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 )
 ) |
+----------+----------+---------------------------------------------------------
---+
| 10:00:00 | NULL     | 00:00:00
   |
| 11:00:00 | 11:15:00 | NULL
   |
+----------+----------+---------------------------------------------------------
---+
2 rows in set (0.00 sec)

mysql> SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( ti
me1 ) ) FROM `test25643` order by id desc;
+----------+----------+---------------------------------------------------------
---+
| time1    | time2    | SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 )
 ) |
+----------+----------+---------------------------------------------------------
---+
| 11:00:00 | 11:15:00 | 00:15:00
   |
| 10:00:00 | NULL     | 00:00:00
   |
+----------+----------+---------------------------------------------------------
---+
2 rows in set (0.00 sec)

mysql> explain SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO
_SEC( time1 ) ) FROM `test25643` order by id desc;
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+----------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  |
rows | Extra          |
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+----------------+
|  1 | SIMPLE      | test25643 | ALL  | NULL          | NULL | NULL    | NULL |
   2 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT time1, time2, SEC_TO_TIME( TIME_TO_SEC( time2 ) - TIME_TO
_SEC( time1 ) ) FROM `test25643`;
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  |
rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+-------+
|  1 | SIMPLE      | test25643 | ALL  | NULL          | NULL | NULL    | NULL |
   2 |       |
+----+-------------+-----------+------+---------------+------+---------+------+-
-----+-------+
1 row in set (0.01 sec)

Thanks for this quick response.

In addition to this, to bypass the problem I can use the following query :

SELECT time1, time2, CASE WHEN ISNULL(( TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 ))) THEN NULL ELSE SEC_TO_TIME(TIME_TO_SEC( time2 ) - TIME_TO_SEC( time1 )) END
FROM `test` 

which result is :
time1           time2           diff
10:00:00  	NULL  	        NULL
11:00:00 	11:15:00 	00:15:00

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/commits/18856

ChangeSet@1.2387, 2007-01-26 16:50:38+02:00, gkodinov@macbook.gmz +3 -0
  Bug #25643: SEC_TO_TIME function problem
   Checking for NULL before calling the val_xxx()
   methods may omit some null values (e.g. function calls).
   Fixed by first calling the val_xxx() method and
   then checking for null in SEC_TO_TIME().
   QUARTER() was not returning 0 (as all the val_int()
   functions do when processing a NULL value).

Thanks for this quick patch ! 

Your team is wonderful (hard to say it in a correct english for a french person like me ;) ).

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/commits/19014

ChangeSet@1.2387, 2007-01-30 17:43:34+02:00, gkodinov@macbook.gmz +3 -0
  Bug #25643: SEC_TO_TIME function problem
   Checking for NULL before calling the val_xxx()
   methods only checks for such arguments that are 
   known to be NULLs at compile time. 
   The arguments that may or may not contain
   NULLs (e.g. function calls and possibly others)
   are not checked at all.
   Fixed by first calling the val_xxx() method and
   then checking for null in SEC_TO_TIME().
   In addition QUARTER() was not returning 0 (as all the 
   val_int() functions do when processing a NULL value).

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

Noted in 5.0.36, 5.1.16 changelogs.

The SEC_TO_TIME() and QUARTER() functions sometimes did not handle
NULL values correctly.