bk commit into 5.1 tree (ramil:1.2612) BUG#26447

[ramil]

Below is the list of changes that have just been committed into a local
5.1 repository of ram. When ram does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-11-07 19:59:58+04:00, ramil@stripped +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.

  mysql-test/include/mix1.inc@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +8 -0
    Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
    and auto_increment keys
      - test case.

  mysql-test/r/innodb.result@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +15 -15
    Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
    and auto_increment keys
      - results adjusted.

  mysql-test/r/innodb_mysql.result@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +13 -8
    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@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +2
-2
    Fix for bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB 
    and auto_increment keys
      - results adjusted.

  sql/sql_select.cc@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +9 -1
    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@stripped, 2007-11-07 19:59:56+04:00, ramil@stripped +28 -16
    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.

diff -Nrup a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
--- a/mysql-test/include/mix1.inc	2007-10-29 20:31:01 +04:00
+++ b/mysql-test/include/mix1.inc	2007-11-07 19:59:56 +04:00
@@ -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
diff -Nrup a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
--- a/mysql-test/r/innodb.result	2007-08-28 05:33:52 +05:00
+++ b/mysql-test/r/innodb.result	2007-11-07 19:59:56 +04:00
@@ -962,7 +962,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	index	NULL	b	4	NULL	#	Using index
 explain select a,b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	b	4	NULL	#	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	#	
 explain select a,b,c from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
@@ -1177,14 +1177,14 @@ UPDATE t1 set a=a+100 where b between 2 
 SELECT * from t1;
 a	b
 1	1
-102	2
-103	3
 4	4
 5	5
 6	6
 7	7
 8	8
 9	9
+102	2
+103	3
 drop table t1;
 CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
 CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
@@ -1208,7 +1208,6 @@ a	b
 update t1,t2 set t1.a=t1.a+100 where t1.a=101;
 select * from t1;
 a	b
-201	1
 102	2
 103	3
 104	4
@@ -1220,10 +1219,11 @@ a	b
 110	10
 111	11
 112	12
+201	1
 update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 a	b
-201	1
+102	12
 103	3
 104	4
 105	5
@@ -1233,34 +1233,34 @@ a	b
 109	9
 110	10
 111	11
-102	12
 112	12
+201	1
 update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
 select * from t1;
 a	b
-201	1
+102	12
 103	5
 104	6
-106	6
 105	7
+106	6
 107	7
 108	8
 109	9
 110	10
 111	11
-102	12
 112	12
+201	1
 select * from t2;
 a	b
 1	1
 2	2
+3	13
+4	14
+5	15
 6	6
 7	7
 8	8
 9	9
