MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:mhansson Date:August 7 2007 4:51pm
Subject:bk commit into 5.0 tree (mhansson:1.2485) BUG#28691
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-08-07 18:51:43+02:00, mhansson@stripped +15 -0
  Bug #28691: GROUP BY optimization ignores functional dependency on unique/primary key
  
  In the general case, MySQL did not exploit ordered keys in order to skip GROUP BY
  and ORDER by, and when it did results were sometimes sorted wrong.
  The problem consisted of:
  
  1) GROUP BY was removed without choosing the ordered index
  2) test_if_skip_sort_order and test_if_order_by_key failed to take into account
     that a full match on one unique ordered key is enough to skip GROUP BY once
     (1) is fixed. (some limitations apply, see comment for sql_select.cc)
  3) Some storage engines (e.g. MyISAM) may not recommend index scan over table
     scan, and test_if_skip_sort_order did not consider 'unrecommended' keys.
  
  Fixed by 1) moving the skipping of GROUP BY to the place where an index is chosen,
  so that not one is done without the other, 2) Adding to test_if_skip_sort_order
  and test_if_order_by_key the ability to recognize good indexes, and 3) by not
  paying attention to what the storage engine thinks is good use of indexes in 
  this case.

  mysql-test/r/ctype_ucs.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +1 -1
    Bug#28691: Changed test result

  mysql-test/r/distinct.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +9 -5
    Bug#28691: Changed test result

  mysql-test/r/group_by.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +80 -0
    Bug#28691: Test result

  mysql-test/r/group_min_max.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +9 -9
    Bug#28691: Changed test result

  mysql-test/r/innodb.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +1 -1
    Bug#28691: Changed test result

  mysql-test/r/innodb_mysql.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +15 -0
    Bug#28691: Test result

  mysql-test/r/merge.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +1 -1
    Bug#28691: Changed test result

  mysql-test/r/myisam.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +3 -3
    Bug#28691: Changed test result

  mysql-test/r/ndb_condition_pushdown.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +14 -14
    Bug#28691: Changed test result

  mysql-test/r/order_by.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +1 -1
    Bug#28691: Changed test result

  mysql-test/r/subselect.result@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +1 -1
    Bug#28691: Changed test result

  mysql-test/t/distinct.test@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +2 -2
    Bug#28691:
    This test was changed (NOT NULL was added to b and c) 
    in the fix for Bug#25551 since in that fix the conditions
    to use an index were made more restrictive. But an index shouldn't ever be 
    used here and the query result was wrong prior to this fix, so the test was 
    restored and the actual result is added.

  mysql-test/t/group_by.test@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +72 -0
    Bug#28691: Test case

  mysql-test/t/innodb_mysql.test@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +14 -0
    Bug#28691: Test case for InnoDB

  sql/sql_select.cc@stripped, 2007-08-07 18:51:37+02:00, mhansson@stripped +77 -24
    Bug#28691: 
    - Added function get_unique_keys_in_list, moved all code 
      from list_contains_unique_index here, and made list_contains_unique_index 
      use the new function as a helper.
    - Added function key_is_unique to test precisely that.
    - Removed the code that removes a GROUP BY from a query without 
      deciding what index to use.
    - Added code in test_if_order_by_key to return true if a unique key is matched.
    - Added some restrictions when not to consider unique keys for sorting,
      i.e. temp, federated and heap tables
    - Added the code to include unique keys when considering sort keys.
    - Added code to remove non-ordered (ie hash) indexes from consideration.
    - Removed the condition that a sort key has to be FORCEd in order to be 
      used unless it's in handler::keys_to_use_for_scanning.

