This is the consolidated patch for mdev-8646:bb-10.2-mdev8646

"Re-factor the code for post-join operations".

The patch mainly contains the code ported from mysql-5.6 and
created for two essential architectural changes:
1. WL#5558: Resolve ORDER BY execution method at the optimization stage
2. WL#6071: Inline tmp tables into the nested loops algorithm

The first task was implemented for mysql-5.6 by Ole John Aske.
It allows to make all decisions on ORDER BY operation at the optimization
stage.

The second task implemented for mysql-5.6 by Evgeny Potemkin adds JOIN_TAB
nodes for post-join operations that require temporary tables. It allows
to execute these operations within the nested loops algorithm that used to
be used before this task only for join queries. Besides these task moves
all planning on the execution of these operations from the execution phase
to the optimization phase.

Some other re-factoring changes of mysql-5.6 were pulled in, mainly because
it was easier to pull them in than roll them back. In particular all
changes concerning Ref_ptr_array were incorporated.

The port required some changes in the MariaDB code that concerned the
functionality of EXPLAIN and ANALYZE. This was done mainly by Sergey
Petrunia.

13 files changed, 108 insertions, 49 deletions

diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result
index 69930bc0215..2e106371c63 100644
--- a/mysql-test/r/analyze_format_json.result
+++ b/mysql-test/r/analyze_format_json.result
@@ -488,7 +488,7 @@ ANALYZE
     "select_id": 1,
     "r_loops": 1,
     "volatile parameter": "REPLACED",
