Merge gkodinov@bk-internal.mysql.com:/home/bk/mysql-5.1-opt

into  rakia.gmz:/home/kgeorge/mysql/autopush/B20420-5.1-opt


mysql-test/r/func_in.result:
  Auto merged
mysql-test/t/func_in.test:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/opt_range.cc:
  Auto merged

1 files changed, 44 insertions, 0 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index cf44c56f061..9ec621b7f35 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -355,6 +355,50 @@ some_id
 1
 2
 drop table t1;
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1);
+CREATE TABLE t2 (a int, b int, PRIMARY KEY (a));
+INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102);
+CREATE TABLE t3 (a int PRIMARY KEY);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TABLE t4 (a int PRIMARY KEY,b int);
+INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002),
+(1003,1003),(1004,1004);
+EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	SIMPLE	t4	ALL	PRIMARY	NULL	NULL	NULL	7	Range checked for each record (index map: 0x1)
+SELECT STRAIGHT_JOIN * FROM t3 
+JOIN t1 ON t3.a=t1.a 
+JOIN t2 ON t3.a=t2.a
+JOIN t4 WHERE t4.a IN (t1.b, t2.b);
+a	a	b	a	b	a	b
+3	3	1	3	2	1	1
+3	3	1	3	2	2	2
+4	4	1	4	2	1	1
+4	4	1	4	2	2	2
+EXPLAIN SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t3	index	PRIMARY	PRIMARY	4	NULL	4	Using index
+1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t3.a	1	
+2	DEPENDENT SUBQUERY	t4	index	NULL	PRIMARY	4	NULL	7	Using where; Using index
+SELECT STRAIGHT_JOIN 
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 
+FROM t3, t1, t2
+WHERE t3.a=t1.a AND t3.a=t2.a;
+(SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b))
+3
+3
+DROP TABLE t1,t2,t3,t4;
 End of 5.0 tests
 create table t1(f1 char(1));
 insert into t1 values ('a'),('b'),('1');