merge.

checkpoint.
does not compile.

22 files changed, 195 insertions, 73 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-semi-consistent.result b/mysql-test/suite/innodb/r/innodb-semi-consistent.result
index 989cb818cec..78ef4a23149 100644
--- a/mysql-test/suite/innodb/r/innodb-semi-consistent.result
+++ b/mysql-test/suite/innodb/r/innodb-semi-consistent.result
@@ -1,4 +1,4 @@
-drop table if exists t1;
+drop table if exists t1,t2;
 set binlog_format=mixed;
 set session transaction isolation level repeatable read;
 create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result
index 2e5585ee7af..a721b42f2b7 100644
--- a/mysql-test/suite/innodb/r/innodb.result
+++ b/mysql-test/suite/innodb/r/innodb.result
@@ -1,3 +1,5 @@
+set optimizer_switch='index_condition_pushdown=off';
+set @@optimizer_use_mrr=disable;
 drop table if exists t1,t2,t3,t4;
 drop database if exists mysqltest;
 create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb;
@@ -1362,7 +1364,7 @@ insert t2 select * from t1;
 insert t3 select * from t1;
 checksum table t1, t2, t3, t4 quick;
 Table	Checksum
-test.t1	2948697075
+test.t1	3442722830
 test.t2	NULL
 test.t3	NULL
 test.t4	NULL
@@ -1370,17 +1372,17 @@ Warnings:
 Error	1146	Table 'test.t4' doesn't exist
 checksum table t1, t2, t3, t4;
 Table	Checksum
-test.t1	2948697075
-test.t2	2948697075
-test.t3	2948697075
+test.t1	3442722830
+test.t2	3442722830
+test.t3	3442722830
 test.t4	NULL
 Warnings:
 Error	1146	Table 'test.t4' doesn't exist
 checksum table t1, t2, t3, t4 extended;
 Table	Checksum
-test.t1	2948697075
-test.t2	2948697075
-test.t3	2948697075
+test.t1	3442722830
+test.t2	3442722830
+test.t3	3442722830
 test.t4	NULL
 Warnings:
 Error	1146	Table 'test.t4' doesn't exist
@@ -1572,6 +1574,12 @@ ERROR 42S21: Duplicate column name 'c1'
 alter table t1 add key (c1,c1,c2);
 ERROR 42S21: Duplicate column name 'c1'
 drop table t1;
+create table t1(a int(1) , b int(1)) engine=innodb;
+insert into t1 values ('1111', '3333');
+select distinct concat(a, b) from t1;
+concat(a, b)
+11113333
+drop table t1;
 CREATE TABLE t1 ( a char(10) ) ENGINE=InnoDB;
 SELECT a FROM t1 WHERE MATCH (a) AGAINST ('test' IN BOOLEAN MODE);
 ERROR HY000: The used table type doesn't support FULLTEXT indexes
@@ -1706,6 +1714,7 @@ show variables like "innodb_sync_spin_loops";
 Variable_name	Value
 innodb_sync_spin_loops	20
 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
+SET @old_innodb_thread_concurrency= @@global.innodb_thread_concurrency;
 show variables like "innodb_thread_concurrency";
 Variable_name	Value
 innodb_thread_concurrency	0
@@ -1723,6 +1732,7 @@ set global innodb_thread_concurrency=16;
 show variables like "innodb_thread_concurrency";
 Variable_name	Value
 innodb_thread_concurrency	16
+SET @@global.innodb_thread_concurrency= @old_innodb_thread_concurrency;
 show variables like "innodb_concurrency_tickets";
 Variable_name	Value
 innodb_concurrency_tickets	500
@@ -3175,3 +3185,5 @@ Variable_name	Value
 Handler_update	1
 Variable_name	Value
 Handler_delete	1
+set optimizer_switch='index_condition_pushdown=default';
+set @@optimizer_use_mrr=default;
diff --git a/mysql-test/suite/innodb/r/innodb_bug53591.result b/mysql-test/suite/innodb/r/innodb_bug53591.result
index 8573fb60718..89aeb84a880 100644
--- a/mysql-test/suite/innodb/r/innodb_bug53591.result
+++ b/mysql-test/suite/innodb/r/innodb_bug53591.result
@@ -9,7 +9,6 @@ SHOW WARNINGS;
 Level	Code	Message
 Error	139	Too big row
 Error	1118	Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. You have to change some columns to TEXT or BLOBs
-Error	1030	Got error 139 from storage engine
 DROP TABLE bug53591;
 SET GLOBAL innodb_file_format=Barracuda;
 SET GLOBAL innodb_file_format_max=Antelope;
