BUG#913030: Optimizer chooses a suboptimal excution plan for Q18 from DBT-3

- When doing join optimization, pre-sort the tables so that they mimic the execution
  order we've had with 'semijoin=off'. 
- That way, we will not get regressions when there are two query plans (the old and the
  new) that have indentical costs but different execution times (because of factors that
  the optimizer was not able to take into account).
  

1 files changed, 19 insertions, 18 deletions

diff --git a/mysql-test/r/subselect_sj2.result b/mysql-test/r/subselect_sj2.result
index 8a9946c404e..b54d4e8db56 100644
--- a/mysql-test/r/subselect_sj2.result
+++ b/mysql-test/r/subselect_sj2.result
@@ -49,9 +49,9 @@ a	b
 19	14
 explain select * from t2 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t2	ref	b	b	5	test.t1.a	2	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t2	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t2 where b in (select a from t1);
 a	b
 1	1
@@ -69,9 +69,9 @@ insert into t3 select a,a, a,a,a from t0;
 insert into t3 select a,a, a+100,a+100,a+100 from t0;
 explain select * from t3 where b in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	3	
-1	PRIMARY	t3	ref	b	b	5	test.t1.a	1	
-2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	20	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	3	
 select * from t3 where b in (select a from t1);
 a	b	pk1	pk2	pk3
 1	1	1	1	1
@@ -95,15 +95,19 @@ A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a, A.a + 10*B.a
 from t0 A, t0 B where B.a <5;
 explain select * from t3 where b in (select a from t0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	10	
-1	PRIMARY	t3	ref	b	b	5	test.t0.a	1	
-2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
+1	PRIMARY	t3	ALL	b	NULL	NULL	NULL	56	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
+2	MATERIALIZED	t0	ALL	NULL	NULL	NULL	NULL	10	
 set @save_ecp= @@engine_condition_pushdown;
 set engine_condition_pushdown=0;
 select * from t3 where b in (select A.a+B.a from t0 A, t0 B where B.a<5);
 a	b	pk1	pk2
 0	0	0	0
 1	1	1	1
+10	10	10	10
+11	11	11	11
+12	12	12	12
+13	13	13	13
 2	2	2	2
 3	3	3	3
 4	4	4	4
@@ -112,10 +116,6 @@ a	b	pk1	pk2
 7	7	7	7
 8	8	8	8
 9	9	9	9
-10	10	10	10
-11	11	11	11
-12	12	12	12
-13	13	13	13
 set engine_condition_pushdown=@save_ecp;
 set join_buffer_size= @save_join_buffer_size;
 set max_heap_table_size= @save_max_heap_table_size;
@@ -748,8 +748,8 @@ c2 in (select 1 from t3, t2) and
 c1 in (select convert(c6,char(1)) from t2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(t2)
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	FirstMatch(t2)
 drop table t2, t3;
 #
@@ -931,9 +931,10 @@ SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	PRIMARY,d	d	9	NULL	17	Using where; Using index; LooseScan
-1	PRIMARY	t1	ref	a	a	5	test.t2.d	1	Using where; Using index; FirstMatch(t2)
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
 1	PRIMARY	t1	ref	b	b	4	test.t2.d	1	
+2	MATERIALIZED	t2	index_merge	PRIMARY,d	d,PRIMARY	4,4	NULL	2	Using sort_union(d,PRIMARY); Using where
+2	MATERIALIZED	t1	ref	a	a	5	test.t2.d	1	Using where; Using index
 SELECT * FROM t1 WHERE b IN (
 SELECT d FROM t2, t1
 WHERE a = d AND ( pk < 2 OR d = 'z' )
@@ -993,8 +994,8 @@ WHERE
 alias2.b = alias1.a AND 
 (alias1.b >= alias1.a OR alias2.b = 'z'));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	19	
-1	PRIMARY	t2	ref	a	a	4	test.alias1.a	1	
+1	PRIMARY	t2	ALL	a	NULL	NULL	NULL	38	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	func,func	1	
 2	MATERIALIZED	alias1	ALL	a	NULL	NULL	NULL	19	Using where
 2	MATERIALIZED	alias2	ref	a	a	4	test.alias1.a	1	Using where
 SELECT * FROM t2