From: Gleb Shchepa
Date: July 2 2011 1:54pm
Subject: bzr push into mysql-trunk branch (gleb.shchepa:3374 to 3375) WL#4897
List-Archive: http://lists.mysql.com/commits/140063
Message-Id: <20110702135421.31B922839D@t>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3375 Gleb Shchepa	2011-07-02
      WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
      
      Merge-time fix for the bug: the Server loses "Using temporary"
      and/or "Using filesort" messages if the respective JOIN_TAB
      is not the first one in the join.
     @ mysql-test/include/explain_non_select.inc
        Test case for the bugfix has been added.
     @ mysql-test/r/compress.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/distinct.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/group_by.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/group_min_max.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/information_schema.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/innodb_explain_non_select.result
        Test case for the bugfix has been added.
     @ mysql-test/r/join_cache_jcl5.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/join_cache_jcl6.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/join_cache_jcl7.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/join_cache_jcl8.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ mysql-test/r/myisam_explain_non_select.result
        Test case for the bugfix has been added.
     @ mysql-test/suite/innodb/r/innodb_mysql.result
        Remove unnecessary result modification (will be eliminated
        by the consolidated patch.
     @ sql/opt_explain.cc
        New Explain_table_base::explain_tmptable_and_filesort function
        has been introduced to append "Using temporary" and/or
        "Using filesort" messages to the "extra" column.
        
        The Explain_join::explain_extra function has been modified to
        1) fix the bug and
        2) remove unnecessary modifications of many result files by this WL.

    modified:
      mysql-test/include/explain_non_select.inc
      mysql-test/r/compress.result
      mysql-test/r/distinct.result
      mysql-test/r/group_by.result
      mysql-test/r/group_min_max.result
      mysql-test/r/information_schema.result
      mysql-test/r/innodb_explain_non_select.result
      mysql-test/r/join_cache_jcl5.result
      mysql-test/r/join_cache_jcl6.result
      mysql-test/r/join_cache_jcl7.result
      mysql-test/r/join_cache_jcl8.result
      mysql-test/r/myisam_explain_non_select.result
      mysql-test/suite/innodb/r/innodb_mysql.result
      sql/opt_explain.cc
 3374 Gleb Shchepa	2011-06-23
      WL#4897: Add EXPLAIN INSERT/UPDATE/DELETE
      
      Post-review bugfix:
      after EXPLAIN bulk INSERT...SELECT and bulk INSERT...SELECT                                          
      to a # MyISAM table the SELECT query may fail with the                                               
      "1030: Got error 124 from storage engine" error message.                                             
                                                                                                           
      The select_insert class functions have been modified                                                 
      to not to call ha_start_bulk_insert() on EXPLAIN query.
     @ mysql-test/include/explain_non_select.inc
        Test case has been added.
     @ mysql-test/r/innodb_explain_non_select.result
        Test case has been added.
     @ mysql-test/r/myisam_explain_non_select.result
        Test case has been added.
     @ sql/sql_insert.cc
        The select_insert class functions have been modified                                                 
        to not to call ha_start_bulk_insert() on EXPLAIN query.

    modified:
      mysql-test/include/explain_non_select.inc
      mysql-test/r/innodb_explain_non_select.result
      mysql-test/r/myisam_explain_non_select.result
      sql/sql_insert.cc
=== modified file 'mysql-test/include/explain_non_select.inc'
--- a/mysql-test/include/explain_non_select.inc	2011-06-22 21:48:04 +0000
+++ b/mysql-test/include/explain_non_select.inc	2011-07-02 13:53:01 +0000
@@ -740,3 +740,13 @@ SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 =
 
 DROP TABLE t1, t2;
 
+--echo #73
+
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+
+DROP TABLE t1,t2;
+

=== modified file 'mysql-test/r/compress.result'
--- a/mysql-test/r/compress.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/compress.result	2011-07-02 13:53:01 +0000
@@ -1434,7 +1434,7 @@ companynr	companynr
 41	40
 explain select distinct t2.companynr,t4.companynr from t2,t4 where t2.companynr=t4.companynr+1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using temporary; Using index
