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
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[9 Jan 2008 8:24] Dao-hui Chen
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.
[9 Jan 2008 9:43] Miguel Solorzano
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.
[10 Jan 2008 1:20] Dao-hui Chen
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.
[11 Jan 2008 11:46] Miguel Solorzano
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)
[30 Jan 2008 20:03] Kevan Benson
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.
[8 Feb 2008 15:18] Bugs System
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.
[12 Feb 2008 11:12] Bugs System
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.
[14 Feb 2008 13:09] Georgi Kodinov
Bug #33348 was marked as a duplicate of this one.
[27 Mar 2008 11:17] Bugs System
Pushed into 5.1.24-rc
[27 Mar 2008 11:20] Bugs System
Pushed into 5.0.60
[27 Mar 2008 17:49] Bugs System
Pushed into 6.0.5-alpha
[2 Apr 2008 16:58] Jon Stephens
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.
[10 Apr 2008 18:25] Sveta Smirnova
Bug #35963 was marked as duplicate of this one.