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

GROUP_MIN_MAX is not applied for ENGINE=HEAP when it could

    XMLWordPrintable

Details

    Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (id INT NOT NULL, a INT NOT NULL) ENGINE=HEAP;
      INSERT INTO t1 VALUES (1, 1);
      INSERT INTO t1 VALUES (1, 2);
      INSERT INTO t1 VALUES (1, 3);
      INSERT INTO t1 VALUES (1, 4);
      INSERT INTO t1 VALUES (2, 1);
      INSERT INTO t1 VALUES (2, 2);
      INSERT INTO t1 VALUES (2, 3);
      INSERT INTO t1 VALUES (2, 4);
      INSERT INTO t1 VALUES (3, 1);
      INSERT INTO t1 VALUES (3, 2);
      INSERT INTO t1 VALUES (3, 3);
      INSERT INTO t1 VALUES (3, 4);
      INSERT INTO t1 VALUES (4, 1);
      INSERT INTO t1 VALUES (4, 2);
      INSERT INTO t1 VALUES (4, 3);
      INSERT INTO t1 VALUES (4, 4);
      ALTER TABLE t1 ADD KEY (id,a) USING BTREE;
      EXPLAIN SELECT id,MIN(a) FROM t1 WHERE a=4 GROUP BY id;
      EXPLAIN SELECT id,MIN(a) FROM t1 WHERE a>=4 GROUP BY id;

      Both EXPLAIN return

      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | index | NULL          | id   | 8       | NULL |   16 | Using where |
      +------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

      which means group-min-max is not used.

      Perhaps it could in both cases.
      Notice, USING BTREE makes the index suitable for ">=".

      Attachments

        Activity

          People

            Unassigned Unassigned
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.