MySQL Bugs: #64176: Sorting issue in a formatted datetime

Bug #64176	Sorting issue in a formatted datetime
Submitted:	31 Jan 2012 9:43	Modified:	14 Feb 2012 16:22
Reporter:	Mark Anthony Ando	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.54	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have an issue regarding sorting a formatted datetime, the output is of course sorted, the real problem is that it doesn't include all items, I noticed this when I tried to sort my table in a desc fashion with reference to `Log Entry Data`, it is missing some records when the query finished executing, although the amount of missing data is dismal, when syncing it with another table it becomes rather disturbing since of course my query is assumed to acquire the last entry, but it doesn't and it interns acquires records that already existed within the table...

How to repeat:
CREATE TABLE `tbllog` (
  `Log Number` int(10) NOT NULL AUTO_INCREMENT,
  `Employee ID` int(10) DEFAULT NULL,
  `Log Entry Data` datetime DEFAULT NULL,
  PRIMARY KEY (`Log Number`),
  UNIQUE KEY `UniqeKey` (`Employee ID`,`Log Entry Data`),
  KEY `Employee ID` (`Employee ID`),
  KEY `tblEmployeetblLog` (`Employee ID`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1

insert several records, as for me at least 17k worth of records
issue this command

SELECT `Employee ID`, date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log Entry Data` FROM tblLog ORDER BY `Log Entry Data` DESC

Suggested fix:
simply replace the alias `Log Entry Data` with anything other than its complete name

SELECT `Employee ID`, date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log Data` FROM tblLog ORDER BY `Log Entry Data` DESC

this solved the issue and included all records for sorting...

What exact server version, 5.1.x, do you use?

sir the version is 5.1.54

Please try the last version released 5.1.61 and comment the result. Thanks.

as of the moment sir, I can't upgrade to any version later than 5.1.54, I'm having issues regarding compatibility with 4.1, with versions later than 5.1.54 so I'm sticking with it for the moment

Thank you for the feedback.

Please check if this is not the same as bug #19116: change your queries to 

SELECT count(*), date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log Entry Data`
FROM tblLog ORDER BY `Log Entry Data` DESC

and

SELECT count(*), date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log Data` FROM
tblLog ORDER BY `Log Entry Data` DESC

then compare results.

I can't find bug 19116 sorry I'm not really familiar with the search engine, however the I kinda think that issuing a count(*) will only slow down the code, since we are going to sort at the same time going to count. I appended LIMIT 1 to my query when I determined that it is already displaying the correct result since I'm only after the last log entry data  to begin with. But thanks for the recommendations sir

Sir Sveta Smirnova, I tried your code, I was incorrect to assume that the count would yield a slower result however the result was incorrect for both your recommendations, In fact instead of yielding the latest log entry data, it yielded one of the earliest log entry data

Thank you for the feedback.

But what is the results of 2 queries I asked you to run? These are not workaround, but I need these results to analyse reason of the problem.

You can find the bug at http://bugs.mysql.com/bug.php?id=19116

Sir the result is as I said, both of the queries you have asked me to perform yielded a result that outputs one of the earliest log entry data, it is the opposite of what I am trying to acquire sir... to further elaborate, if I had 20 records with 1 being the earliest and 20 being the latest, the query you have asked me to perform would yield a result around 3 or 2.

Thank you for the feedback.

> the query you have asked me to perform would yield a result around 3 or 2.

I don't need "a result around", but copy-paste of both queries and their return results in your environment.

Finally read that bug 19116, and I now understand that it is not a bug, since to begin with value returned by date format is not a date but a String.

As for your request sir Sveta Smirnova

SELECT count(*), date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log
Data` FROM
tblLog ORDER BY `Log Entry Data` DESC

12732	02/14/2011 08:10:51 AM

then the other query

SELECT count(*), date_format(`Log Entry Data`, '%m/%d/%Y %r') as `Log
Entry Data`
FROM tblLog ORDER BY `Log Entry Data` DESC

12732	02/14/2011 08:10:51 AM

Thank you for the feedback.

Closing as "Not a Bug" because last comment.