Description:
The following case returns wrong amount of rows.
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> a CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
-> KEY a(a(1))
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t VALUES (1, 'ab'), (2, 'ab');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t FORCE INDEX (a) WHERE a = 'ab' ORDER BY id DESC;
+----+----+
| id | a |
+----+----+
| 2 | ab |
+----+----+
1 row in set (0.01 sec)
The problem disappears when any of the following:
- using collate other than utf8_bin
- using full-length index
- ordering by asc
- using other engine than innodb.
How to repeat:
DROP TABLE IF EXISTS t;
CREATE TABLE t (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
KEY a(a(1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO t VALUES (1, 'ab'), (2, 'ab');
SELECT * FROM t FORCE INDEX (a) WHERE a = 'ab' ORDER BY id DESC;
Description: The following case returns wrong amount of rows. mysql> DROP TABLE IF EXISTS t; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE t ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> a CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, -> KEY a(a(1)) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO t VALUES (1, 'ab'), (2, 'ab'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t FORCE INDEX (a) WHERE a = 'ab' ORDER BY id DESC; +----+----+ | id | a | +----+----+ | 2 | ab | +----+----+ 1 row in set (0.01 sec) The problem disappears when any of the following: - using collate other than utf8_bin - using full-length index - ordering by asc - using other engine than innodb. How to repeat: DROP TABLE IF EXISTS t; CREATE TABLE t ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a CHAR(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, KEY a(a(1)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO t VALUES (1, 'ab'), (2, 'ab'); SELECT * FROM t FORCE INDEX (a) WHERE a = 'ab' ORDER BY id DESC;