From 47b357522a2e9329b2c0376db0199508fe3654d8 Mon Sep 17 00:00:00 2001
From: "sergefp@mysql.com" <>
Date: Fri, 29 Sep 2006 15:58:47 +0400
Subject: BUG#14940: Slow join order is chosen: [2nd commit with post-review
 fixes] - Re-worked the prev_record_reads() function to return the lower bound
 of   number of different table access scans that will be performed.

---
 mysql-test/r/join.result | 28 ++++++++++++++++++++++++++++
 mysql-test/t/join.test   | 21 +++++++++++++++++++++
 2 files changed, 49 insertions(+)

(limited to 'mysql-test')

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 75daa0fd46d..897ec4f7a6a 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -776,3 +776,31 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ALL	a,b	NULL	NULL	NULL	1000	Using where
 1	SIMPLE	t3	ref	b	b	5	test.t2.b	1	Using where
 drop table t1, t2, t3;
+create table t1 (a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, primary key(a));
+insert into t2 select @v:=A.a+10*B.a, @v  from t1 A, t1 B;
+explain select * from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+show status like '%cost%';
+Variable_name	Value
+Last_query_cost	4.016090
+select 'The cost of accessing t1 (dont care if it changes' '^';
+The cost of accessing t1 (dont care if it changes
+The cost of accessing t1 (dont care if it changes^
+select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
+Z
+vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv
+explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	10	
+1	SIMPLE	A	eq_ref	PRIMARY	PRIMARY	4	test.t1.a	1	
+1	SIMPLE	B	eq_ref	PRIMARY	PRIMARY	4	test.A.b	1	
+show status like '%cost%';
+Variable_name	Value
+Last_query_cost	24.016090
+select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
+Z
+^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error
+drop table t1, t2;
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 98bfb33b1e6..d0005f3b8f7 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -609,3 +609,24 @@ explain select * from t2,t3 where t2.a < 200 and t2.b=t3.b;
 
 drop table t1, t2, t3;
 
+# BUG#14940 {Wrong query plan is chosen because of odd results of
+# prev_record_reads() function }
+create table t1 (a int); 
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t2 (a int, b int, primary key(a));
+insert into t2 select @v:=A.a+10*B.a, @v  from t1 A, t1 B;
+
+explain select * from t1;
+show status like '%cost%';
+select 'The cost of accessing t1 (dont care if it changes' '^';
+
+select 'vv: Following query must use ALL(t1), eq_ref(A), eq_ref(B): vv' Z;
+
+explain select * from t1, t2 A, t2 B where A.a = t1.a and B.a=A.b;
+show status like '%cost%';
+select '^^: The above should be ~= 20 + cost(select * from t1). Value less than 20 is an error' Z;
+
+
+
+drop table t1, t2;
-- 
cgit v1.2.1