MySQL Bugs: #29714: Problems with VARCHAR primary key and order by desc

Bug #29714	Problems with VARCHAR primary key and order by desc
Submitted:	11 Jul 2007 8:07	Modified:	11 Aug 2007 8:53
Reporter:	Rick Mulder	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	5.0.41-1	OS:	Linux (Debian Sarge )
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	varchar primary key where clause order by

Description:
SELECT statements with WHERE condition doing a primary key lookup
on a VARCHAR primary key produce empty result sets. This only occurs when doing a order by DESC on an DATE field.

Any of the following makes the problem go away:
- use CHAR(n) instead of VARCHAR(n) for the PK
- use "ORDER BY CAST(geldig_vanaf AS DATETIME) DESC"  instead of "ORDER BY geldig_vanaf DESC"
- use HAVING instead of WHERE

This issue looks like issue #19956 which is is closed and fixed in 5.0.36. My problem occurs in MySQL-Server 5.0.41.-1.

How to repeat:
Import the attached dumpfile (default in the database called 'bedragen') including all test-records.

Execute the following query:
SELECT groep_id FROM bedragen.bedrag_waarde
WHERE bedrag_id = 'RegWWB_6lid4_datum'
ORDER BY geldig_vanaf ASC;

Result = 1 record!

Execute the following query:
SELECT groep_id FROM bedragen.bedrag_waarde
WHERE bedrag_id = 'RegWWB_6lid4_datum'
ORDER BY geldig_vanaf DESC;

Result = empty!

Suggested fix:
Probably investigating issue #19956 as this is a look-a-like.

Thank you for the report.

I can not repeat described behaviour using test data.

Please provide data I can repeat issue with. Also indicate accurate name of MySQL package you use (file name).

Thank you for the report.

I can not repeat described behaviour using test data.

Please provide data I can repeat issue with. Also indicate accurate name of MySQL package
you use (file name).
------------

That's strange!! This testfile should be enough.

The mysql-package installed:
ii  libmysqlclient 5.0.41-1.dotde MySQL database client library
ii  mysql-client-5 5.0.41-1.dotde MySQL database client binaries
ii  mysql-common   5.0.41-1.dotde MySQL database common files
ii  mysql-server-5 5.0.41-1.dotde MySQL database server binaries

Packages are comming from packages.dotdeb.org (for Sarge release).

So both queries have one result-row for you?

Thank you for the feedback.

Yes, I get 1 row in both cases.

Please try our packages accessible from http://dev.mysql.com/downloads/mysql/5.0.html and say us result. You can use generic Linux packages on Debian.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".