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

Partial result set if index_merge_intersection optimization used

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Cannot Reproduce
    • Affects Version/s: 8.0.15-5
    • Fix Version/s: None
    • Component/s: MyRocks
    • Labels:
      None

      Description

      How to repeat

      CREATE TABLE `t3` (
        `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,
        `m` json DEFAULT NULL,
        `dd` int(6) unsigned NOT NULL DEFAULT '0',
        `s` tinyint(1) NOT NULL DEFAULT '0',
        `u` int(8) unsigned NOT NULL,
        `l` int(12) unsigned NOT NULL,
        `p` int(10) unsigned NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`,`p`,`l`),
        KEY `u_s` (`u`,`dd`,`s`),
        KEY `l_s` (`l`,`s`,`dd`)
      ) ENGINE=ROCKSDB AUTO_INCREMENT=1950201 DEFAULT CHARSET=utf8
      /*!50100 PARTITION BY LIST (`p`)
      SUBPARTITION BY HASH (`l`)
      SUBPARTITIONS 5
      (PARTITION p0 VALUES IN (0) ENGINE = ROCKSDB,
       PARTITION p1 VALUES IN (1) ENGINE = ROCKSDB,
       PARTITION p2 VALUES IN (2) ENGINE = ROCKSDB) */;
      
      
      mysql> explain SELECT id, m->>'$."3"' AS value FROM t3 WHERE dd =0 AND s = 5 AND u = 558 AND l = 671;
      +----+-------------+-------+----------------------------+-------------+---------------+---------+---------+------+------+----------+---------------------------------------+
      | id | select_type | table | partitions                 | type        | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                 |
      +----+-------------+-------+----------------------------+-------------+---------------+---------+---------+------+------+----------+---------------------------------------+
      |  1 | SIMPLE      | t3    | p0_p0sp1,p1_p1sp1,p2_p2sp1 | index_merge | u_s,l_s       | u_s,l_s | 9,9     | NULL |    1 |   100.00 | Using intersect(u_s,l_s); Using where |
      +----+-------------+-------+----------------------------+-------------+---------------+---------+---------+------+------+----------+---------------------------------------+
      1 row in set, 1 warning (0.00 sec)mysql> SELECT id, m->>'$."3"' AS value FROM t3 WHERE dd =0 AND s = 5 AND u = 558 AND l = 671;
      +---------+-------+
      | id      | value |
      +---------+-------+
      |  216120 | NULL  |
      | 1436886 | NULL  |
      +---------+-------+
      2 rows in set (0.01 sec)
      
      mysql> SET SESSION optimizer_switch="index_merge_intersection=off";
      Query OK, 0 rows affected (0.00 sec)mysql> explain SELECT id, m->>'$."3"' AS value FROM t3 WHERE dd =0 AND s = 5 AND u = 558 AND l = 671;
      +----+-------------+-------+----------------------------+------+---------------+------+---------+-------------------+------+----------+-----------------------+
      | id | select_type | table | partitions                 | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra                 |
      +----+-------------+-------+----------------------------+------+---------------+------+---------+-------------------+------+----------+-----------------------+
      |  1 | SIMPLE      | t3    | p0_p0sp1,p1_p1sp1,p2_p2sp1 | ref  | u_s,l_s       | u_s  | 9       | const,const,const |   66 |     0.08 | Using index condition |
      +----+-------------+-------+----------------------------+------+---------------+------+---------+-------------------+------+----------+-----------------------+
      1 row in set, 1 warning (0.00 sec)mysql> SELECT id, m->>'$."3"' AS value FROM t3 WHERE dd =0 AND s = 5 AND u = 558 AND l = 671;
      +---------+-------+
      | id      | value |
      +---------+-------+
      |  216120 | NULL  |
      |  324599 | NULL  |
      |  364144 | NULL  |
      |  427184 | NULL  |
      |  432374 | NULL  |
      |  624790 | NULL  |
      | 1063203 | NULL  |
      | 1247038 | NULL  |
      | 1436886 | NULL  |
      | 1634468 | NULL  |
      +---------+-------+
      10 rows in set (0.00 sec)

      Table dump will be provided.

      I used self-compiled trunk for this test. With release 8.0.15 version same test case crashes the server.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                sveta.smirnova Sveta Smirnova
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: