MySQL Bugs: #31890: Partitions: ORDER BY DESC not working

Bug #31890	Partitions: ORDER BY DESC not working
Submitted:	26 Oct 2007 21:46	Modified:	14 Dec 2007 17:59
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.23-beta-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Alexey Botchkov	Target Version:	5.1+
Tags:	partitioning, order, by desc
Triage:	D2 (Serious)

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'.

Thank you for the bug report. Verified as described.

P2 as wrong order => bad data.

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.

Pushed into 5.1.23-rc

Pushed into 6.0.5-alpha

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.