MySQL Bugs: #51431: Wrong sort order after import of dump file

Bug #51431	Wrong sort order after import of dump file
Submitted:	23 Feb 2010 17:49	Modified:	14 Oct 2010 14:12
Reporter:	Johannes Hammoud	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.44	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	DESC, dump, import, order, regression, sort, wrong

Description:
I detect that descend sorting does not work in some special cases.
This happens after import a dump and execute an select with 2 where + order desc + limit.
Could not find an solution or reason for this special behaviour, so i opened this bug and tell what i have tried till now:

TestCase1
Select with only one where clause works correct

select * from TEST
where F_ID = 1
order by T_ID desc
limit 5;

Expected: desc
Result: desc

TestCase2:
Select with two where clause FAILS: sort order is asc instead desc

select * from TEST
where F_ID = 1 and T_STATUS = true
order by T_ID desc
limit 5;

Expected: desc
Result: asc

TestCase3:
Same as TestCase2 but without limit works correct again

select * from TEST
where F_ID = 1 and T_STATUS = true
order by T_ID desc;

Expected: desc
Result: desc

As i did not found any bugs to the sql statement as reason for the wrong sort order of TestCase2, 
i thought this problem could depend to the import, so i tried this additional things:

TestCase4:
Just tried to reduce the rows inside the dump file.
So delete just the last row with Primary T_ID=27 and import the lesser rows newly
=> The select of TestCase2 works now correct

TestCase5:
Import all rows of the dumpfile again (included the T_ID=27 row) - but change key in dump file from:
UNIQUE KEY `T_U01` (`F_ID`,`T_NUMBER`),
to:
KEY `T_U01` (`F_ID`),
=> The select of TestCase2 works also correct

TestCase6:
Copy the "create table" statement from import file and execute it from a sql client
Copy the "insert" statements from import file and execute it from a sql client
=> The select of TestCase2 works now correct

So i think the import does build the index wrong or something else.
At least i tried this on serveral versions:

Could repeat the bug with:
Debian 5 + MySQL 5.1.39
Windows Vista + MySQL 5.1.39
Windows Vista + MySQL 5.1.44

TestCase works correct in:
Windows Vista + MySQL 5.0.15

How to repeat:
1. Import the the attached dump file used command: 
"mysql -h localhost -P 3306 -u root -pmysql TestDB_local < Test.dump"

2. Execute the TestCase2 with two where + order desc + limit
select * from TEST
where F_ID = 1 and T_STATUS = true
order by T_ID desc
limit 5;

3. The result is in ascending order instead of descending

Dump with single table to reproduce the bug

Attachment: TEST.dump (application/octet-stream, text), 2.87 KiB.

Thank you for the bug report.

C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.45-Win X64-log Source distribution

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

mysql 5.1 >use testdb_local
Database changed
mysql 5.1 >select * from TEST
    -> where F_ID = 1 and T_STATUS = true
    -> order by T_ID desc
    -> limit 5;
+------+------+----------+----------+
| T_ID | F_ID | T_NUMBER | T_STATUS |
+------+------+----------+----------+
|    1 |    1 |      991 |        1 |
|    2 |    1 |      992 |        1 |
|    3 |    1 |      993 |        1 |
|    4 |    1 |      994 |        1 |
|    5 |    1 |      995 |        1 |
+------+------+----------+----------+
5 rows in set (0.00 sec)

mysql 5.1 >exit
Bye

C:\DBS>50

C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-Win X64-log Source distribution

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

mysql 5.0 > create database TestDB_local;
Query OK, 1 row affected (0.00 sec)

mysql 5.0 > use testdb_local
Database changed
mysql 5.0 > source c:/bugs/test.dump
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

<cut>

Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > select * from TEST
    -> where F_ID = 1 and T_STATUS = true
    -> order by T_ID desc
    -> limit 5;
+------+------+----------+----------+
| T_ID | F_ID | T_NUMBER | T_STATUS |
+------+------+----------+----------+
|   27 |    1 |     9922 |        1 |
|   26 |    1 |     9921 |        1 |
|   25 |    1 |     9920 |        1 |
|   24 |    1 |     9919 |        1 |
|   23 |    1 |     9918 |        1 |
+------+------+----------+----------+
5 rows in set (0.01 sec)

mysql 5.0 >