diff -Nrup a/mysql-test/r/ctype_ucs.result b/mysql-test/r/ctype_ucs.result
--- a/mysql-test/r/ctype_ucs.result	2007-07-04 09:04:55 +02:00
+++ b/mysql-test/r/ctype_ucs.result	2007-08-07 18:51:37 +02:00
@@ -310,7 +310,7 @@ INSERT INTO t1 (word) VALUES ("aardvara"
 INSERT INTO t1 (word) VALUES ("aardvarz");
 EXPLAIN SELECT * FROM t1 ORDER BY word;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	130	NULL	6	
 SELECT * FROM t1 ORDER BY word;
 word	bar
 a	0
diff -Nrup a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
--- a/mysql-test/r/distinct.result	2007-04-26 22:10:37 +02:00
+++ b/mysql-test/r/distinct.result	2007-08-07 18:51:37 +02:00
@@ -526,12 +526,11 @@ id	select_type	table	type	possible_keys	
 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	index	NULL	PRIMARY	4	NULL	3	
 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	
-CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
-PRIMARY KEY (a,b));
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	
+CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -554,7 +553,12 @@ id	select_type	table	type	possible_keys	
 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 temporary; Using filesort
+SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+a	b	d
+1	1	50
+2	1	4
+1	2	40
 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");
diff -Nrup a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
--- a/mysql-test/r/group_by.result	2007-04-24 20:35:19 +02:00
+++ b/mysql-test/r/group_by.result	2007-08-07 18:51:37 +02:00
@@ -1047,3 +1047,83 @@ select t1.f1,t.* from t1, t1 t group by 
 ERROR 42000: 'test.t.f1' isn't in GROUP BY
 drop table t1;
 SET SQL_MODE = '';
+CREATE TABLE t1( 
+a INT PRIMARY KEY, 
+b INT ,
+KEY( b ) 
+);
+INSERT INTO t1 VALUES ( 1, 2 );
+INSERT INTO t1 VALUES ( 2, 1 );
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+CREATE TABLE t2( 
+a INT , 
+b INT PRIMARY KEY, 
+KEY( a ) 
+);
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+CREATE TABLE t3 (
+a INT,
+b INT,
+c INT,
+d INT,
+PRIMARY KEY( a, b )
+);
+INSERT INTO t3 VALUES ( 1, 1, 1, 1 ), ( 1, 2, 2, 2 ), 
+( 2, 1, 3, 3 ), ( 2, 2, 4, 4 );
+ANALYZE TABLE t3;
+Table	Op	Msg_type	Msg_text
+test.t3	analyze	status	OK
+CREATE TABLE t4 (
+a INT,
+b INT,
+c INT,
+KEY ab( a, b )
+);
+INSERT INTO t4 VALUES ( 1, 1, 1 ), ( 1, 2, 2 ), ( 2, 1, 3 ), ( 2, 2, 4 ),
+( 1, 1, 1 ), ( 1, 2, 2 ), ( 2, 1, 3 ), ( 2, 2, 4 );
+ANALYZE TABLE t4;
+Table	Op	Msg_type	Msg_text
+test.t4	analyze	status	OK
+CREATE TABLE t5 (
+a INT NOT NULL,
+b INT NOT NULL,
+KEY ab( a, b )  
+);
+INSERT INTO t5 VALUES ( 1, 1 ), ( 1, 2 ), ( 2, 1 ), ( 2, 2 );
+ANALYZE TABLE t5;
+Table	Op	Msg_type	Msg_text
+test.t5	analyze	status	OK
+CREATE TABLE t6 (
+a INT NOT NULL,
+KEY USING HASH( a )
+);
+EXPLAIN SELECT t1.a, t1.b, max( t2.b )
+FROM t1 JOIN t2 USING(a) 
+GROUP BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	2	
+1	SIMPLE	t2	ref	a	a	5	test.t1.a	1	Using where
+EXPLAIN SELECT t3.a, t3.b, max( t4.b ) 
+FROM t3 JOIN t4 USING( a, b ) 
+GROUP BY t3.a, t3.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	PRIMARY	PRIMARY	8	NULL	4	Using index
+1	SIMPLE	t4	ref	ab	ab	10	test.t3.a,test.t3.b	2	Using where; Using index
+EXPLAIN SELECT t5.a, t5.b, max( t4.b ) 
+FROM t5 JOIN t4 USING( a, b ) 
+GROUP BY t5.a, t5.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t5	index	ab	ab	8	NULL	4	Using index
+1	SIMPLE	t4	ref	ab	ab	10	test.t5.a,test.t5.b	2	Using where; Using index
+EXPLAIN SELECT a FROM t6 GROUP BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	system	NULL	NULL	NULL	NULL	0	const row not found
+EXPLAIN SELECT a FROM t6 ORDER BY a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t6	system	NULL	NULL	NULL	NULL	0	const row not found
+DROP TABLE t1, t2, t3, t4, t5, t6;
diff -Nrup a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result
--- a/mysql-test/r/group_min_max.result	2007-06-24 08:33:52 +02:00
+++ b/mysql-test/r/group_min_max.result	2007-08-07 18:51:37 +02:00
@@ -1907,10 +1907,10 @@ concat(ord(min(b)),ord(max(b)))	min(b)	m
 9798	a	b
 explain select a1,a2,b,d,min(c),max(c) from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	
 explain select a1,a2,b,d from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	
 explain select a1,a2,min(b),max(b) from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -1918,33 +1918,33 @@ id	select_type	table	type	possible_keys	
 explain select a1,a2,b,min(c),max(c) from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	128	Using where
 explain select a1,a2,b,c from t1
 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (d > 'xy2') group by a1,a2,b,c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	idx_t1_0,idx_t1_1,idx_t1_2	NULL	NULL	NULL	128	Using where; Using temporary; Using filesort
