MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases

5 files changed, 186 insertions, 10 deletions

diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
index 06a8a8a06b8..9d1c495eb27 100644
--- a/mysql-test/r/group_min_max.result
+++ b/mysql-test/r/group_min_max.result
@@ -3686,3 +3686,43 @@ a	b
 3	2
 3	3
 drop table t1;
+#
+# Start of 10.0 tests
+#
+#
+# MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+#
+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>=DATE'2001-01-04' GROUP BY id;
+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
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+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
+DROP TABLE t1;
+#
+# End of 10.0 tests
+#
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
 #
diff --git a/mysql-test/t/group_min_max.test b/mysql-test/t/group_min_max.test
index c809401bbf8..f58350013be 100644
--- a/mysql-test/t/group_min_max.test
+++ b/mysql-test/t/group_min_max.test
@@ -1488,3 +1488,38 @@ SELECT distinct a, b FROM t1 where a = '3' ORDER BY b;
 SELECT distinct a, b FROM t1 where a = '3' ORDER BY b; 
 
 drop table t1;
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+--echo #
+--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+--echo #
+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>=DATE'2001-01-04' GROUP BY id;
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=DATE'2001-01-04' GROUP BY id;
+DROP TABLE t1;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
diff --git a/mysql-test/t/type_enum.test b/mysql-test/t/type_enum.test
index 0a4bdc864e3..6d006e891b3 100644
--- a/mysql-test/t/type_enum.test
+++ b/mysql-test/t/type_enum.test
@@ -261,5 +261,36 @@ EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING (c1);
 DROP TABLE t1, t2;
 
 --echo #
+--echo # MDEV-6991 GROUP_MIN_MAX optimization is erroneously applied in some cases
+--echo #
+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;
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+ALTER TABLE t1 ADD KEY(id,a);
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+--echo # Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='02' GROUP BY id;
+SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+--echo # Should NOT use group_min_max optimization
+EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a<=3 GROUP BY id;
+DROP TABLE t1;
+
+--echo #
 --echo # End of 10.0 tests
 --echo #
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 7854a4b0081..dec104c3903 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -13320,16 +13320,31 @@ check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
           DBUG_RETURN(FALSE);
 
         /* Check for compatible string comparisons - similar to get_mm_leaf. */
-        if (args[0] && args[1] && !args[2] && // this is a binary function
-            min_max_arg_item->result_type() == STRING_RESULT &&
-            /*
-              Don't use an index when comparing strings of different collations.
-            */
-            ((args[1]->result_type() == STRING_RESULT &&
-              image_type == Field::itRAW &&
-              min_max_arg_item->field->charset() !=
-              pred->compare_collation())
-             ||
+        if (args[0] && args[1] && !args[2]) // this is a binary function
+        {
+          if (args[1]->cmp_type() == TIME_RESULT &&
+              min_max_arg_item->field->cmp_type() != TIME_RESULT)
+            DBUG_RETURN(FALSE);
+
+          /*
+            Can't use GROUP_MIN_MAX optimization for ENUM and SET,
+            because the values are stored as numbers in index,
+            while MIN() and MAX() work as strings.
+            It would return the records with min and max enum numeric indexes.
+           "Bug#45300 MAX() and ENUM type" should be fixed first.
+          */
+          if (min_max_arg_item->field->real_type() == MYSQL_TYPE_ENUM ||
+              min_max_arg_item->field->real_type() == MYSQL_TYPE_SET)
+            DBUG_RETURN(FALSE);
+
+          if (min_max_arg_item->result_type() == STRING_RESULT &&
+              /*
+                Don't use an index when comparing strings of different collations.
+              */
+              ((args[1]->result_type() == STRING_RESULT &&
+                image_type == Field::itRAW &&
+                min_max_arg_item->field->charset() !=
+                pred->compare_collation()) ||
              /*
                We can't always use indexes when comparing a string index to a
                number.
@@ -13337,6 +13352,7 @@ check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
              (args[1]->result_type() != STRING_RESULT &&
               min_max_arg_item->field->cmp_type() != args[1]->result_type())))
           DBUG_RETURN(FALSE);
+        }
       }
       else
         has_other= true;