merge with 5.3

sql/sql_insert.cc:
  CREATE ... IF NOT EXISTS may do nothing, but
  it is still not a failure. don't forget to my_ok it.
  ******
  CREATE ... IF NOT EXISTS may do nothing, but
  it is still not a failure. don't forget to my_ok it.
sql/sql_table.cc:
  small cleanup
  ******
  small cleanup

1 files changed, 151 insertions, 6 deletions

diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 10d960deef5..918ed1f21e6 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1307,7 +1307,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select '1' AS `f1`,NULL AS `f2`,'3' AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce('1',NULL),'3') in ((1,3),(2,2)))
+Note	1003	select 1 AS `f1`,NULL AS `f2`,3 AS `f3`,NULL AS `f1`,NULL AS `f2` from `test`.`t2` where ((coalesce(1,NULL),3) in ((1,3),(2,2)))
 SELECT * FROM t1 LEFT JOIN t2 ON t1.f2 = t2.f2
 WHERE (COALESCE(t1.f1, t2.f1), f3) IN ((1, 3), (2, 2));
 f1	f2	f3	f1	f2
@@ -1340,7 +1340,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	tt6	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	SIMPLE	tt7	ALL	NULL	NULL	NULL	NULL	2	Using where
 1	SIMPLE	tt8	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	SIMPLE	tt9	ALL	NULL	NULL	NULL	NULL	2	Using join buffer
+1	SIMPLE	tt9	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (flat, BNL join)
 SET optimizer_search_depth = DEFAULT;
 DROP TABLE t1;
 #
@@ -1359,7 +1359,7 @@ EXPLAIN SELECT STRAIGHT_JOIN COUNT(*) FROM t1 TA1
 RIGHT JOIN t2 TA2 JOIN t2 TA3 ON TA2.f1 ON TA3.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	TA2	ALL	NULL	NULL	NULL	NULL	20	Using where
-1	SIMPLE	TA3	ALL	NULL	NULL	NULL	NULL	20	Using join buffer
+1	SIMPLE	TA3	ALL	NULL	NULL	NULL	NULL	20	Using join buffer (flat, BNL join)
 1	SIMPLE	TA1	ALL	NULL	NULL	NULL	NULL	2	Using where
 DROP TABLE t1, t2;
 #
@@ -1383,7 +1383,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	jt5	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
 1	SIMPLE	jt2	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
+Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1))) on(1) where 1
 EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
 RIGHT JOIN t1 AS jt2
 RIGHT JOIN t1 AS jt3
@@ -1400,7 +1400,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	jt2	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
 1	SIMPLE	jt1	index	NULL	PRIMARY	4	NULL	2	100.00	Using where; Using index
 Warnings:
-Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
+Note	1003	select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1)) left join `test`.`t1` `jt1` on(1) where 1
 DROP TABLE t1;
 #
 # Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
@@ -1685,7 +1685,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 2	DEPENDENT SUBQUERY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.pk	1	100.00	Using where; Using index
 Warnings:
 Note	1276	Field or reference 'test.t2.pk' of SELECT #2 was resolved in SELECT #1
