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

GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5, 10.1, 10.0
    • Fix Version/s: 10.1.6
    • Component/s: Optimizer
    • Labels:
      None
    • Sprint:
      10.1.6-1

      Description

      This problem is similar to MDEV-6991, but now for BETWEEN.

      This script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) 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');
      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

      correctly returns:

      +----+-------------+-------------+
      | id | MIN(a)      | MAX(a)      |
      +----+-------------+-------------+
      |  1 |  2001-01-04 |  2001-01-04 |
      |  2 |  2001-01-04 |  2001-01-04 |
      |  3 |  2001-01-04 |  2001-01-04 |
      |  4 |  2001-01-04 |  2001-01-04 |
      +----+-------------+-------------+

      Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records.

      Now if I add an index and run the query again:

      ALTER TABLE t1 ADD KEY(id,a);
      SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

      it returns empty set.

      This EXPLAIN:

      EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

      tells that group-min-max optimization is used for the query

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

      This is wrong, because DATE comparison cannot correctly use a VARCHAR index.
      GROUP_MIN_MAX optimization should not be used for this query.

        Attachments

          Activity

            People

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

              Dates

              Created:
              Updated:
              Resolved: