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, 158 insertions, 13 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index deb84a87e93..8e41a951ab8 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -187,7 +187,7 @@ min(7)
 explain select min(7) from t2i join t1i;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
 select min(7) from t2i join t1i;
 min(7)
 NULL
@@ -203,7 +203,7 @@ max(7)
 explain select max(7) from t2i join t1i;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
-1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
+1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer (flat, BNL join)
 select max(7) from t2i join t1i;
 max(7)
 NULL
@@ -679,8 +679,6 @@ INSERT INTO t1(b,c) SELECT b,c FROM t2;
 UPDATE t2 SET c='2007-01-03';
 INSERT INTO t1(b,c) SELECT b,c FROM t2;
 set @@sort_buffer_size=8192;
-Warnings:
-Warning	1292	Truncated incorrect sort_buffer_size value: '8192'
 SELECT COUNT(*) FROM t1;
 COUNT(*)
 3072
@@ -1605,7 +1603,17 @@ TRUNCATE t1;
 INSERT INTO t1 VALUES (1,'init');
 CREATE PROCEDURE p1()
 BEGIN
+# retry the UPDATE in case it times out the lock before con1 has time
+# to COMMIT.
+DECLARE do_retry INT DEFAULT 0;
+DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
+retry_loop:LOOP
 UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
+IF do_retry = 0 THEN
+LEAVE retry_loop;
+END IF;
+SET do_retry = 0;
+END LOOP;
 INSERT INTO t2 VALUES ();
 END|
 BEGIN;
@@ -1740,8 +1748,8 @@ EXPLAIN
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	5		5	Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ref	c3,c2	c3	5	const	2	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
 ENGINE=InnoDB;
@@ -1754,8 +1762,8 @@ EXPLAIN
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	9		5	Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ref	c3,c2	c3	9	const	2	Using where; Using filesort
 DROP TABLE t1;
 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 
 KEY (c3), KEY (c2, c3))
@@ -1769,8 +1777,8 @@ EXPLAIN
 SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
-2	DERIVED	t1	ALL	c3,c2	c3	7		5	Using filesort
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	t1	ref	c3,c2	c3	7	const	2	Using where; Using filesort
 DROP TABLE t1;
 End of 5.1 tests
 #
@@ -2342,6 +2350,28 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 drop table t1,t2;
 #
 #
