3 files changed, 43 insertions, 5 deletions

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;