MySQL Bugs: #63371: Invalid sort ordering in 5.1, innodb composite pkeys joined to subqueries

Bug #63371	Invalid sort ordering in 5.1, innodb composite pkeys joined to subqueries
Submitted:	21 Nov 2011 22:29	Modified:	22 Dec 2011 17:54
Reporter:	Joe Uhl	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.59, 5.1.61	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	sort ordering innodb

Description:
We discovered this bug after migrating some very large, old 5.0 databases to 5.1.49.  

This error does not occur in 5.0 or 5.5 - only in 5.1. 

For a certain query involving a couple joins (one notably to an empty subquery result) the order by against a pkey column is ignored - always ascending regardless of direction specified in query.

How to repeat:
I've tried to reduce this to a minimal example.  You can load this into any MySQL DB and it will work correctly in 5.0 and 5.5 but not in 5.1.

create table accounts (
  account_id int auto_increment, 
  user_id int, 
  primary key (account_id, user_id),
  key (user_id)
) engine=InnoDB default charset=utf8;
insert into accounts (user_id) values (3), (3), (3);

create table account_info (
  account_id int,
  user_id int,
  primary key (account_id, user_id)
) engine=InnoDB default charset=utf8;
insert into account_info (account_id, user_id) select account_id, user_id from accounts;

create table account_errors (
  account_id int,
  error_id int auto_increment,
  user_id int,
  primary key (error_id, account_id)
) engine=InnoDB default charset=utf8;

With that schema in place, this is the query that presents the problem:

select a.account_id from accounts a
  inner join account_info ai on (ai.user_id = a.user_id) and (ai.account_id = a.account_id)
  left join (select account_id, user_id from account_errors where user_id = 3) ae on ae.account_id = a.account_id and ae.user_id = a.user_id
where a.user_id = 3
order by a.account_id desc;

Note that the result is ordered by account_id ASC and not DESC as specified by the query.

Some notes about tweaking the test setup:

- This does not happen in MyISAM, only InnoDB on 5.1.  
- That subquery involved in the left join must match no results for this error to present.  If you insert a row into that table such that the subquery returns results the final result is ordered by account_id DESC as expected.
- If you drop the secondary key on user_id from the accounts table then the result is correctly ordered by account_id DESC.
- The explain plans between 5.0 and 5.1 match.

Suggested fix:
Would be ideal for this to match 5.0 and 5.5 behavior.  I did my best to search for a similar issue and came up empty.  If there is a duplicate I failed to identify I apologize.

Please, check if the same problem still happens with a newer version, 5.1.59.

This problem exists in both 5.1.49 (where it was initially seen) and 5.1.59 (to see if it was fixed in a newer build).

I've updated the version for the ticket to be more specific, sorry about that.

I can reproduce in 5.1.60 ('built-in' InnoDB - not 'plugin') with Winx64 server.

Result:

account_id
----------
         1
         2
         3

MySQL 5.5.18:

account_id
----------
         3
         2
         1

One last update, I had mentioned 5.0 not having this bug previously.  The specific version that I tested was 5.0.77.

Thank you for the bug report. Verified with current 5.1.61 on FC14:

[openxs@chief 5.1]$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.61-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table accounts (
    ->   account_id int auto_increment,
    ->   user_id int,
    ->   primary key (account_id, user_id),
    ->   key (user_id)
    -> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into accounts (user_id) values (3), (3), (3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table account_info (
    ->   account_id int,
    ->   user_id int,
    ->   primary key (account_id, user_id)
    -> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into account_info (account_id, user_id) select account_id, user_id from accounts;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table account_errors (
    ->   account_id int,
    ->   error_id int auto_increment,
    ->   user_id int,
    ->   primary key (error_id, account_id)
    -> ) engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.07 sec)

mysql> select a.account_id from accounts a
    ->   inner join account_info ai on (ai.user_id = a.user_id) and (ai.account_id =
    -> a.account_id)
    ->   left join (select account_id, user_id from account_errors where user_id = 3) ae on
    -> ae.account_id = a.account_id and ae.user_id = a.user_id
    -> where a.user_id = 3
    -> order by a.account_id desc;
+------------+
| account_id |
+------------+
|          1 |
|          2 |
|          3 |
+------------+
3 rows in set (0.02 sec)

Issue is fixed in 5.5 and will not be fixed in 5.1