diff --git a/mysql-test/suite/innodb/r/innodb_bug54679.result b/mysql-test/suite/innodb/r/innodb_bug54679.result
index 948696fb31d..9763c5c0dd3 100644
--- a/mysql-test/suite/innodb/r/innodb_bug54679.result
+++ b/mysql-test/suite/innodb/r/innodb_bug54679.result
@@ -21,7 +21,7 @@ ALTER TABLE bug54679 KEY_BLOCK_SIZE=1;
 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables
 WHERE TABLE_NAME='bug54679';
 TABLE_NAME	ROW_FORMAT	CREATE_OPTIONS
-bug54679	Compressed	KEY_BLOCK_SIZE=1
+bug54679	Compressed	key_block_size=1
 ALTER TABLE bug54679 ROW_FORMAT=REDUNDANT;
 ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
 SHOW WARNINGS;
@@ -38,7 +38,7 @@ ALTER TABLE bug54679 KEY_BLOCK_SIZE=2;
 SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables
 WHERE TABLE_NAME='bug54679';
 TABLE_NAME	ROW_FORMAT	CREATE_OPTIONS
-bug54679	Compressed	row_format=REDUNDANT KEY_BLOCK_SIZE=2
+bug54679	Compressed	row_format=REDUNDANT key_block_size=2
 SET GLOBAL innodb_file_format=Antelope;
 ALTER TABLE bug54679 KEY_BLOCK_SIZE=4;
 ERROR HY000: Can't create table '#sql-temporary' (errno: 1478)
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 6224d46180c..f290410a488 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -356,7 +356,7 @@ SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
-1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using index
 EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%' OR FALSE;
@@ -1431,12 +1431,15 @@ DROP TABLE t1;
 #
 create table t1 (a bit(1) not null,b int) engine=myisam;
 create table t2 (c int) engine=innodb;
+set @save_optimizer_switch=@@optimizer_switch;
+set @@optimizer_switch='partial_match_rowid_merge=off,partial_match_table_scan=off';
 explain
 select b from t1 where a not in (select b from t1,t2 group by a) group by a;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
 2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