+1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	128	Using where
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b < 'b') group by a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
+1	SIMPLE	t2	index	NULL	idx_t2_0	65	NULL	164	Using where
 explain select a1,a2,b from t1 where (a1 = 'b' or a1 = 'd' or a1 = 'a' or a1 = 'c') and (a2 > 'a') and (c > 'a111') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	130	NULL	76	Using where; Using index
 explain select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
+1	SIMPLE	t2	index	NULL	idx_t2_0	65	NULL	164	Using where
 select a1,a2,min(b),c from t2 where (a2 = 'a') and (c = 'a111') group by a1;
 a1	a2	min(b)	c
 a	a	a	a111
 explain select a1,a2,b,max(c),min(c) from t2 where (a2 = 'a') and (b = 'b') or (b = 'a') group by a1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
+1	SIMPLE	t2	index	NULL	idx_t2_0	65	NULL	164	Using where
 explain select a1,a2,b,min(c),max(c) from t2
 where (c > 'a000') and (c <= 'd999') and (c like '_8__') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	index	NULL	idx_t2_1	163	NULL	164	Using where; Using index
 explain select a1, a2, b, c, min(d), max(d) from t1 group by a1,a2,b,c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using temporary; Using filesort
+1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	
 explain select a1,a2,count(a2) from t1 group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using index
@@ -1956,7 +1956,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	index	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	163	NULL	128	Using where; Using index
 explain select distinct(a1) from t1 where ord(a2) = 98;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	idx_t1_1	163	NULL	128	Using where; Using index
+1	SIMPLE	t1	index	NULL	idx_t1_0	65	NULL	128	Using where
 select distinct(a1) from t1 where ord(a2) = 98;
 a1
 a
diff -Nrup a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
--- a/mysql-test/r/innodb.result	2007-06-21 20:08:49 +02:00
+++ b/mysql-test/r/innodb.result	2007-08-07 18:51:37 +02:00
@@ -1738,7 +1738,7 @@ a	b	c	d	e
 3	3	ab	3	3
 explain select * from t1 order by a,b,c,d;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	269	NULL	3	
 drop table t1;
 create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
 insert into t1 values ('8', '6'), ('4', '7');
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-07-23 16:07:28 +02:00
+++ b/mysql-test/r/innodb_mysql.result	2007-08-07 18:51:37 +02:00
@@ -1007,4 +1007,19 @@ CALL p1();
 CALL p1();
 DROP PROCEDURE p1;
 DROP TABLE t1;
