6 files changed, 95 insertions, 10 deletions
diff --git a/mysql-test/r/ctype_collate.result b/mysql-test/r/ctype_collate.result
index a3e29d24264..add730fe68f 100644
--- a/mysql-test/r/ctype_collate.result
+++ b/mysql-test/r/ctype_collate.result
@@ -541,3 +541,50 @@ s2 CHAR(5) COLLATE latin1_swedish_ci);
 SELECT * FROM t1 WHERE s1 = s2;
 ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
 DROP TABLE t1;
+SET NAMES latin1;
+CREATE TABLE t1 
+(s1 char(10) COLLATE latin1_german1_ci,
+s2 char(10) COLLATE latin1_swedish_ci,
+KEY(s1),
+KEY(s2));
+INSERT INTO t1 VALUES ('a','a');
+INSERT INTO t1 VALUES ('b','b');
+INSERT INTO t1 VALUES ('c','c');
+INSERT INTO t1 VALUES ('d','d');
+INSERT INTO t1 VALUES ('e','e');
+INSERT INTO t1 VALUES ('f','f');
+INSERT INTO t1 VALUES ('g','g');
+INSERT INTO t1 VALUES ('h','h');
+INSERT INTO t1 VALUES ('i','i');
+INSERT INTO t1 VALUES ('j','j');
+EXPLAIN SELECT * FROM t1 WHERE s1='a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s1	s1	11	const	1	Using where
+EXPLAIN SELECT * FROM t1 WHERE s2='a';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s2	s2	11	const	1	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	s1	s1	11	const	1	Using where
+EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	2	Using where
+EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 IN  ('a','b' COLLATE latin1_german1_ci);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	2	Using where
+EXPLAIN SELECT * FROM t1 WHERE s2 IN  ('a','b' COLLATE latin1_german1_ci);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	s1	s1	11	NULL	1	Using where
+EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	s2	NULL	NULL	NULL	10	Using where
+DROP TABLE t1;
diff --git a/mysql-test/r/key_cache.result b/mysql-test/r/key_cache.result
index af3cf956222..b83a226776d 100644
--- a/mysql-test/r/key_cache.result
+++ b/mysql-test/r/key_cache.result
@@ -100,7 +100,7 @@ p	i	a
 4	3	zzzz
 update t1 set p=2 where p=1;
 update t2 set i=2 where i=1;
-cache index t1 keys (`primary`) in keycache1;
+cache index t1 key (`primary`) in keycache1;
 Table	Op	Msg_type	Msg_text
 test.t1	assign_to_keycache	status	OK
 explain select p from t1;
@@ -177,7 +177,7 @@ yyyy
 zzzz
 cache index t1 in unknown_key_cache;
 ERROR HY000: Unknown key cache 'unknown_key_cache'
-cache index t1 keys (unknown_key) in keycache1;
+cache index t1 key (unknown_key) in keycache1;
 Table	Op	Msg_type	Msg_text
 test.t1	assign_to_keycache	error	Key column 'unknown_key' doesn't exist in table
 test.t1	assign_to_keycache	status	Operation failed
diff --git a/mysql-test/r/preload.result b/mysql-test/r/preload.result
index bd7b828d5b9..f0b99a8d6f1 100644
--- a/mysql-test/r/preload.result
+++ b/mysql-test/r/preload.result
@@ -117,7 +117,7 @@ set session preload_buffer_size=1*1024;
 select @@preload_buffer_size;
 @@preload_buffer_size
 1024
-load index into cache t1, t2 keys (primary,b) ignore leaves;
+load index into cache t1, t2 key (primary,b) ignore leaves;
 Table	Op	Msg_type	Msg_text
 test.t1	preload_keys	status	OK
 test.t2	preload_keys	status	OK
@@ -141,7 +141,7 @@ show status like "key_read%";
 Variable_name	Value
 Key_read_requests	0
 Key_reads	0
-load index into cache t3, t2 keys (primary,b) ;
+load index into cache t3, t2 key (primary,b) ;
 Table	Op	Msg_type	Msg_text
 test.t3	preload_keys	error	Table 'test.t3' doesn't exist
 test.t2	preload_keys	status	OK
@@ -155,7 +155,7 @@ show status like "key_read%";
 Variable_name	Value
 Key_read_requests	0
 Key_reads	0