+set optimizer_switch=@save_optimizer_switch;
 DROP TABLE t1,t2;
 End of 5.0 tests
 CREATE TABLE `t2` (
@@ -2336,28 +2339,6 @@ 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	b
-key_len	10
-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
 #
@@ -2393,6 +2374,36 @@ Comment
 Index_comment	
 DROP TABLE t1;
 #
+# Bug #53334: wrong result for outer join with impossible ON condition
+# (see the same test case for MyISAM in join.test)
+#
+create table t1 (id int primary key);
+create table t2 (id int);
+insert into t1 values (75);
+insert into t1 values (79);
+insert into t1 values (78);
+insert into t1 values (77);
+replace into t1 values (76);
+replace into t1 values (76);
+insert into t1 values (104);
+insert into t1 values (103);
+insert into t1 values (102);
+insert into t1 values (101);
+insert into t1 values (105);
+insert into t1 values (106);
+insert into t1 values (107);
+insert into t2 values (107),(75),(1000);
+select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+where t2.id=75 and t1.id is null;
+id	id
+NULL	75
+explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+where t2.id=75 and t1.id is null;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	const	PRIMARY	NULL	NULL	NULL	1	Impossible ON condition
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
+drop table t1,t2;
+#
 # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when 
 #  JOINed during an UPDATE
 #
@@ -2608,7 +2619,7 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	b
+key	PRIMARY
 key_len	8
 ref	NULL
 rows	3
@@ -2621,8 +2632,8 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	b
-key_len	13
+key	PRIMARY
+key_len	8
 ref	NULL
 rows	3
 Extra	Using index
@@ -2640,9 +2651,45 @@ ref	NULL
 rows	3
 Extra	Using index
 DROP TABLE t1;
-#
 End of 5.1 tests
 #
+# Bug#668644: HAVING + ORDER BY
+#
+CREATE TABLE t1 (
+pk int  NOT NULL PRIMARY KEY, i int DEFAULT NULL,
+INDEX idx (i)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES
+(6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
+CREATE TABLE t2 (
+i int DEFAULT NULL,
+pk int NOT NULL PRIMARY KEY,
+INDEX idx (i)
+) ENGINE= INNODB;
+INSERT INTO t2 VALUES
+(-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
+(-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
+(5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
+(576061440,3);
+EXPLAIN
+SELECT t1 .i AS f FROM t1, t2
+WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
+HAVING f > 7
+ORDER BY f;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	PRIMARY	PRIMARY	4	NULL	3	Using where; Using filesort
+1	SIMPLE	t2	ref	idx	idx	5	test.t1.pk	1	Using index
+SELECT t1 .i AS f FROM t1, t2
+WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
+HAVING f > 7
+ORDER BY f;
+f
+1148715008
+1541734400
+1541734400
+DROP TABLE t1, t2;
+End of 5.3 tests
+#
 # Test for bug #39932 "create table fails if column for FK is in different
 #                      case than in corr index".
 #
diff --git a/mysql-test/suite/innodb/t/innodb-consistent-master.opt b/mysql-test/suite/innodb/t/innodb-consistent-master.opt
index e76299453d3..cb48f1aaf60 100644
--- a/mysql-test/suite/innodb/t/innodb-consistent-master.opt
+++ b/mysql-test/suite/innodb/t/innodb-consistent-master.opt
@@ -1 +1 @@
---innodb_lock_wait_timeout=2
+--loose-innodb_lock_wait_timeout=2
diff --git a/mysql-test/suite/innodb/t/innodb-master.opt b/mysql-test/suite/innodb/t/innodb-master.opt
index 8b247193a4e..5266978e4f0 100644
--- a/mysql-test/suite/innodb/t/innodb-master.opt
+++ b/mysql-test/suite/innodb/t/innodb-master.opt
@@ -1,4 +1,4 @@
---binlog_cache_size=32768 --innodb_lock_wait_timeout=1
+--binlog-cache-size=32768 --loose-innodb-lock-wait-timeout=1
 --default-storage-engine=MyISAM
 --innodb-strict-mode=0
 --innodb-file-per-table=0
diff --git a/mysql-test/suite/innodb/t/innodb-semi-consistent-master.opt b/mysql-test/suite/innodb/t/innodb-semi-consistent-master.opt
index e76299453d3..cb48f1aaf60 100644
--- a/mysql-test/suite/innodb/t/innodb-semi-consistent-master.opt
+++ b/mysql-test/suite/innodb/t/innodb-semi-consistent-master.opt
@@ -1 +1 @@
---innodb_lock_wait_timeout=2
+--loose-innodb_lock_wait_timeout=2
diff --git a/mysql-test/suite/innodb/t/innodb-semi-consistent.test b/mysql-test/suite/innodb/t/innodb-semi-consistent.test
index 28bf532ff1f..2551db99cf3 100644
--- a/mysql-test/suite/innodb/t/innodb-semi-consistent.test
+++ b/mysql-test/suite/innodb/t/innodb-semi-consistent.test
@@ -2,7 +2,7 @@
 -- source include/have_innodb.inc
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
 --enable_warnings
 
 # basic tests of semi-consistent reads
diff --git a/mysql-test/suite/innodb/t/innodb-use-sys-malloc.test b/mysql-test/suite/innodb/t/innodb-use-sys-malloc.test
index 325dd19d086..07ccf261713 100644
--- a/mysql-test/suite/innodb/t/innodb-use-sys-malloc.test
+++ b/mysql-test/suite/innodb/t/innodb-use-sys-malloc.test
@@ -1,4 +1,7 @@
 --source include/have_innodb.inc
+# XtraDB has lots of "still reachable" memory leak warnings at shutdown when
+# --innodb-use-sys-malloc
+--source include/not_valgrind.inc
 
 #display current value of innodb_use_sys_malloc
 SELECT @@GLOBAL.innodb_use_sys_malloc;
diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test
index a283cd26ccb..b839fb9b59f 100644
--- a/mysql-test/suite/innodb/t/innodb.test
+++ b/mysql-test/suite/innodb/t/innodb.test
@@ -22,6 +22,11 @@
 -- source include/have_innodb.inc
 
 let $MYSQLD_DATADIR= `select @@datadir`;
+let collation=utf8_unicode_ci;
+--source include/have_collation.inc
+
+set optimizer_switch='index_condition_pushdown=off';
+set @@optimizer_use_mrr=disable;
 
 # Save the original values of some variables in order to be able to
 # estimate how much they have changed during the tests. Previously this
@@ -1216,11 +1221,11 @@ drop table t1;
 #
 # Bug #4082: integer truncation
 #
-# disable because the bug has resurfaced
-#create table t1(a int(1) , b int(1)) engine=innodb;
-#insert into t1 values ('1111', '3333');
-#select distinct concat(a, b) from t1;
-#drop table t1;
+
+create table t1(a int(1) , b int(1)) engine=innodb;
+insert into t1 values ('1111', '3333');
+select distinct concat(a, b) from t1;
+drop table t1;
 
 #
 # BUG#7709 test case - Boolean fulltext query against unsupported 
@@ -1334,6 +1339,7 @@ show variables like "innodb_sync_spin_loops";
 set global innodb_sync_spin_loops=@innodb_sync_spin_loops_orig;
 
 # Test for innodb_thread_concurrency variable
+SET @old_innodb_thread_concurrency= @@global.innodb_thread_concurrency;
 show variables like "innodb_thread_concurrency";
 set global innodb_thread_concurrency=1001;
 show variables like "innodb_thread_concurrency";
@@ -1341,6 +1347,7 @@ set global innodb_thread_concurrency=0;
 show variables like "innodb_thread_concurrency";
 set global innodb_thread_concurrency=16;
 show variables like "innodb_thread_concurrency";
+SET @@global.innodb_thread_concurrency= @old_innodb_thread_concurrency;
 
 # Test for innodb_concurrency_tickets variable
 show variables like "innodb_concurrency_tickets";
@@ -2551,6 +2558,9 @@ SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig;
 
 -- enable_query_log
 
+set optimizer_switch='index_condition_pushdown=default';
+set @@optimizer_use_mrr=default;
+
 #######################################################################
 #                                                                     #
 # Please, DO NOT TOUCH this file as well as the innodb.result file.   #
diff --git a/mysql-test/suite/innodb/t/innodb_autoinc_lock_mode_zero-master.opt b/mysql-test/suite/innodb/t/innodb_autoinc_lock_mode_zero-master.opt
index fad0da2ac2e..e27ee9b2c78 100644
--- a/mysql-test/suite/innodb/t/innodb_autoinc_lock_mode_zero-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_autoinc_lock_mode_zero-master.opt
@@ -1 +1 @@
---innodb-autoinc-lock-mode=0
+--loose-innodb-autoinc-lock-mode=0
diff --git a/mysql-test/suite/innodb/t/innodb_bug30919-master.opt b/mysql-test/suite/innodb/t/innodb_bug30919-master.opt
index 8636d2d8734..58b254fe596 100644
--- a/mysql-test/suite/innodb/t/innodb_bug30919-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_bug30919-master.opt
@@ -1 +1 @@
---innodb --innodb_autoinc_lock_mode=0
+--loose-innodb --loose-innodb_autoinc_lock_mode=0
diff --git a/mysql-test/suite/innodb/t/innodb_bug39438-master.opt b/mysql-test/suite/innodb/t/innodb_bug39438-master.opt
index 43fac202fd4..0746d13d1c0 100644
--- a/mysql-test/suite/innodb/t/innodb_bug39438-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_bug39438-master.opt
@@ -1 +1 @@
---innodb-file-per-table=1
+--loose-innodb-file-per-table=1
diff --git a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt b/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt
index d71dbe17d5b..e82f614c897 100644
--- a/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_bug42101-nonzero-master.opt
@@ -1 +1 @@
---innodb_commit_concurrency=1
+--loose-innodb-commit-concurrency=1
diff --git a/mysql-test/suite/innodb/t/innodb_bug53674-master.opt b/mysql-test/suite/innodb/t/innodb_bug53674-master.opt
index f1cfd7ab6c7..1fe48c3a33a 100644
--- a/mysql-test/suite/innodb/t/innodb_bug53674-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_bug53674-master.opt
@@ -1 +1 @@
---log-bin --innodb-locks-unsafe-for-binlog --binlog-format=mixed
+--loose-innodb-locks-unsafe-for-binlog --binlog-format=mixed
diff --git a/mysql-test/suite/innodb/t/innodb_bug53674.test b/mysql-test/suite/innodb/t/innodb_bug53674.test
index 47f67f109c3..c96c3403b36 100644
--- a/mysql-test/suite/innodb/t/innodb_bug53674.test
+++ b/mysql-test/suite/innodb/t/innodb_bug53674.test
@@ -1,4 +1,5 @@
 -- source include/have_innodb.inc
+-- source include/have_log_bin.inc
 
 create table bug53674(a int)engine=innodb;
 insert into bug53674 values (1),(2);
diff --git a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1-master.opt b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1-master.opt
index 462f8fbe828..82dec8b25fd 100644
--- a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1-master.opt
@@ -1 +1 @@
---innodb_lock_wait_timeout=1
+--loose-innodb_lock_wait_timeout=1
diff --git a/mysql-test/suite/innodb/t/innodb_mysql-master.opt b/mysql-test/suite/innodb/t/innodb_mysql-master.opt
index c46f5079568..a177f285d66 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_mysql-master.opt
@@ -1 +1 @@
---innodb-lock-wait-timeout=2 --default-storage-engine=MyISAM
+--loose-innodb-lock-wait-timeout=2 --default-storage-engine=MyISAM
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
index d95992898a1..522e32a53ca 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -5,7 +5,11 @@
 #               main testing code t/innodb_mysql.test -> include/mix1.inc              
 #
 
+# Slow test, don't run during staging part
+-- source include/not_staging.inc
 -- source include/have_innodb.inc
+-- source include/have_query_cache.inc
+
 let $engine_type= InnoDB;
 let $other_engine_type= MEMORY;
 # InnoDB does support FOREIGN KEYFOREIGN KEYs
@@ -555,36 +559,49 @@ drop table t1,t2;
 
 
 --echo #
---echo # Bug #39653: find_shortest_key in sql_select.cc does not consider
---echo #             clustered primary keys
+--echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
+--echo #   corrupt definition at engine
 --echo #
 
-CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
-                 KEY (b,c)) ENGINE=INNODB;
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) 
+  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);
+ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
 
---query_vertical EXPLAIN SELECT COUNT(*) FROM t1
+--query_vertical SHOW INDEXES FROM t1;
 
 DROP TABLE t1;
 
 --echo #
---echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
---echo #   corrupt definition at engine
+--echo # Bug #53334: wrong result for outer join with impossible ON condition
+--echo # (see the same test case for MyISAM in join.test)
 --echo #
 
-CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b)) 
-  ENGINE=InnoDB;
+create table t1 (id int primary key);
+create table t2 (id int);
 
-ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
+insert into t1 values (75);
+insert into t1 values (79);
+insert into t1 values (78);
+insert into t1 values (77);
+replace into t1 values (76);
+replace into t1 values (76);
+insert into t1 values (104);
+insert into t1 values (103);
+insert into t1 values (102);
+insert into t1 values (101);
+insert into t1 values (105);
+insert into t1 values (106);
+insert into t1 values (107);
 
---query_vertical SHOW INDEXES FROM t1;
+insert into t2 values (107),(75),(1000);
 
-DROP TABLE t1;
+select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+  where t2.id=75 and t1.id is null;
+explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+  where t2.id=75 and t1.id is null;
 
+drop table t1,t2;
 
 --echo #
 --echo # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when 
@@ -822,11 +839,44 @@ CREATE INDEX b ON t1(a,b,c,d);
 
 DROP TABLE t1;
 
+--echo End of 5.1 tests
+
+--echo #
+--echo # Bug#668644: HAVING + ORDER BY
 --echo #
 
+CREATE TABLE t1 (
+  pk int  NOT NULL PRIMARY KEY, i int DEFAULT NULL,
+  INDEX idx (i)
+) ENGINE=INNODB;
+INSERT INTO t1 VALUES
+  (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);
+
+CREATE TABLE t2 (
+  i int DEFAULT NULL,
+  pk int NOT NULL PRIMARY KEY,
+  INDEX idx (i)
+) ENGINE= INNODB;
+INSERT INTO t2 VALUES
+  (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
+  (-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
+  (5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
+  (576061440,3);
+
+EXPLAIN
+SELECT t1 .i AS f FROM t1, t2
+  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
+  HAVING f > 7
+  ORDER BY f;
+SELECT t1 .i AS f FROM t1, t2
+  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
+  HAVING f > 7
+  ORDER BY f;
+
+DROP TABLE t1, t2;
 
---echo End of 5.1 tests
 
+--echo End of 5.3 tests
 
 --echo #
 --echo # Test for bug #39932 "create table fails if column for FK is in different
diff --git a/mysql-test/suite/innodb/t/innodb_mysql_rbk-master.opt b/mysql-test/suite/innodb/t/innodb_mysql_rbk-master.opt
index 0e400f9c36b..0de618855c3 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql_rbk-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_mysql_rbk-master.opt
@@ -1 +1 @@
---innodb_lock_wait_timeout=1 --innodb_rollback_on_timeout=1
+--loose-innodb_lock_wait_timeout=1 --loose-innodb_rollback_on_timeout=1
diff --git a/mysql-test/suite/innodb/t/innodb_timeout_rollback-master.opt b/mysql-test/suite/innodb/t/innodb_timeout_rollback-master.opt
index 50921bb4df0..a451be2eef0 100644
--- a/mysql-test/suite/innodb/t/innodb_timeout_rollback-master.opt
+++ b/mysql-test/suite/innodb/t/innodb_timeout_rollback-master.opt
@@ -1 +1 @@
---innodb_lock_wait_timeout=2 --innodb_rollback_on_timeout
+--loose-innodb_lock_wait_timeout=2 --loose-innodb_rollback_on_timeout