+1	SIMPLE	t4	index	NULL	PRIMARY	1	NULL	12	Using index; Using temporary
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1199	Using where; Using join buffer (BNL, incremental buffers)
 select t2.fld1,t2.companynr,fld3,period from t3,t2 where t2.fld1 = 38208 and t2.fld1=t3.t2nr and period = 1008 or t2.fld1 = 38008 and t2.fld1 =t3.t2nr and period = 1008;
 fld1	companynr	fld3	period

=== modified file 'mysql-test/r/distinct.result'
--- a/mysql-test/r/distinct.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/distinct.result	2011-07-02 13:53:01 +0000
@@ -189,7 +189,7 @@ insert into t4 select * from t3;
 insert into t3 select * from t4;
 explain select distinct t1.a from t1,t3 where t1.a=t3.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using temporary; Using index
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	4	Using index; Using temporary
 1	SIMPLE	t3	ref	a	a	5	test.t1.a	11	Using index; Distinct
 select distinct t1.a from t1,t3 where t1.a=t3.a;
 a
@@ -299,7 +299,7 @@ WHERE
 ((t1.id=j_lj_t2.id AND t2_lj.id IS NULL) OR (t1.id=t2.id AND t2.idx=2))
 AND ((t1.id=j_lj_t3.id AND t3_lj.id IS NULL) OR (t1.id=t3.id AND t3.idx=2));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	id	id	4	NULL	2	Using temporary; Using index
+1	SIMPLE	t1	index	id	id	4	NULL	2	Using index; Using temporary
 1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (BNL, incremental buffers)
 1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer (BNL, incremental buffers)
@@ -753,7 +753,7 @@ INSERT INTO t1(a, b, c) VALUES (1, 1, 1)
 (1, 2, 3);
 EXPLAIN SELECT DISTINCT a, b, d, c FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	PRIMARY	16	NULL	7	Using temporary; Using index for group-by
+1	SIMPLE	t1	range	NULL	PRIMARY	16	NULL	7	Using index for group-by; Using temporary
 SELECT DISTINCT a, b, d, c FROM t1;
 a	b	d	c
 1	1	0	1

=== modified file 'mysql-test/r/group_by.result'
--- a/mysql-test/r/group_by.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/group_by.result	2011-07-02 13:53:01 +0000
@@ -304,10 +304,10 @@ spid	sum(userid)
 1	1
 explain select sql_big_result score,count(*) from t1 group by score desc;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using filesort; Using index
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index; Using filesort
 explain select sql_big_result score,count(*) from t1 group by score desc order by null;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using filesort; Using index
+1	SIMPLE	t1	index	NULL	score	3	NULL	8	Using index; Using filesort
 select sql_big_result score,count(*) from t1 group by score desc;
 score	count(*)
 3	5
@@ -905,7 +905,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using index
 EXPLAIN SELECT SQL_BIG_RESULT b, sum(1) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using filesort; Using index
+1	SIMPLE	t1	index	NULL	b	5	NULL	128	Using index; Using filesort
 SELECT b, sum(1) FROM t1 GROUP BY b;
 b	sum(1)
 0	6
@@ -1648,7 +1648,7 @@ NULL
 1
 EXPLAIN SELECT b from t2 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	a	10	NULL	6	Using temporary; Using filesort; Using index
+1	SIMPLE	t2	index	NULL	a	10	NULL	6	Using index; Using temporary; Using filesort
 SELECT b from t2 GROUP BY b;
 b
 NULL

=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/group_min_max.result	2011-07-02 13:53:01 +0000
@@ -1714,7 +1714,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t1	range	idx_t1_0,idx_t1_1,idx_t1_2	idx_t1_1	147	NULL	14	Using where; Using index for group-by
 explain select distinct b from t1 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using temporary; Using filesort; Using index for group-by
+1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by; Using temporary; Using filesort
 explain select distinct a1,a2,b from t2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using index for group-by
