MySQL Bugs: #16758: DATE_FORMAT(time_column,...)

Bug #16758	DATE_FORMAT(time_column,...) - incorrect result
Submitted:	24 Jan 2006 22:44	Modified:	2 Feb 2006 19:59
Reporter:	Mark V	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.0.18-nt	OS:	Windows (WIN-XP-Pro (latest patches))
Assigned to:		CPU Architecture:	Any

Description:
In an update statement the DATE_FORMAT( t1.time,'%H:%i')  and TIME_FORMAT( t1.time,'%H:%i') can incorrectly
return '00:00:00' when t1.time is a whole minute.

This may be related to bug #7622 ?

The code below generates (on my system at least) without any error messages, but 4 warnings:
+------------+----------+-----------+-----+
| date       | time     | trunctime | sec |
+------------+----------+-----------+-----+
| 2005-10-03 | 10:13:58 | 10:13:00  | 118 |
| 2005-10-03 | 10:13:59 | 10:13:00  | 119 |
| 2005-10-03 | 10:14:00 | 00:00:00  |   0 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
+------------+----------+-----------+-----+

The workaround gives the correct result, with 1 warning:
+------------+----------+-----------+-----+
| date       | time     | trunctime | sec |
+------------+----------+-----------+-----+
| 2005-10-03 | 10:13:58 | 10:13:00  | 118 |
| 2005-10-03 | 10:13:59 | 10:13:00  | 119 |
| 2005-10-03 | 10:14:00 | 10:14:00  |  60 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
+------------+----------+-----------+-----+

I'm not a (My)SQL expert so the SQL below my be 'illegal'?

How to repeat:
# Created: 25/01/2006
#
# Description:
# Set out a possible bug in DATE_FORMAT and TIME_FORMAT behaviour.
#

CREATE DATABASE IF NOT EXISTS scratch
    DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs;

USE scratch; 

# 
# Required table is created.
# 

SET FOREIGN_KEY_CHECKS=0;   # Ignore foreign key constraints during the load operation.

DROP TABLE IF EXISTS scratch.dtf_behavior;

CREATE TABLE scratch.dtf_behavior 
        (
        date DATE NOT NULL,
	time TIME NOT NULL	
        )
    ENGINE=MyISAM
    ROW_FORMAT=FIXED    # For MyISAM only - improves performance, less curruption prone.
    COMMENT="First stop for trade data after TAQ extraction."
    PACK_KEYS=1         # Smaller indexes - makes updates slower and reads faster
    DELAY_KEY_WRITE=1   # Delay key updates for the table until the table is closed (MyISAM only).
    ;

#
# Insert data 
#
insert into dtf_behavior(date, time) values ('2005-10-03', '10:13:58');
insert into dtf_behavior(date,time) values ('2005-10-03', '10:13:59');
insert into dtf_behavior(date,time) values ('2005-10-03', '10:14:00');
insert into dtf_behavior(date,time) values ('2005-10-03', '10:14:01');
insert into dtf_behavior(date,time) values ('2005-10-03', '10:14:01');

ALTER TABLE scratch.dtf_behavior
     ADD COLUMN trunctime TIME NOT NULL, 
     ADD COLUMN sec INT UNSIGNED NOT NULL;

UPDATE scratch.dtf_behavior t1
    SET t1.trunctime = DATE_FORMAT( t1.time,'%H:%i'),
	t1.sec = case when TIME_FORMAT( t1.time, '%p') = 'AM' then @s + 60 else @s + 120 end
    WHERE @s := CAST( TIME_FORMAT( t1.time, '%S'  ) AS UNSIGNED );

SELECT * FROM scratch.dtf_behavior t1;

#
# The workaround:
#
UPDATE scratch.dtf_behavior t1
    SET t1.trunctime = DATE_FORMAT( t1.time,'%H:%i'),
	t1.sec = case when TIME_FORMAT( t1.time, '%p') = 'AM' then CAST( TIME_FORMAT( t1.time, '%S'  ) AS UNSIGNED ) + 60 else CAST( TIME_FORMAT( t1.time, '%S'  ) AS UNSIGNED ) + 120 end;
   # WHERE @s := CAST( TIME_FORMAT( t1.time, '%S'  ) AS UNSIGNED );

SELECT * FROM scratch.dtf_behavior t1;

Suggested fix:
See the work around above.

Correction:
There are no warnings issued in either case.  I misread the MySQL output - apologies.

Thanks for your bug report.  I reproduced this under XP and FC4 using 5.0.18:

mysql> SELECT * FROM scratch.dtf_behavior t1;
+------------+----------+-----------+-----+
| date       | time     | trunctime | sec |
+------------+----------+-----------+-----+
| 2005-10-03 | 10:13:58 | 10:13:00  | 118 |
| 2005-10-03 | 10:13:59 | 10:13:00  | 119 |
| 2005-10-03 | 10:14:00 | 00:00:00  |   0 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
| 2005-10-03 | 10:14:01 | 10:14:00  |  61 |
+------------+----------+-----------+-----+
5 rows in set (0.00 sec)

mysql>

Sorry to interrupt and give you a comment, though I'm not 
a MySQL engineer but a mere user.

Mark, you wrote like this;
UPDATE scratch.dtf_behavior SET ...
WHERE @s := CAST( TIME_FORMAT( t1.time, '%S'  ) AS UNSIGNED );

When t1.time is '10:14:00', TIME_FORMAT(t1.time,'%S') returns a string '00'.
And CAST(... AS UNSIGNED) converts it to integer 0.
And the variable @s is set to 0, because The ':=' is an assignment operator. 
Finally the where clause becomes "WHERE 0", and that row is NOT updated.

I say again what happened in order.

You added 2 columns, trunctime and sec, by ALTER TABLE statement.
At that time, these 2 columns of all 5 rows were automatically 
set to 00:00:00 and 0 respectively.

Next, you tried to update these columns by UPDATE statement.
But only 4 rows were affected (mysql client showed this message, didn't it?). 
The rest 1 row was not updated (i.e remained as 00:00:00 and 0), 
because the where clause evaluation was false.

So everything works as designed, I think. My explanation makes sense?

Thanks for pointing that out. I've closed this bug, and set the severity indicator as low as possible.  Apologies for the wild goose chase.