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

[16 Jan 2007 9:18] Jean Lamy
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
[16 Jan 2007 9:53] Sveta Smirnova
Thank you for the report.

Verified as described on Linux using last BK sources. All versions are affected.
[16 Jan 2007 10:07] Valeriy Kravchuk
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)
[16 Jan 2007 11:21] Jean Lamy
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
[26 Jan 2007 14:51] 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/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).
[30 Jan 2007 15:17] Jean Lamy
Thanks for this quick patch ! 

Your team is wonderful (hard to say it in a correct english for a french person like me ;) ).
[30 Jan 2007 15:44] 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/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).
[3 Feb 2007 6:23] Igor Babaev
The fix has been pushed into 5.0.36, 5.1.16-beta main trees.
[13 Feb 2007 19:11] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.

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