summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/innodb/r/innodb_mysql.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb_mysql.result')
-rw-r--r--mysql-test/suite/innodb/r/innodb_mysql.result3364
1 files changed, 3364 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
new file mode 100644
index 00000000..aa8cc118
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -0,0 +1,3364 @@
+SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
+drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
+drop procedure if exists p1;
+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;
+slai_id
+12
+select * from t1 where org_id is null;
+c_id org_id
+2 NULL
+120 NULL
+141 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;
+slai_id
+12
+drop table t1, t2;
+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;
+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;
+b a
+1 1
+2 2
+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;
+b a
+1 1
+2 2
+2 2
+3 3
+3 3
+DROP TABLE t1, t2, t3;
+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);
+id1
+2
+DROP TABLE t1, t2;
+create table t1 (c1 int) engine=innodb;
+connect con1,localhost,root,,;
+connect con2,localhost,root,,;
+connection con2;
+handler t1 open;
+handler t1 read first;
+c1
+disconnect con2;
+connection con1;
+Before and after comparison
+0
+connection default;
+drop table t1;
+disconnect con1;
+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;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+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;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+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;
+c1 cnt
+1a 1
+INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
+SELECT * FROM t1;
+c1 cnt
+1a 2
+DROP TABLE t1;
+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';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
+t.a6=t.a6 AND t1.a6=NULL AND
+t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
+create table t1m (a int) engine = MEMORY;
+create table t1i (a int);
+create table t2m (a int) engine = MEMORY;
+create table t2i (a int);
+insert into t2m values (5);
+insert into t2i values (5);
+select min(a) from t1i;
+min(a)
+NULL
+select min(7) from t1i;
+min(7)
+NULL
+select min(7) from DUAL;
+min(7)
+7
+explain select min(7) from t2i join t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1i ALL NULL NULL NULL NULL 0
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
+select min(7) from t2i join t1i;
+min(7)
+NULL
+select max(a) from t1i;
+max(a)
+NULL
+select max(7) from t1i;
+max(7)
+NULL
+select max(7) from DUAL;
+max(7)
+7
+explain select max(7) from t2i join t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1i ALL NULL NULL NULL NULL 0
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
+select max(7) from t2i join t1i;
+max(7)
+NULL
+select 1, min(a) from t1i where a=99;
+1 min(a)
+1 NULL
+select 1, min(a) from t1i where 1=99;
+1 min(a)
+1 NULL
+select 1, min(1) from t1i where a=99;
+1 min(1)
+1 NULL
+select 1, min(1) from t1i where 1=99;
+1 min(1)
+1 NULL
+select 1, max(a) from t1i where a=99;
+1 max(a)
+1 NULL
+select 1, max(a) from t1i where 1=99;
+1 max(a)
+1 NULL
+select 1, max(1) from t1i where a=99;
+1 max(1)
+1 NULL
+select 1, max(1) from t1i where 1=99;
+1 max(1)
+1 NULL
+explain select count(*), min(7), max(7) from t1m, t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found
+1 SIMPLE t1i ALL NULL NULL NULL NULL 0
+select count(*), min(7), max(7) from t1m, t1i;
+count(*) min(7) max(7)
+0 NULL NULL
+explain select count(*), min(7), max(7) from t1m, t2i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found
+1 SIMPLE t2i ALL NULL NULL NULL NULL 1
+select count(*), min(7), max(7) from t1m, t2i;
+count(*) min(7) max(7)
+0 NULL NULL
+explain select count(*), min(7), max(7) from t2m, t1i;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2m system NULL NULL NULL NULL 1
+1 SIMPLE t1i ALL NULL NULL NULL NULL 0
+select count(*), min(7), max(7) from t2m, t1i;
+count(*) min(7) max(7)
+0 NULL NULL
+drop table t1m, t1i, t2m, t2i;
+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 = MEMORY;
+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');
+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 ' '
+);
+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;
+Table Op Msg_type Msg_text
+test.t4 analyze status Engine-independent statistics collected
+test.t4 analyze status OK
+select distinct a1 from t4 where pk_col not in (1,2,3,4);
+a1
+a
+b
+c
+d
+drop table t1,t4;
+DROP TABLE IF EXISTS t2, t1;
+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;
+Warnings:
+Warning 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)
+SELECT * FROM t1, t2;
+i i
+1 1
+DROP TABLE t2, t1;
+End of 4.1 tests.
+create table t1 (
+a varchar(30), b varchar(30), primary key(a), key(b)
+);
+select distinct a from t1;
+a
+drop table t1;
+create table t1(a int, key(a));
+insert into t1 values(1);
+select a, count(a) from t1 group by a with rollup;
+a count(a)
+1 1
+NULL 1
+drop table t1;
+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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary
+explain select distinct f1, f2 from t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index
+drop table t1;
+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));
+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);
+EXPLAIN
+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 range PRIMARY,name name 23 NULL 2 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;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range PRIMARY,name name 23 NULL 2 Using where; Using index
+1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
+DROP TABLE t1,t2;
+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;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+flush tables;
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref name name 22 const 2 Using where; Using index
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name dept
+rs5 cs10
+rs5 cs9
+DELETE FROM t1;
+# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
+EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref name name 22 const # Using where; Using index
+SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
+name dept
+DROP TABLE t1;
+set
+use_stat_tables=@tmp_uss,
+optimizer_use_condition_selectivity=@tmp_occ;
+drop table if exists t1;
+show variables like 'innodb_rollback_on_timeout';
+Variable_name Value
+innodb_rollback_on_timeout OFF
+create table t1 (a int unsigned not null primary key) engine = innodb;
+insert into t1 values (1);
+commit;
+connect con1,localhost,root,,;
+connect con2,localhost,root,,;
+connection con2;
+begin work;
+insert into t1 values (2);
+select * from t1;
+a
+1
+2
+connection con1;
+begin work;
+insert into t1 values (5);
+select * from t1;
+a
+1
+5
+insert into t1 values (2);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+select * from t1;
+a
+1
+5
+commit;
+connection con2;
+select * from t1;
+a
+1
+2
+commit;
+connection default;
+select * from t1;
+a
+1
+2
+5
+drop table t1;
+disconnect con1;
+disconnect con2;
+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`;
+Warnings:
+Note 1051 Unknown table 'test.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;
+test1 test2
+tes 5678
+INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
+ON DUPLICATE KEY UPDATE `test2` = '1234';
+select * from test;
+test1 test2
+tes 1234
+flush tables;
+select * from test;
+test1 test2
+tes 1234
+disconnect con2;
+connection default;
+drop table test;
+set global query_cache_type=@save_qcache_type;
+set global query_cache_size=@save_qcache_size;
+drop table if exists t1;
+show variables like 'innodb_rollback_on_timeout';
+Variable_name Value
+innodb_rollback_on_timeout OFF
+create table t1 (a int unsigned not null primary key) engine = innodb;
+insert into t1 values (1);
+commit;
+connect con1,localhost,root,,;
+connect con2,localhost,root,,;
+connection con2;
+begin work;
+insert into t1 values (2);
+select * from t1;
+a
+1
+2
+connection con1;
+begin work;
+insert into t1 values (5);
+select * from t1;
+a
+1
+5
+insert into t1 values (2);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+select * from t1;
+a
+1
+5
+commit;
+connection con2;
+select * from t1;
+a
+1
+2
+commit;
+connection default;
+select * from t1;
+a
+1
+2
+5
+drop table t1;
+disconnect con1;
+disconnect con2;
+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;
+id c counter
+2 a 2
+insert into t1 (id, c) values
+(NULL, 'b')
+on duplicate key update id = values(id), counter = counter + 1;
+select * from t1;
+id c counter
+2 a 2
+3 b 1
+truncate table t1;
+insert into t1 (id, c) values (NULL, 'a');
+select * from t1;
+id c counter
+1 a 1
+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;
+id c counter
+1 a 1
+3 b 2
+insert into t1 (id, c) values (NULL, 'a')
+on duplicate key update id = values(id), c = values(c), counter = counter + 1;
+select * from t1;
+id c counter
+3 b 2
+4 a 2
+drop table t1;
+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;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
+Warnings:
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+Warning 1365 Division by 0
+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;
+Table Op Msg_type Msg_text
+test.t1 optimize status OK
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+40960
+SELECT COUNT(*) FROM t1 WHERE acct_id=785;
+COUNT(*)
+8702
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
+INSERT INTO t2 SELECT * FROM t1;
+OPTIMIZE TABLE t2;
+Table Op Msg_type Msg_text
+test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
+test.t2 optimize status OK
+DROP TABLE t1,t2;
+create table t1(a int) engine=innodb;
+alter table t1 comment '123';
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci COMMENT='123'
+drop table t1;
+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';
+a
+uk
+DELETE FROM t1 WHERE a = 'uk';
+SELECT * FROM t1 WHERE a = 'uk';
+a
+UPDATE t1 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t1 WHERE a = 'uk';
+a
+CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
+INSERT INTO t2 VALUES ('uk'),('bg');
+SELECT * FROM t2 WHERE a = 'uk';
+a
+uk
+DELETE FROM t2 WHERE a = 'uk';
+SELECT * FROM t2 WHERE a = 'uk';
+a
+INSERT INTO t2 VALUES ('uk');
+UPDATE t2 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t2 WHERE a = 'uk';
+a
+CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
+INSERT INTO t3 VALUES ('uk'),('bg');
+SELECT * FROM t3 WHERE a = 'uk';
+a
+uk
+DELETE FROM t3 WHERE a = 'uk';
+SELECT * FROM t3 WHERE a = 'uk';
+a
+INSERT INTO t3 VALUES ('uk');
+UPDATE t3 SET a = 'us' WHERE a = 'uk';
+SELECT * FROM t3 WHERE a = 'uk';
+a
+DROP TABLE t1,t2,t3;
+create table t1 (a int) engine=innodb;
+select * from bug29807;
+ERROR 42S02: Table 'test.bug29807' doesn't exist in engine
+drop table t1;
+drop table bug29807;
+Warnings:
+Warning 1932 Table 'test.bug29807' doesn't exist in engine
+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;
+LOCK TABLES t1 READ, t2 READ;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+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;
+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;
+set @@sort_buffer_size=8192;
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+3072
+EXPLAIN
+SELECT COUNT(*) FROM t1
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
+SELECT COUNT(*) FROM t1
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+COUNT(*)
+3072
+EXPLAIN
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where
+SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
+WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
+COUNT(*)
+3072
+set @@sort_buffer_size=default;
+DROP TABLE t1,t2;
+CREATE TABLE t1 (a int, b int);
+insert into t1 values (1,1),(1,2);
+CREATE TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 'test.t2'
+CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+drop table if exists t2;
+Warnings:
+Note 1051 Unknown table 'test.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;
+Warnings:
+Note 1050 Table 't2' already exists
+SELECT * from t2;
+a b
+100 100
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+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;
+Warnings:
+Note 1050 Table 't2' already exists
+SELECT * from t2;
+a b
+100 100
+COMMIT;
+BEGIN;
+INSERT INTO t2 values(101,101);
+CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
+Warnings:
+Note 1050 Table 't2' already exists
+SELECT * from t2;
+a b
+100 100
+101 101
+ROLLBACK;
+SELECT * from t2;
+a b
+100 100
+TRUNCATE table t2;
+INSERT INTO t2 select * from t1;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+SELECT * from t2;
+a b
+drop table t1,t2;
+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;
+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;
+EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL b 5 NULL 128
+EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
+DROP TABLE t1;
+drop table if exists t1;
+show variables like 'innodb_rollback_on_timeout';
+Variable_name Value
+innodb_rollback_on_timeout OFF
+create table t1 (a int unsigned not null primary key) engine = innodb;
+insert into t1 values (1);
+commit;
+connect con1,localhost,root,,;
+connect con2,localhost,root,,;
+connection con2;
+begin work;
+insert into t1 values (2);
+select * from t1;
+a
+1
+2
+connection con1;
+begin work;
+insert into t1 values (5);
+select * from t1;
+a
+1
+5
+insert into t1 values (2);
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+select * from t1;
+a
+1
+5
+commit;
+connection con2;
+select * from t1;
+a
+1
+2
+commit;
+connection default;
+select * from t1;
+a
+1
+2
+5
+drop table t1;
+disconnect con1;
+disconnect con2;
+drop table if exists t1;
+create table t1 (a int) engine=innodb;
+alter table t1 alter a set default 1;
+drop table t1;
+
+Bug#24918 drop table and lock / inconsistent between
+perm and temp tables
+
+Check transactional tables under LOCK TABLES
+
+drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
+t24918_access;
+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;
+select * from t24918_access;
+ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
+drop table t24918_trans;
+select * from t24918_access;
+ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
+drop table t24918_trans_tmp;
+select * from t24918_access;
+ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
+drop table t24918_tmp;
+select * from t24918_access;
+ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
+unlock tables;
+drop table t24918_access;
+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;
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
+id 1
+select_type SIMPLE
+table t1
+type ref
+possible_keys bkey
+key bkey
+key_len 5
+ref const
+rows 16
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a;
+a b
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+id 1
+select_type SIMPLE
+table t1
+type range
+possible_keys bkey
+key bkey
+key_len 5
+ref NULL
+rows 32
+Extra Using where; Using index; Using filesort
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
+a b
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+17 1
+18 1
+19 1
+20 1
+21 1
+22 1
+23 1
+24 1
+25 1
+26 1
+27 1
+28 1
+29 1
+30 1
+31 1
+32 1
+EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+id 1
+select_type SIMPLE
+table t1
+type range
+possible_keys bkey
+key bkey
+key_len 5
+ref NULL
+rows 32
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
+a b
+17 1
+18 1
+19 1
+20 1
+21 1
+22 1
+23 1
+24 1
+25 1
+26 1
+27 1
+28 1
+29 1
+30 1
+31 1
+32 1
+1 2
+2 2
+3 2
+4 2
+5 2
+6 2
+7 2
+8 2
+9 2
+10 2
+11 2
+12 2
+13 2
+14 2
+15 2
+16 2
+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;
+EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 5
+ref const
+rows 16
+Extra Using where; Using index; Using filesort
+SELECT * FROM t2 WHERE b=1 ORDER BY a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 16
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 16
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+id 1
+select_type SIMPLE
+table t2
+type ref
+possible_keys bkey
+key bkey
+key_len 10
+ref const,const
+rows 16
+Extra Using where; Using index
+SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
+a b c
+1 1 1
+2 1 1
+3 1 1
+4 1 1
+5 1 1
+6 1 1
+7 1 1
+8 1 1
+9 1 1
+10 1 1
+11 1 1
+12 1 1
+13 1 1
+14 1 1
+15 1 1
+16 1 1
+DROP TABLE t1,t2;
+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;
+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;|
+CONNECT con1,localhost,root,,;
+CONNECT con2,localhost,root,,;
+connection con1;
+CALL p1();
+connection con2;
+CALL p1();
+connection default;
+CALL p1();
+connection con1;
+connection con2;
+connection default;
+disconnect con1;
+disconnect con2;
+DROP PROCEDURE p1;
+DROP TABLE t1;
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` text DEFAULT NULL,
+ KEY `a` (`a`(1024))
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
+drop table t1;
+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;
+a b
+2 20
+connect conn2, localhost, root,,test;
+START TRANSACTION;
+SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
+a b
+1 10
+2 10
+ROLLBACK;
+disconnect conn2;
+connection default;
+ROLLBACK;
+DROP TABLE t1;
+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);
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
+SELECT c,b,d FROM t1 GROUP BY c,b,d;
+c b d
+1 1 50
+3 1 4
+3 2 40
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3
+SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
+c b d
+1 1 50
+3 1 4
+3 2 40
+EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
+SELECT c,b,d FROM t1 ORDER BY c,b,d;
+c b d
+1 1 50
+3 1 4
+3 2 40
+EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 8 NULL 3
+SELECT c,b,d FROM t1 GROUP BY c,b;
+c b d
+1 1 50
+3 1 4
+3 2 40
+EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL c 8 NULL 3 Using index
+SELECT c,b FROM t1 GROUP BY c,b;
+c b
+1 1
+3 1
+3 2
+DROP TABLE t1;
+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);
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+id 1
+select_type SIMPLE
+table t1
+type ref
+possible_keys b
+key b
+key_len 5
+ref const
+rows 2
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
+a b
+2 2
+3 2
+EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+id 1
+select_type SIMPLE
+table t1
+type ref
+possible_keys b
+key b
+key_len 5
+ref const
+rows 2
+Extra Using where; Using index
+SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
+a b
+3 2
+2 2
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 5
+ref NULL
+rows 3
+Extra Using index
+SELECT * FROM t1 ORDER BY b ASC, a ASC;
+a b
+1 1
+2 2
+3 2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 5
+ref NULL
+rows 3
+Extra Using index
+SELECT * FROM t1 ORDER BY b DESC, a DESC;
+a b
+3 2
+2 2
+1 1
+EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 5
+ref NULL
+rows 3
+Extra Using index; Using filesort
+SELECT * FROM t1 ORDER BY b ASC, a DESC;
+a b
+1 1
+3 2
+2 2
+EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 5
+ref NULL
+rows 3
+Extra Using index; Using filesort
+SELECT * FROM t1 ORDER BY b DESC, a ASC;
+a b
+2 2
+3 2
+1 1
+DROP TABLE t1;
+
+#
+# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
+#
+
+# - prepare;
+
+DROP TABLE IF EXISTS t1;
+
+CREATE TABLE t1(c INT)
+ENGINE = InnoDB
+ROW_FORMAT = COMPACT;
+
+# - initial check;
+
+SELECT table_schema, table_name, row_format
+FROM INFORMATION_SCHEMA.TABLES
+WHERE table_schema = DATABASE() AND table_name = 't1';
+table_schema table_name row_format
+test t1 Compact
+
+# - change ROW_FORMAT and check;
+
+ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
+
+SELECT table_schema, table_name, row_format
+FROM INFORMATION_SCHEMA.TABLES
+WHERE table_schema = DATABASE() AND table_name = 't1';
+table_schema table_name row_format
+test t1 Redundant
+
+# - that's it, cleanup.
+
+DROP TABLE t1;
+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;
+Field Type Null Key Default Extra
+a char(10) NO UNI NULL
+b char(4) NO PRI NULL
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` char(10) NOT NULL,
+ `b` char(4) NOT NULL,
+ UNIQUE KEY `bb` (`b`),
+ UNIQUE KEY `aa` (`a`(1))
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+drop table t1;
+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);
+EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
+SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
+id type d
+191 member 1
+NULL member 3
+NULL member 4
+DROP TABLE t1;
+set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
+set global innodb_autoextend_increment=8;
+set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
+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;
+EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range t1_b t1_b 5 NULL 8 Using where
+SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
+a b c
+8 1 1
+7 1 1
+6 1 1
+5 1 1
+4 1 1
+DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
+CREATE INDEX i1 on t1 (a(3));
+SELECT * FROM t1 WHERE a = 'abcde';
+a
+DROP TABLE t1;
+#
+# BUG #26288: savepoint are not deleted on comit, if the transaction
+# was otherwise empty
+#
+BEGIN;
+SAVEPOINT s1;
+COMMIT;
+RELEASE SAVEPOINT s1;
+ERROR 42000: SAVEPOINT s1 does not exist
+BEGIN;
+SAVEPOINT s2;
+COMMIT;
+ROLLBACK TO SAVEPOINT s2;
+ERROR 42000: SAVEPOINT s2 does not exist
+BEGIN;
+SAVEPOINT s3;
+ROLLBACK;
+RELEASE SAVEPOINT s3;
+ERROR 42000: SAVEPOINT s3 does not exist
+BEGIN;
+SAVEPOINT s4;
+ROLLBACK;
+ROLLBACK TO SAVEPOINT s4;
+ERROR 42000: SAVEPOINT s4 does not exist
+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;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `f1` int(11) NOT NULL COMMENT 'My ID#',
+ `f2` int(11) DEFAULT NULL,
+ `f3` char(10) DEFAULT 'My ID#',
+ PRIMARY KEY (`f1`),
+ KEY `f2_ref` (`f2`),
+ CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t1;
+#
+# Bug #36995: valgrind error in remove_const during subquery executions
+#
+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;
+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` (
+`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();
+last_insert_id()
+2
+select * from t2;
+k a c
+1 6 NULL
+2 7 NULL
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+last_insert_id()
+2
+select last_insert_id(0);
+last_insert_id(0)
+0
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1;
+select last_insert_id();
+last_insert_id()
+0
+select * from t2;
+k a c
+1 6 2
+2 7 NULL
+insert ignore into t2 values (null,6,1),(10,8,1);
+Warnings:
+Warning 1062 Duplicate entry '6' for key 'idx_1'
+select last_insert_id();
+last_insert_id()
+0
+insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
+Warnings:
+Warning 1062 Duplicate entry '6' for key 'idx_1'
+Warning 1062 Duplicate entry '8' for key 'idx_1'
+select last_insert_id();
+last_insert_id()
+11
+select * from t2;
+k a c
+1 6 2
+2 7 NULL
+10 8 1
+11 15 1
+12 20 1
+insert into t2 ( a ) values ( 6 ) on duplicate key update c =
+ifnull( c,
+0 ) + 1, k=last_insert_id(k);
+select last_insert_id();
+last_insert_id()
+1
+select * from t2;
+k a c
+1 6 3
+2 7 NULL
+10 8 1
+11 15 1
+12 20 1
+drop table t2;
+drop table if exists t1, t2;
+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;
+drop table if exists t1;
+create table t1 (i int);
+insert into t1 values ();
+lock table t1 write;
+alter table t1 modify i int default 1;
+insert into t1 values ();
+select * from t1;
+i
+NULL
+1
+alter table t1 change i c char(10) default "Two";
+insert into t1 values ();
+select * from t1;
+c
+NULL
+1
+Two
+unlock tables;
+select * from t1;
+c
+NULL
+1
+Two
+drop tables t1;
+create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
+CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
+insert into t1(f1) values(1);
+select @a:=f2 from t1;
+@a:=f2
+#
+update t1 set f1=1;
+select @b:=f2 from t1;
+@b:=f2
+#
+select if(@a=@b,"ok","wrong");
+if(@a=@b,"ok","wrong")
+ok
+insert into t1(f1) values (1) on duplicate key update f1="1";
+select @b:=f2 from t1;
+@b:=f2
+#
+select if(@a=@b,"ok","wrong");
+if(@a=@b,"ok","wrong")
+ok
+insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
+select @b:=f2 from t1;
+@b:=f2
+#
+select if(@a=@b,"ok","wrong");
+if(@a=@b,"ok","wrong")
+ok
+drop table t1;
+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;
+CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
+ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1,2);
+# 1. test for locking:
+BEGIN;
+UPDATE t1 SET b = 12 WHERE a = 1;
+affected rows: 1
+info: Rows matched: 1 Changed: 1 Warnings: 0
+SELECT * FROM t1;
+a b
+1 12
+connection con2;
+UPDATE t1 SET b = 21 WHERE a = 1;
+ERROR HY000: Lock wait timeout exceeded; try restarting transaction
+connection con1;
+SELECT * FROM t1;
+a b
+1 12
+ROLLBACK;
+connection con2;
+ROLLBACK;
+connection con1;
+# 2. test for serialized update:
+CREATE TABLE t2 (a INT);
+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;
+UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
+affected rows: 1
+info: Rows matched: 1 Changed: 1 Warnings: 0
+SELECT * FROM t1;
+a b
+1 init+con1
+connection con2;
+CALL p1;;
+connection con1;
+SELECT * FROM t1;
+a b
+1 init+con1
+COMMIT;
+SELECT * FROM t1;
+a b
+1 init+con1
+connection con2;
+SELECT * FROM t1;
+a b
+1 init+con1+con2
+COMMIT;
+connection con1;
+# 3. test for updated key column:
+TRUNCATE t1;
+TRUNCATE t2;
+INSERT INTO t1 VALUES (1,'init');
+BEGIN;
+UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
+affected rows: 1
+info: Rows matched: 1 Changed: 1 Warnings: 0
+SELECT * FROM t1;
+a b
+2 init+con1
+connection con2;
+CALL p1;;
+connection con1;
+SELECT * FROM t1;
+a b
+2 init+con1
+COMMIT;
+SELECT * FROM t1;
+a b
+2 init+con1
+connection con2;
+SELECT * FROM t1;
+a b
+2 init+con1
+connection default;
+disconnect con1;
+disconnect con2;
+DROP PROCEDURE p1;
+DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
+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 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
+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;
+ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
+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;
+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 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
+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;
+ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
+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;
+Table Create Table
+t2 CREATE TABLE `t2` (
+ `c` int(11) NOT NULL,
+ `d` int(11) NOT NULL,
+ PRIMARY KEY (`c`,`d`),
+ CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
+ CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
+ CONSTRAINT `f3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
+ CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+DROP TABLE t2;
+DROP TABLE t1;
+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;
+Warnings:
+Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
+drop table t1;
+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);
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where
+SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
+vid tid idx name type
+3 1 4 c_extra NULL
+3 1 3 c2 NULL
+3 1 2 c1 NULL
+3 1 1 pk NULL
+DROP TABLE t1;
+#
+# Bug #44290: explain crashes for subquery with distinct in
+# SQL_SELECT::test_quick_select
+# (reproduced only with InnoDB tables)
+#
+CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
+FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+1
+1
+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> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where
+DROP TABLE t1;
+CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
+FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+1
+1
+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> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where
+DROP TABLE t1;
+CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2),
+KEY (c3), KEY (c2, c3))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
+SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
+FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
+1
+1
+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> ALL NULL NULL NULL NULL 2
+2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where
+DROP TABLE t1;
+End of 5.1 tests
+#
+# Bug#43600: Incorrect type conversion caused wrong result.
+#
+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;
+explain select * from t1, t2 where t2.a=t1.a and t2.b + 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 5
+1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition
+select * from t1, t2 where t2.a=t1.a and t2.b + 1;
+a a b filler
+0 0 1 filler
+1 1 1 filler
+2 2 1 filler
+3 3 1 filler
+4 4 1 filler
+drop table t1,t2;
+# End of test case for the bug#43600
+#
+# Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
+#
+# Check that a TRUNCATE TABLE statement, needing an exclusive meta
+# data lock, waits for a shared metadata lock owned by a concurrent
+# transaction.
+#
+CREATE TABLE t1 (a INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2),(3);
+BEGIN;
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+3
+connect con1, localhost, root,,;
+TRUNCATE TABLE t1;;
+connection default;
+SELECT * FROM t1 ORDER BY a;
+a
+1
+2
+3
+ROLLBACK;
+connection con1;
+# Reaping TRUNCATE TABLE
+SELECT * FROM t1;
+a
+disconnect con1;
+connection default;
+DROP TABLE t1;
+drop table if exists t1, t2, t3;
+#
+# BUG#35850: Performance regression in 5.1.23/5.1.24
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
+insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
+# this must use key 'a', not PRIMARY:
+explain select a from t2 where a=b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index
+drop table t1, t2;
+#
+# Bug #40360: Binlog related errors with binlog off
+#
+SET SESSION BINLOG_FORMAT=STATEMENT;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
+@@session.sql_log_bin 1
+@@session.binlog_format STATEMENT
+@@session.tx_isolation READ-COMMITTED
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1);
+DROP TABLE t1;
+#
+# Bug#37284 Crash in Field_string::type()
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
+CREATE INDEX i1 on t1 (a(3));
+SELECT * FROM t1 WHERE a = 'abcde';
+a
+DROP TABLE t1;
+#
+# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
+# requested column
+#
+CREATE TABLE foo (a int, b int, c char(10),
+PRIMARY KEY (c(3)),
+KEY b (b)
+) engine=innodb;
+CREATE TABLE foo2 (a int, b int, c char(10),
+PRIMARY KEY (c),
+KEY b (b)
+) engine=innodb;
+CREATE TABLE bar (a int, b int, c char(10),
+PRIMARY KEY (c(3)),
+KEY b (b)
+) engine=myisam;
+INSERT INTO foo VALUES
+(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
+(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
+INSERT INTO bar SELECT * FROM foo;
+INSERT INTO foo2 SELECT * FROM foo;
+ANALYZE TABLE bar;
+ANALYZE TABLE foo;
+ANALYZE TABLE foo2;
+EXPLAIN SELECT c FROM bar WHERE b>2;;
+id 1
+select_type SIMPLE
+table bar
+type ALL
+possible_keys b
+key NULL
+key_len NULL
+ref NULL
+rows 6
+Extra Using where
+EXPLAIN SELECT c FROM foo WHERE b>2;;
+id 1
+select_type SIMPLE
+table foo
+type ALL
+possible_keys b
+key NULL
+key_len NULL
+ref NULL
+rows 6
+Extra Using where
+EXPLAIN SELECT c FROM foo2 WHERE b>2;;
+id 1
+select_type SIMPLE
+table foo2
+type range
+possible_keys b
+key b
+key_len 5
+ref NULL
+rows 5
+Extra Using where; Using index
+EXPLAIN SELECT c FROM bar WHERE c>2;;
+id 1
+select_type SIMPLE
+table bar
+type ALL
+possible_keys PRIMARY
+key NULL
+key_len NULL
+ref NULL
+rows 6
+Extra Using where
+EXPLAIN SELECT c FROM foo WHERE c>2;;
+id 1
+select_type SIMPLE
+table foo
+type ALL
+possible_keys PRIMARY
+key NULL
+key_len NULL
+ref NULL
+rows 6
+Extra Using where
+EXPLAIN SELECT c FROM foo2 WHERE c>2;;
+id 1
+select_type SIMPLE
+table foo2
+type index
+possible_keys PRIMARY
+key b
+key_len 5
+ref NULL
+rows 6
+Extra Using where; Using index
+DROP TABLE foo, bar, foo2;
+#
+# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
+#
+DROP TABLE IF EXISTS t1,t3,t2;
+DROP FUNCTION IF EXISTS f1;
+CREATE FUNCTION f1() RETURNS VARCHAR(250)
+BEGIN
+return 'hhhhhhh' ;
+END|
+CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
+BEGIN WORK;
+CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
+CREATE TEMPORARY TABLE t3 LIKE t2;
+INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
+SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
+PREPARE stmt1 FROM @stmt;
+SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
+PREPARE stmt3 FROM @stmt;
+EXECUTE stmt1;
+COMMIT;
+DEALLOCATE PREPARE stmt1;
+DEALLOCATE PREPARE stmt3;
+DROP TABLE t1,t3,t2;
+DROP FUNCTION f1;
+#
+# Bug#37016: TRUNCATE TABLE removes some rows but not all
+#
+DROP TABLE IF EXISTS t1,t2;
+CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE t2 (id INT PRIMARY KEY,
+t1_id INT, INDEX par_ind (t1_id),
+FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (3,2);
+SET AUTOCOMMIT = 0;
+START TRANSACTION;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
+SELECT * FROM t1;
+id
+1
+2
+COMMIT;
+SELECT * FROM t1;
+id
+1
+2
+START TRANSACTION;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
+SELECT * FROM t1;
+id
+1
+2
+ROLLBACK;
+SELECT * FROM t1;
+id
+1
+2
+SET AUTOCOMMIT = 1;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+1
+2
+COMMIT;
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
+SELECT * FROM t1;
+id
+1
+2
+DELETE FROM t2 WHERE id = 3;
+START TRANSACTION;
+SELECT * FROM t1;
+id
+1
+2
+TRUNCATE TABLE t1;
+ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
+ROLLBACK;
+SELECT * FROM t1;
+id
+1
+2
+TRUNCATE TABLE t2;
+DROP TABLE t2;
+DROP TABLE t1;
+#
+# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+#
+CREATE TABLE t1 (
+id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (id)
+) ENGINE=InnoDB;
+CREATE TABLE t2 (
+id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+aid INT UNSIGNED NOT NULL,
+PRIMARY KEY (id),
+FOREIGN KEY (aid) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+CREATE TABLE t3 (
+bid INT UNSIGNED NOT NULL,
+FOREIGN KEY (bid) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+CREATE TABLE t4 (
+a INT
+) ENGINE=InnoDB;
+CREATE TABLE t5 (
+a INT
+) ENGINE=InnoDB;
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
+INSERT INTO t3 (bid) VALUES (1);
+INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
+INSERT INTO t5 VALUES (1);
+DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
+DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP TABLES t4,t5;
+# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+# Testing for any side effects of IGNORE on AFTER DELETE triggers used with
+# transactional tables.
+#
+CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
+CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
+FOREIGN KEY (t1i) REFERENCES t1(i))
+ENGINE=InnoDB;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @b:='EXECUTED TRIGGER';
+INSERT INTO t2 VALUES (@b);
+SET @a:= error_happens_here;
+END||
+SET @b:="";
+SET @a:="";
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 SELECT * FROM t1;
+** An error in a trigger causes rollback of the statement.
+DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
+SELECT @a,@b;
+@a @b
+ EXECUTED TRIGGER
+SELECT * FROM t2;
+a
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i i
+1 1
+2 2
+3 3
+4 4
+** Same happens with the IGNORE option
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
+SELECT * FROM t2;
+a
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i i
+1 1
+2 2
+3 3
+4 4
+**
+** The following is an attempt to demonstrate
+** error handling inside a row iteration.
+**
+DROP TRIGGER trg;
+DELETE FROM t1;
+DELETE FROM t2;
+DELETE FROM t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+INSERT INTO t4 VALUES (3,3),(4,4);
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
+INSERT INTO t2 VALUES (@b);
+END||
+** DELETE is prevented by foreign key constrains but errors are silenced.
+** The AFTER trigger isn't fired.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+** Tables are modified by best effort:
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i i
+3 3
+4 4
+** The AFTER trigger was only executed on successful rows:
+SELECT * FROM t2;
+a
+EXECUTED TRIGGER FOR ROW 1
+EXECUTED TRIGGER FOR ROW 2
+DROP TRIGGER trg;
+**
+** Induce an error midway through an AFTER-trigger
+**
+DELETE FROM t4;
+DELETE FROM t1;
+DELETE FROM t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+SET @a:= @a+1;
+IF @a > 2 THEN
+INSERT INTO t4 VALUES (5,5);
+END IF;
+END||
+SET @a:=0;
+** Errors in the trigger causes the statement to abort.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`t1i`) REFERENCES `t1` (`i`))
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+i i
+1 1
+2 2
+3 3
+4 4
+SELECT * FROM t4;
+i t1i
+DROP TRIGGER trg;
+DROP TABLE t4;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+#
+# Bug#43580: Issue with Innodb on multi-table update
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
+INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+UPDATE t2 straight_join t1 SET t1.a = t1.a + 100, t2.b = t1.a + 10
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
+SELECT * FROM t2;
+a b
+1 1
+2 12
+3 13
+4 14
+5 5
+UPDATE t4 straight_join t3 SET t3.a = t3.a + 100, t4.b = t3.a + 10
+WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
+SELECT * FROM t4;
+a b
+1 1
+2 12
+3 13
+4 14
+5 5
+DROP TABLE t1, t2, t3, t4;
+#
+# Bug#44886: SIGSEGV in test_if_skip_sort_order() -
+# uninitialized variable used as subscript
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1,0);
+CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,2);
+CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1, 1);
+SELECT * FROM t1, t2, t3
+WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
+GROUP BY t1.b;
+a b c d a b e a b
+1 1 1 0 1 1 2 1 1
+DROP TABLE t1, t2, t3;
+#
+# Bug #45828: Optimizer won't use partial primary key if another
+# index can prevent filesort
+#
+CREATE TABLE `t1` (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+PRIMARY KEY (c1,c2),
+KEY (c3)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+c1 c2 c3
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort
+CREATE TABLE t2 (
+c1 int NOT NULL,
+c2 int NOT NULL,
+c3 int NOT NULL,
+KEY (c1,c2),
+KEY (c3)
+) ENGINE=InnoDB;
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort
+DROP TABLE t1,t2;
+#
+# 36259: Optimizing with ORDER BY
+#
+CREATE TABLE t1 (
+a INT NOT NULL AUTO_INCREMENT,
+b INT NOT NULL,
+c INT NOT NULL,
+d VARCHAR(5),
+e INT NOT NULL,
+PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+ANALYZE TABLE t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL {checked} Using where
+DROP TABLE t1;
+#
+# Bug #47963: Wrong results when index is used
+#
+CREATE TABLE t1(
+a VARCHAR(5) NOT NULL,
+b VARCHAR(5) NOT NULL,
+c DATETIME NOT NULL,
+KEY (c)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
+a b c
+TEST TEST 2009-10-09 00:00:00
+SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+a b c
+EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
+c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+DROP TABLE t1;
+#
+# Bug #46175: NULL read_view and consistent read assertion
+#
+CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
+CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
+INSERT INTO t1 VALUES (),();
+INSERT INTO t2 VALUES (),();
+CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
+WHERE b =(SELECT a FROM t1 LIMIT 1);
+CONNECT con1, localhost, root,,;
+connection default;
+CREATE PROCEDURE p1(num INT)
+BEGIN
+DECLARE i INT DEFAULT 0;
+REPEAT
+SHOW CREATE VIEW v1;
+SET i:=i+1;
+UNTIL i>num END REPEAT;
+END|
+# Should not crash
+# Should not crash
+disconnect con1;
+DROP PROCEDURE p1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+#
+# Bug #49324: more valgrind errors in test_if_skip_sort_order
+#
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
+# should not cause valgrind warnings
+SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
+1
+DROP TABLE t1;
+#
+# Bug#50843: Filesort used instead of clustered index led to
+# performance degradation.
+#
+create table t1(f1 int not null primary key, f2 int) engine=innodb;
+create table t2(f1 int not null, key (f1)) engine=innodb;
+insert into t1 values (1,1),(2,2),(3,3);
+insert into t2 values (1),(2),(3);
+analyze table t1;
+analyze table t2;
+explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index NULL PRIMARY 4 NULL 3
+1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index
+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);
+ANALYZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 analyze status Engine-independent statistics collected
+test.t1 analyze status OK
+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
+#
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
+ENGINE=InnoDB;
+ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
+SHOW INDEXES FROM t1;;
+Table t1
+Non_unique 0
+Key_name k
+Seq_in_index 1
+Column_name a
+Collation A
+Cardinality 0
+Sub_part NULL
+Packed NULL
+Null
+Index_type BTREE
+Comment
+Index_comment
+Ignored NO
+Table t1
+Non_unique 0
+Key_name k
+Seq_in_index 2
+Column_name b
+Collation A
+Cardinality 0
+Sub_part NULL
+Packed NULL
+Null
+Index_type BTREE
+Comment
+Index_comment
+Ignored NO
+DROP TABLE t1;
+#
+# Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when
+# JOINed during an UPDATE
+#
+CREATE TABLE t1 (d INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT, b INT,
+c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
+# set up our data elements
+INSERT INTO t1 (d) VALUES (1);
+INSERT INTO t2 (a,b) VALUES (1,1);
+SELECT SECOND(c) INTO @bug47453 FROM t2;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+SELECT SLEEP(1);
+SLEEP(1)
+0
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+# should be 0
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+SECOND(c)-@bug47453
+0
+DROP TABLE t1, t2;
+#
+# 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#38999 valgrind warnings for update statement in function compare_record()
+#
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 values (1),(2),(3),(4),(5);
+INSERT INTO t2 values (1);
+SELECT * FROM t1 WHERE a = 2;
+a
+2
+UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
+DROP TABLE t1,t2;
+#
+# Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
+#
+CREATE TABLE t1 (a INT, b INT, c INT, d INT,
+PRIMARY KEY(a,b,c), KEY(b,d))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 77, 1, 3);
+UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
+DROP TABLE t1;
+#
+# Bug#50389 Using intersect does not return all rows
+#
+CREATE TABLE t1 (
+f1 INT(10) NOT NULL,
+f2 INT(10),
+f3 INT(10),
+f4 TINYINT(4),
+f5 VARCHAR(50),
+PRIMARY KEY (f1),
+KEY idx1 (f2,f5,f4),
+KEY idx2 (f2,f4)
+) ENGINE=InnoDB;
+LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
+ANALYZE TABLE t1;
+set @tmp_innodb_mysql= @@optimizer_switch;
+set optimizer_switch='extended_keys=off';
+SELECT * FROM t1 WHERE f1 IN
+(3305028,3353871,3772880,3346860,4228206,3336022,
+3470988,3305175,3329875,3817277,3856380,3796193,
+3784744,4180925,4559596,3963734,3856391,4494153)
+AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
+f1 f2 f3 f4 f5
+3305175 1221457 0 0 abcdefghijklmnopwrst
+3329875 1221457 1382427 0 abcdefghijklmnopwrst
+3336022 1221457 0 0 abcdefghijklmnopwrst
+3346860 1221457 0 0 abcdefghijklmnopwrst
+3772880 1221457 0 0 abcdefghijklmnopwrst
+3784744 1221457 1382427 0 abcdefghijklmnopwrst
+3796193 1221457 0 0 abcdefghijklmnopwrst
+4228206 1221457 0 0 abcdefghijklmnopwrst
+4494153 1221457 0 0 abcdefghijklmnopwrst
+4559596 1221457 0 0 abcdefghijklmnopwrst
+EXPLAIN SELECT * FROM t1 WHERE f1 IN
+(3305028,3353871,3772880,3346860,4228206,3336022,
+3470988,3305175,3329875,3817277,3856380,3796193,
+3784744,4180925,4559596,3963734,3856391,4494153)
+AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,60,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where
+set optimizer_switch=@tmp_innodb_mysql;
+DROP TABLE t1;
+#
+# Bug#51431 Wrong sort order after import of dump file
+#
+CREATE TABLE t1 (
+f1 INT(11) NOT NULL,
+f2 int(11) NOT NULL,
+f3 int(11) NOT NULL,
+f4 tinyint(1) NOT NULL,
+PRIMARY KEY (f1),
+UNIQUE KEY (f2, f3),
+KEY (f4)
+) ENGINE=InnoDB STATS_PERSISTENT=0;
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+FLUSH TABLES;
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+f1 f2 f3 f4
+27 1 9922 1
+26 1 9921 1
+25 1 9920 1
+24 1 9919 1
+23 1 9918 1
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range f2,f4 f4 1 NULL 22 Using where
+DROP TABLE t1;
+#
+# Bug#54117 crash in thr_multi_unlock, temporary table
+#
+CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;
+LOCK TABLES t1 READ;
+ALTER TABLE t1 COMMENT 'test';
+UNLOCK TABLES;
+DROP TABLE t1;
+#
+# Bug#55656: mysqldump can be slower after bug #39653 fix
+#
+CREATE TABLE t1 (a INT , b INT, c INT, d INT,
+KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB STATS_PERSISTENT=0;
+INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 4
+ref NULL
+rows 3
+Extra Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key b
+key_len 8
+ref NULL
+rows 3
+Extra Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key PRIMARY
+key_len 8
+ref NULL
+rows 3
+Extra Using index
+DROP INDEX b ON t1;
+CREATE INDEX b ON t1(a,b,c,d);
+EXPLAIN SELECT COUNT(*) FROM t1;
+id 1
+select_type SIMPLE
+table t1
+type index
+possible_keys NULL
+key PRIMARY
+key_len 8
+ref NULL
+rows 3
+Extra Using index
+DROP TABLE t1;
+#
+# Bug#55826: create table .. select crashes with when KILL_BAD_DATA
+# is returned
+#
+CREATE TABLE t1(a INT) ENGINE=innodb;
+INSERT INTO t1 VALUES (0);
+SET SQL_MODE='STRICT_ALL_TABLES';
+CREATE TABLE t2
+SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
+ERROR 22007: Truncated incorrect datetime value: ''
+DROP TABLE t1;
+SET SQL_MODE=DEFAULT;
+#
+# Bug#56862 Execution of a query that uses index merge returns a wrong result
+#
+CREATE TABLE t1 (
+pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
+a int,
+b int,
+INDEX idx(a))
+ENGINE=INNODB;
+INSERT INTO t1(a,b) VALUES
+(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
+(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
+(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
+(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
+INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
+INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
+INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t1 VALUES (1000000, 0, 0);
+set @optimizer_switch_saved=@@optimizer_switch;
+SET SESSION optimizer_switch='derived_merge=off';
+SET SESSION sort_buffer_size = 1024*36;
+EXPLAIN
+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 <derived2> ALL NULL NULL NULL NULL #
+2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL # Using sort_union(idx,PRIMARY); Using where
+SELECT COUNT(*) FROM
+(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
+WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
+COUNT(*)
+1537
+set @@optimizer_switch=@optimizer_switch_saved;
+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;
+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 COLLATE=latin1_swedish_ci
+drop table t2, t1;
+#
+# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
+# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
+#
+DROP TABLE IF EXISTS t1;
+CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
+INSERT INTO t1 VALUES (1);
+LOCK TABLES t1 READ;
+# Even though temporary table was locked for READ we
+# still allow writes to it to be compatible with MyISAM.
+# This is possible since due to fact that temporary tables
+# are specific to connection and therefore locking for them
+# is irrelevant.
+UPDATE t1 SET c = 5;
+UNLOCK TABLES;
+DROP TEMPORARY TABLE t1;
+# End of 5.1 tests
+#
+# Bug#49604 "6.0 processing compound WHERE clause incorrectly
+# with Innodb - extra rows"
+#
+CREATE TABLE t1 (
+c1 INT NOT NULL,
+c2 INT,
+PRIMARY KEY (c1),
+KEY k1 (c2)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (12,1);
+INSERT INTO t1 VALUES (15,1);
+INSERT INTO t1 VALUES (16,1);
+INSERT INTO t1 VALUES (22,1);
+INSERT INTO t1 VALUES (20,2);
+CREATE TABLE t2 (
+c1 INT NOT NULL,
+c2 INT,
+PRIMARY KEY (c1)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,2);
+INSERT INTO t2 VALUES (2,9);
+SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1
+FROM t1 JOIN t2 ON t1.c2 = t2.c1
+WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1);
+c2 c2 c1
+2 1 1
+2 1 1
+2 1 1
+2 1 1
+9 2 2
+DROP TABLE t1, t2;
+#
+# Bug#44613 SELECT statement inside FUNCTION takes a shared lock
+#
+DROP TABLE IF EXISTS t1;
+DROP FUNCTION IF EXISTS f1;
+CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb;
+INSERT INTO t1 VALUES (1, 0), (2, 0);
+CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA
+RETURN (SELECT x FROM t1 WHERE x = z);
+START TRANSACTION;
+SELECT f1(1);
+f1(1)
+1
+START TRANSACTION;
+SELECT f1(1);
+f1(1)
+1
+UPDATE t1 SET y = 1 WHERE x = 1;
+COMMIT;
+disconnect con2;
+connection default;
+COMMIT;
+DROP TABLE t1;
+DROP FUNCTION f1;
+#
+# Bug#42744: Crash when using a join buffer to join a table with a blob
+# column and an additional column used for duplicate elimination.
+#
+CREATE TABLE t1 (a tinyblob) ENGINE=InnoDB;
+CREATE TABLE t2 (a int PRIMARY KEY, b tinyblob) ENGINE=InnoDB;
+INSERT INTO t1 VALUES ('1'), (NULL);
+INSERT INTO t2 VALUES (1, '1');
+EXPLAIN
+SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 1
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+1 PRIMARY t2 index NULL PRIMARY 4 NULL 1 Using index; FirstMatch(t1); Using join buffer (incremental, BNL join)
+SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
+b
+1
+DROP TABLE t1,t2;
+#
+# Bug#48093: 6.0 Server not processing equivalent IN clauses properly
+# with Innodb tables
+#
+CREATE TABLE t1 (
+i int(11) DEFAULT NULL,
+v1 varchar(1) DEFAULT NULL,
+v2 varchar(20) DEFAULT NULL,
+KEY i (i),
+KEY v (v1,i)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (1,'f','no');
+INSERT INTO t1 VALUES (2,'u','yes-u');
+INSERT INTO t1 VALUES (2,'h','yes-h');
+INSERT INTO t1 VALUES (3,'d','no');
+
+SELECT v2
+FROM t1
+WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
+v2
+yes-u
+yes-h
+
+# Should not use index_merge
+EXPLAIN
+SELECT v2
+FROM t1
+WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ref i,v i 5 const 2 Using where
+DROP TABLE t1;
+#
+# Bug#54606 innodb fast alter table + pack_keys=0
+# prevents adding new indexes
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b))
+ENGINE=InnoDB
+PACK_KEYS=0;
+CREATE INDEX a ON t1 (a);
+CREATE INDEX c on t1 (c);
+DROP TABLE t1;
+#
+# Additional coverage for refactoring which is made as part
+# of fix for Bug#27480 "Extend CREATE TEMPORARY TABLES privilege
+# to allow temp table operations".
+#
+# Check that OPTIMIZE table works for temporary InnoDB tables.
+DROP TABLE IF EXISTS t1;
+CREATE TEMPORARY TABLE t1 (a INT) ENGINE=InnoDB;
+OPTIMIZE TABLE t1;
+Table Op Msg_type Msg_text
+test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
+test.t1 optimize status OK
+DROP TABLE t1;
+#
+# Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE
+# COMMAND CAN FAIL IN INNODB PLUGIN 1.0
+#
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id),
+INDEX a (a)) ENGINE=innodb;
+ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a);
+ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b);
+DROP TABLE t1;
+End of 6.0 tests
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-18 13:27:30 +0000