-    "having_condition": "(TOP > a)",
+    "having_condition": "(TOP > t2.a)",
     "filesort": {
       "r_loops": 1,
       "volatile parameter": "REPLACED",
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index d6e5a69e217..e2a7c462efd 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -175,7 +175,7 @@ explain SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	4	Using where; Using temporary
 1	SIMPLE	t3	ref	a	a	5	test.t1.b	2	Using index
-1	SIMPLE	t2	index	a	a	4	NULL	5	Using where; Using index; Distinct; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	index	a	a	4	NULL	5	Using where; Using index; Using join buffer (flat, BNL join)
 SELECT distinct t3.a FROM t3,t2,t1 WHERE t3.a=t1.b AND t1.a=t2.a;
 a
 1
@@ -302,11 +302,11 @@ WHERE
 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 index; Using temporary
-1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (flat, BNL join)
-1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Distinct; Using join buffer (flat, BNL join)
-1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer (flat, BNL join)
-1	SIMPLE	t2_lj	ref	id	id	4	test.j_lj_t2.id	1	Using where; Using index; Distinct
-1	SIMPLE	j_lj_t3	index	id	id	4	NULL	2	Using where; Using index; Distinct; Using join buffer (flat, BNL join)
+1	SIMPLE	t2	index	id	id	8	NULL	1	Using index; Using join buffer (flat, BNL join)
+1	SIMPLE	t3	index	id	id	8	NULL	1	Using index; Using join buffer (flat, BNL join)
+1	SIMPLE	j_lj_t2	index	id	id	4	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
+1	SIMPLE	t2_lj	ref	id	id	4	test.j_lj_t2.id	1	Using where; Using index
+1	SIMPLE	j_lj_t3	index	id	id	4	NULL	2	Using where; Using index; Using join buffer (flat, BNL join)
 1	SIMPLE	t3_lj	ref	id	id	4	test.j_lj_t3.id	1	Using where; Using index; Distinct
 SELECT DISTINCT
 t1.id
@@ -518,7 +518,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1_1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
-1	SIMPLE	t1_2	index	NULL	PRIMARY	4	NULL	3	Using index; Distinct; Using join buffer (flat, BNL join)
+1	SIMPLE	t1_2	index	NULL	PRIMARY	4	NULL	3	Using index; Using join buffer (flat, BNL join)
 EXPLAIN SELECT DISTINCT t1_1.a, t1_1.b FROM t1 t1_1, t1 t1_2
 WHERE t1_1.a = t1_2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -916,8 +916,8 @@ SELECT STRAIGHT_JOIN DISTINCT t1.id  FROM
 t1, v1, t2 WHERE v1.id = t2.i AND t1.i1 = v1.i1 AND t2.i != 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	Using where; Using temporary
-1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.i1	9	100.00	Using where; Distinct
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Distinct; Using join buffer (flat, BNL join)
+1	PRIMARY	<derived2>	ref	key0	key0	5	test.t1.i1	9	100.00	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	96	100.00	
 Warnings:
 Note	1003	select straight_join distinct `test`.`t1`.`id` AS `id` from `test`.`t1` join `test`.`v1` join `test`.`t2` where ((`test`.`t2`.`i` = `v1`.`id`) and (`v1`.`i1` = `test`.`t1`.`i1`) and (`v1`.`id` <> 3))
diff --git a/mysql-test/r/func_group.result b/mysql-test/r/func_group.result
index 659580059b5..aa12100e0dc 100644
--- a/mysql-test/r/func_group.result
+++ b/mysql-test/r/func_group.result
@@ -1519,7 +1519,7 @@ SELECT MAX(pk) as max, i
 FROM t1
 ORDER BY max;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
 
 # Only 11 is correct for collumn i in this result
 SELECT MAX(pk) as max, i
diff --git a/mysql-test/r/group_by.result b/mysql-test/r/group_by.result
index 7f32643b727..f55489b7d93 100644
--- a/mysql-test/r/group_by.result
+++ b/mysql-test/r/group_by.result
@@ -1346,12 +1346,43 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	144	
+#
+#  For this explain, the query plan is weird: if we are using 
+#  the primary key for reasons other than doing grouping, can't
+#  GROUP BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
+#  Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+show status like 'Sort_%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	144
+Sort_scan	1
+# Proof ends.
+# 
+#  For this explain, the query plan is weird: if we are using 
+#  the primary key for reasons other than doing sorting, can't
+#  ORDER BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index
+1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	144	Using index; Using filesort
+#  Here's a proof it is really doing sorting:
+flush status;
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+show status like 'Sort_%';
+Variable_name	Value
+Sort_merge_passes	0
+Sort_priority_queue_sorts	0
+Sort_range	0
+Sort_rows	144
+Sort_scan	1
+# Proof ends.
+# 
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 a
 1
diff --git a/mysql-test/r/having.result b/mysql-test/r/having.result
index eda67460205..627edd60141 100644
--- a/mysql-test/r/having.result
+++ b/mysql-test/r/having.result
@@ -470,10 +470,9 @@ WHERE table2.f1 = 2
 GROUP BY table1.f1, table2.f2
 HAVING (table2.f2 = 8 AND table1.f1 >= 6);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	table2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using filesort
-1	SIMPLE	table1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having ((7 = 8) and (`test`.`table1`.`f1` >= 6))
+Note	1003	select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having 0
 EXPLAIN EXTENDED
 SELECT table1.f1, table2.f2
 FROM t1 AS table1
@@ -482,10 +481,9 @@ WHERE table2.f1 = 2
 GROUP BY table1.f1, table2.f2
 HAVING (table2.f2 = 8);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	SIMPLE	table2	const	PRIMARY	PRIMARY	4	const	1	100.00	Using filesort
-1	SIMPLE	table1	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible HAVING noticed after reading const tables
 Warnings:
-Note	1003	select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having (7 = 8)
+Note	1003	select `test`.`table1`.`f1` AS `f1`,7 AS `f2` from `test`.`t1` `table1` join `test`.`t1` `table2` where (`test`.`table1`.`f3` = 9) group by `test`.`table1`.`f1`,7 having 0
 DROP TABLE t1;
 #
 # Bug#52336 Segfault / crash in 5.1 copy_fields (param=0x9872980) at sql_select.cc:15355
diff --git a/mysql-test/r/limit.result b/mysql-test/r/limit.result
index 176a93c7a46..064fa5a18a7 100644
--- a/mysql-test/r/limit.result
+++ b/mysql-test/r/limit.result
@@ -80,13 +80,13 @@ create table t1 (a int);
 insert into t1 values (1),(2),(3),(4),(5),(6),(7);
 explain select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 select count(*) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 c
 7
 explain select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where; Using temporary
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	7	Using where
 select sum(a) c FROM t1 WHERE a > 0 ORDER BY c LIMIT 3;
 c
 28
diff --git a/mysql-test/r/limit_rows_examined.result b/mysql-test/r/limit_rows_examined.result
index 130d17ae270..318039db068 100644
--- a/mysql-test/r/limit_rows_examined.result
+++ b/mysql-test/r/limit_rows_examined.result
@@ -471,9 +471,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0;
 c1	sum(c2)
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 1;
-ERROR HY000: Sort aborted: 
+c1	sum(c2)
+Warnings:
+Warning	1931	Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete.
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 20;
 c1	sum(c2)
 aa	3
@@ -496,9 +498,11 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0;
 c1	sum(c2)
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 1;
-ERROR HY000: Sort aborted: 
+c1	sum(c2)
+Warnings:
+Warning	1931	Query execution was interrupted. The query examined at least 3 rows, which exceeds LIMIT ROWS EXAMINED (1). The query result may be incomplete.
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 20;
 c1	sum(c2)
 aa	3
@@ -627,7 +631,7 @@ CREATE TABLE t4 (a int);
 INSERT INTO t4 values (1), (2);
 INSERT INTO t4 SELECT a + 2 FROM t4 LIMIT ROWS EXAMINED 0;
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 2 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0). The query result may be incomplete.
 select * from t4;
 a
 1
