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: Sergey Glukhov
Tags: DESC, dump, import, order, regression, sort, wrong
Triage: Triaged: D2 (Serious)

[23 Feb 2010 17:49] Johannes Hammoud
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
[23 Feb 2010 17:50] Johannes Hammoud
Dump with single table to reproduce the bug

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

[23 Feb 2010 18:55] Miguel Solorzano
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>
[4 May 2010 7:03] Pete Yandell
I ran the above test on 5.1.38 (on OS X 10.6), and saw the same problem.
[5 May 2010 4:41] Pete Yandell
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.
[7 Jun 2010 9:48] 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/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.
[10 Jun 2010 8:22] 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/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.
[30 Jun 2010 6:19] 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/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
[30 Jun 2010 13:03] 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/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
[19 Jul 2010 14:35] Bugs System
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)
[20 Jul 2010 2:42] Paul Dubois
Noted in 5.1.49 changelog.

Some ORDER BY ... DESC queries sorted incorrectly.

Setting report to Need Merge pending further pushes.
[23 Jul 2010 12:26] Bugs System
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)
[23 Jul 2010 12:33] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[31 Jul 2010 16:45] Paul Dubois
Noted in 5.5.6 changelog.
[14 Oct 2010 8:33] Bugs System
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)
[14 Oct 2010 8:49] Bugs System
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)
[14 Oct 2010 9:03] Bugs System
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)
[14 Oct 2010 14:12] Jon Stephens
Already documented in the 5.1.49 changelog; no additional changelog entries required. Set back to Closed state.