Bug #30596 GROUP BY optimization gives wrong result order

  
The optimization that uses a unique index to remove GROUP BY did not 
ensure that the index was actually used, thus violating the ORDER BY
that is implied by GROUP BY.
Fixed by replacing GROUP BY with ORDER BY if the GROUP BY clause contains
a unique index over non-nullable field(s). In case GROUP BY ... ORDER BY 
null is used, GROUP BY is simply removed.


mysql-test/include/mix1.inc:
  Bug#30596: Test case for InnoDB
  Here, as opposed to for MyISAM, row lookup is done using index 
  whenever the index covers the group list.
mysql-test/r/distinct.result:
  Bug#30596: Changed test case. 
  Prior to Bug#16458, These queries use temp table and filesort. The
  bug was that they used a temp table. However, that patch removed
  filesort also, in which case we can no longer gurantee correct ordering.
mysql-test/r/group_by.result:
  Bug#30596: Correct result
  The test case for IGNORE INDEX FOR GROUP BY gets degraded performance 
  (unneccesary filesort). This is due to Bug#30665, which will be fixed separately.
mysql-test/r/innodb_mysql.result:
  Bug#30596: Test result
mysql-test/t/group_by.test:
  Bug#30596: Test case
sql/sql_select.cc:
  Bug#30596: The fix: 
  - replace GROUP BY with ORDER BY unless ORDER BY [NULL|<constant>]
  - make sure to use the keys for GROUP BY in this ORDER BY.

1 files changed, 3 insertions, 3 deletions

diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 795d8956a08..b2a9eb04c04 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -526,10 +526,10 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	Using index
 EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
 PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
@@ -554,7 +554,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
 EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using filesort
 DROP TABLE t1,t2;
 create table t1 (id int, dsc varchar(50));
 insert into t1 values (1, "line number one"), (2, "line number two"), (3, "line number three");