Bug #79757 Wrong result when search by utf8_bin partial-field key with order by desc
Submitted: 23 Dec 2015 9:59 Modified: 23 Dec 2015 10:30
Reporter: Ievgen Petrov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.9, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[23 Dec 2015 9:59] Ievgen Petrov
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;
[23 Dec 2015 10:30] Umesh Shastry
Hello Ievgen Petrov,

Thank you for the report and test case.
Observed this with 5.6.28, 5.7.10 builds.

Thanks,
Umesh