C:\bugs\mysql-5.1.23a-maria-alpha-winx64>bin\mysql -uroot --port=3321
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23a-maria-alpha-community-maria MySQL Community Server [Maria] (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database TestDB_local;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb_local
Database changed
mysql> source c:/bugs/test.dump
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from TEST
    -> where F_ID = 1 and T_STATUS = true
    -> order by T_ID desc
    -> limit 5;
+------+------+----------+----------+
| T_ID | F_ID | T_NUMBER | T_STATUS |
+------+------+----------+----------+
|   27 |    1 |     9922 |        1 |
|   26 |    1 |     9921 |        1 |
|   25 |    1 |     9920 |        1 |
|   24 |    1 |     9919 |        1 |
|   23 |    1 |     9918 |        1 |
+------+------+----------+----------+
5 rows in set (0.00 sec)

mysql>

I ran the above test on 5.1.38 (on OS X 10.6), and saw the same problem.

We're seeing this issue intermittently when running the tests that back the envato.com marketplace sites (about 40 millions hits per month.)

The table being affected is:

CREATE TABLE `lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txn_id` varchar(255) DEFAULT NULL,
  `account` varchar(255) DEFAULT NULL,
  `scope` varchar(255) DEFAULT NULL,
  `code` varchar(255) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  `partner_id` int(11) DEFAULT NULL,
  `partner_account` varchar(255) DEFAULT NULL,
  `partner_scope` varchar(255) DEFAULT NULL,
  `meta` varchar(255) DEFAULT NULL,
  `detail_id` int(11) DEFAULT NULL,
  `detail_type` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lines_account_idx` (`account`),
  KEY `lines_scope_account_idx` (`scope`,`account`),
  KEY `lines_account_code_created_at_idx` (`account`,`code`,`created_at`),
  KEY `lines_account_id_idx` (`account`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The following query is the one that's giving us problems:

SELECT * FROM `lines` WHERE (account = 'earnings' AND scope = '602') ORDER BY id DESC;

Whether the query sorts correctly depends on which index the query optimiser decides to use. After a query returning incorrectly sorted results, an EXPLAIN on the query gives:

+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys                                                                                    | key               | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | lines | range | lines_account_code_created_at_idx,lines_account_id_idx,lines_account_idx,lines_scope_account_idx | lines_account_idx | 768     | NULL |    1 | Using where |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------+-------------------+---------+------+------+-------------+

If I then do an ANALYZE TABLE `lines`, the query will start returning correctly sorted results, and EXPLAIN now gives:

EXPLAIN SELECT * FROM `lines` WHERE (account = 'earnings' AND scope = '602') ORDER BY id DESC;
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                                    | key                               | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | lines | ref  | lines_account_code_created_at_idx,lines_account_id_idx,lines_account_idx,lines_scope_account_idx | lines_account_code_created_at_idx | 768     | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+--------------------------------------------------------------------------------------------------+-----------------------------------+---------+-------+------+-----------------------------+

Unfortunately I haven't got a data set that will reproducibly get the table into a state where the query is failing.

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/110323

3405 Sergey Glukhov	2010-06-07
      Bug#51431 Wrong sort order after import of dump file
      The problem is that QUICK_SELECT_DESC behaviour depends
      on used_key_parts value which can be bigger than selected
      best_key_parts value if an engine supports clustered key.
      But used_key_parts is overwritten with best_key_parts
      value that prevents from correct selection of index
      access method. The fix is to preserve used_key_parts
      value for further use in QUICK_SELECT_DESC.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        preserve used_key_parts value for further use in QUICK_SELECT_DESC.

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/110691

3422 Sergey Glukhov	2010-06-10
      Bug#51431 Wrong sort order after import of dump file
      The problem is that QUICK_SELECT_DESC behaviour depends
      on used_key_parts value which can be bigger than selected
      best_key_parts value if an engine supports clustered key.
      But used_key_parts is overwritten with best_key_parts
      value that prevents from correct selection of index
      access method. The fix is to preserve used_key_parts
      value for further use in QUICK_SELECT_DESC.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        preserve used_key_parts value for further use in QUICK_SELECT_DESC.

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/112515

3453 Sergey Glukhov	2010-06-30
      Bug#51431 Wrong sort order after import of dump file
      The problem is that QUICK_SELECT_DESC behaviour depends
      on used_key_parts value which can be bigger than selected
      best_key_parts value if an engine supports clustered key.
      But used_key_parts is overwritten with best_key_parts
      value that prevents from correct selection of index
      access method. The fix is to preserve used_key_parts
      value for further use in QUICK_SELECT_DESC.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        preserve used_key_parts value for further use in QUICK_SELECT_DESC

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/112578

3454 Sergey Glukhov	2010-06-30
      Bug#51431 Wrong sort order after import of dump file
      The problem is that QUICK_SELECT_DESC behaviour depends
      on used_key_parts value which can be bigger than selected
      best_key_parts value if an engine supports clustered key.
      But used_key_parts is overwritten with best_key_parts
      value that prevents from correct selection of index
      access method. The fix is to preserve used_key_parts
      value for further use in QUICK_SELECT_DESC.
     @ mysql-test/r/innodb_mysql.result
        test case
     @ mysql-test/t/innodb_mysql.test
        test case
     @ sql/sql_select.cc
        preserve used_key_parts value for further use in QUICK_SELECT_DESC

Pushed into 5.1.49 (revid:build@mysql.com-20100719143034-omcma40sblwmay3x) (version source revid:sergey.glukhov@sun.com-20100630130625-9p7mu3m3x8a04idz) (merge vers: 5.1.48) (pib:16)

Noted in 5.1.49 changelog.

Some ORDER BY ... DESC queries sorted incorrectly.

Setting report to Need Merge pending further pushes.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)

Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)

Noted in 5.5.6 changelog.

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:21)

Already documented in the 5.1.49 changelog; no additional changelog entries required. Set back to Closed state.