summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/include/mix1.inc8
-rw-r--r--mysql-test/r/innodb.result30
-rw-r--r--mysql-test/r/innodb_mysql.result21
-rw-r--r--mysql-test/r/join_outer_innodb.result4
-rw-r--r--sql/sql_select.cc10
-rw-r--r--sql/sql_table.cc44
6 files changed, 75 insertions, 42 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
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index d0b67e90afb..086bd714078 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -962,7 +962,7 @@ id select_type table type possible_keys key key_len ref rows Extra
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 and 3 and a < 1000;
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),(null),(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 --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
index 8f46df311ad..775151db9f0 100644
--- a/mysql-test/r/innodb_mysql.result
+++ b/mysql-test/r/innodb_mysql.result
@@ -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 --git a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
index e8a2d6f668b..24a11b12b03 100644
--- a/mysql-test/r/join_outer_innodb.result
+++ b/mysql-test/r/join_outer_innodb.result
@@ -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 --git a/sql/sql_select.cc b/sql/sql_select.cc
index 1c8ff12eb92..2e28663e383 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -6436,7 +6436,15 @@ make_join_readinfo(JOIN *join, ulonglong options)
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 --git a/sql/sql_table.cc b/sql/sql_table.cc
index e6d84d0ef45..d63a6d7d3fc 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6813,23 +6813,35 @@ copy_data_between_tables(TABLE *from,TABLE *to,
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 */
View Lorry Controller Status