merge of 5.0-opt -> 5.1-opt

5 files changed, 90 insertions, 97 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 0069146bfe3..9b09cc0a43c 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -351,49 +351,6 @@ 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);
-CREATE TABLE t3 (a int PRIMARY KEY);
-INSERT INTO t3 VALUES (1),(2),(3),(4);
-CREATE TABLE t4 (a int PRIMARY KEY);
-INSERT INTO t4 VALUES (1),(2);
-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	2	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
-3	3	1	3	2	1
-3	3	1	3	2	2
-4	4	1	4	2	1
-4	4	1	4	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	2	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');
@@ -419,7 +376,7 @@ Warnings:
 Warning	1292	Truncated incorrect DOUBLE value: 'b'
 explain select f1 from t1 where f1 in ('a',1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	t1f1_idx	2	NULL	3	Using where; Using index
+1	SIMPLE	t1	index	t1f1_idx	t1f1_idx	2	NULL	3	Using where; Using index
 select f1 from t1 where f1 in ('a','b');
 f1
 a
@@ -448,7 +405,7 @@ Warning	1292	Truncated incorrect DOUBLE value: 'a'
 Warning	1292	Truncated incorrect DOUBLE value: 'a'
 explain select f2 from t2 where f2 in ('a',2);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
 select f2 from t2 where f2 in ('a','b');
 f2
 0
@@ -470,6 +427,6 @@ Warning	1292	Truncated incorrect DOUBLE value: 'b'
 Warning	1292	Truncated incorrect DOUBLE value: 'b'
 explain select f2 from t2 where f2 in (1,'b');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	t2f2	5	NULL	3	Using where; Using index
+1	SIMPLE	t2	index	t2f2	t2f2	5	NULL	3	Using where; Using index
 drop table t1, t2;
 End of 5.1 tests
diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result
index fea7464f854..e6c323276ea 100644
--- a/mysql-test/r/func_str.result
+++ b/mysql-test/r/func_str.result
@@ -1306,15 +1306,15 @@ Note	1003	select `test`.`t1`.`s` AS `s` from `test`.`t1` where (trim(both _latin
 DROP TABLE t1;
 create table t1(f1 varchar(4));
 explain extended select encode(f1,'zxcv') as 'enc' from t1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select encode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+Note	1003	select encode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1`
 explain extended select decode(f1,'zxcv') as 'enc' from t1;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	const row not found
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	0	0.00	const row not found
 Warnings:
-Note	1003	select decode(`test`.`t1`.`f1`,'zxcv') AS `enc` from `test`.`t1`
+Note	1003	select decode(`test`.`t1`.`f1`,_latin1'zxcv') AS `enc` from `test`.`t1`
 drop table t1;
 End of 4.1 tests
 create table t1 (d decimal default null);
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index c94efb50bfe..1023287572d 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -705,7 +705,7 @@ v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using where
-1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	5	Range checked for each record (index map: 0x4)
+1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	6	Range checked for each record (index map: 0x4)
 SELECT s.oxid FROM t1 v, t1 s 
 WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index 77d9d8c8973..86765efc42f 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -2970,16 +2970,6 @@ UPDATE t1 SET i= f1();
 DROP FUNCTION f1;
 DROP VIEW v1;
 DROP TABLE t1;
-CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
-CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION;
-INSERT INTO v1 (val) VALUES (2);
-INSERT INTO v1 (val) VALUES (4);
-INSERT INTO v1 (val) VALUES (6);
-ERROR HY000: CHECK OPTION failed 'test.v1'
-UPDATE v1 SET val=6 WHERE id=2;
-ERROR HY000: CHECK OPTION failed 'test.v1'
-DROP VIEW v1;
-DROP TABLE t1;
 DROP VIEW IF EXISTS v1, v2;
 DROP TABLE IF EXISTS t1;
 CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
@@ -3026,6 +3016,50 @@ x
 5
 DROP VIEW v;
 End of 5.0 tests.
+CREATE TABLE t1(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT UNSIGNED NOT NULL);
+CREATE VIEW v1 AS SELECT id, val FROM t1 WHERE val >= 1 AND val <= 5 WITH CHECK OPTION;
+INSERT INTO v1 (val) VALUES (2);
+INSERT INTO v1 (val) VALUES (4);
+INSERT INTO v1 (val) VALUES (6);
+ERROR HY000: CHECK OPTION failed 'test.v1'
+UPDATE v1 SET val=6 WHERE id=2;
+ERROR HY000: CHECK OPTION failed 'test.v1'
+DROP VIEW v1;
+DROP TABLE t1;
+DROP VIEW IF EXISTS v1, v2;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY, j INT);
+CREATE VIEW v1 AS SELECT j FROM t1;
+CREATE VIEW v2 AS SELECT * FROM t1;
+INSERT INTO t1 (j) VALUES (1);
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v1 (j) VALUES (2);
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+1
+INSERT INTO v2 (j) VALUES (3);
+# LAST_INSERT_ID() should be updated.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+INSERT INTO v1 (j) SELECT j FROM t1;
+# LAST_INSERT_ID() should not change.
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+3
+SELECT * FROM t1;
+i	j
+1	1
+2	2
+3	3
+4	1
+5	2
+6	3
+DROP VIEW v1, v2;
+DROP TABLE t1;
 DROP DATABASE IF EXISTS `d-1`;
 CREATE DATABASE `d-1`;
 USE `d-1`;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index db730cf1828..2a2d5535915 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -254,42 +254,44 @@ select some_id from t1 where some_id not in(-4,-1,-4);
 select some_id from t1 where some_id not in(-4,-1,3423534,2342342);
 drop table t1;
 
+# TODO:Disabled until re-resolution of bug #20420 for 5.1.
+# Results must be the same as in 5.0
+##
+## BUG#20420: optimizer reports wrong keys on left join with IN
+##
+#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);
 #
-# BUG#20420: optimizer reports wrong keys on left join with IN
+#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 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);
-
-CREATE TABLE t3 (a int PRIMARY KEY);
-INSERT INTO t3 VALUES (1),(2),(3),(4);
-
-CREATE TABLE t4 (a int PRIMARY KEY);
-INSERT INTO t4 VALUES (1),(2);
-
-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);
-
-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);
-
-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;
-
-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;
-
-DROP TABLE t1,t2,t3,t4;  
+#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);
+#
+#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);
+#
+#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;
+#
+#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;
+#
+#DROP TABLE t1,t2,t3,t4;  
 
 --echo End of 5.0 tests