Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 1731 insertions, 0 deletions

diff --git a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
new file mode 100644
index 00000000..2ec0868c
--- /dev/null
+++ b/mysql-test/include/mix1.inc
@@ -0,0 +1,1731 @@
+# include/mix1.inc
+#
+# The variables
+#     $engine_type       -- storage engine to be tested
+#     $other_engine_type -- storage engine <> $engine_type
+#                           $other_engine_type must point to an all
+#                           time available storage engine
+#                              2006-08 MySQL 5.1 MyISAM and MEMORY only
+#    $test_foreign_keys -- 0, skip foreign key tests
+#                       -- 1, do not skip foreign key tests
+# have to be set before sourcing this script.
+#
+# Note: The comments/expectations refer to InnoDB.
+#       They might be not valid for other storage engines.
+#
+# Last update:
+# 2006-08-15 ML refactoring of t/innodb_mysql.test
+#               - shift main code of t/innodb_mysql.test to include/mix1.inc
+#               - replace hardcoded assignment of storage engine by
+#                 use of $engine_type and $other_engine_type variables
+#               - remove redundant replay testcase of
+#                 Bug#12882 min/max inconsistent on empty table
+#               - corrected analyze table t1; to analyze table t4;
+#                 Much older versions of this test show that the table
+#                 where just some indexes have been created must be used.
+#
+
+eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
+
+--disable_warnings
+drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
+drop procedure if exists p1;
+--enable_warnings
+
+
+# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
+# (repeatable only w/innodb).
+create table t1 (
+  c_id int(11) not null default '0',
+  org_id int(11) default null,
+  unique key contacts$c_id (c_id),
+  key contacts$org_id (org_id)
+);
+insert into t1 values
+  (2,null),(120,null),(141,null),(218,7), (128,1),
+  (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
+  (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
+
+create table t2 (
+  slai_id int(11) not null default '0',
+  owner_tbl int(11) default null,
+  owner_id int(11) default null,
+  sla_id int(11) default null,
+  inc_web int(11) default null,
+  inc_email int(11) default null,
+  inc_chat int(11) default null,
+  inc_csr int(11) default null,
+  inc_total int(11) default null,
+  time_billed int(11) default null,
+  activedate timestamp null default null,
+  expiredate timestamp null default null,
+  state int(11) default null,
+  sla_set int(11) default null,
+  unique key t2$slai_id (slai_id),
+  key t2$owner_id (owner_id),
+  key t2$sla_id (sla_id)
+);
+insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
+  (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
+  (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
+
+flush tables;
+select si.slai_id
+from t1 c join t2 si on
+  ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+   ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
+  c.c_id = 218 and expiredate is null;
+
+select * from t1 where org_id is null;
+select si.slai_id
+from t1 c join t2 si on
+  ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
+   ( si.owner_tbl = 2 and si.owner_id = c.c_id))
+where
+  c.c_id = 218 and expiredate is null;
+
+drop table t1, t2;
+
+#
+# Bug#17212: results not sorted correctly by ORDER BY when using index
+# (repeatable only w/innodb because of index props)
+#
+CREATE TABLE t1 (a int, b int, KEY b (b));
+CREATE TABLE t2 (a int, b int, PRIMARY KEY  (a,b));
+CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY  (a),
+  UNIQUE KEY b (b,c), KEY a (a,b,c));
+
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
+INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
+
+INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
+INSERT INTO t2 SELECT a + 1, b FROM t2;
+DELETE FROM t2 WHERE a = 1 AND b < 2;
+
+INSERT INTO t3 VALUES (1,1,1),(2,1,2);
+INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
+INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
+
+# demonstrate a problem when a must-use-sort table flag
+# (sort_by_table=1) is being neglected.
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+  t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+  ORDER BY t1.b LIMIT 2;
+
+# demonstrate the problem described in the bug report
+SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
+  t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
+  ORDER BY t1.b LIMIT 5;
+DROP TABLE t1, t2, t3;
+
+
+# BUG#21077 (The testcase is not deterministic so correct execution doesn't
+# prove anything) For proof one should track if sequence of ha_innodb::* func
+# calls is correct.
+CREATE TABLE `t1` (`id1` INT) ;
+INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
+
+CREATE TABLE `t2` (
+  `id1` INT,
+  `id2` INT NOT NULL,
+  `id3` INT,
+  `id4` INT NOT NULL,
+  UNIQUE (`id2`,`id4`),
+  KEY (`id1`)
+);
+
+INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
+(1,1,1,0),
+(1,1,2,1),
+(5,1,2,2),
+(6,1,2,3),
+(1,2,2,2),
+(1,2,1,1);
+
+SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
+DROP TABLE t1, t2;
+
+#
+# Bug #22728 - Handler_rollback value is growing
+#
+
+let $before= `show /*!50002 GLOBAL */ status like 'Handler_rollback'`;
+create table t1 (c1 int) engine=innodb;
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+connection con2;
+handler t1 open;
+handler t1 read first;
+disconnect con2;
+connection con1;
+let $after= `show /*!50002 GLOBAL */ status like 'Handler_rollback'`;
+# Compare the before and after value, it should be equal
+--disable_query_log
+eval select STRCMP("$before", "$after") as "Before and after comparison";
+--enable_query_log
+connection default;
+drop table t1;
+disconnect con1;
+
+#
+# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields
+# used in partial unique indices.
+#
+
+CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
+  ENGINE=INNODB CHARACTER SET UTF8;
+INSERT INTO t1 (c1) VALUES ('1a');
+SELECT * FROM t1;
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+#
+# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
+#
+
+CREATE TABLE t1 (
+  a1 decimal(10,0) DEFAULT NULL,
+  a2 blob,
+  a3 time DEFAULT NULL,
+  a4 blob,
+  a5 char(175) DEFAULT NULL,
+  a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  a7 tinyblob,
+  INDEX idx (a6,a7(239),a5)
+) ENGINE=InnoDB;
+
+EXPLAIN SELECT a4 FROM t1 WHERE
+a6=NULL AND
+a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+
+DROP TABLE t1;
+
+#
+# Bug #12882  	min/max inconsistent on empty table
+#
+
+--disable_warnings
+eval create table t1m (a int) engine = $other_engine_type;
+create table t1i (a int);
+eval create table t2m (a int) engine = $other_engine_type;
+create table t2i (a int);
+--enable_warnings
+insert into t2m values (5);
+insert into t2i values (5);
+
+-- disable_query_log
+-- disable_result_log
+analyze table t1i;
+analyze table t1m;
+analyze table t2i;
+analyze table t2m;
+-- enable_result_log
+-- enable_query_log
+
+# test with $engine_type
+select min(a) from t1i;
+select min(7) from t1i;
+select min(7) from DUAL;
+explain select min(7) from t2i join t1i;
+select min(7) from t2i join t1i;
+
+select max(a) from t1i;
+select max(7) from t1i;
+select max(7) from DUAL;
+explain select max(7) from t2i join t1i;
+select max(7) from t2i join t1i;
+
+select 1, min(a) from t1i where a=99;
+select 1, min(a) from t1i where 1=99;
+select 1, min(1) from t1i where a=99;
+select 1, min(1) from t1i where 1=99;
+
+select 1, max(a) from t1i where a=99;
+select 1, max(a) from t1i where 1=99;
+select 1, max(1) from t1i where a=99;
+select 1, max(1) from t1i where 1=99;
+
+# mixed $engine_type/$other_engine_type test
+explain select count(*), min(7), max(7) from t1m, t1i;
+select count(*), min(7), max(7) from t1m, t1i;
+
+explain select count(*), min(7), max(7) from t1m, t2i;
+select count(*), min(7), max(7) from t1m, t2i;
+
+explain select count(*), min(7), max(7) from t2m, t1i;
+select count(*), min(7), max(7) from t2m, t1i;
+
+drop table t1m, t1i, t2m, t2i;
+
+#
+# Bug #12882: primary key implcitly included in every innodb index
+# (was part of group_min_max.test)
+#
+
+eval create table t1 (
+  a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
+) ENGINE = $other_engine_type;
+
+insert into t1 (a1, a2, b, c, d) values
+('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
+('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
+('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
+('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
+('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
+('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
+('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
+('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
+('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
+('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
+('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
+('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
+('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
+('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
+('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
+('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
+('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
+('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
+('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
+('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
+('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
+('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
+('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
+('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
+('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
+('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
+('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
+('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
+('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
+('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
+('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
+('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
+--disable_warnings
+create table t4 (
+  pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
+);
+--enable_warnings
+insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
+
+create index idx12672_0 on t4 (a1);
+create index idx12672_1 on t4 (a1,a2,b,c);
+create index idx12672_2 on t4 (a1,a2,b);
+analyze table t4;
+
+select distinct a1 from t4 where pk_col not in (1,2,3,4);
+
+drop table t1,t4;
+
+
+#
+# BUG#18819: DELETE IGNORE hangs on foreign key parent delete
+#
+# The bug itself does not relate to InnoDB, but we have to use foreign
+# keys to reproduce it.
+#
+--disable_warnings
+DROP TABLE IF EXISTS t2, t1;
+--enable_warnings
+
+CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
+CREATE TABLE t2 (
+  i INT NOT NULL,
+  FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
+) ENGINE= InnoDB;
+
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (1);
+
+DELETE IGNORE FROM t1 WHERE i = 1;
+
+SELECT * FROM t1, t2;
+
+DROP TABLE t2, t1;
+
+
+--echo End of 4.1 tests.
+
+
+#
+# Bug #6142: a problem with the empty innodb table
+# (was part of group_min_max.test)
+#
+
+--disable_warnings
+create table t1 (
+  a varchar(30), b varchar(30), primary key(a), key(b)
+);
+--enable_warnings
+select distinct a from t1;
+drop table t1;
+
+#
+# Bug #9798: group by with rollup
+# (was part of group_min_max.test)
+#
+
+--disable_warnings
+create table t1(a int, key(a));
+--enable_warnings
+insert into t1 values(1);
+select a, count(a) from t1 group by a with rollup;
+drop table t1;
+
+#
+# Bug #13293 Wrongly used index results in endless loop.
+# (was part of group_min_max.test)
+#
+create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0;
+insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
+alter table t1 drop primary key, add primary key (f2, f1);
+explain select distinct f1 a, f1 b from t1;
+explain select distinct f1, f2 from t1;
+drop table t1;
+
+#
+# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
+#
+
+CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
+                 INDEX (name));
+CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
+# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
+#                  FOREIGN KEY (fkey) REFERENCES t2(id));
+if ($test_foreign_keys)
+{
+   ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
+}
+INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
+INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+ANALYZE TABLE t2;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+  WHERE t1.name LIKE 'A%';
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
+  WHERE t1.name LIKE 'A%' OR FALSE;
+
+DROP TABLE t1,t2;
+
+#
+# Bug#26159: crash for a loose scan of a table that has been emptied 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL,
+  name varchar(20) NOT NULL,
+  dept varchar(20) NOT NULL,
+  age tinyint(3) unsigned NOT NULL,
+  PRIMARY KEY (id),
+  INDEX (name,dept)
+) ENGINE=InnoDB STATS_PERSISTENT=0;
+INSERT INTO t1(id, dept, age, name) VALUES
+  (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
+  (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
+  (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
+  (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
+
+set 
+  @tmp_uss=@@use_stat_tables,
+  @tmp_occ=@@optimizer_use_condition_selectivity;
+set 
+  use_stat_tables='preferably',
+  optimizer_use_condition_selectivity=4;
+
+analyze table t1 persistent for all;
+flush tables;
+
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+DELETE FROM t1;
+--echo # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
+--replace_column 9 #
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+
+DROP TABLE t1;
+set 
+  use_stat_tables=@tmp_uss,
+  optimizer_use_condition_selectivity=@tmp_occ;
+
+--source include/innodb_rollback_on_timeout.inc
+
+#
+# Bug #27210: INNODB ON DUPLICATE KEY UPDATE
+#
+
+set @save_qcache_size=@@global.query_cache_size;
+set @save_qcache_type=@@global.query_cache_type;
+set global query_cache_size=10*1024*1024;
+set global query_cache_type=1;
+connect (con1,localhost,root,,);
+connection con1;
+drop table if exists `test`;
+CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
+  `test2` varchar(4) NOT NULL,PRIMARY KEY  (`test1`))
+  ENGINE=InnoDB DEFAULT CHARSET=latin1;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
+disconnect con1;
+connect (con2,localhost,root,,);
+connection con2;
+select * from test;
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
+  ON DUPLICATE KEY UPDATE `test2` = '1234';
+select * from test;
+flush tables;
+select * from test;
+disconnect con2;
+connection default;
+drop table test;
+set global query_cache_type=@save_qcache_type;
+set global query_cache_size=@save_qcache_size;
+
+--source include/innodb_rollback_on_timeout.inc
+
+#
+# Bug #27650: INSERT fails after multi-row INSERT of the form:
+# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
+#
+
+create table t1(
+id int auto_increment,
+c char(1) not null,
+counter int not null default 1,
+primary key (id),
+unique key (c)
+) engine=innodb;
+
+insert into t1 (id, c) values
+(NULL, 'a'),
+(NULL, 'a')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+
+select * from t1;
+
+truncate table t1;
+
+insert into t1 (id, c) values (NULL, 'a');
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+
+select * from t1;
+
+drop table t1;
+
+#
+# Bug #28189: optimizer erroniously prefers ref access to range access 
+#             for an InnoDB table
+#
+
+CREATE TABLE t1(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=MyISAM;
+
+CREATE TABLE t2(
+  id int AUTO_INCREMENT PRIMARY KEY,
+  stat_id int NOT NULL,
+  acct_id int DEFAULT NULL,
+  INDEX idx1 (stat_id, acct_id),
+  INDEX idx2 (acct_id)
+) ENGINE=InnoDB STATS_PERSISTENT=0;
+
+INSERT INTO t1(stat_id,acct_id) VALUES
+  (1,759), (2,831), (3,785), (4,854), (1,921),
+  (1,553), (2,589), (3,743), (2,827), (2,545),
+  (4,779), (4,783), (1,597), (1,785), (4,832),
+  (1,741), (1,833), (3,788), (2,973), (1,907);
+
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+UPDATE t1 SET acct_id=785 
+  WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
+OPTIMIZE TABLE t1;
+
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 
+
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t2;
+-- enable_result_log
+-- enable_query_log
+
+DROP TABLE t1,t2; 
+
+#
+# Bug #28652: assert when alter innodb table operation
+#
+create table t1(a int) engine=innodb;
+alter table t1 comment '123';
+show create table t1;
+drop table t1;
+
+#
+# Bug #25866: Getting "#HY000 Can't find record in..." on and INSERT
+#
+CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
+INSERT INTO t1 VALUES ('uk'),('bg');
+SELECT * FROM t1 WHERE a = 'uk';
+DELETE FROM t1 WHERE a = 'uk';
+SELECT * FROM t1 WHERE a = 'uk';
+UPDATE t1 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t1 WHERE a = 'uk';
+
+CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
+INSERT INTO t2 VALUES ('uk'),('bg');
+SELECT * FROM t2 WHERE a = 'uk';
+DELETE FROM t2 WHERE a = 'uk';
+SELECT * FROM t2 WHERE a = 'uk';
+INSERT INTO t2 VALUES ('uk');
+UPDATE t2 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t2 WHERE a = 'uk';
+
+CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
+INSERT INTO t3 VALUES ('uk'),('bg');
+SELECT * FROM t3 WHERE a = 'uk';
+DELETE FROM t3 WHERE a = 'uk';
+SELECT * FROM t3 WHERE a = 'uk';
+INSERT INTO t3 VALUES ('uk');
+UPDATE t3 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t3 WHERE a = 'uk';
+
+DROP TABLE t1,t2,t3;
+
+#
+# Test bug when trying to drop data file which no InnoDB directory entry
+#
+
+--disable_query_log
+call mtr.add_suppression("InnoDB: Table .*bug29807.*");
+call mtr.add_suppression("InnoDB: Cannot open table test/bug29807 from");
+--enable_query_log
+
+create table t1 (a int) engine=innodb;
+let $MYSQLD_DATADIR= `select @@datadir`;
+copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/bug29807.frm;
+--error ER_NO_SUCH_TABLE_IN_ENGINE
+select * from bug29807;
+drop table t1;
+drop table bug29807;
+
+
+#
+# Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked
+#
+
+CREATE TABLE t1 (a INT) ENGINE=InnoDB; 
+CREATE TABLE t2 (a INT) ENGINE=InnoDB; 
+
+CONNECT (c1,localhost,root,,);
+CONNECT (c2,localhost,root,,);
+
+CONNECTION c1;
+SET AUTOCOMMIT=0;
+INSERT INTO t2 VALUES (1);
+
+CONNECTION c2;
+SET AUTOCOMMIT=0;
+SET @old_lock_wait_timeout= @@lock_wait_timeout;
+SET lock_wait_timeout= 1;
+--error ER_LOCK_WAIT_TIMEOUT
+LOCK TABLES t1 READ, t2 READ;
+SET  @@lock_wait_timeout= @old_lock_wait_timeout;
+CONNECTION c1;
+COMMIT;
+INSERT INTO t1 VALUES (1);
+
+CONNECTION default;
+SET AUTOCOMMIT=default;
+DISCONNECT c1;
+DISCONNECT c2;
+DROP TABLE t1,t2;
+
+#
+# Bug #25798: a query with forced index merge returns wrong result 
+#
+
+CREATE TABLE t1 (
+  id int NOT NULL auto_increment PRIMARY KEY,
+  b int NOT NULL,
+  c datetime NOT NULL,
+  INDEX idx_b(b),
+  INDEX idx_c(c)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+  b int NOT NULL auto_increment PRIMARY KEY,
+  c datetime NOT NULL
+) ENGINE= MyISAM;
+
+INSERT INTO t2(c) VALUES ('2007-01-01');
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+INSERT INTO t2(c) SELECT c FROM t2;
+
+INSERT INTO t1(b,c) SELECT b,c FROM t2;
+UPDATE t2 SET c='2007-01-02';
+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;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+ANALYZE TABLE t2;
+-- enable_result_log
+-- enable_query_log
+
+set @@sort_buffer_size=8192;
+
+SELECT COUNT(*) FROM t1;
+
+--replace_column 9 #
+EXPLAIN 
+SELECT COUNT(*) FROM t1 
+  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+SELECT COUNT(*) FROM t1 
+  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+
+--replace_column 9 #
+EXPLAIN 
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 
+  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+  WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+
+set @@sort_buffer_size=default;
+
+DROP TABLE t1,t2;
+
+# Test of behaviour with CREATE ... SELECT
+#
+
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+--error ER_DUP_ENTRY
+CREATE TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+--error ER_DUP_ENTRY
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+# This should give warning
+drop table if exists t2;
+CREATE TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t2;
+
+CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
+BEGIN;
+INSERT INTO t2 values(100,100);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+SELECT * from t2;
+ROLLBACK;
+SELECT * from t2;
+TRUNCATE table t2;
+--error ER_DUP_ENTRY
+INSERT INTO t2 select * from t1;
+SELECT * from t2;
+drop table t1,t2;
+
+#
+# Bug#17530: Incorrect key truncation on table creation caused server crash.
+#
+create table t1(f1 varchar(800) binary not null, key(f1))
+  character set utf8 collate utf8_general_ci;
+insert into t1 values('aaa');
+drop table t1;
+
+
+#
+# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
+#
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES (    1 , 1              , 1);
+INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20), 1 FROM t1;
+INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20), 1 FROM t1;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+DROP TABLE t1;
+
+--source include/innodb_rollback_on_timeout.inc
+
+#
+# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
+# (possible deadlock).
+#
+# The bug is applicable only to a transactoinal table.
+# Cover with tests behavior that no longer causes an
+# assertion.
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int) engine=innodb;
+alter table t1 alter a set default 1;
+drop table t1;
+
+--echo
+--echo Bug#24918 drop table and lock / inconsistent between 
+--echo perm and temp tables
+--echo
+--echo Check transactional tables under LOCK TABLES
+--echo
+--disable_warnings
+drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, 
+t24918_access;
+--enable_warnings
+create table t24918_access (id int);
+create table t24918 (id int) engine=myisam;
+create temporary table t24918_tmp (id int) engine=myisam;
+create table t24918_trans (id int) engine=innodb;
+create temporary table t24918_trans_tmp (id int) engine=innodb;
+
+lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
+drop table t24918;
+--error ER_TABLE_NOT_LOCKED
+select * from t24918_access;
+drop table t24918_trans;
+--error ER_TABLE_NOT_LOCKED
+select * from t24918_access;
+drop table t24918_trans_tmp;
+--error ER_TABLE_NOT_LOCKED
+select * from t24918_access;
+drop table t24918_tmp;
+--error ER_TABLE_NOT_LOCKED
+select * from t24918_access;
+unlock tables;
+
+drop table t24918_access;
+#
+# Bug #28591: MySQL need not sort the records in case of ORDER BY
+# primary_key on InnoDB table
+#
+
+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
+INSERT INTO t1 SELECT a + 8, 2 FROM t1;
+INSERT INTO t1 SELECT a + 16, 1 FROM t1;
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+
+CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
+  ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
+INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
+INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t2;
+-- enable_result_log
+-- enable_query_log
+
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+
+DROP TABLE t1,t2;
+
+
+#
+# Bug #29644: alter table hangs if records locked in share mode by long
+# running transaction
+#
+
+CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
+INSERT INTO t1 SELECT a + 8  FROM t1;
+INSERT INTO t1 SELECT a + 16 FROM t1;
+
+DELIMITER |;
+CREATE PROCEDURE p1 ()
+BEGIN
+  DECLARE i INT DEFAULT 50;
+  DECLARE cnt INT;
+  # Continue even in the presence of ER_LOCK_DEADLOCK.
+  DECLARE CONTINUE HANDLER FOR 1213 BEGIN END;
+  START TRANSACTION;
+    ALTER TABLE t1 ENGINE=InnoDB;
+  COMMIT;
+  START TRANSACTION;
+  WHILE (i > 0) DO
+    SET i = i - 1;
+    SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
+  END WHILE;
+  COMMIT;
+END;|
+
+DELIMITER ;|
+
+CONNECT (con1,localhost,root,,);
+CONNECT (con2,localhost,root,,);
+
+CONNECTION con1;
+SEND CALL p1();
+CONNECTION con2;
+SEND CALL p1();
+CONNECTION default;
+CALL p1();
+
+CONNECTION con1;
+REAP;
+CONNECTION con2;
+REAP;
+CONNECTION default;
+DISCONNECT con1;
+DISCONNECT con2;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+#
+# Bug #28125: ERROR 2013 when adding index.
+#
+create table t1(a text) engine=innodb default charset=utf8; 
+insert into t1 values('aaa');
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
+alter table t1 add index(a(1024));
+show create table t1;
+drop table t1;
+
+#
+# Bug #28570: handler::index_read() is called with different find_flag when 
+# ORDER BY is used
+#
+
+CREATE TABLE t1 (
+  a INT,
+  b INT,
+  KEY (b)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
+
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=20 FOR UPDATE;
+
+--connect (conn2, localhost, root,,test)
+
+# This statement gives a "failed: 1205: Lock wait timeout exceeded; try
+# restarting transaction" message when the bug is present.
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
+ROLLBACK;
+
+--disconnect conn2
+--connection default
+
+ROLLBACK;
+DROP TABLE t1;
+
+#
+# Bug#30596: GROUP BY optimization gives wrong result order
+#  
+CREATE TABLE t1(
+  a INT, 
+  b INT NOT NULL, 
+  c INT NOT NULL, 
+  d INT, 
+  UNIQUE KEY (c,b)
+) engine=innodb;
+
+INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
+SELECT c,b,d FROM t1 GROUP BY c,b,d;
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
+SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
+EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
+SELECT c,b,d FROM t1 ORDER BY c,b,d;
+
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
+SELECT c,b,d FROM t1 GROUP BY c,b;
+EXPLAIN SELECT c,b   FROM t1 GROUP BY c,b;
+SELECT c,b   FROM t1 GROUP BY c,b;
+
+DROP TABLE t1;
+
+#
+# Bug #31001: ORDER BY DESC in InnoDB not working
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
+INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+#The two queries below should produce different results, but they don't.
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+
+DROP TABLE t1;
+
+###########################################################################
+
+--echo
+--echo #
+--echo # Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
+--echo #
+
+--echo
+--echo # - prepare;
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+--echo
+
+CREATE TABLE t1(c INT)
+  ENGINE = InnoDB
+  ROW_FORMAT = COMPACT;
+
+--echo
+--echo # - initial check;
+--echo
+
+SELECT table_schema, table_name, row_format
+FROM INFORMATION_SCHEMA.TABLES
+WHERE table_schema = DATABASE() AND table_name = 't1';
+
+--echo
+--echo # - change ROW_FORMAT and check;
+--echo
+
+ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
+
+--echo
+
+SELECT table_schema, table_name, row_format
+FROM INFORMATION_SCHEMA.TABLES
+WHERE table_schema = DATABASE() AND table_name = 't1';
+
+--echo
+--echo # - that's it, cleanup.
+--echo
+
+DROP TABLE t1;
+
+###########################################################################
+
+#
+# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0
+#
+create table t1(a char(10) not null, unique key aa(a(1)),
+                b char(4) not null, unique key bb(b(4))) engine=innodb;
+desc t1;
+show create table t1;
+drop table t1;
+
+#
+# Bug #32815: query with ORDER BY and a possible ref_or_null access
+#
+
+CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
+INSERT INTO t1 VALUES 
+  (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); 
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+
+DROP TABLE t1;
+
+#
+# Bug #34223: Assertion failed: (optp->var_type & 127) == 8,
+#             file .\my_getopt.c, line 830
+#
+
+set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
+set global innodb_autoextend_increment=8;
+set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
+
+#
+# Bug #37830: ORDER BY ASC/DESC - no difference
+#
+
+CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b))
+ ENGINE=InnoDB;
+
+INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1);
+INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+# should be range access
+EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
+
+# should produce '8 7 6 5 4' for a
+SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
+
+DROP TABLE t1;
+
+# 
+# Bug#37284 Crash in Field_string::type() 
+# 
+--disable_warnings 
+DROP TABLE IF EXISTS t1; 
+--enable_warnings 
+CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; 
+CREATE INDEX i1 on t1 (a(3)); 
+SELECT * FROM t1 WHERE a = 'abcde'; 
+DROP TABLE t1;
+
+
+--echo #
+--echo # BUG #26288: savepoint are not deleted on comit, if the transaction 
+--echo # was otherwise empty
+--echo #
+BEGIN;
+SAVEPOINT s1;
+COMMIT;
+--error 1305
+RELEASE SAVEPOINT s1;
+
+BEGIN;
+SAVEPOINT s2;
+COMMIT;
+--error 1305
+ROLLBACK TO SAVEPOINT s2;
+
+BEGIN;
+SAVEPOINT s3;
+ROLLBACK;
+--error 1305
+RELEASE SAVEPOINT s3;
+
+BEGIN;
+SAVEPOINT s4;
+ROLLBACK;
+--error 1305
+ROLLBACK TO SAVEPOINT s4;
+
+#
+# Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value
+#
+
+#This statement should be written on a single line for proper testing
+CREATE TABLE t1 (f1 INTEGER PRIMARY KEY COMMENT 'My ID#', f2 INTEGER DEFAULT NULL, f3 CHAR(10) DEFAULT 'My ID#', CONSTRAINT f2_ref FOREIGN KEY (f2) REFERENCES t1 (f1)) ENGINE=INNODB;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #36995: valgrind error in remove_const during subquery executions
+--echo #
+
+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='in_to_exists=on,materialization=off';
+explain
+select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a;
+set optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1,t2;
+
+--echo End of 5.0 tests
+
+# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
+# UPDATE": if the row is updated, it's like a regular UPDATE:
+# LAST_INSERT_ID() is not affected.
+CREATE TABLE `t2` (
+  `k` int(11) NOT NULL auto_increment,
+  `a` int(11) default NULL,
+  `c` int(11) default NULL,
+  PRIMARY KEY  (`k`),
+  UNIQUE KEY `idx_1` (`a`)
+);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+insert into t2 ( a ) values ( 7 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+# test again when last_insert_id() is 0 initially
+select last_insert_id(0);
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+select * from t2;
+
+# Test of LAST_INSERT_ID() when autogenerated will fail:
+# last_insert_id() should not change
+insert ignore into t2 values (null,6,1),(10,8,1);
+select last_insert_id();
+# First and second autogenerated will fail, last_insert_id() should
+# point to third
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+select last_insert_id();
+select * from t2;
+
+# Test of the workaround which enables people to know the id of the
+# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
+# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
+
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+select * from t2;
+
+drop table t2;
+
+
+#
+# Tests for bug #28415 "Some ALTER TABLE statements no longer work
+# under LOCK TABLES" and some aspects of fast ALTER TABLE behaviour
+# for transactional tables.
+#
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (i int);
+alter table t1 modify i int default 1;
+alter table t1 modify i int default 2, rename t2;
+lock table t2 write;
+alter table t2 modify i int default 3;
+unlock tables;
+lock table t2 write;
+alter table t2 modify i int default 4, rename t1;
+unlock tables;
+drop table t1;
+
+
+# 
+# Some more tests for ALTER TABLE and LOCK TABLES for transactional tables.
+#
+# Table which is altered under LOCK TABLES should stay in list of locked
+# tables and be available after alter takes place unless ALTER contains
+# RENAME clause. We should see the new definition of table, of course.
+# Before 5.1 this behavior was inconsistent across the platforms and
+# different engines. See also tests in alter_table.test
+#
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (i int);
+insert into t1 values ();
+lock table t1 write;
+# Example of so-called 'fast' ALTER TABLE
+alter table t1 modify i int default 1;
+insert into t1 values ();
+select * from t1;
+# And now full-blown ALTER TABLE
+alter table t1 change i c char(10) default "Two";
+insert into t1 values ();
+select * from t1;
+unlock tables;
+select * from t1;
+drop tables t1;
+
+#
+# Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
+#
+create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
+  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
+insert into t1(f1) values(1);
+--replace_column 1 #
+select @a:=f2 from t1;
+--sleep 5
+update t1 set f1=1;
+--replace_column 1 #
+select @b:=f2 from t1;
+select if(@a=@b,"ok","wrong");
+--sleep 5
+insert into t1(f1) values (1) on duplicate key update f1="1";
+--replace_column 1 #
+select @b:=f2 from t1;
+select if(@a=@b,"ok","wrong");
+--sleep 5
+insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
+--replace_column 1 #
+select @b:=f2 from t1;
+select if(@a=@b,"ok","wrong");
+drop table t1;
+
+#
+# Bug #31310: Locked rows silently skipped in read-committed isolation level.
+#
+
+connect (con1,localhost,root,,);
+connect (con2,localhost,root,,);
+SET SESSION AUTOCOMMIT = 0;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+set binlog_format=mixed;
+connection con1;
+
+eval
+CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
+ENGINE = $engine_type;
+INSERT INTO t1 VALUES (1,2);
+
+--echo # 1. test for locking:
+
+BEGIN;
+--enable_info
+UPDATE t1 SET b = 12 WHERE a = 1;
+--disable_info
+SELECT * FROM t1;
+
+connection con2;
+
+--enable_info
+--disable_abort_on_error
+--error ER_LOCK_WAIT_TIMEOUT
+UPDATE t1 SET b = 21 WHERE a = 1;
+--disable_info
+
+connection con1;
+SELECT * FROM t1;
+ROLLBACK;
+
+connection con2;
+ROLLBACK;
+
+connection con1;
+
+--echo # 2. test for serialized update:
+
+CREATE TABLE t2 (a INT);
+
+TRUNCATE t1;
+INSERT INTO t1 VALUES (1,'init');
+
+DELIMITER |;
+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|
+DELIMITER ;|
+
+BEGIN;
+--enable_info
+UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
+--disable_info
+SELECT * FROM t1;
+
+connection con2;
+
+--send CALL p1;
+
+connection con1;
+SELECT * FROM t1;
+COMMIT;
+
+let $bug31310 = 1;
+while ($bug31310)
+{
+  let $bug31310= `SELECT 1 - COUNT(*) FROM t2`;
+}
+
+SELECT * FROM t1;
+
+connection con2;
+--reap
+SELECT * FROM t1;
+COMMIT;
+
+connection con1;
+
+--echo # 3. test for updated key column:
+
+TRUNCATE t1;
+TRUNCATE t2;
+
+INSERT INTO t1 VALUES (1,'init');
+
+BEGIN;
+--enable_info
+UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
+--disable_info
+SELECT * FROM t1;
+
+connection con2;
+
+--send CALL p1;
+
+connection con1;
+SELECT * FROM t1;
+COMMIT;
+
+let $bug31310 = 1;
+while ($bug31310)
+{
+  let $bug31310= `SELECT 1 - COUNT(*) FROM t2`;
+}
+
+SELECT * FROM t1;
+
+connection con2;
+--reap
+SELECT * FROM t1;
+
+--enable_abort_on_error
+connection default;
+disconnect con1;
+disconnect con2;
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+# Bug#30747 Create table with identical constraint names behaves incorrectly
+#
+
+if ($test_foreign_keys)
+{
+  CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+  ALTER TABLE t2 DROP FOREIGN KEY c2;
+  DROP TABLE t2;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+                  FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+                  FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+  DROP TABLE t1;
+}
+
+#
+# Bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB and 
+#             auto_increment keys
+#
+create table t1 (a int auto_increment primary key) engine=innodb;
+set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
+alter table t1 order by a;
+drop table t1;
+
+#
+# Bug #33697: ORDER BY primary key DESC vs. ref access + filesort
+# (reproduced only with InnoDB tables)
+#
+
+CREATE TABLE t1
+  (vid integer NOT NULL,
+   tid integer NOT NULL,
+   idx integer NOT NULL,
+   name varchar(128) NOT NULL,
+   type varchar(128) NULL,
+   PRIMARY KEY(idx, vid, tid),
+   UNIQUE(vid, tid, name)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+  (1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
+  (5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
+  (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
+  (4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
+  (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+
+SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #44290: explain crashes for subquery with distinct in
+--echo #             SQL_SELECT::test_quick_select
+--echo #             (reproduced only with InnoDB tables)
+--echo #
+
+eval
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3))
+  ENGINE=$engine_type;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+EXPLAIN 
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+
+DROP TABLE t1;
+
+eval
+CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
+  ENGINE=$engine_type;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+EXPLAIN 
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+
+DROP TABLE t1;
+
+eval
+CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 
+  KEY (c3), KEY (c2, c3))
+  ENGINE=$engine_type;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+
+-- disable_query_log
+-- disable_result_log
+ANALYZE TABLE t1;
+-- enable_result_log
+-- enable_query_log
+
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+EXPLAIN 
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 
+                 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+
+DROP TABLE t1;
+
+--echo End of 5.1 tests
+
+--echo #
+--echo # Bug#43600: Incorrect type conversion caused wrong result.
+--echo #
+CREATE TABLE t1 (
+  a int NOT NULL
+) engine= innodb;
+
+CREATE TABLE t2 (
+  a int NOT NULL,
+  b int NOT NULL,
+  filler char(100) DEFAULT NULL,
+  KEY a (a,b)
+) engine= innodb;
+
+insert into t1 values (0),(1),(2),(3),(4);
+insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B;
+
+-- disable_query_log
+-- disable_result_log
+analyze table t1;
+analyze table t2;
+-- enable_result_log
+-- enable_query_log
+
+explain select * from t1, t2 where t2.a=t1.a and t2.b + 1;
+select * from t1, t2 where t2.a=t1.a and t2.b + 1;
+
+drop table t1,t2;
+--echo # End of test case for the bug#43600
+
+--echo #
+--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
+--echo #
+--echo # Check that a TRUNCATE TABLE statement, needing an exclusive meta
+--echo # data lock, waits for a shared metadata lock owned by a concurrent
+--echo # transaction.
+--echo #
+
+eval CREATE TABLE t1 (a INT) ENGINE=$engine_type;
+INSERT INTO t1 VALUES (1),(2),(3);
+BEGIN;
+SELECT * FROM t1 ORDER BY a;
+connect (con1, localhost, root,,);
+--send TRUNCATE TABLE t1;
+connection default;
+let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist
+  WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1';
+--source include/wait_condition.inc
+SELECT * FROM t1 ORDER BY a;
+ROLLBACK;
+connection con1;
+--echo # Reaping TRUNCATE TABLE
+--reap
+SELECT * FROM t1;
+disconnect con1;
+connection default;
+DROP TABLE t1;