@@ -1729,7 +1729,7 @@ id	select_type	table	type	possible_keys
 1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	146	NULL	#	Using where; Using index for group-by
 explain select distinct b from t2 where (a2 >= 'b') and (b = 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using temporary; Using filesort; Using index for group-by
+1	SIMPLE	t2	range	NULL	idx_t2_1	146	NULL	#	Using where; Using index for group-by; Using temporary; Using filesort
 select distinct a1,a2,b from t1;
 a1	a2	b
 a	a	a
@@ -2354,7 +2354,7 @@ a	MIN(b)	MAX(b)
 EXPLAIN
 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using temporary; Using filesort; Using index for group-by
+1	SIMPLE	t1	range	NULL	break_it	10	NULL	7	Using index for group-by; Using temporary; Using filesort
 SELECT a, MIN(b), MAX(b) FROM t1 GROUP BY a ORDER BY a DESC;
 a	MIN(b)	MAX(b)
 4	1	3
@@ -2444,7 +2444,7 @@ a	b
 3	13
 explain extended select sql_buffer_result a, max(b)+1 from t1 where a = 0 group by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	t1	range	PRIMARY,index	PRIMARY	4	NULL	3	100.00	Using where; Using temporary; Using index for group-by
+1	SIMPLE	t1	range	PRIMARY,index	PRIMARY	4	NULL	3	100.00	Using where; Using index for group-by; Using temporary
 Warnings:
 Note	1003	select sql_buffer_result `test`.`t1`.`a` AS `a`,(max(`test`.`t1`.`b`) + 1) AS `max(b)+1` from `test`.`t1` where (`test`.`t1`.`a` = 0) group by `test`.`t1`.`a`
 drop table t1;
@@ -2833,7 +2833,7 @@ COUNT(DISTINCT b)
 8
 EXPLAIN SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using filesort; Using index
+1	SIMPLE	t1	index	NULL	a	10	NULL	16	Using index; Using filesort
 SELECT COUNT(DISTINCT a) FROM t1 GROUP BY b;
 COUNT(DISTINCT a)
 2
@@ -2882,7 +2882,7 @@ SELECT 1 FROM t1 GROUP BY a HAVING COUNT
 1
 EXPLAIN SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1_1	index	NULL	a	10	NULL	16	Using temporary; Using filesort; Using index
+1	SIMPLE	t1_1	index	NULL	a	10	NULL	16	Using index; Using temporary; Using filesort
 1	SIMPLE	t1_2	index	NULL	a	10	NULL	16	Using index; Using join buffer (BNL, incremental buffers)
 SELECT COUNT(DISTINCT t1_1.a) FROM t1 t1_1, t1 t1_2 GROUP BY t1_1.a;
 COUNT(DISTINCT t1_1.a)

=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result	2011-05-30 11:03:42 +0000
+++ b/mysql-test/r/information_schema.result	2011-07-02 13:53:01 +0000
@@ -1286,7 +1286,7 @@ from information_schema.tables
 order by object_schema;
 explain select * from v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Using filesort; Open_frm_only; Scanned all databases
+1	SIMPLE	tables	ALL	NULL	NULL	NULL	NULL	NULL	Open_frm_only; Scanned all databases; Using filesort
 explain select * from (select table_name from information_schema.tables) as a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	0	const row not found

=== modified file 'mysql-test/r/innodb_explain_non_select.result'
--- a/mysql-test/r/innodb_explain_non_select.result	2011-06-22 21:48:04 +0000
+++ b/mysql-test/r/innodb_explain_non_select.result	2011-07-02 13:53:01 +0000
@@ -3076,3 +3076,12 @@ id	select_type	table	type	possible_keys
 INSERT INTO t2 SELECT * FROM t1;
 SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
 DROP TABLE t1, t2;
+#73
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (BNL, incremental buffers)
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/join_cache_jcl5.result'
--- a/mysql-test/r/join_cache_jcl5.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl5.result	2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using temporary; Using filesort; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA, regular buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (BKA, regular buffers)
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (BKA, regular buffers)
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;

=== modified file 'mysql-test/r/join_cache_jcl6.result'
--- a/mysql-test/r/join_cache_jcl6.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl6.result	2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using temporary; Using filesort; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA, incremental buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (BKA, incremental buffers)
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (BKA, incremental buffers)
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;

=== modified file 'mysql-test/r/join_cache_jcl7.result'
--- a/mysql-test/r/join_cache_jcl7.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl7.result	2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using temporary; Using filesort; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (BKA_UNIQUE, regular buffers)
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (BKA_UNIQUE, regular buffers)
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;

=== modified file 'mysql-test/r/join_cache_jcl8.result'
--- a/mysql-test/r/join_cache_jcl8.result	2011-06-16 14:13:47 +0000
+++ b/mysql-test/r/join_cache_jcl8.result	2011-07-02 13:53:01 +0000
@@ -1449,7 +1449,7 @@ explain
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using temporary; Using filesort; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	8	Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	i_a	i_a	5	test.t1.a	2	Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
 select t1.a, count(t2.p) as count
 from t1 left join t2 on t1.a=t2.a and t2.p % 2 = 1 group by t1.a;
@@ -1760,7 +1760,7 @@ explain
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using join buffer (BKA_UNIQUE, incremental buffers)
 select t1.id1, sum(t2.id2) from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 group by t1.id1;
@@ -1772,7 +1772,7 @@ explain
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using temporary; Using filesort; Using index
+1	SIMPLE	t1	ref	idx1	idx1	5	const	4	Using where; Using index; Using temporary; Using filesort
 1	SIMPLE	t2	ref	idx2	idx2	5	test.t1.id1	2	Using where; Using join buffer (BKA_UNIQUE, incremental buffers)
 select t1.id1  from t1 join t2 on t1.id1=t2.id1 
 where t1.d=3 and t2.id2 > 200 order by t1.id1;

=== modified file 'mysql-test/r/myisam_explain_non_select.result'
--- a/mysql-test/r/myisam_explain_non_select.result	2011-06-22 21:48:04 +0000
+++ b/mysql-test/r/myisam_explain_non_select.result	2011-07-02 13:53:01 +0000
@@ -2857,3 +2857,12 @@ id	select_type	table	type	possible_keys
 INSERT INTO t2 SELECT * FROM t1;
 SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
 DROP TABLE t1, t2;
+#73
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+INSERT INTO t1 VALUES (1), (2);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 USING(id) GROUP BY t1.id;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	0	const row not found
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary; Using filesort
+DROP TABLE t1,t2;

=== modified file 'mysql-test/suite/innodb/r/innodb_mysql.result'
--- a/mysql-test/suite/innodb/r/innodb_mysql.result	2011-06-02 13:39:03 +0000
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result	2011-07-02 13:53:01 +0000
@@ -340,10 +340,10 @@ insert into t1 values ( 1,"e"),(2,"a"),(
 alter table t1 drop primary key, add primary key (f2, f1);
 explain select distinct f1 a, f1 b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	4	Using temporary; Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	4	Using index; Using temporary
 explain select distinct f1, f2 from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	3	Using temporary; Using index for group-by
+1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	3	Using index for group-by; Using temporary
 drop table t1;
 CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
 INDEX (name));
@@ -993,7 +993,7 @@ key	bkey
 key_len	5
 ref	const
 rows	16
-Extra	Using where; Using filesort; Using index
+Extra	Using where; Using index; Using filesort
 SELECT * FROM t2 WHERE b=1 ORDER BY a;
 a	b	c
 1	1	1
@@ -1279,7 +1279,7 @@ key	b
 key_len	5
 ref	NULL
 rows	3
-Extra	Using filesort; Using index
+Extra	Using index; Using filesort
 SELECT * FROM t1 ORDER BY b ASC, a DESC;
 a	b
 1	1
@@ -1295,7 +1295,7 @@ key	b
 key_len	5
 ref	NULL
 rows	3
-Extra	Using filesort; Using index
+Extra	Using index; Using filesort
 SELECT * FROM t1 ORDER BY b DESC, a ASC;
 a	b
 2	2

=== modified file 'sql/opt_explain.cc'
--- a/sql/opt_explain.cc	2011-06-17 14:05:57 +0000
+++ b/sql/opt_explain.cc	2011-07-02 13:53:01 +0000
@@ -289,9 +289,10 @@ protected:
                             const JOIN_TAB *tab,
                             int quick_type,
                             uint keyno,
-                            bool need_tmp_table,
-                            bool need_sort,
                             String *str_extra);
