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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.14
    • 10.1.4
    • Optimizer
    • 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

          There are no comments yet on this issue.

          People

            bar Alexander Barkov
            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.