+# Bug #39653: find_shortest_key in sql_select.cc does not consider
+#             clustered primary keys
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
+KEY (b,c)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
+(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
+(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
+(11,11,11,11,11,11);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id	1
+select_type	SIMPLE
+table	t1
+type	index
+possible_keys	NULL
+key	PRIMARY
+key_len	4
+ref	NULL
+rows	10
+Extra	Using index
+DROP TABLE t1;
+#
 # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
 #   corrupt definition at engine
 #
@@ -2572,7 +2602,7 @@ INSERT INTO t1 VALUES (0);
 SET SQL_MODE='STRICT_ALL_TABLES';
 CREATE TABLE t2 
 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
-ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 2
+ERROR 22007: Incorrect datetime value: ''
 DROP TABLE t1;
 SET SQL_MODE=DEFAULT;
 #
@@ -2654,6 +2684,13 @@ ref	NULL
 rows	3
 Extra	Using index
 DROP TABLE t1;
+#
+# ALTER TABLE IGNORE didn't ignore duplicates for unique add index
+#
+create table t1 (a int primary key, b int) engine = innodb;
+insert into t1 values (1,1),(2,1);
+alter ignore table t1 add unique `main` (b);
+drop table t1;
 End of 5.1 tests
 #
 #
@@ -2665,7 +2702,7 @@ INSERT INTO t1 VALUES (0);
 SET SQL_MODE='STRICT_ALL_TABLES';
 CREATE TABLE t2 
 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
-ERROR 22007: Incorrect datetime value: '' for column 'NOW()' at row 2
+ERROR 22007: Incorrect datetime value: ''
 DROP TABLE t1;
 SET SQL_MODE=DEFAULT;
 #
@@ -2700,7 +2737,7 @@ SELECT COUNT(*) FROM
 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	1536	
 2	DERIVED	t1	index_merge	PRIMARY,idx	idx,PRIMARY	5,4	NULL	1536	Using sort_union(idx,PRIMARY); Using where
 SELECT COUNT(*) FROM
 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
@@ -2710,6 +2747,83 @@ COUNT(*)
 SET SESSION sort_buffer_size = DEFAULT;
 DROP TABLE t1;
 #
+# Test for bug #39932 "create table fails if column for FK is in different
+#                      case than in corr index".
+#
+drop tables if exists t1, t2;
+create table t1 (pk int primary key) engine=InnoDB;
+# Even although the below statement uses uppercased field names in
+# foreign key definition it still should be able to find explicitly
+# created supporting index. So it should succeed and should not
+# create any additional supporting indexes.
+create table t2 (fk int, key x (fk),
+constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `fk` int(11) DEFAULT NULL,
+  KEY `x` (`fk`),
+  CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+drop table t2, t1;
+#
+# Bug #663818: wrong result when BNLH is used 
+#
+CREATE TABLE t1(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+(1), (2), (11), (12), (13), (14),
+(15), (16), (17), (18), (19);
+CREATE TABLE t2(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t2 VALUES 
+(1), (10), (11), (12), (13), (14),
+(15), (16), (17), (18), (19), (20), (21);
+SET SESSION join_buffer_size=10000;
+Warnings:
+Warning	1292	Truncated incorrect join_buffer_size value: '10000'
+SET SESSION join_cache_level=3;
+EXPLAIN
+SELECT t1.pk FROM t1,t2 
+WHERE t1.pk = t2.pk AND t2.pk <> 8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	11	Using where; Using index
+1	SIMPLE	t2	hash_index	PRIMARY	#hash#PRIMARY:PRIMARY	4:4	test.t1.pk	13	Using join buffer (flat, BNLH join)
+SELECT t1.pk FROM t1,t2 
+WHERE t1.pk = t2.pk AND t2.pk <> 8;
+pk
+1
+11
+12
+13
+14
+15
+16
+17
+18
+19
+SET SESSION join_cache_level=1;
+EXPLAIN
+SELECT t1.pk FROM t1,t2 
+WHERE t1.pk = t2.pk AND t2.pk <> 8;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	11	Using where; Using index
+1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.pk	1	Using index
+SELECT t1.pk FROM t1,t2 
+WHERE t1.pk = t2.pk AND t2.pk <> 8;
+pk
+1
+11
+12
+13
+14
+15
+16
+17
+18
+19
+DROP TABLE t1,t2;
+SET SESSION join_cache_level=DEFAULT;
+SET SESSION join_buffer_size=DEFAULT;
+#
 # Bug#668644: HAVING + ORDER BY
 #
 CREATE TABLE t1 (
@@ -2745,6 +2859,36 @@ f
 1541734400
 1541734400
 DROP TABLE t1, t2;
+#
+# Test for bug #56619 - Assertion failed during
+# ALTER TABLE RENAME, DISABLE KEYS
+#
+DROP TABLE IF EXISTS t1, t2;
+CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb;
+ALTER TABLE t1 RENAME TO t2, DISABLE KEYS;
+DROP TABLE IF EXISTS t1, t2;
+#
+# Bug#702322: HAVING with two ANDed predicates + ORDER BY
+#
+CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY (a)) ENGINE=InnoDB;
+CREATE TABLE t2 (a int, KEY (a)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(18,0),(9,10),(8,11),(2,15),(7,19),(1,20);
+SET SESSION join_cache_level = 0;
+EXPLAIN 
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a 
+WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11
+ORDER BY t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	4	Using where; Using filesort
+1	SIMPLE	t2	ref	a	a	5	test.t1.pk	1	Using index
+SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a 
+WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11
+ORDER BY t1.a;
+a
+10
+19
+DROP TABLE t1,t2;
 End of 5.3 tests
 #
 # Test for bug #39932 "create table fails if column for FK is in different
@@ -2818,3 +2962,4 @@ PACK_KEYS=0;
 CREATE INDEX a ON t1 (a);
 CREATE INDEX c on t1 (c);
 DROP TABLE t1;
+End of 5.1 tests