-Note	1003	select `test`.`t2`.`pk` AS `pk`,<expr_cache><`test`.`t2`.`pk`>((select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1)) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
+Note	1003	select `test`.`t2`.`pk` AS `pk`,(select (`test`.`t3`.`pk` + if(isnull(`test`.`t4`.`pk`),0,`test`.`t4`.`pk`)) from `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`pk` = `test`.`t3`.`pk`)) where (`test`.`t3`.`pk` = (`test`.`t2`.`pk` + 1000)) limit 1) AS `t` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`pk` = (`test`.`t1`.`pk` + 1000)) and (`test`.`t1`.`pk` > 1000)) group by `test`.`t2`.`pk`
 select t2.pk, 
 (select t3.pk+if(isnull(t4.pk),0,t4.pk)
 from t3 left join t4 on t4.pk=t3.pk 
@@ -1696,4 +1696,149 @@ group by t2.pk;
 pk	t
 2001	3001
 drop table t1,t2,t3,t4;
+#
+# Bug#57024: Poor performance when conjunctive condition over the outer 
+#            table is used in the on condition of an outer join
+# 
+create table t1 (a int);
+insert into t1 values (NULL), (NULL), (NULL), (NULL);
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 select * from t1;
+insert into t1 values (4), (2), (1), (3);
+create table t2 like t1;
+insert into t2 select if(t1.a is null, 10, t1.a) from t1;
+create table t3 (a int, b int, index idx(a));
+insert into t3 values (1, 100), (3, 301), (4, 402), (1, 102), (1, 101);
+analyze table t1,t2,t3;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+test.t2	analyze	status	OK
+test.t3	analyze	status	OK
+flush status;
+select sum(t3.b) from t1 left join t3 on t3.a=t1.a and t1.a is not null;
+sum(t3.b)
+1006
+show status like "handler_read%";
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	4
+Handler_read_last	0
+Handler_read_next	5
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	1048581
+flush status;
+select sum(t3.b) from t2 left join t3 on t3.a=t2.a and t2.a <> 10;
+sum(t3.b)
+1006
+show status like "handler_read%";
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	4
+Handler_read_last	0
+Handler_read_next	5
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	1048581
+drop table t1,t2,t3;
+#
+# Bug#57688 Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field
+#
+CREATE TABLE t1 (f1 INT NOT NULL, PRIMARY KEY (f1));
+CREATE TABLE t2 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY (f1, f2));
+INSERT INTO t1 VALUES (4);
+INSERT INTO t2 VALUES (3, 3);
+INSERT INTO t2 VALUES (7, 7);
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
+WHERE t1.f1 = 4
+GROUP BY t2.f1, t2.f2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using temporary; Using filesort
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using index
+SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
+WHERE t1.f1 = 4
+GROUP BY t2.f1, t2.f2;
+f1	f1	f2
+4	NULL	NULL
+EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
+WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
+GROUP BY t2.f1, t2.f2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	system	PRIMARY	NULL	NULL	NULL	1	Using filesort
+1	SIMPLE	t2	ref	PRIMARY	PRIMARY	4	const	1	Using where; Using index
+SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1
+WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL
+GROUP BY t2.f1, t2.f2;
+f1	f1	f2
+DROP TABLE t1,t2;
 End of 5.1 tests
+#
+# LP bug #813447: LEFT JOIN with single-row inner table and
+#                 a subquery in ON expression
+#
+CREATE TABLE t1 (a int);
+INSERT INTO t1 VALUES (0);
+CREATE TABLE t2 (a int);
+INSERT INTO t2 VALUES (0);
+CREATE TABLE t3 (a int);
+INSERT INTO t3 VALUES (0), (0);
+SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3);
+a
+NULL
+EXPLAIN EXTENDED
+SELECT t2.a FROM t1 LEFT JOIN t2 ON (6) IN (SELECT a FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` where 1
+DROP TABLE t1,t2,t3;
+#
+# LP bug #817384 Wrong result with outer join + subquery in ON
+# clause +unique key
+#
+CREATE TABLE t1 ( c int NOT NULL , b char(1) NOT NULL ) ;
+INSERT INTO t1 VALUES (1,'b');
+CREATE TABLE t2 ( a int NOT NULL , b char(1) NOT NULL , PRIMARY KEY (a)) ;
+INSERT INTO t2 VALUES (1,'a');
+create table t3 (c1 char(1), c2 char(2));
+insert into t3 values ('c','d');
+insert into t3 values ('c','d');
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON t1.c = t2.a AND ( t2.b , t1.b ) IN (SELECT * from t3);
+b
+NULL
+EXPLAIN SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	system	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	t2	const	PRIMARY	PRIMARY	4	const	1	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT t2.b
+FROM t1 LEFT JOIN t2 ON (t2.b) IN (SELECT c2 from t3) AND t2.a = 1;
+b
+NULL
+DROP TABLE t1,t2,t3;