Bug #63563 Binary operator now causes a full scan

• Submitted: 3 Dec 2011 14:09
• Modified: 4 Dec 2011 7:29
• Reporter: Ben Mildren
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.5.17
• OS: Any
• 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.