Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
8.0.22-13
-
None
Description
When using the RocksDB storage engine, a SELECT query doesn't return the matching rows.
How to reproduce (using Percona Server 8.0.22-13):
USE test; DROP TABLE IF EXISTS `table1`; CREATE TABLE `table1` ( `field0` binary(16) NOT NULL, `field1` binary(16) DEFAULT NULL, `field2` varchar(63) DEFAULT NULL, `internal_field2` varchar(63) DEFAULT NULL, PRIMARY KEY (`field0`), KEY `fields_index` (`field1`,`field2`) ) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO table1 (field1, field2, internal_field2, field0) VALUES (0, 'none', 'aoeu', 1);
Then try:
mysql> -- full table scan mysql> SELECT * FROM table1 use index () WHERE (`field1`=x'30000000000000000000000000000000') and (`field2`!='archived') and (`internal_field2`!='deleted'); +------------------------------------+------------------------------------+--------+-----------------+ | field0 | field1 | field2 | internal_field2 | +------------------------------------+------------------------------------+--------+-----------------+ | 0x31000000000000000000000000000000 | 0x30000000000000000000000000000000 | none | aoeu | +------------------------------------+------------------------------------+--------+-----------------+ 1 row in set (0.01 sec)
It worked as expected, when forcing a full table scan. When using indexes:
mysql> SELECT * FROM table1 WHERE (`field1`=x'30000000000000000000000000000000') and (`field2`!='archived') and (`internal_field2`!='deleted'); Empty set (0.00 sec)
Note that the query is exactly the same, except for index hints.