-3	13
-4	14
-5	15
 drop table t1,t2;
 CREATE TABLE t2 (   NEXT_T         BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
 CREATE TABLE t1 (  B_ID           INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
@@ -1311,11 +1311,11 @@ insert into t1 (id) values (null),(null)
 update t1 set fk=69 where fk is null order by id limit 1;
 SELECT * from t1;
 id	fk
+1	69
 2	NULL
 3	NULL
 4	NULL
 5	NULL
-1	69
 drop table t1;
 create table t1 (a int not null, b int not null, key (a));
 insert into t1 values
(1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
@@ -2444,8 +2444,8 @@ insert into t1 (b) values (1);
 replace into t1 (b) values (2), (1), (3);
 select * from t1;
 a	b
-3	1
 2	2
+3	1
 4	3
 truncate table t1;
 insert into t1 (b) values (1);
@@ -2454,8 +2454,8 @@ replace into t1 (b) values (1);
 replace into t1 (b) values (3);
 select * from t1;
 a	b
-3	1
 2	2
+3	1
 4	3
 drop table t1;
 create table t1 (rowid int not null auto_increment, val int not null,primary
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-10-29 20:31:01 +04:00
+++ b/mysql-test/r/innodb_mysql.result	2007-11-07 19:59:56 +04:00
@@ -352,13 +352,13 @@ EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
+1	SIMPLE	t1	index	PRIMARY,name	PRIMARY	4	NULL	3	Using where
 1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
 EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%' OR FALSE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
@@ -1260,11 +1260,11 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	b
-key_len	5
+key	PRIMARY
+key_len	4
 ref	NULL
 rows	3
-Extra	Using index; Using filesort
+Extra	Using filesort
 SELECT * FROM t1 ORDER BY b ASC, a DESC;
 a	b
 1	1
@@ -1276,11 +1276,11 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	b
-key_len	5
+key	PRIMARY
+key_len	4
 ref	NULL
 rows	3
-Extra	Using index; Using filesort
+Extra	Using filesort
 SELECT * FROM t1 ORDER BY b DESC, a ASC;
 a	b
 2	2
@@ -1470,4 +1470,9 @@ t2	CREATE TABLE `t2` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 DROP TABLE t2;
 DROP TABLE t1;
+create table t1 (a int auto_increment primary key) engine=innodb;
+alter table t1 order by a;
+Warnings:
+Warning	1105	ORDER BY ignored as there is a user-defined clustered index in the table
't1'
+drop table t1;
 End of 5.1 tests
diff -Nrup a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
--- a/mysql-test/r/join_outer_innodb.result	2006-06-11 12:04:17 +05:00
+++ b/mysql-test/r/join_outer_innodb.result	2007-11-07 19:59:56 +04:00
@@ -8,12 +8,12 @@ EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
 WHERE t1.name LIKE 'A%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
+1	SIMPLE	t1	index	PRIMARY,name	PRIMARY	4	NULL	3	Using where
 1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
 EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
 WHERE t1.name LIKE 'A%' OR FALSE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-10-23 01:32:19 +05:00
+++ b/sql/sql_select.cc	2007-11-07 19:59:56 +04:00
@@ -6436,7 +6436,15 @@ make_join_readinfo(JOIN *join, ulonglong
 	  else if (!table->covering_keys.is_clear_all() &&
 		   !(tab->select && tab->select->quick))
 	  {					// Only read index tree
-	    tab->index=find_shortest_key(table, & table->covering_keys);
+	    /*
+	      See bug #26447: "Using the clustered index for a table scan
+	      is always faster than using a secondary index".
+	    */
+            if (table->s->primary_key != MAX_KEY &&
+                table->file->primary_key_is_clustered())
+              tab->index= table->s->primary_key;
+            else
+              tab->index=find_shortest_key(table, & table->covering_keys);
 	    tab->read_first_record= join_read_first;
 	    tab->type=JT_NEXT;		// Read with index_first / index_next
 	  }
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc	2007-10-29 17:30:33 +04:00
+++ b/sql/sql_table.cc	2007-11-07 19:59:56 +04:00
@@ -6813,23 +6813,35 @@ copy_data_between_tables(TABLE *from,TAB
 
   if (order)
   {
-    from->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
-					      MYF(MY_FAE | MY_ZEROFILL));
-    bzero((char*) &tables,sizeof(tables));
-    tables.table= from;
-    tables.alias= tables.table_name= from->s->table_name.str;
-    tables.db=    from->s->db.str;
-    error=1;
+    if (to->s->primary_key != MAX_KEY &&
to->file->primary_key_is_clustered())
+    {
+      char warn_buff[MYSQL_ERRMSG_SIZE];
+      my_snprintf(warn_buff, sizeof(warn_buff), 
+                  "ORDER BY ignored as there is a user-defined clustered index"
+                  " in the table '%-.192s'", from->s->table_name.str);
+      push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN, ER_UNKNOWN_ERROR,
+                   warn_buff);
+    }
+    else
+    {
+      from->sort.io_cache=(IO_CACHE*) my_malloc(sizeof(IO_CACHE),
+                                                MYF(MY_FAE | MY_ZEROFILL));
+      bzero((char *) &tables, sizeof(tables));
+      tables.table= from;
+      tables.alias= tables.table_name= from->s->table_name.str;
+      tables.db= from->s->db.str;
+      error= 1;
 
-    if (thd->lex->select_lex.setup_ref_array(thd, order_num) ||
-	setup_order(thd, thd->lex->select_lex.ref_pointer_array,
-		    &tables, fields, all_fields, order) ||
-	!(sortorder=make_unireg_sortorder(order, &length, NULL)) ||
-	(from->sort.found_records = filesort(thd, from, sortorder, length,
-					     (SQL_SELECT *) 0, HA_POS_ERROR, 1,
-					     &examined_rows)) ==
-	HA_POS_ERROR)
-      goto err;
+      if (thd->lex->select_lex.setup_ref_array(thd, order_num) ||
+          setup_order(thd, thd->lex->select_lex.ref_pointer_array,
+                      &tables, fields, all_fields, order) ||
+          !(sortorder= make_unireg_sortorder(order, &length, NULL)) ||
+          (from->sort.found_records= filesort(thd, from, sortorder, length,
+                                              (SQL_SELECT *) 0, HA_POS_ERROR,
+                                              1, &examined_rows)) ==
+          HA_POS_ERROR)
+        goto err;
+    }
   };
 
   /* Tell handler that we have values for all columns in the to table */