opt_range.cc, range.result, range.test:

  Bug #6045: Binary Comparison regression in MySQL 4.1
  Binary searches didn't use a case insensitive index, now they do.


mysql-test/t/range.test:
  Bug #6045: Binary Comparison regression in MySQL 4.1
  Binary searches didn't use a case insensitive index, now they do.
mysql-test/r/range.result:
  Bug #6045: Binary Comparison regression in MySQL 4.1
  Binary searches didn't use a case insensitive index, now they do.
sql/opt_range.cc:
  Bug #6045: Binary Comparison regression in MySQL 4.1
  Binary searches didn't use a case insensitive index, now they do.

3 files changed, 42 insertions, 3 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 4ca96316800..17ed9513653 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -561,3 +561,19 @@ select count(*) from t1 where x = 18446744073709551601;
 count(*)
 1
 drop table t1;
+set names latin1;
+create table t1 (a char(10), b text, key (a)) character set latin1;
+INSERT INTO t1 (a) VALUES
+('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
+explain select * from t1 where a='aaa';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	11	const	2	Using where
+explain select * from t1 where a=binary 'aaa';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	11	NULL	2	Using where
+explain select * from t1 where a='aaa' collate latin1_bin;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	11	NULL	2	Using where
+explain select * from t1 where a='aaa' collate latin1_german1_ci;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	a	NULL	NULL	NULL	9	Using where
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index cd6620f7126..44f55da5722 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -431,3 +431,17 @@ select count(*) from t1 where x = 18446744073709551601;
 
 drop table t1;
 
+#
+# Bug #6045: Binary Comparison regression in MySQL 4.1
+# Binary searches didn't use a case insensitive index.
+#
+set names latin1;
+create table t1 (a char(10), b text, key (a)) character set latin1;
+INSERT INTO t1 (a) VALUES
+('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');
+# all these three can be optimized
+explain select * from t1 where a='aaa';
+explain select * from t1 where a=binary 'aaa';
+explain select * from t1 where a='aaa' collate latin1_bin;
+# this one cannot:
+explain select * from t1 where a='aaa' collate latin1_german1_ci;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 541acc69ec7..c9528af7d98 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -1013,13 +1013,22 @@ get_mm_leaf(PARAM *param, COND *conf_func, Field *field, KEY_PART *key_part,
   }
 
   /*
-    We can't use an index when comparing strings of 
-    different collations 
+    1. Usually we can't use an index if the column collation
+       differ from the operation collation.
+
+    2. However, we can reuse a case insensitive index for
+       the binary searches:
+
+       WHERE latin1_swedish_ci_column = 'a' COLLATE lati1_bin;
+
+       WHERE latin1_swedish_ci_colimn = BINARY 'a '
+
   */
   if (field->result_type() == STRING_RESULT &&
       value->result_type() == STRING_RESULT &&
       key_part->image_type == Field::itRAW &&
-      ((Field_str*)field)->charset() != conf_func->compare_collation())
+      ((Field_str*)field)->charset() != conf_func->compare_collation() &&
+      !(conf_func->compare_collation()->state & MY_CS_BINSORT))
     DBUG_RETURN(0);
 
   if (type == Item_func::LIKE_FUNC)