@@ -666,7 +670,7 @@ MDEV-115
 
 SET @@optimizer_switch='in_to_exists=on,outer_join_with_cache=on';
 CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM;
-INSERT INTO t1 VALUES ('USA');
+INSERT INTO t1 VALUES ('USA'),('CAN');
 CREATE TABLE t2 ( b INT );
 INSERT INTO t2 VALUES (3899),(3914),(3888);
 CREATE TABLE t3 ( c VARCHAR(33), d INT );
@@ -676,8 +680,8 @@ SELECT DISTINCT a AS field1 FROM t1, t2
 WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d) 
 HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Distinct
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	3	
 2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (flat, BNL join)
 SELECT DISTINCT a AS field1 FROM t1, t2 
@@ -685,24 +689,27 @@ WHERE EXISTS (SELECT c FROM t3 LEFT JOIN t2 ON b = d)
 HAVING field1 > 'aaa' LIMIT ROWS EXAMINED 20;
 field1
 Warnings:
-Warning	1931	Query execution was interrupted. The query examined at least 23 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete.
+Warning	1931	Query execution was interrupted. The query examined at least 21 rows, which exceeds LIMIT ROWS EXAMINED (20). The query result may be incomplete.
 EXPLAIN
 SELECT DISTINCT a FROM t1, t2  HAVING a > ' ' LIMIT ROWS EXAMINED 14;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
-1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Distinct
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using join buffer (flat, BNL join)
 SELECT DISTINCT a FROM t1, t2  HAVING a > ' ' LIMIT ROWS EXAMINED 14;
 a
+USA
 Warnings:
 Warning	1931	Query execution was interrupted. The query examined at least 15 rows, which exceeds LIMIT ROWS EXAMINED (14). The query result may be incomplete.
 SELECT DISTINCT a FROM t1, t2  HAVING a > ' ' LIMIT ROWS EXAMINED 15;
 a
 USA
+CAN
 Warnings:
 Warning	1931	Query execution was interrupted. The query examined at least 16 rows, which exceeds LIMIT ROWS EXAMINED (15). The query result may be incomplete.
 SELECT DISTINCT a FROM t1, t2  HAVING a > ' ' LIMIT ROWS EXAMINED 16;
 a
 USA
+CAN
 Warnings:
 Warning	1931	Query execution was interrupted. The query examined at least 17 rows, which exceeds LIMIT ROWS EXAMINED (16). The query result may be incomplete.
 drop table t1,t2,t3;
diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result
index 093de1339f3..59f3a6fc5cc 100644
--- a/mysql-test/r/myisam.result
+++ b/mysql-test/r/myisam.result
@@ -523,11 +523,11 @@ a
 explain select sql_big_result distinct t1.a from t1,t2 order by t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
-1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	2	Using index; Distinct
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	2	Using index
 explain select distinct t1.a from t1,t2 order by t2.a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	Using temporary