+  void explain_tmptable_and_filesort(bool need_tmp_table_arg,
+                                     bool need_sort_arg,
+                                     String *str_extra);
 };
 
 
@@ -302,8 +303,8 @@ protected:
 class Explain_join : public Explain_table_base
 {
 private:
-  const bool need_tmp_table; ///< add "Using temporary" to "extra" if true
-  const bool need_order; ///< add "Using filesort"" to "extra" if true
+  bool need_tmp_table; ///< add "Using temporary" to "extra" if true
+  bool need_order; ///< add "Using filesort"" to "extra" if true
   const bool distinct; ///< add "Distinct" string to "extra" column if true
 
   uint tabnum; ///< current tab number in join->join_tab[]
@@ -713,8 +714,6 @@ void Explain_table_base::explain_extra_c
                                               const JOIN_TAB *tab,
                                               int quick_type,
                                               uint keyno,
-                                              bool need_tmp_table,
-                                              bool need_sort,
                                               String *str_extra)
 {
   if ((keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno &&
@@ -782,9 +781,15 @@ void Explain_table_base::explain_extra_c
     */
     str_extra->append(STRING_WITH_LEN("; Using MRR"));
   }
-  if (need_tmp_table)
+}
+
+void Explain_table_base::explain_tmptable_and_filesort(bool need_tmp_table_arg,
+                                                       bool need_sort_arg,
+                                                       String *str_extra)
+{
+  if (need_tmp_table_arg)
     str_extra->append(STRING_WITH_LEN("; Using temporary"));
-  if (need_sort)
+  if (need_sort_arg)
     str_extra->append(STRING_WITH_LEN("; Using filesort"));
 }
 
@@ -967,10 +972,7 @@ bool Explain_join::explain_extra()
     else if (select && select->quick)
       keyno = select->quick->index;
 
-    explain_extra_common(select, tab, quick_type, keyno,
-                         (tabnum == 0 && need_tmp_table),
-                         (tabnum == 0 && need_order),
-                         &str_extra);
+    explain_extra_common(select, tab, quick_type, keyno, &str_extra);
 
     const TABLE_LIST *table_list= table->pos_in_table_list;
     if (table_list->schema_table &&
@@ -1008,6 +1010,9 @@ bool Explain_join::explain_extra()
         str_extra.append(STRING_WITH_LEN("; Using index"));
     }
 
+    explain_tmptable_and_filesort(need_tmp_table, need_order, &str_extra);
+    need_tmp_table= need_order= false;
+
     if (distinct && test_all_bits(used_tables,thd->used_tables))
       str_extra.append(STRING_WITH_LEN("; Distinct"));
 
@@ -1163,8 +1168,8 @@ bool Explain_table::explain_extra()
   const uint keyno= (select && select->quick) ? select->quick->index : key;
   const int quick_type= (select && select->quick) ? select->quick->get_type() 
                                                   : -1;
-  explain_extra_common(select, NULL, quick_type, keyno, need_tmp_table,
-                       need_sort, &str_extra);
+  explain_extra_common(select, NULL, quick_type, keyno, &str_extra);
+  explain_tmptable_and_filesort(need_tmp_table, need_sort, &str_extra);
 
   /* Skip initial "; "*/
   const char *str= str_extra.ptr();

No bundle (reason: useless for push emails).