-load index into cache t3 keys (b), t2 keys (c) ;
+load index into cache t3 key (b), t2 key (c) ;
 Table	Op	Msg_type	Msg_text
 test.t3	preload_keys	error	Table 'test.t3' doesn't exist
 test.t2	preload_keys	error	Key column 'c' doesn't exist in table
diff --git a/mysql-test/t/ctype_collate.test b/mysql-test/t/ctype_collate.test
index 2d9a4be5b36..5916e3da241 100644
--- a/mysql-test/t/ctype_collate.test
+++ b/mysql-test/t/ctype_collate.test
@@ -156,3 +156,41 @@ CREATE TABLE t1
 --error 1266
 SELECT * FROM t1 WHERE s1 = s2;
 DROP TABLE t1;
+
+
+#
+# Test that optimizer doesn't use indexes with wrong collation
+#
+SET NAMES latin1;
+CREATE TABLE t1 
+(s1 char(10) COLLATE latin1_german1_ci,
+ s2 char(10) COLLATE latin1_swedish_ci,
+ KEY(s1),
+ KEY(s2));
+
+INSERT INTO t1 VALUES ('a','a');
+INSERT INTO t1 VALUES ('b','b');
+INSERT INTO t1 VALUES ('c','c');
+INSERT INTO t1 VALUES ('d','d');
+INSERT INTO t1 VALUES ('e','e');
+INSERT INTO t1 VALUES ('f','f');
+INSERT INTO t1 VALUES ('g','g');
+INSERT INTO t1 VALUES ('h','h');
+INSERT INTO t1 VALUES ('i','i');
+INSERT INTO t1 VALUES ('j','j');
+
+EXPLAIN SELECT * FROM t1 WHERE s1='a';
+EXPLAIN SELECT * FROM t1 WHERE s2='a';
+EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
+EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
+
+EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
+
+EXPLAIN SELECT * FROM t1 WHERE s1 IN  ('a','b' COLLATE latin1_german1_ci);
+EXPLAIN SELECT * FROM t1 WHERE s2 IN  ('a','b' COLLATE latin1_german1_ci);
+
+EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
+EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
+
+DROP TABLE t1;
diff --git a/mysql-test/t/key_cache.test b/mysql-test/t/key_cache.test
index b2bc57f3804..d9a2200a636 100644
--- a/mysql-test/t/key_cache.test
+++ b/mysql-test/t/key_cache.test
@@ -75,7 +75,7 @@ select * from t2;
 update t1 set p=2 where p=1;
 update t2 set i=2 where i=1;
 
-cache index t1 keys (`primary`) in keycache1;
+cache index t1 key (`primary`) in keycache1;
 
 explain select p from t1;
 select p from t1;
@@ -101,7 +101,7 @@ select a from t2;
 # Test some error conditions
 --error 1283
 cache index t1 in unknown_key_cache;
-cache index t1 keys (unknown_key) in keycache1;
+cache index t1 key (unknown_key) in keycache1;
 
 select @@keycache2.key_buffer_size;
 select @@keycache2.key_cache_block_size;
diff --git a/mysql-test/t/preload.test b/mysql-test/t/preload.test
index 7eff5cee08f..7a049d06a86 100644
--- a/mysql-test/t/preload.test
+++ b/mysql-test/t/preload.test
@@ -81,7 +81,7 @@ flush tables; flush status;
 show status like "key_read%";
 set session preload_buffer_size=1*1024;
 select @@preload_buffer_size;
-load index into cache t1, t2 keys (primary,b) ignore leaves;
+load index into cache t1, t2 key (primary,b) ignore leaves;
 show status like "key_read%";
 select count(*) from t1 where b = 'test1';
 select count(*) from t2 where b = 'test1';
@@ -89,12 +89,12 @@ show status like "key_read%";
 
 flush tables; flush status;
 show status like "key_read%";
-load index into cache t3, t2 keys (primary,b) ;
+load index into cache t3, t2 key (primary,b) ;
 show status like "key_read%";
 
 flush tables; flush status;
 show status like "key_read%";
-load index into cache t3 keys (b), t2 keys (c) ;
+load index into cache t3 key (b), t2 key (c) ;
 show status like "key_read%";
 
 drop table t1, t2;