-1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	2	Using index; Distinct
+1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	2	Using index
 drop table t1,t2;
 create table t1 (
 c1 varchar(32),
diff --git a/mysql-test/r/select_found.result b/mysql-test/r/select_found.result
index 92758fa134b..238a9414ead 100644
--- a/mysql-test/r/select_found.result
+++ b/mysql-test/r/select_found.result
@@ -83,20 +83,20 @@ UNIQUE KEY e_n (email,name)
 );
 EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	PRIMARY,kid	NULL	NULL	NULL	0	const row not found
-1	SIMPLE	t2	index	NULL	e_n	104	NULL	10	
+1	SIMPLE	t1	system	PRIMARY,kid	NULL	NULL	NULL	0	const row not found; Using temporary
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	200	
 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1  ON kid = t2.id WHERE t1.id IS NULL LIMIT 10;
 email
 email1
+email2
+email3
+email4
+email5
+email6
+email7
+email8
+email9
 email10
-email100
-email101
-email102
-email103
-email104
-email105
-email106
-email107
 SELECT FOUND_ROWS();
 FOUND_ROWS()
 200
diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result
index 0dc6f2b28ac..8a9cb4c9997 100644
--- a/mysql-test/r/show_explain.result
+++ b/mysql-test/r/show_explain.result
@@ -1044,7 +1044,7 @@ set debug_dbug='+d,show_explain_probe_do_select';
 SELECT b AS field1, b AS field2 FROM t1, t2, t3 WHERE d = b ORDER BY field1, field2;
 show explain for $thr2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	
 1	SIMPLE	t1	range	b	b	6	NULL	107	Using where; Using index
 1	SIMPLE	t3	ref	PRIMARY	PRIMARY	5	test.t1.b	1	Using index
 Warnings:
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 87645d187f2..4cd814468a4 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -19,7 +19,7 @@ SELECT 1 FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
 ORDER BY count(*);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using where; Using index; Using temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	t3	system	NULL	NULL	NULL	NULL	0	const row not found
 # should not crash the next statement
diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 531cec6b730..18e244d8152 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -996,8 +996,33 @@ EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
 EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
 EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
+
+--echo #
+--echo #  For this explain, the query plan is weird: if we are using 
+--echo #  the primary key for reasons other than doing grouping, can't
+--echo #  GROUP BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+--echo #  Here's a proof it is really doing sorting:
+flush status;
+--disable_result_log
+SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+--enable_result_log
+show status like 'Sort_%';
+--echo # Proof ends.
+--echo # 
+
+--echo #  For this explain, the query plan is weird: if we are using 
+--echo #  the primary key for reasons other than doing sorting, can't
+--echo #  ORDER BY code take advantage of this?  Well, currently it doesnt:
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+--echo #  Here's a proof it is really doing sorting:
+flush status;
+--disable_result_log
+SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+--enable_result_log
+show status like 'Sort_%';
+--echo # Proof ends.
+--echo # 
 SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
 EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
   IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
diff --git a/mysql-test/t/limit_rows_examined.test b/mysql-test/t/limit_rows_examined.test
index 45ee483c7aa..382530234be 100644
--- a/mysql-test/t/limit_rows_examined.test
+++ b/mysql-test/t/limit_rows_examined.test
@@ -305,7 +305,6 @@ select c1, sum(c2) from t3 group by c1;
 explain
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0;
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 0;
---error 1028
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 1;
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 20;
 select c1, sum(c2) from t3 group by c1 LIMIT ROWS EXAMINED 21;
@@ -321,7 +320,6 @@ insert into t3i values
 explain
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0;
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 0;
---error 1028
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 1;
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 20;
 select c1, sum(c2) from t3i group by c1 LIMIT ROWS EXAMINED 21;
@@ -432,7 +430,7 @@ drop table t1,t2,t1i,t2i;
 SET @@optimizer_switch='in_to_exists=on,outer_join_with_cache=on';
 
 CREATE TABLE t1 ( a VARCHAR(3) ) ENGINE=MyISAM;
-INSERT INTO t1 VALUES ('USA');
+INSERT INTO t1 VALUES ('USA'),('CAN');
 
 CREATE TABLE t2 ( b INT );
 INSERT INTO t2 VALUES (3899),(3914),(3888);