Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7474

ROCKSDB: Row not retrieved when using indexes.

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • 8.0.22-13
    • 8.0.23-14 (Q1 2021)
    • MyRocks
    • 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.

      Attachments

        Activity

          People

            przemyslaw.skibinski Przemyslaw Skibinski
            agustin.gallego Agustín Gallego
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist