cleanup: make a couple of tests more robust

with --sorted_result

11 files changed, 177 insertions, 172 deletions

diff --git a/mysql-test/r/row.result b/mysql-test/r/row.result
index 9b796c24354..7483f37970f 100644
--- a/mysql-test/r/row.result
+++ b/mysql-test/r/row.result
@@ -354,12 +354,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	test.t1.a	1	Using index
 SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b;
 a	b	a	b	c
-1	1	1	1	2
-1	1	3	1	3
-1	2	1	2	2
 1	1	1	1	1
+1	1	1	1	2
 1	1	3	1	1
+1	1	3	1	3
 1	2	1	2	1
+1	2	1	2	2
 SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
 a	b	a	b	c
 1	2	1	1	1
@@ -388,10 +388,10 @@ Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` - 1 = `test`.`t2`.`a` - 1 and `test`.`t1`.`b` = `test`.`t2`.`b` + 1
 SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
 a	b	a	b	c
-1	2	1	1	2
-3	2	3	1	3
 1	2	1	1	1
+1	2	1	1	2
 3	2	3	1	1
+3	2	3	1	3
 EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	PRIMARY	PRIMARY	8	const,const	1	Using index
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index f3187568428..3b18a4d0262 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -6012,26 +6012,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6362,26 +6362,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/subselect_no_exists_to_in.result b/mysql-test/r/subselect_no_exists_to_in.result
index d1cad4f9389..ecefc0b8883 100644
--- a/mysql-test/r/subselect_no_exists_to_in.result
+++ b/mysql-test/r/subselect_no_exists_to_in.result
@@ -6012,26 +6012,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6362,26 +6362,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 32d39224e2a..49a60546b78 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -6009,26 +6009,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6357,26 +6357,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 2daf33980d6..304a427df6b 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -6005,26 +6005,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6353,26 +6353,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 09530f3ac0f..c045be5197c 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -6018,26 +6018,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6368,26 +6368,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 7efb5127d2b..3440ecbc6c1 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -6005,26 +6005,26 @@ SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
 WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 DROP TABLE t1,t2;
 #
 # LP bug 919427: EXPLAIN for a query over a single-row table 
@@ -6353,26 +6353,26 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 f1	f1_key
-v	j
+s	c
+s	d
+s	d
+s	d
 s	j
-v	v
+s	m
+s	s
+s	t
 s	v
+s	y
 v	c
-s	c
-v	m
-s	m
 v	d
-s	d
 v	d
-s	d
-v	y
-s	y
-v	t
-s	t
 v	d
-s	d
+v	j
+v	m
 v	s
-s	s
+v	t
+v	v
+v	y
 explain SELECT table1.f1, table2.f1_key
 FROM t1 AS table1, t2 AS table2
 WHERE EXISTS 
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 7aa1ad3c044..0cd2655777c 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -1579,22 +1579,22 @@ execute stmt1 using @a;
 deallocate prepare stmt1;
 select * from v3;
 a	b
-100	1000
-101	1000
-300	1000
-301	1000
+100	0
 100	10
-101	10
-300	10
-301	10
+100	1000
 100	2000
-101	2000
-300	2000
-301	2000
-100	0
 101	0
+101	10
+101	1000
+101	2000
 300	0
+300	10
+300	1000
+300	2000
 301	0
+301	10
+301	1000
+301	2000
 drop view v3;
 drop tables t1,t2;
 create table t1(f1 int);
diff --git a/mysql-test/t/row.test b/mysql-test/t/row.test
index 1c1d1b00910..80c61c414bf 100644
--- a/mysql-test/t/row.test
+++ b/mysql-test/t/row.test
@@ -187,6 +187,7 @@ SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b);
 
 EXPLAIN SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.b=2;
 EXPLAIN SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
+--sorted_result
 SELECT * FROM t1,t2 WHERE t1.a=1 and t1.b=t2.b;
 SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,2);
 
@@ -194,6 +195,7 @@ EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1);
 SELECT * FROM t1,t2 WHERE (t1.a,t1.b)=(t2.a,t2.b+1);
 
 EXPLAIN EXTENDED SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
+--sorted_result
 SELECT * FROM t1,t2 WHERE (t1.a-1,t1.b)=(t2.a-1,t2.b+1);
 
 EXPLAIN SELECT * FROM t2 WHERE a=3 AND b=2;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 44a400dca20..f2e0adf9d53 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5031,6 +5031,7 @@ EXPLAIN
 SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
   WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
                   WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
+--sorted_result
 SELECT table1.f1, table2.f1_key FROM t1 AS table1, t2 AS table2
   WHERE EXISTS (SELECT DISTINCT f1_key FROM t2 
                   WHERE f1_key != table2.f1_key AND f1_key >= table1.f1);
@@ -5330,6 +5331,7 @@ SELECT DISTINCT f1_key
 FROM t2 
 WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
 
+--sorted_result
 eval $query;
 eval explain $query;
 
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 229df8b893f..3475d325321 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -1407,6 +1407,7 @@ execute stmt1 using @a;
 set @a= 301;
 execute stmt1 using @a;
 deallocate prepare stmt1;
+--sorted_result
 select * from v3;
 
 drop view v3;