Bug #63563 Binary operator now causes a full scan
Submitted: 3 Dec 2011 14:09 Modified: 4 Dec 2011 7:29
Reporter: Ben Mildren Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.5.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[3 Dec 2011 14:09] Ben Mildren
Description:
Previously in MySQL 5.1, using the binary operator on a string value in the where clause allowed a case sensitive search whilst still utilizing any  available indexes.
Testing in MySQL 5.5, the same statement now causes a full table scan.  There's nothing in the documentation to suggest the functionality has changed, so I assuming this must be a bug?

How to repeat:
MySQL 5.1:
show create table testbin\G
*************************** 1. row ***************************
       Table: testbin
Create Table: CREATE TABLE `testbin` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(12) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

INSERT INTO testbin (c2, c3, c4) VALUES ('Adam',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('adaM',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('Ben',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('bEn',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('chris',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('CHRIS',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('dAvE',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('DaVe',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('eric',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('eRiC',1,2);

explain SELECT * FROM testbin WHERE (c2 like binary 'eRiC') LIMIT 1;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testbin | range | c2            | c2   | 15      | NULL |    1 | Using where |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+

MySQL 5.5:
show create table testbin\G
*************************** 1. row ***************************
       Table: testbin
Create Table: CREATE TABLE `testbin` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(12) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

INSERT INTO testbin (c2, c3, c4) VALUES ('Adam',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('adaM',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('Ben',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('bEn',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('chris',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('CHRIS',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('dAvE',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('DaVe',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('eric',1,2);
INSERT INTO testbin (c2, c3, c4) VALUES ('eRiC',1,2);

explain SELECT * FROM testbin WHERE (c2 like binary 'eRiC') LIMIT 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | testbin | ALL  | c2            | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
[3 Dec 2011 14:28] Peter Laursen
Explain EXTENDED also shows a small difference:

5.1:

SELECT `test`.`testbin`.`c1` AS `c1`,`test`.`testbin`.`c2` AS `c2`,`test`.`testbin`.`c3` AS `c3`,`test`.`testbin`.`c4` AS `c4` FROM `test`.`testbin` WHERE (`test`.`testbin`.`c2` LIKE CAST('eRiC' AS CHAR CHARSET BINARY)) LIMIT 1 

5.5:

SELECT `test`.`testbin`.`c1` AS `c1`,`test`.`testbin`.`c2` AS `c2`,`test`.`testbin`.`c3` AS `c3`,`test`.`testbin`.`c4` AS `c4` FROM `test`.`testbin` WHERE (`test`.`testbin`.`c2` LIKE <cache>(CAST('eRiC' AS CHAR CHARSET BINARY))) LIMIT 1

.. but don't know if it matters! But I don't like the <hints> that were added in MySQL 5.5 as the optimized query cannot be executed. I have an old 'not a bug' bug report here about same. 

Peter
(not a MySQL person)
[4 Dec 2011 7:29] Valeriy Kravchuk
Thank you for the bug report. Verified just as described on Mac OS X.