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 |
+-------------------------------------+----------+
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 | +-------------------------------------+----------+