diff options
author | Alexander Barkov <bar@mnogosearch.org> | 2014-11-18 23:15:54 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mnogosearch.org> | 2014-11-18 23:15:54 +0400 |
commit | b52d4d0076a7fbd2f4cb4d226947b708077ed8a6 (patch) | |
tree | 6e8405f94f824c9b20790a5fb827f058d6e0968e /mysql-test/r/type_enum.result | |
parent | e52b1637e0d59d3a4368fe2ce3a41c47d4041c2a (diff) | |
download | mariadb-git-b52d4d0076a7fbd2f4cb4d226947b708077ed8a6.tar.gz |
MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
Diffstat (limited to 'mysql-test/r/type_enum.result')
-rw-r--r-- | mysql-test/r/type_enum.result | 54 |
1 files changed, 54 insertions, 0 deletions
diff --git a/mysql-test/r/type_enum.result b/mysql-test/r/type_enum.result index bc71d51152d..89ab69d1715 100644 --- a/mysql-test/r/type_enum.result +++ b/mysql-test/r/type_enum.result @@ -1908,5 +1908,59 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 index PRIMARY PRIMARY 1 NULL 2 Using where; Using index DROP TABLE t1, t2; # +# MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases +# +CREATE TABLE t1 (id INT NOT NULL, a ENUM('04','03','02','01')) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,'01'); +INSERT INTO t1 VALUES (1,'02'); +INSERT INTO t1 VALUES (1,'03'); +INSERT INTO t1 VALUES (1,'04'); +INSERT INTO t1 VALUES (2,'01'); +INSERT INTO t1 VALUES (2,'02'); +INSERT INTO t1 VALUES (2,'03'); +INSERT INTO t1 VALUES (2,'04'); +INSERT INTO t1 VALUES (3,'01'); +INSERT INTO t1 VALUES (3,'02'); +INSERT INTO t1 VALUES (3,'03'); +INSERT INTO t1 VALUES (3,'04'); +INSERT INTO t1 VALUES (4,'01'); +INSERT INTO t1 VALUES (4,'02'); +INSERT INTO t1 VALUES (4,'03'); +INSERT INTO t1 VALUES (4,'04'); +SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id; +id MIN(a) MAX(a) +1 02 04 +2 02 04 +3 02 04 +4 02 04 +SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id; +id MIN(a) MAX(a) +1 02 04 +2 02 04 +3 02 04 +4 02 04 +ALTER TABLE t1 ADD KEY(id,a); +SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id; +id MIN(a) MAX(a) +1 02 04 +2 02 04 +3 02 04 +4 02 04 +# Should NOT use group_min_max optimization +EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL id 6 NULL 16 Using where; Using index +SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id; +id MIN(a) MAX(a) +1 02 04 +2 02 04 +3 02 04 +4 02 04 +# Should NOT use group_min_max optimization +EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL id 6 NULL 16 Using where; Using index +DROP TABLE t1; +# # End of 10.0 tests # |