Bug #4036 multiple SELECT DATE_FORMAT, incorrect results
Submitted: 7 Jun 2004 12:41 Modified: 8 Jun 2004 10:04
Reporter: Carl Franks Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.18-max-debug OS:Windows (Windows XP)
Assigned to: Sergei Glukhov CPU Architecture:Any

[7 Jun 2004 12:41] Carl Franks
Description:
When SELECTing from a table with more than one 'time' columns, if DATE_FORMAT is used on the first or on both time columns, an incorrect result is returned for the first column.

Have tested using different format strings for the two time columns, still returns incorrect result.

First came across this on mysql 3.23.49-log, sun solaris. Have confirmed same results with 4.0.18, Windows.

How to repeat:
mysql> CREATE TABLE `carltest` (`start_time` time default NULL, `end_time` time default NULL) TYPE=MyISAM;

mysql> INSERT INTO `carltest` (`start_time`, `end_time` ) VALUES ('09:00', '12:00');

mysql> SELECT * FROM `carltest` WHERE 1 LIMIT 0 , 30;
+------------+----------+
| start_time | end_time |
+------------+----------+
| 09:00:00   | 12:00:00 |
+------------+----------+

// INCORRECT RESULT FOLLOWS...

mysql> SELECT DATE_FORMAT(start_time, "%l.%i %p") , DATE_FORMAT(end_time, "%l.%i %p") 
FROM `carltest` WHERE 1 LIMIT 0 , 30;
+-------------------------------------+-----------------------------------+
| DATE_FORMAT(start_time, "%l.%i %p") | DATE_FORMAT(end_time, "%l.%i %p") |
+-------------------------------------+-----------------------------------+
| 12.00 AM                            | 12.00 AM                          |
+-------------------------------------+-----------------------------------+

// OR...

mysql> SELECT DATE_FORMAT(start_time, "%l.%i %p") , end_time 
FROM `carltest` WHERE 1 LIMIT 0 , 30;
+-------------------------------------+----------+
| DATE_FORMAT(start_time, "%l.%i %p") | end_time |
+-------------------------------------+----------+
| 12.00 AM                            | 12:00:00 |
+-------------------------------------+----------+
[7 Jun 2004 12:55] Carl Franks
Update:
Have confirmed the same results using the latest binary, 4.0.20a-debug on Windows XP.
[7 Jun 2004 17:34] Sergei Glukhov
bk commit into 3.23 tree (1.1441)
[8 Jun 2004 10:04] Sergei Glukhov
The right bug comment should be 
'DATE_FORMAT(time_column,...) - incorrect result'.
The issue was that function DATE_FORMAT handled time column as date.
fixed in 3.23 source tree.