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.


mysql-test/include/mix1.inc:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - test case.
mysql-test/r/innodb.result:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - results adjusted.
mysql-test/r/innodb_mysql.result:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - results adjusted.
mysql-test/r/join_outer_innodb.result:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - results adjusted.
sql/sql_select.cc:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - use the clustered index for a table scan (if any) as it's faster than
      using a secondary index.
sql/sql_table.cc:
  Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
  and auto_increment keys
    - ALTER TABLE ... ORDER BY doesn't make sence if there's a 
      user-defined clustered index in the table. Ignore it in such cases
      and raise a warning.

1 files changed, 8 insertions, 0 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
index 4d910e86e0f..2887a1ac40a 100644
--- a/mysql-test/include/mix1.inc
+++ b/mysql-test/include/mix1.inc
@@ -1189,4 +1189,12 @@ if ($test_foreign_keys)
   DROP TABLE t1;
 }
 
+#
+# Bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB and 
+#             auto_increment keys
+#
+create table t1 (a int auto_increment primary key) engine=innodb;
+alter table t1 order by a;
+drop table t1;
+
 --echo End of 5.1 tests