Bug #6045 Binary Comparison regression in MySQL 4.1

Description:
Starting  MySQL 4.1 one can no more get index used both for case insensitive comparisons
and case sensitive comparisons.

 CREATE TABLE `my` (
  `c` char(20) default NULL,
  KEY `c` (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

MySQL 4.0

mysql> explain select * from my where c= binary "aaa";
+-------+------+---------------+------+---------+-------+------+--------------------------+
| table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+-------+------+---------------+------+---------+-------+------+--------------------------+
| my    | ref  | c             | c    |      21 | const |    2 | Using where; Using index |
+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

MySQL 4.1

mysql> explain select * from my where c=binary "aaa";
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | my    | index | NULL          | c    |      21 | NULL |    9 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

As you can see in last case full index is scanned. 

One Can workaround it this way:

mysql> explain select * from my where c="aaa" and c=binary "aaa";
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | my    | ref  | c             | c    |      21 | const |    2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

However why does not MySQL do it by itself ? 

Someone could tell "binary" has different meaning in  MySQL 4.1, so I tried using binary collation instead:

mysql> explain select * from my where  c="aaa" collate latin1_bin;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | my    | index | c             | c    |      21 | NULL |    9 | Using where; Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

So it does not work even right way :)

How to repeat:
CREATE TABLE my (
  c char(20) default NULL,
  KEY c (c)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `my`
--

INSERT INTO my VALUES ('111'),('222'),('222'),('222'),('222'),('444'),('aaa'),('AAA'),('bbb');

explain select * from my where  c="aaa" collate latin1_bin;
explain select * from my where c=binary "aaa";

A case insensitive index now can be used for optimization for both cases:
WHERE column = 'aaa' COLLATE xxx_bin
WHERE column = BINARY 'aaa'

Thanks for reporting, Peter!

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89482

2924 Mikael Ronstrom	2009-11-05
      BUG#48447, BUG#48161, fixed a regression from fix of BUG#6045, where binary collations can use indexes/partition pruning for cases using equality conditions, however it cannot be used for any other condition like <, >, <=, >=, <>, also added test case for verification of BUG#47774 in this patch

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091110083317-h00x61ugz9fxhdod) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091110083426-bm3am5445pfrrci9) (merge vers: 5.5.0-beta) (pib:13)

Per Mikael, the preceding two pushes are for  Bug#48447 and Bug#48161, not Bug#6045. Re-closing.