Bug #26447 "ALTER TABLE .. ORDER" does not work with InnoDB and auto_increment keys
Submitted: 16 Feb 2007 15:45 Modified: 18 Jun 2010 1:01
Reporter: Christian Hammers (Silver Quality Contributor) (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.36-BK, 5.0.30 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: qc
Triage: D2 (Serious)

[16 Feb 2007 15:45] Christian Hammers
Description:
"ALTER TABLE .. ORDER BY .." does not work with InnoDB.
Instead it seems to prefer sorting the table using the varchar column..

bye,

-christian-

How to repeat:
Server version: 5.0.30-Debian_1~bpo.1-log Debian etch distribution

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

mysql> CREATE TABLE `t` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `name` varchar(255) default NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `name` (`name`)
    -> ) ENGINE=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t VALUES (2,"two"), (-1,"minus one"), (4,"four");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | four      | 
| -1 | minus one | 
|  2 | two       | 
+----+-----------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE t ORDER BY id;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | four      | 
| -1 | minus one | 
|  2 | two       | 
+----+-----------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE t ENGINE=myisam;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t ORDER BY id;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+----+-----------+
| id | name      |
+----+-----------+
| -1 | minus one | 
|  2 | two       | 
|  4 | four      | 
+----+-----------+
3 rows in set (0.00 sec)

Suggested fix:
Correct ALTER TABLE.
[16 Feb 2007 16:17] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.36-BK on Linux:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.36    |
+-----------+
1 row in set (0.01 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t (
    -> id int auto_increment primary key,
    -> name varchar(255),
    -> key k1(name)) engine=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t VALUES (2,'two'), (-1,'minus one'), (4,'four');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: k1
      key_len: 258
          ref: NULL
         rows: 3
        Extra: Using index
1 row in set (0.00 sec)

This is bug #1 here: why k1 index is used? To select ALL ROWS from InnoDB table?

mysql> select * from t;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | four      |
| -1 | minus one |
|  2 | two       |
+----+-----------+
3 rows in set (0.01 sec)

mysql> alter table t order by id;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: k1
      key_len: 258
          ref: NULL
         rows: 3
        Extra: Using index
1 row in set (0.01 sec)

Same plan, and

mysql> select * from t;
+----+-----------+
| id | name      |
+----+-----------+
|  4 | four      |
| -1 | minus one |
|  2 | two       |
+----+-----------+
3 rows in set (0.00 sec)

same result. I am not sure this is a bug anyway, as nothing in http://dev.mysql.com/doc/refman/5.0/en/alter-table.html tells you that any specific order is guaranteed after execution of this statement. But it may worth mentioning that explicitely. So, let it be bug #2 here.

This is how one may get desired results:

mysql> select * from t ignore index(k1);
+----+-----------+
| id | name      |
+----+-----------+
| -1 | minus one |
|  2 | two       |
|  4 | four      |
+----+-----------+
3 rows in set (0.02 sec)

and:

mysql> explain select * from t ignore index(k1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra:
1 row in set (0.01 sec)

really shown that when ALL access method is used, rows are really come in "desired" order.
[19 Feb 2007 13:57] Heikki Tuuri
Hi!

This is a docs bug, an SQL optimizer bug, and an SQL interpreter bug.

The fact that SELECT * FROM t does not use the PRIMARY KEY (= clustered index)to retrieve the rows might be considered an optimizer bug. Using the clustered index for a table scan is always faster than using a secondary index.

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

"
ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.
"

The above makes no sense for InnoDB or BDB if there is a user-defined clustered index in the table.

The MySQL interpreter bug: it should give an error in:

ALTER TABLE ... ORDER BY;

if it is an InnoDB table and there is a PRIMARY KEY or a NOT NULL UNIQUE index in the table. InnoDB is not aware of the ORDER BY, and cannot give the error.

Regards,

Heikki
[7 Nov 2007 10:28] Ramil Kalimullin
The issue will be fixed in the 5.1 branch.
[7 Nov 2007 16:00] 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/37270

ChangeSet@1.2612, 2007-11-07 19:59:58+04:00, ramil@mysql.com +6 -0
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
  
  Problems: 
    1. ALTER TABLE ... ORDER BY... doesn't make sence if there's a 
       user-defined clustered index in the table.
    2. using a secondary index is slower than using a clustered one 
       for a table scan.
  
  Fixes:
    1. raise a warning.
    2. use the clustered index.
[7 Dec 2007 23:07] Bugs System
Pushed into 6.0.5-alpha
[7 Dec 2007 23:09] Bugs System
Pushed into 5.1.23-rc
[29 Feb 2008 19:03] Paul Dubois
Noted in 5.1.23, 6.0.5 changelogs.

It makes no sense to attempt to use ALTER TABLE ... ORDER BY to order
an InnoDB table if there is a user-defined clustered index, because
rows are always ordered by the clustered index. Such attempts now are
ignored and produce a warning.

Also, in some cases, InnoDB incorrectly used a secondary index when
the clustered index would produce a faster scan. EXPLAIN output now
indicates use of the clustered index (for tables that have one) as
lines with a type value of index, a key value of PRIMARY, and without
Using index in the Extra value.
[5 May 2010 15:18] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 2:54] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[6 May 2010 2:54] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 5:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:19] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:47] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 19:22] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 11:51] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:28] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:16] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)