MySQL Bugs: #26447: "ALTER TABLE .. ORDER" does not work with InnoDB and auto

Bug #26447	"ALTER TABLE .. ORDER" does not work with InnoDB and auto_increment keys
Submitted:	16 Feb 2007 16:45	Modified:	29 Feb 20:03
Reporter:	Christian Hammers (Silver Quality Contributor)
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0.36-BK, 5.0.30	OS:	Any
Assigned to:	Ramil Kalimullin	Target Version:
Tags:	qc
Triage:	D2 (Serious)

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.

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.

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

The issue will be fixed in the 5.1 branch.

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.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

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.