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: | |
| 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: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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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 | +-------------------------------------+----------+