Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
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.