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

GROUP_MIN_MAX optimization is not applied in some cases when it could

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.14
    • Fix Version/s: 10.1.4
    • Component/s: Optimizer
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (1,'2001-01-01');
      INSERT INTO t1 VALUES (1,'2001-01-02');
      INSERT INTO t1 VALUES (1,'2001-01-03');
      INSERT INTO t1 VALUES (1,'2001-01-04');
      INSERT INTO t1 VALUES (2,'2001-01-01');
      INSERT INTO t1 VALUES (2,'2001-01-02');
      INSERT INTO t1 VALUES (2,'2001-01-03');
      INSERT INTO t1 VALUES (2,'2001-01-04');
      INSERT INTO t1 VALUES (3,'2001-01-01');
      INSERT INTO t1 VALUES (3,'2001-01-02');
      INSERT INTO t1 VALUES (3,'2001-01-03');
      INSERT INTO t1 VALUES (3,'2001-01-04');
      INSERT INTO t1 VALUES (4,'2001-01-01');
      INSERT INTO t1 VALUES (4,'2001-01-02');
      INSERT INTO t1 VALUES (4,'2001-01-03');
      INSERT INTO t1 VALUES (4,'2001-01-04');
      EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;

      returns:

      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      | id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                 |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
      |    1 | SIMPLE      | t1    | range | NULL          | id   | 8       | NULL |    9 | Using where; Using index for group-by |
      +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+

      It's going to scan 9 out of 16 rows, which means group_min_max optimization is enabled.

      Now if I change the constant from '2001-01-05' to 20010104 and rerun EXPLAIN:

      EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104 GROUP BY id;

      it returns:

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

      It will scan all 16 rows, that means group_min_max optimization is not applied.
      It safely could. Comparison in both cases is done by DATE anyway.

      The same is true for DECIMAL and DOUBLE literals.

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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