+CREATE TABLE t1(  
+a INT NOT NULL PRIMARY KEY, 
+b INT NOT NULL,  KEY( b ) 
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES ( 1, 1 );
+INSERT INTO t1 VALUES ( 2, 3 );
+INSERT INTO t1 VALUES ( 3, 2 );
+SELECT t1.a, b FROM t1 GROUP BY t1.a, t1.b;
+a	b
+1	1
+2	3
+3	2
+EXPLAIN SELECT t1.a, b FROM t1 GROUP BY t1.a, t1.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	
 End of 5.0 tests
diff -Nrup a/mysql-test/r/merge.result b/mysql-test/r/merge.result
--- a/mysql-test/r/merge.result	2007-06-14 13:18:00 +02:00
+++ b/mysql-test/r/merge.result	2007-08-07 18:51:37 +02:00
@@ -15,8 +15,8 @@ a	b
 3	t2
 select * from t3 order by a desc;
 a	b
-3	t1
 3	t2
+3	t1
 2	table
 2	table
 1	Testing
diff -Nrup a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
--- a/mysql-test/r/myisam.result	2007-05-22 14:58:29 +02:00
+++ b/mysql-test/r/myisam.result	2007-08-07 18:51:37 +02:00
@@ -53,10 +53,10 @@ create table t1 (a int not null, b int n
 insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);
 explain select * from t1 order by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	
 explain select * from t1 order by b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+1	SIMPLE	t1	index	NULL	b	4	NULL	4	
 explain select * from t1 order by c;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
@@ -68,7 +68,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	index	NULL	b	4	NULL	4	Using index
 explain select a,b from t1 order by b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using filesort
+1	SIMPLE	t1	index	NULL	b	4	NULL	4	
 explain select a,b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
diff -Nrup a/mysql-test/r/ndb_condition_pushdown.result b/mysql-test/r/ndb_condition_pushdown.result
--- a/mysql-test/r/ndb_condition_pushdown.result	2006-09-12 16:24:29 +02:00
+++ b/mysql-test/r/ndb_condition_pushdown.result	2007-08-07 18:51:37 +02:00
@@ -513,7 +513,7 @@ time_field = '01:01:01' and 
 date_time = '1901-01-01 01:01:01' 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string = "aaaa" and 
 vstring = "aaaa" and 
@@ -570,7 +570,7 @@ time_field != '01:01:01' and 
 date_time != '1901-01-01 01:01:01' 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string != "aaaa" and 
 vstring != "aaaa" and 
@@ -629,7 +629,7 @@ time_field > '01:01:01' and
 date_time > '1901-01-01 01:01:01'
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string > "aaaa" and 
 vstring > "aaaa" and 
@@ -688,7 +688,7 @@ time_field >= '01:01:01' and 
 date_time >= '1901-01-01 01:01:01' 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string >= "aaaa" and 
 vstring >= "aaaa" and 
@@ -748,7 +748,7 @@ time_field < '04:04:04' and 
 date_time < '1904-04-04 04:04:04' 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string < "dddd" and 
 vstring < "dddd" and 
@@ -807,7 +807,7 @@ time_field <= '04:04:04' and 
 date_time <= '1904-04-04 04:04:04' 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string <= "dddd" and 
 vstring <= "dddd" and 
@@ -1202,7 +1202,7 @@ bin like concat(0xBB, '%') and
 vbin like concat(0xBB, '%')
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string like "b%" and
 vstring like "b%" and
@@ -1219,7 +1219,7 @@ bin not like concat(0xBB, '%') and
 vbin not like concat(0xBB, '%')
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where 
 string not like "b%" and
 vstring not like "b%" and
@@ -1617,7 +1617,7 @@ select auto from t1 where
 '1901-01-01 01:01:01' not in(date_time) 
 order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where with pushed condition; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	4	Using where with pushed condition
 select auto from t1 where
 "aaaa" not in(string) and 
 "aaaa" not in(vstring) and 
@@ -1704,7 +1704,7 @@ count(*)
 explain 
 select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	PRIMARY	NULL	NULL	NULL	6	Using where with pushed condition; Using filesort
+1	SIMPLE	t2	index	PRIMARY	PRIMARY	4	NULL	6	Using where with pushed condition
 select * from t2 where attr3 is null or attr1 > 2 and pk1= 3 order by pk1;
 pk1	attr1	attr2	attr3
 2	2	NULL	NULL
@@ -1712,7 +1712,7 @@ pk1	attr1	attr2	attr3
 explain
 select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where with pushed condition; Using filesort
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	6	Using where with pushed condition
 select * from t2 where attr3 is not null and attr1 > 2 order by pk1;
 pk1	attr1	attr2	attr3
 3	3	3	d
@@ -1721,7 +1721,7 @@ pk1	attr1	attr2	attr3
 explain
 select * from t3 where attr2 >  9223372036854775803 and attr3 != 3 order by pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	6	Using where with pushed condition; Using filesort
+1	SIMPLE	t3	index	NULL	PRIMARY	4	NULL	6	Using where with pushed condition
 select * from t3 where attr2 >  9223372036854775803 and attr3 != 3 order by pk1;
 pk1	attr1	attr2	attr3	attr4
 2	2	9223372036854775804	2	c
@@ -1756,11 +1756,11 @@ pk1	attr1	attr2	attr3	attr4	pk1	attr1	at
 explain
 select auto from t1 where string = "aaaa" collate latin1_general_ci order by auto;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using where; Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	3	Using where
 explain
 select * from t2 where (attr1 < 2) = (attr2 < 2) order by pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using filesort
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	6	Using where
 explain
 select * from t3 left join t4 on t4.attr2 = t3.attr2 where t4.attr1 > 1 and t4.attr3 < 5 or t4.attr1 is null order by t4.pk1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff -Nrup a/mysql-test/r/order_by.result b/mysql-test/r/order_by.result
--- a/mysql-test/r/order_by.result	2007-04-04 07:24:55 +02:00
+++ b/mysql-test/r/order_by.result	2007-08-07 18:51:37 +02:00
@@ -585,7 +585,7 @@ DROP TABLE t1;
 create table t1(id int not null auto_increment primary key, t char(12));
 explain select id,t from t1 order by id;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using filesort
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1000	
 explain select id,t from t1 force index (primary) order by id;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1000	
diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2007-06-29 09:39:15 +02:00
+++ b/mysql-test/r/subselect.result	2007-08-07 18:51:37 +02:00
@@ -3419,7 +3419,7 @@ EXPLAIN
 SELECT * FROM t1 WHERE (a,b) = ANY (SELECT a, max(b) FROM t1 GROUP BY a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	9	Using where
-2	DEPENDENT SUBQUERY	t1	index	NULL	a	8	NULL	9	Using filesort
+2	DEPENDENT SUBQUERY	t1	index	NULL	a	8	NULL	9	
 DROP TABLE t1;
 create table t1( f1 int,f2 int);
 insert into t1 values (1,1),(2,2);
diff -Nrup a/mysql-test/t/distinct.test b/mysql-test/t/distinct.test
--- a/mysql-test/t/distinct.test	2007-04-10 15:55:47 +02:00
+++ b/mysql-test/t/distinct.test	2007-08-07 18:51:37 +02:00
@@ -364,8 +364,7 @@ EXPLAIN SELECT a FROM t1 GROUP BY a;
 EXPLAIN SELECT a,b FROM t1 GROUP BY a,b;
 EXPLAIN SELECT DISTINCT a,b FROM t1 GROUP BY a,b;
 
-CREATE TABLE t2(a INT, b INT NOT NULL, c INT NOT NULL, d INT, 
-                PRIMARY KEY (a,b));
+CREATE TABLE t2(a INT, b INT, c INT, d INT, PRIMARY KEY (a,b));
 INSERT INTO t2 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
 EXPLAIN SELECT DISTINCT a FROM t2;
 EXPLAIN SELECT DISTINCT a,a FROM t2;
@@ -376,6 +375,7 @@ EXPLAIN SELECT DISTINCT c,a,b FROM t2;
 EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
 CREATE UNIQUE INDEX c_b_unq ON t2 (c,b);
 EXPLAIN SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
+SELECT DISTINCT a,b,d FROM t2 GROUP BY c,b,d;
 
 DROP TABLE t1,t2;
 
diff -Nrup a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
--- a/mysql-test/t/group_by.test	2007-04-24 20:35:24 +02:00
+++ b/mysql-test/t/group_by.test	2007-08-07 18:51:37 +02:00
@@ -766,3 +766,75 @@ select * from t1 group by f1, f2;
 select t1.f1,t.* from t1, t1 t group by 1;
 drop table t1;
 SET SQL_MODE = '';
+
+#
+# Bug #28691: GROUP BY optimization ignores functional dependency on 
+# unique/primary key
+#
+CREATE TABLE t1( 
+  a INT PRIMARY KEY, 
+  b INT ,
+  KEY( b ) 
+);
+INSERT INTO t1 VALUES ( 1, 2 );
+INSERT INTO t1 VALUES ( 2, 1 );
+ANALYZE TABLE t1;
+
+CREATE TABLE t2( 
+  a INT , 
+  b INT PRIMARY KEY, 
+  KEY( a ) 
+);
+INSERT INTO t2 VALUES ( 1, 1 ), ( 2, 2 ), ( 3, 3 );
+ANALYZE TABLE t2;
+
+CREATE TABLE t3 (
+  a INT,
+  b INT,
+  c INT,
+  d INT,
+  PRIMARY KEY( a, b )
+);
+INSERT INTO t3 VALUES ( 1, 1, 1, 1 ), ( 1, 2, 2, 2 ), 
+                      ( 2, 1, 3, 3 ), ( 2, 2, 4, 4 );
+ANALYZE TABLE t3;
+
+CREATE TABLE t4 (
+  a INT,
+  b INT,
+  c INT,
+  KEY ab( a, b )
+);
+INSERT INTO t4 VALUES ( 1, 1, 1 ), ( 1, 2, 2 ), ( 2, 1, 3 ), ( 2, 2, 4 ),
+                      ( 1, 1, 1 ), ( 1, 2, 2 ), ( 2, 1, 3 ), ( 2, 2, 4 );
+ANALYZE TABLE t4;
+
+CREATE TABLE t5 (
+  a INT NOT NULL,
+  b INT NOT NULL,
+  KEY ab( a, b )  
+);
+INSERT INTO t5 VALUES ( 1, 1 ), ( 1, 2 ), ( 2, 1 ), ( 2, 2 );
+ANALYZE TABLE t5;
+
+CREATE TABLE t6 (
+  a INT NOT NULL,
+  KEY USING HASH( a )
+);
+
+EXPLAIN SELECT t1.a, t1.b, max( t2.b )
+FROM t1 JOIN t2 USING(a) 
+GROUP BY t1.a;
+
+EXPLAIN SELECT t3.a, t3.b, max( t4.b ) 
+FROM t3 JOIN t4 USING( a, b ) 
+GROUP BY t3.a, t3.b;
+
+EXPLAIN SELECT t5.a, t5.b, max( t4.b ) 
+FROM t5 JOIN t4 USING( a, b ) 
+GROUP BY t5.a, t5.b;
+
+EXPLAIN SELECT a FROM t6 GROUP BY a;
+EXPLAIN SELECT a FROM t6 ORDER BY a;
+
+DROP TABLE t1, t2, t3, t4, t5, t6;
diff -Nrup a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
--- a/mysql-test/t/innodb_mysql.test	2007-07-23 16:07:28 +02:00
+++ b/mysql-test/t/innodb_mysql.test	2007-08-07 18:51:37 +02:00
@@ -840,5 +840,19 @@ DISCONNECT con2;
 DROP PROCEDURE p1;
 DROP TABLE t1;
 
+#
+# Bug #28691: GROUP BY optimization ignores functional dependency on 
+# unique/primary key
+#
+CREATE TABLE t1(  
+  a INT NOT NULL PRIMARY KEY, 
+  b INT NOT NULL,  KEY( b ) 
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES ( 1, 1 );
+INSERT INTO t1 VALUES ( 2, 3 );
+INSERT INTO t1 VALUES ( 3, 2 );
+
+SELECT t1.a, b FROM t1 GROUP BY t1.a, t1.b;
+EXPLAIN SELECT t1.a, b FROM t1 GROUP BY t1.a, t1.b;
 
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-07-23 05:25:59 +02:00
+++ b/sql/sql_select.cc	2007-08-07 18:51:37 +02:00
@@ -166,6 +166,9 @@ static bool test_if_skip_sort_order(JOIN
 				    ha_rows select_limit, bool no_changes);
 static bool list_contains_unique_index(TABLE *table,
                           bool (*find_func) (Field *, void *), void *data);
+static key_map get_unique_keys_in_list(TABLE *table,
+                                       bool (*find_func) (Field *, void *), 
+                                       void *data, uint limit);
 static bool find_field_in_item_list (Field *field, void *data);
 static bool find_field_in_order_list (Field *field, void *data);
 static int create_sort_index(THD *thd, JOIN *join, ORDER *order,
@@ -216,6 +219,7 @@ static void select_describe(JOIN *join, 
 			    bool distinct, const char *message=NullS);
 static Item *remove_additional_cond(Item* conds);
 static void add_group_and_distinct_keys(JOIN *join, JOIN_TAB *join_tab);
+static bool key_is_unique(uint keynr, TABLE *table);
 
 
 /*
@@ -1011,7 +1015,7 @@ JOIN::optimize()
      We can do that if there are no aggregate functions, the
      fields in DISTINCT clause (if present) and/or columns in GROUP BY
      (if present) contain direct references to all key parts of
-     an unique index (in whatever order) and if the key parts of the
+     a unique index (in whatever order) and if the key parts of the
      unique index cannot contain NULLs.
      Note that the unique keys for DISTINCT and GROUP BY should not
      be the same (as long as they are unique).
@@ -1025,14 +1029,6 @@ JOIN::optimize()
        join_tab[const_tables].select->quick->get_type() != 
        QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX))
   {
-    if (group_list &&
-       list_contains_unique_index(join_tab[const_tables].table,
-                                 find_field_in_order_list,
-                                 (void *) group_list))
-    {
-      group_list= 0;
-      group= 0;
-    }
     if (select_distinct &&
        list_contains_unique_index(join_tab[const_tables].table,
                                  find_field_in_item_list,
@@ -12058,6 +12054,9 @@ static int test_if_order_by_key(ORDER *o
       DBUG_RETURN(0);
     reverse=flag;				// Remember if reverse
     key_part++;
+    if(key_part == key_part_end && key_is_unique(idx, table))
+      /* We matched a unique key already */
+      break;
   }
   *used_key_parts= on_primary_key ? table->key_info[idx].key_parts :
     (uint) (key_part - table->key_info[idx].key_part);
@@ -12193,16 +12192,53 @@ static bool
 list_contains_unique_index(TABLE *table,
                           bool (*find_func) (Field *, void *), void *data)
 {
+  return !get_unique_keys_in_list(table, find_func, data, 1).is_clear_all();
+}
+
+/**
+   @brief True if a key is unique in the SQL sense.
+   @param keynr The key's ordinal number in the table.
+   @param table The table that the key belongs to
+   @return true iff the key is unique.
+ */
+   
+static bool key_is_unique(uint keynr, TABLE *table) 
+{
+  return keynr == table->s->primary_key || 
+    (table->key_info[keynr].flags & HA_NOSAME);
+}
+
+/**
+  @brief Find a set of unique keys in a list with arbitrary implementation.
+  
+  @param table     The table whose keys are of interest.
+  @param find_func Pointer to function for searching for a field within list.
+  @param data      The list of keys.
+  @param limit     Number of keys in result.
+
+  @details The function loops through all keyparts of all keys of <table>,
+  collecting those that are in the list <data>. <find_func> is used to search
+  the list. The algorithm will stop when the set contains <limit> elements.
+ 
+  @return The set of all unique keys in <data>.
+*/
+
+static key_map get_unique_keys_in_list(TABLE *table,
+                                       bool (*find_func) (Field *, void *), 
+                                       void *data, uint limit)
+{
+  key_map unique_keys;
+  unique_keys.clear_all();
+  uint number_keys= 0;
   for (uint keynr= 0; keynr < table->s->keys; keynr++)
   {
-    if (keynr == table->s->primary_key ||
-         (table->key_info[keynr].flags & HA_NOSAME))
+    if (key_is_unique(keynr, table))
     {
       KEY *keyinfo= table->key_info + keynr;
       KEY_PART_INFO *key_part, *key_part_end;
-
-      for (key_part=keyinfo->key_part,
-           key_part_end=key_part+ keyinfo->key_parts;
+      
+      for (key_part= keyinfo->key_part,
+             key_part_end= key_part+ keyinfo->key_parts;
            key_part < key_part_end;
            key_part++)
       {
@@ -12210,11 +12246,14 @@ list_contains_unique_index(TABLE *table,
             !find_func(key_part->field, data))
           break;
       }
-      if (key_part == key_part_end)
-        return 1;
+      if (key_part == key_part_end) {
+        unique_keys.set_bit(keynr);
+        if(++number_keys == limit)
+          return unique_keys;
+      }
     }
   }
-  return 0;
+  return unique_keys;
 }
 
 
@@ -12322,6 +12361,20 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
   usable_keys= table->keys_in_use_for_query;
   DBUG_ASSERT(usable_keys.is_subset(table->s->keys_in_use));
 
+  key_map matched_unique_keys;
+
+  /* 
+     We don't use unique keys from temp tables, federated, and heap, 
+     respectively.
+  */
+  if (strcmp(table->alias, "") == 0 ||  
+      table->s->db_type == DB_TYPE_FEDERATED_DB ||  
+      table->s->db_type == DB_TYPE_HEAP)
+    matched_unique_keys.clear_all();
+  else
+    matched_unique_keys= get_unique_keys_in_list(table, find_field_in_order_list, 
+                                                 order, matched_unique_keys.length());
+
   for (ORDER *tmp_order=order; tmp_order ; tmp_order=tmp_order->next)
   {
     Item *item= (*tmp_order->item)->real_item();
@@ -12331,10 +12384,16 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
       DBUG_RETURN(0);
     }
     usable_keys.intersect(((Item_field*) item)->field->part_of_sortkey);
+    usable_keys.merge(matched_unique_keys);
     if (usable_keys.is_clear_all())
       DBUG_RETURN(0);					// No usable keys
   }
 
+  /* Remove non-ordered indexes */
+  for (uint keynr= 0; keynr < table->s->keys; keynr++)
+    if (tab->table->key_info[keynr].algorithm == HA_KEY_ALG_HASH)
+      usable_keys.clear_bit(keynr);
+
   ref_key= -1;
   /* Test if constant range in WHERE */
   if (tab->ref.key >= 0 && tab->ref.key_parts)
@@ -12495,13 +12554,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
       keys= *table->file->keys_to_use_for_scanning();
       keys.merge(table->used_keys);
 
-      /*
-	We are adding here also the index specified in FORCE INDEX clause, 
-	if any.
-        This is to allow users to use index in ORDER BY.
-      */
-      if (table->force_index) 
-	keys.merge(table->keys_in_use_for_query);
+      keys.merge(table->keys_in_use_for_query);
       keys.intersect(usable_keys);
     }
     else
Thread
bk commit into 5.0 tree (mhansson:1.2485) BUG#28691mhansson7 Aug