mysql> CREATE TABLE t1 (cola char(3) not null, colb char(3) not null, filler char(200), key ka(cola), key kb(colb)) default charset utf8mb4 engine=rocksdb;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> INSERT INTO t1(cola,colb,filler) VALUES('aaa','aaa', 'Z1'), ('aaa','aaa', 'Z2'), ('aaa','aaa', 'Z3'), ('bbb','bbb', 'Z4');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1;
+------+------+--------+
| cola | colb | filler |
+------+------+--------+
| aaa | aaa | Z1 |
| aaa | aaa | Z2 |
| aaa | aaa | Z3 |
| bbb | bbb | Z4 |
+------+------+--------+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM t1 WHERE cola = 'aaa';
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE cola= 'aaa';
+------+------+--------+
| cola | colb | filler |
+------+------+--------+
| aaa | aaa | Z1 |
| aaa | aaa | Z2 |
| aaa | aaa | Z3 |
+------+------+--------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM t1 WHERE cola <> 'aaa';
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 WHERE cola <> 'aaa';
+------+------+--------+
| cola | colb | filler |
+------+------+--------+
| aaa | aaa | Z1 | <<<< !!!! WRONG RESULT !!!!
+------+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 FORCE INDEX(kb) WHERE cola <> 'aaa';
+------+------+--------+
| cola | colb | filler |
+------+------+--------+
| bbb | bbb | Z4 |
+------+------+--------+
1 row in set (0.00 sec)