summaryrefslogtreecommitdiff
path: root/mysql-test/r/innodb_mysql.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/innodb_mysql.result')
-rw-r--r--mysql-test/r/innodb_mysql.result2495
1 files changed, 0 insertions, 2495 deletions
diff --git a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
deleted file mode 100644
index 3d409d3bfb1..00000000000
--- a/mysql-test/r/innodb_mysql.result
+++ /dev/null
@@ -1,2495 +0,0 @@
-set global innodb_support_xa=default;
-set session innodb_support_xa=default;
-SET SESSION 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;
-handler t1 open;
-handler t1 read first;
-c1
-Before and after comparison
-0
-drop table t1;
-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 t2i ALL NULL NULL NULL NULL 1
-1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
-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 t2i ALL NULL NULL NULL NULL 1
-1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
-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 1
-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 1
-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 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:
-Error 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));
-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 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary
-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 index PRIMARY,name name 23 NULL 3 Using where; Using index
-1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
-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 t2 index NULL fkey 5 NULL 5 Using index
-1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
-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;
-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');
-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 range name name 44 NULL 2 Using where; Using index for group-by
-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 range name name 44 NULL # Using where; Using index for group-by
-SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
-name dept
-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;
-begin work;
-insert into t1 values (2);
-select * from t1;
-a
-1
-2
-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;
-select * from t1;
-a
-1
-2
-commit;
-select * from t1;
-a
-1
-2
-5
-drop table t1;
-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;
-drop table if exists `test`;
-Warnings:
-Note 1051 Unknown table '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');
-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
-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;
-begin work;
-insert into t1 values (2);
-select * from t1;
-a
-1
-2
-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;
-select * from t1;
-a
-1
-2
-commit;
-select * from t1;
-a
-1
-2
-5
-drop table t1;
-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;
-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 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 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 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;
-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
-EXPLAIN SELECT COUNT(*) FROM t2 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 t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
-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 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
-drop table t1;
-drop table bug29807;
-ERROR 42S02: Unknown table 'bug29807'
-create table bug29807 (a int);
-drop table bug29807;
-CREATE TABLE t1 (a INT) ENGINE=InnoDB;
-CREATE TABLE t2 (a INT) ENGINE=InnoDB;
-switch to connection c1
-SET AUTOCOMMIT=0;
-INSERT INTO t2 VALUES (1);
-switch to connection c2
-SET AUTOCOMMIT=0;
-LOCK TABLES t1 READ, t2 READ;
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-switch to connection c1
-COMMIT;
-INSERT INTO t1 VALUES (1);
-switch to connection default
-SET AUTOCOMMIT=default;
-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;
-Warnings:
-Warning 1292 Truncated incorrect sort_buffer_size value: '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 8,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 '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 '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;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-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;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-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;
-ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
-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;
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
-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;
-begin work;
-insert into t1 values (2);
-select * from t1;
-a
-1
-2
-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;
-select * from t1;
-a
-1
-2
-commit;
-select * from t1;
-a
-1
-2
-5
-drop table t1;
-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 16
-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 16
-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 8
-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 8
-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 8
-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 8
-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;|
-CALL p1();
-CALL p1();
-CALL p1();
-DROP PROCEDURE p1;
-DROP TABLE t1;
-create table t1(a text) engine=innodb default charset=utf8;
-insert into t1 values('aaa');
-alter table t1 add index(a(1024));
-Warnings:
-Warning 1071 Specified key was too long; max key length is 767 bytes
-Warning 1071 Specified key was too long; max key length is 767 bytes
-show create table t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `a` text,
- KEY `a` (`a`(255))
-) ENGINE=InnoDB DEFAULT CHARSET=utf8
-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
-START TRANSACTION;
-SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
-a b
-1 10
-2 10
-ROLLBACK;
-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 1
-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 1
-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
-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;
-set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
-set global innodb_commit_concurrency=0;
-set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
-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 index t1_b PRIMARY 4 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
-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;
-explain
-select b from t1 where a not in (select b from t1,t2 group by a) group by a;
-id select_type table type possible_keys key key_len ref rows Extra
-1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
-2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
-2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
-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);
-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);
-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;
-SET SESSION AUTOCOMMIT = 0;
-SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-set binlog_format=mixed;
-# Switch to 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
-# Switch to connection con2
-UPDATE t1 SET b = 21 WHERE a = 1;
-ERROR HY000: Lock wait timeout exceeded; try restarting transaction
-# Switch to connection con1
-SELECT * FROM t1;
-a b
-1 12
-ROLLBACK;
-# Switch to connection con2
-ROLLBACK;
-# Switch to connection con1
-# 2. test for serialized update:
-CREATE TABLE t2 (a INT);
-TRUNCATE t1;
-INSERT INTO t1 VALUES (1,'init');
-CREATE PROCEDURE p1()
-BEGIN
-UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
-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
-# Switch to connection con2
-CALL p1;;
-# Switch to connection con1
-SELECT * FROM t1;
-a b
-1 init+con1
-COMMIT;
-SELECT * FROM t1;
-a b
-1 init+con1
-# Switch to connection con2
-SELECT * FROM t1;
-a b
-1 init+con1+con2
-COMMIT;
-# Switch to 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
-# Switch to connection con2
-CALL p1;;
-# Switch to connection con1
-SELECT * FROM t1;
-a b
-2 init+con1
-COMMIT;
-SELECT * FROM t1;
-a b
-2 init+con1
-# Switch to connection con2
-SELECT * FROM t1;
-a b
-2 init+con1
-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 `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
- CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-DROP TABLE t2;
-DROP TABLE t1;
-create table t1 (a int auto_increment primary key) engine=innodb;
-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> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 10 NULL 5
-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> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 18 NULL 5
-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> system NULL NULL NULL NULL 1
-2 DERIVED t1 index c3,c2 c2 14 NULL 5
-DROP TABLE t1;
-End of 5.1 tests
-#
-# 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
-# Connection con1
-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;
-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;
-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;
-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;
-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;
-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 3
-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;
-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;
-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 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
-SELECT * FROM t1;
-id
-1
-2
-COMMIT;
-SELECT * FROM t1;
-id
-1
-2
-START TRANSACTION;
-TRUNCATE TABLE t1;
-ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `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 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `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;
-ROLLBACK;
-SELECT * FROM t1;
-id
-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;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t2;
-TRUNCATE TABLE 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
-**
-TRUNCATE TABLE t4;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE 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;
-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 t1, t2 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 t3, t4 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;
-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;
-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 128 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);
-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
-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);
-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);
-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
-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
-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;
-End of 5.1 tests
-#
-# Test for bug #39932 "create table fails if column for FK is in different
-# case than in corr index".
-#
-drop tables if exists t1, t2;
-create table t1 (pk int primary key) engine=InnoDB;
-# Even although the below statement uses uppercased field names in
-# foreign key definition it still should be able to find explicitly
-# created supporting index. So it should succeed and should not
-# create any additional supporting indexes.
-create table t2 (fk int, key x (fk),
-constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
-show create table t2;
-Table Create Table
-t2 CREATE TABLE `t2` (
- `fk` int(11) DEFAULT NULL,
- KEY `x` (`fk`),
- CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-drop table t2, t1;
-#
-# Bug#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);
-# Connection default
-START TRANSACTION;
-SELECT f1(1);
-f1(1)
-1
-# Connection con2
-START TRANSACTION;
-SELECT f1(1);
-f1(1)
-1
-UPDATE t1 SET y = 1 WHERE x = 1;
-COMMIT;
-# Connection default
-COMMIT;
-DROP TABLE t1;
-DROP FUNCTION f1;
View Lorry Controller Status