| Bug #6045 | Binary Comparison regression in MySQL 4.1 | ||
|---|---|---|---|
| Submitted: | 12 Oct 2004 16:19 | Modified: | 12 Nov 2009 18:25 |
| Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1.6 | OS: | Any |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[3 Nov 2004 12:26]
Alexander Barkov
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!
[5 Nov 2009 14:46]
Bugs System
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
[11 Nov 2009 6:51]
Bugs System
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)
[12 Nov 2009 8:20]
Bugs System
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)
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";