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

GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 10.1, 10.0, 5.5
        • 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.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment - - edited

                In 5.5 one should use DATE('XXXX-XX-XX') syntax instead of DATE'XXXX-XX-XX' to reproduce the problem.

                Show
                bar Alexander Barkov added a comment - - edited In 5.5 one should use DATE('XXXX-XX-XX') syntax instead of DATE'XXXX-XX-XX' to reproduce the problem.

                  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:

                      Agile