MySQL Bugs: #33758: Got query result when using ORDER BY ASC, but empty result when using DESC

Bug #33758	Got query result when using ORDER BY ASC, but empty result when using DESC
Submitted:	9 Jan 2008 8:24	Modified:	2 Apr 2008 16:58
Reporter:	Dao-hui Chen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.0.51 and 5.1.22-rc	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
I got a problem when using ORDER BY. When I issue a query with ORDER BY colunm_a ASC, mysql returns correct data (1 record), but if I change to ORDER BY column_a DESC, I only got "Empty set". Using explain to check the query, both query(asc and desc) has 1 row. Dumping and reloading database has no effect. Tested on 5.0.51 + ubuntu 7.04 and 5.1.22-rc + Windows 2003 SP2 server.

How to repeat:
I leave my query and explain result in private comment. I can provide raw data if the developer need this.

Thank you for the bug report. Could you please provide the dump file of the
tables involved so we can test the offended queries?. Thanks in advance.

I've uploaded the raw data. The data has been reduced to the smallest column set that can reproduce this bug on my machine. Two strange things: if I drop some specific column (in this case fn), I can get the result correctly; and the explain result is changed when I drop column, the "rows" column in real dataset is 2, and in this reduced dataset is 3.

Thank you for the feedback.

mysql> select fr.md5key from fm_faxrec fr, fm_faxfile ff where fr.md5key=ff.md5key and
    -> ff.md5key='1a70b58e0cd94dfced359d270e12387c' and viewable='Y' order by rev desc;
Empty set (0.03 sec)

mysql> select fr.md5key from fm_faxrec fr, fm_faxfile ff where fr.md5key=ff.md5key and
    -> ff.md5key='1a70b58e0cd94dfced359d270e12387c' and viewable='Y' order by rev asc;
+----------------------------------+
| md5key                           |
+----------------------------------+
| 1a70b58e0cd94dfced359d270e12387c |
+----------------------------------+
1 row in set (0.00 sec)

I can confirm this bug in numerous tables in an my own databases.  I had though it was associated with sorting by date, as I have only seen it in tables where I am attempting to sort by a datetime field.

I'm actually using the CentOS provided mysql package (recompiled from the RHWAS I believe).  The source RPM is available at http://mirrors.kernel.org/centos/4/centosplus/SRPMS/mysql-5.0.54-1.el4.centos.src.rpm which includes the source version and all patches applied.  I don't expect you to troubleshoot their binaries, I'm merely supplying more information about the problem.

Bug did not seem apparent in mysql.com supplied binary distribution version 5.0.24, but I can't confirm this at this time.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/41944

ChangeSet@1.2570, 2008-02-08 19:17:48+04:00, ramil@mysql.com +1 -0
  Fix for bug #33758: Got query result when using ORDER BY ASC, but 
  empty result when using DESC
  
  Problem: fetching MyISAM keys we copy a key block pointer to the end of the key buffer.
  However, we don't take into account the pointer length calculatig the buffer size,
  that may leads to memory overwriting and in turn to unpredictable results.
  
  Fix: increase key buffer size by length of the key block pointer.
  
  Note: no simple test case.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42099

ChangeSet@1.2570, 2008-02-12 15:12:45+04:00, ramil@mysql.com +1 -0
  Fix for bug #33758: Got query result when using ORDER BY ASC, but 
  empty result when using DESC
  
  Problem: fetching MyISAM keys we copy a key block pointer to the end of the key buffer.
  However, we don't take into account the pointer length calculatig the buffer size,
  that may leads to memory overwriting and in turn to unpredictable results.
  
  Fix: increase key buffer size by length of the key block pointer.
  
  Note: no simple test case.

Bug #33348 was marked as a duplicate of this one.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        In some cases a query that produced a result set using ORDER BY ASC did
        not return any results when using ORDER BY DESC.

Bug #35963 was marked as duplicate of this one.