Bug #31890 Partitions: ORDER BY DESC not working
Submitted: 26 Oct 2007 19:46 Modified: 14 Dec 2007 16:59
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.23-beta-debug OS:Linux (SUSE 10 64-bit)
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: by desc, order, partitioning
Triage: D2 (Serious)

[26 Oct 2007 19:46] Peter Gulutzan
Description:
I create a partitioned InnoDB table.
I insert 13 rows.
I select with an ORDER BY ... DESC clause.
The rows are not in order.

The title will remind you of
Bug #31001  	ORDER BY DESC in InnoDB not working
but this must be something different.
I cannot 'bk pull', but 'bk changes' says:
ChangeSet@1.2594, 2007-10-19 ...
The fix for Bug#31001 was pushed before 2007-10-19.

How to repeat:
CREATE TABLE t8
(int_column INT, char_column CHAR(5),
PRIMARY KEY(char_column,int_column))
engine=innodb
PARTITION BY KEY(char_column,int_column)
PARTITIONS 101;
INSERT INTO t8 (int_column, char_column) VALUES
(      39868 ,'zZZRW'),       
(     545592 ,'zZzSD'),       
(       4936 ,'zzzsT'),       
(       9274 ,'ZzZSX'),       
(     970185 ,'ZZzTN'),       
(     786036 ,'zZzTO'),       
(      37240 ,'zZzTv'),       
(     313801 ,'zzzUM'),       
(     782427 ,'ZZZva'),       
(     907955 ,'zZZvP'),       
(     453491 ,'zzZWV'),       
(     756594 ,'ZZZXU'),       
(     718061 ,'ZZzZH');       
SELECT * FROM t8 ORDER BY char_column DESC;

The result of the SELECT looks like:

mysql> SELECT * FROM t8 ORDER BY char_column DESC;
+------------+-------------+
| int_column | char_column |
+------------+-------------+
|     718061 | ZZzZH       |
|     756594 | ZZZXU       |
|     453491 | zzZWV       |
|      37240 | zZzTv       |
|     786036 | zZzTO       |
|     907955 | zZZvP       |
|     782427 | ZZZva       |
|     313801 | zzzUM       |
|     970185 | ZZzTN       |
|       9274 | ZzZSX       |
|       4936 | zzzsT       |
|     545592 | zZzSD       |
|      39868 | zZZRW       |
+------------+-------------+
13 rows in set (0.01 sec)

Notice that 'zZzTO' comes before 'zZZvP'.
[26 Oct 2007 22:13] Miguel Solorzano
Thank you for the bug report. Verified as described.
[29 Oct 2007 14:37] Timour Katchaounov
P2 as wrong order => bad data.
[14 Nov 2007 18:25] 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/37764

ChangeSet@1.2648, 2007-11-14 22:20:31+04:00, holyfoot@mysql.com +4 -0
  Bug #31890 Partitions: ORDER BY DESC in InnoDB not working.
  
  It's not InnoDB specific bug.
  Error is in QUEUE code, about the way we handle queue->max_at_top.
  It's either '0' or '-2' and we do '^' operation to get the proper
  direction. Though queue->compare() function can return '-2' as
  a result of comparison sometimes. So we'll get
  queue->compare() ^ queue->max_at_top == 0 (when max_at_top is -2)
  and _downheap() function code will go wrong way here:
  ...
      if (next_index < elements &&
          (queue->compare(queue->first_cmp_arg,
                          queue->root[next_index]+offset_to_key,
                          queue->root[next_index+1]+offset_to_key) ^
           queue->max_at_top) > 0)
        next_index++;
  ...
  
  Fixed by changing max_at_top to be either 1 or -1, doing
  '* max_at_top' to get proper direction.
[14 Dec 2007 8:19] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:22] Bugs System
Pushed into 6.0.5-alpha
[14 Dec 2007 16:59] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.23 and 6.0.5 changelogs as follows:

        ORDER BY ... DESC did not always work
        correctly when selecting from partitioned tables.

Removed InnoDB from Synopsis since developer notes indicate this wasn't an InnoDB-specific issue.