Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5112

InnoDB index intersection returns less results than expected

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.33a
    • Fix Version/s: 5.5.34, 10.0.6
    • Component/s: None
    • Labels:
      None
    • Environment:
      Debian GNU/Linux 6.0

      Description

      This bug affects MariaDB 5.5.3x perhaps older versions too.

      This bug concerns also all MySQL 5.6 versions (>= 5.6.8), it will be fixed on 5.6.14 : http://bugs.mysql.com/bug.php?id=69581

      The test case below comes from the above link :

      USE test;
      DROP TABLE IF EXISTS `table1`;
      CREATE TABLE `table1` (
        `col1` bigint(20) unsigned NOT NULL ,
        `col2` bigint(20) unsigned NOT NULL ,
        `col3` datetime NOT NULL ,
        PRIMARY KEY (`col3`),
        KEY (`col1`),
        KEY (`col2`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1
       PARTITION BY RANGE (TO_DAYS(col3))
      (
       PARTITION p_20130310 VALUES LESS THAN (735303) ENGINE = InnoDB,
       PARTITION p_20130311 VALUES LESS THAN (735304) ENGINE = InnoDB,
       PARTITION p_20130312 VALUES LESS THAN (735305) ENGINE = InnoDB
      );
      INSERT INTO `table1` VALUES (2,96,'2013-03-08 16:28:05');
      INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:47:39');
      INSERT INTO `table1` VALUES (1,2,'2013-03-08 16:50:27');
      INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:04');
      INSERT INTO `table1` VALUES (1,2,'2013-03-11 16:33:24');
      INSERT INTO `table1` VALUES (2,2,'2013-03-12 10:11:48');
       
      SET optimizer_switch='index_merge=on';
      SELECT @@optimizer_switch;
      SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
          AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
      GROUP BY 1, 2, 3;
      EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
          AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
      GROUP BY 1, 2, 3;
       
      SET optimizer_switch='index_merge=off';
      SELECT @@optimizer_switch;
      SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
          AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
      GROUP BY 1, 2, 3;
      EXPLAIN SELECT * FROM table1 WHERE col1 = 1 AND col2 = 2
          AND col3 BETWEEN '2013-03-08 00:00:00' AND '2013-03-12 12:00:00'
      GROUP BY 1, 2, 3;

      With index_merge=on you obtain :

      +------+------+---------------------+
      | col1 | col2 | col3                |
      +------+------+---------------------+
      |    1 |    2 | 2013-03-08 16:47:39 |
      |    1 |    2 | 2013-03-08 16:50:27 |
      +------+------+---------------------+
      2 rows in set (0.00 sec)

      but you should obtain what you have when you disable index_merge :

      +------+------+---------------------+
      | col1 | col2 | col3                |
      +------+------+---------------------+
      |    1 |    2 | 2013-03-08 16:47:39 |
      |    1 |    2 | 2013-03-08 16:50:27 |
      |    1 |    2 | 2013-03-11 16:33:04 |
      |    1 |    2 | 2013-03-11 16:33:24 |
      +------+------+---------------------+
      4 rows in set (0.00 sec)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              agadal Arnaud Gadal
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: