1# include/mix1.inc 2# 3# The variables 4# $engine_type -- storage engine to be tested 5# $other_engine_type -- storage engine <> $engine_type 6# $other_engine_type must point to an all 7# time available storage engine 8# 2006-08 MySQL 5.1 MyISAM and MEMORY only 9# $test_foreign_keys -- 0, skip foreign key tests 10# -- 1, do not skip foreign key tests 11# have to be set before sourcing this script. 12# 13# Note: The comments/expectations refer to InnoDB. 14# They might be not valid for other storage engines. 15# 16# Last update: 17# 2006-08-15 ML refactoring of t/innodb_mysql.test 18# - shift main code of t/innodb_mysql.test to include/mix1.inc 19# - replace hardcoded assignment of storage engine by 20# use of $engine_type and $other_engine_type variables 21# - remove redundant replay testcase of 22# Bug#12882 min/max inconsistent on empty table 23# - corrected analyze table t1; to analyze table t4; 24# Much older versions of this test show that the table 25# where just some indexes have been created must be used. 26# 27 28eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type; 29 30--disable_warnings 31drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4; 32drop procedure if exists p1; 33--enable_warnings 34 35 36# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer 37# (repeatable only w/innodb). 38create table t1 ( 39 c_id int(11) not null default '0', 40 org_id int(11) default null, 41 unique key contacts$c_id (c_id), 42 key contacts$org_id (org_id) 43); 44insert into t1 values 45 (2,null),(120,null),(141,null),(218,7), (128,1), 46 (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3), 47 (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4); 48 49create table t2 ( 50 slai_id int(11) not null default '0', 51 owner_tbl int(11) default null, 52 owner_id int(11) default null, 53 sla_id int(11) default null, 54 inc_web int(11) default null, 55 inc_email int(11) default null, 56 inc_chat int(11) default null, 57 inc_csr int(11) default null, 58 inc_total int(11) default null, 59 time_billed int(11) default null, 60 activedate timestamp null default null, 61 expiredate timestamp null default null, 62 state int(11) default null, 63 sla_set int(11) default null, 64 unique key t2$slai_id (slai_id), 65 key t2$owner_id (owner_id), 66 key t2$sla_id (sla_id) 67); 68insert into t2(slai_id, owner_tbl, owner_id, sla_id) values 69 (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7), 70 (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12); 71 72flush tables; 73select si.slai_id 74from t1 c join t2 si on 75 ((si.owner_tbl = 3 and si.owner_id = c.org_id) or 76 ( si.owner_tbl = 2 and si.owner_id = c.c_id)) 77where 78 c.c_id = 218 and expiredate is null; 79 80select * from t1 where org_id is null; 81select si.slai_id 82from t1 c join t2 si on 83 ((si.owner_tbl = 3 and si.owner_id = c.org_id) or 84 ( si.owner_tbl = 2 and si.owner_id = c.c_id)) 85where 86 c.c_id = 218 and expiredate is null; 87 88drop table t1, t2; 89 90# 91# Bug#17212: results not sorted correctly by ORDER BY when using index 92# (repeatable only w/innodb because of index props) 93# 94CREATE TABLE t1 (a int, b int, KEY b (b)); 95CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b)); 96CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a), 97 UNIQUE KEY b (b,c), KEY a (a,b,c)); 98 99INSERT INTO t1 VALUES (1, 1); 100INSERT INTO t1 SELECT a + 1, b + 1 FROM t1; 101INSERT INTO t1 SELECT a + 2, b + 2 FROM t1; 102 103INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8); 104INSERT INTO t2 SELECT a + 1, b FROM t2; 105DELETE FROM t2 WHERE a = 1 AND b < 2; 106 107INSERT INTO t3 VALUES (1,1,1),(2,1,2); 108INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3; 109INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3; 110 111# demonstrate a problem when a must-use-sort table flag 112# (sort_by_table=1) is being neglected. 113SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 114 t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 115 ORDER BY t1.b LIMIT 2; 116 117# demonstrate the problem described in the bug report 118SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE 119 t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2) 120 ORDER BY t1.b LIMIT 5; 121DROP TABLE t1, t2, t3; 122 123 124# BUG#21077 (The testcase is not deterministic so correct execution doesn't 125# prove anything) For proof one should track if sequence of ha_innodb::* func 126# calls is correct. 127CREATE TABLE `t1` (`id1` INT) ; 128INSERT INTO `t1` (`id1`) VALUES (1),(5),(2); 129 130CREATE TABLE `t2` ( 131 `id1` INT, 132 `id2` INT NOT NULL, 133 `id3` INT, 134 `id4` INT NOT NULL, 135 UNIQUE (`id2`,`id4`), 136 KEY (`id1`) 137); 138 139INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES 140(1,1,1,0), 141(1,1,2,1), 142(5,1,2,2), 143(6,1,2,3), 144(1,2,2,2), 145(1,2,1,1); 146 147SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2); 148DROP TABLE t1, t2; 149 150# 151# Bug #22728 - Handler_rollback value is growing 152# 153 154let $before= `show /*!50002 GLOBAL */ status like 'Handler_rollback'`; 155create table t1 (c1 int) engine=innodb; 156connect (con1,localhost,root,,); 157connect (con2,localhost,root,,); 158connection con2; 159handler t1 open; 160handler t1 read first; 161disconnect con2; 162connection con1; 163let $after= `show /*!50002 GLOBAL */ status like 'Handler_rollback'`; 164# Compare the before and after value, it should be equal 165--disable_query_log 166eval select STRCMP("$before", "$after") as "Before and after comparison"; 167--enable_query_log 168connection default; 169drop table t1; 170disconnect con1; 171 172# 173# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields 174# used in partial unique indices. 175# 176 177CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1) 178 ENGINE=INNODB CHARACTER SET UTF8; 179INSERT INTO t1 (c1) VALUES ('1a'); 180SELECT * FROM t1; 181INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 182SELECT * FROM t1; 183DROP TABLE t1; 184 185CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) 186 ENGINE=INNODB CHARACTER SET UTF8; 187INSERT INTO t1 (c1) VALUES ('1a'); 188SELECT * FROM t1; 189INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 190SELECT * FROM t1; 191DROP TABLE t1; 192 193CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1) 194 ENGINE=INNODB CHARACTER SET UTF8; 195INSERT INTO t1 (c1) VALUES ('1a'); 196SELECT * FROM t1; 197INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1; 198SELECT * FROM t1; 199DROP TABLE t1; 200 201# 202# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table 203# 204 205CREATE TABLE t1 ( 206 a1 decimal(10,0) DEFAULT NULL, 207 a2 blob, 208 a3 time DEFAULT NULL, 209 a4 blob, 210 a5 char(175) DEFAULT NULL, 211 a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 212 a7 tinyblob, 213 INDEX idx (a6,a7(239),a5) 214) ENGINE=InnoDB; 215 216EXPLAIN SELECT a4 FROM t1 WHERE 217a6=NULL AND 218a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; 219 220EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE 221t.a6=t.a6 AND t1.a6=NULL AND 222t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS'; 223 224DROP TABLE t1; 225 226# 227# Bug #12882 min/max inconsistent on empty table 228# 229 230--disable_warnings 231eval create table t1m (a int) engine = $other_engine_type; 232create table t1i (a int); 233eval create table t2m (a int) engine = $other_engine_type; 234create table t2i (a int); 235--enable_warnings 236insert into t2m values (5); 237insert into t2i values (5); 238 239-- disable_query_log 240-- disable_result_log 241analyze table t1i; 242analyze table t1m; 243analyze table t2i; 244analyze table t2m; 245-- enable_result_log 246-- enable_query_log 247 248# test with $engine_type 249select min(a) from t1i; 250select min(7) from t1i; 251select min(7) from DUAL; 252explain select min(7) from t2i join t1i; 253select min(7) from t2i join t1i; 254 255select max(a) from t1i; 256select max(7) from t1i; 257select max(7) from DUAL; 258explain select max(7) from t2i join t1i; 259select max(7) from t2i join t1i; 260 261select 1, min(a) from t1i where a=99; 262select 1, min(a) from t1i where 1=99; 263select 1, min(1) from t1i where a=99; 264select 1, min(1) from t1i where 1=99; 265 266select 1, max(a) from t1i where a=99; 267select 1, max(a) from t1i where 1=99; 268select 1, max(1) from t1i where a=99; 269select 1, max(1) from t1i where 1=99; 270 271# mixed $engine_type/$other_engine_type test 272explain select count(*), min(7), max(7) from t1m, t1i; 273select count(*), min(7), max(7) from t1m, t1i; 274 275explain select count(*), min(7), max(7) from t1m, t2i; 276select count(*), min(7), max(7) from t1m, t2i; 277 278explain select count(*), min(7), max(7) from t2m, t1i; 279select count(*), min(7), max(7) from t2m, t1i; 280 281drop table t1m, t1i, t2m, t2i; 282 283# 284# Bug #12882: primary key implcitly included in every innodb index 285# (was part of group_min_max.test) 286# 287 288eval create table t1 ( 289 a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' ' 290) ENGINE = $other_engine_type; 291 292insert into t1 (a1, a2, b, c, d) values 293('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 294('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 295('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 296('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 297('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 298('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 299('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 300('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 301('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 302('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 303('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 304('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 305('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 306('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 307('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 308('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'), 309('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'), 310('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'), 311('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'), 312('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'), 313('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'), 314('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'), 315('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'), 316('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'), 317('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'), 318('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'), 319('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'), 320('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'), 321('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'), 322('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'), 323('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'), 324('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'); 325--disable_warnings 326create table t4 ( 327 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 ' ' 328); 329--enable_warnings 330insert into t4 (a1, a2, b, c, d, dummy) select * from t1; 331 332create index idx12672_0 on t4 (a1); 333create index idx12672_1 on t4 (a1,a2,b,c); 334create index idx12672_2 on t4 (a1,a2,b); 335analyze table t4; 336 337select distinct a1 from t4 where pk_col not in (1,2,3,4); 338 339drop table t1,t4; 340 341 342# 343# BUG#18819: DELETE IGNORE hangs on foreign key parent delete 344# 345# The bug itself does not relate to InnoDB, but we have to use foreign 346# keys to reproduce it. 347# 348--disable_warnings 349DROP TABLE IF EXISTS t2, t1; 350--enable_warnings 351 352CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB; 353CREATE TABLE t2 ( 354 i INT NOT NULL, 355 FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION 356) ENGINE= InnoDB; 357 358INSERT INTO t1 VALUES (1); 359INSERT INTO t2 VALUES (1); 360 361DELETE IGNORE FROM t1 WHERE i = 1; 362 363SELECT * FROM t1, t2; 364 365DROP TABLE t2, t1; 366 367 368--echo End of 4.1 tests. 369 370 371# 372# Bug #6142: a problem with the empty innodb table 373# (was part of group_min_max.test) 374# 375 376--disable_warnings 377create table t1 ( 378 a varchar(30), b varchar(30), primary key(a), key(b) 379); 380--enable_warnings 381select distinct a from t1; 382drop table t1; 383 384# 385# Bug #9798: group by with rollup 386# (was part of group_min_max.test) 387# 388 389--disable_warnings 390create table t1(a int, key(a)); 391--enable_warnings 392insert into t1 values(1); 393select a, count(a) from t1 group by a with rollup; 394drop table t1; 395 396# 397# Bug #13293 Wrongly used index results in endless loop. 398# (was part of group_min_max.test) 399# 400create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0; 401insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d"); 402alter table t1 drop primary key, add primary key (f2, f1); 403explain select distinct f1 a, f1 b from t1; 404explain select distinct f1, f2 from t1; 405drop table t1; 406 407# 408# Test for bug #17164: ORed FALSE blocked conversion of outer join into join 409# 410 411CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), 412 INDEX (name)); 413CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11)); 414# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), 415# FOREIGN KEY (fkey) REFERENCES t2(id)); 416if ($test_foreign_keys) 417{ 418 ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id); 419} 420INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); 421INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); 422 423-- disable_query_log 424-- disable_result_log 425ANALYZE TABLE t1; 426ANALYZE TABLE t2; 427-- enable_result_log 428-- enable_query_log 429 430EXPLAIN 431SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 432 WHERE t1.name LIKE 'A%'; 433 434EXPLAIN 435SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 436 WHERE t1.name LIKE 'A%' OR FALSE; 437 438DROP TABLE t1,t2; 439 440# 441# Bug#26159: crash for a loose scan of a table that has been emptied 442# 443 444CREATE TABLE t1 ( 445 id int NOT NULL, 446 name varchar(20) NOT NULL, 447 dept varchar(20) NOT NULL, 448 age tinyint(3) unsigned NOT NULL, 449 PRIMARY KEY (id), 450 INDEX (name,dept) 451) ENGINE=InnoDB STATS_PERSISTENT=0; 452INSERT INTO t1(id, dept, age, name) VALUES 453 (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'), 454 (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'), 455 (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'), 456 (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6'); 457 458set 459 @tmp_uss=@@use_stat_tables, 460 @tmp_occ=@@optimizer_use_condition_selectivity; 461set 462 use_stat_tables='preferably', 463 optimizer_use_condition_selectivity=4; 464 465analyze table t1 persistent for all; 466flush tables; 467 468EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 469SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 470DELETE FROM t1; 471--echo # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746). 472--replace_column 9 # 473EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 474SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5'; 475 476DROP TABLE t1; 477set 478 use_stat_tables=@tmp_uss, 479 optimizer_use_condition_selectivity=@tmp_occ; 480 481--source include/innodb_rollback_on_timeout.inc 482 483# 484# Bug #27210: INNODB ON DUPLICATE KEY UPDATE 485# 486 487set @save_qcache_size=@@global.query_cache_size; 488set @save_qcache_type=@@global.query_cache_type; 489set global query_cache_size=10*1024*1024; 490set global query_cache_type=1; 491connect (con1,localhost,root,,); 492connection con1; 493drop table if exists `test`; 494CREATE TABLE `test` (`test1` varchar(3) NOT NULL, 495 `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`)) 496 ENGINE=InnoDB DEFAULT CHARSET=latin1; 497INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678'); 498disconnect con1; 499connect (con2,localhost,root,,); 500connection con2; 501select * from test; 502INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234') 503 ON DUPLICATE KEY UPDATE `test2` = '1234'; 504select * from test; 505flush tables; 506select * from test; 507disconnect con2; 508connection default; 509drop table test; 510set global query_cache_type=@save_qcache_type; 511set global query_cache_size=@save_qcache_size; 512 513--source include/innodb_rollback_on_timeout.inc 514 515# 516# Bug #27650: INSERT fails after multi-row INSERT of the form: 517# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id) 518# 519 520create table t1( 521id int auto_increment, 522c char(1) not null, 523counter int not null default 1, 524primary key (id), 525unique key (c) 526) engine=innodb; 527 528insert into t1 (id, c) values 529(NULL, 'a'), 530(NULL, 'a') 531on duplicate key update id = values(id), counter = counter + 1; 532 533select * from t1; 534 535insert into t1 (id, c) values 536(NULL, 'b') 537on duplicate key update id = values(id), counter = counter + 1; 538 539select * from t1; 540 541truncate table t1; 542 543insert into t1 (id, c) values (NULL, 'a'); 544 545select * from t1; 546 547insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b') 548on duplicate key update id = values(id), c = values(c), counter = counter + 1; 549 550select * from t1; 551 552insert into t1 (id, c) values (NULL, 'a') 553on duplicate key update id = values(id), c = values(c), counter = counter + 1; 554 555select * from t1; 556 557drop table t1; 558 559# 560# Bug #28189: optimizer erroniously prefers ref access to range access 561# for an InnoDB table 562# 563 564CREATE TABLE t1( 565 id int AUTO_INCREMENT PRIMARY KEY, 566 stat_id int NOT NULL, 567 acct_id int DEFAULT NULL, 568 INDEX idx1 (stat_id, acct_id), 569 INDEX idx2 (acct_id) 570) ENGINE=MyISAM; 571 572CREATE TABLE t2( 573 id int AUTO_INCREMENT PRIMARY KEY, 574 stat_id int NOT NULL, 575 acct_id int DEFAULT NULL, 576 INDEX idx1 (stat_id, acct_id), 577 INDEX idx2 (acct_id) 578) ENGINE=InnoDB STATS_PERSISTENT=0; 579 580INSERT INTO t1(stat_id,acct_id) VALUES 581 (1,759), (2,831), (3,785), (4,854), (1,921), 582 (1,553), (2,589), (3,743), (2,827), (2,545), 583 (4,779), (4,783), (1,597), (1,785), (4,832), 584 (1,741), (1,833), (3,788), (2,973), (1,907); 585 586INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 587INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 588INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 589INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 590INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 591INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 592INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 593INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 594INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 595INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 596INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1; 597UPDATE t1 SET acct_id=785 598 WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id); 599OPTIMIZE TABLE t1; 600 601SELECT COUNT(*) FROM t1; 602SELECT COUNT(*) FROM t1 WHERE acct_id=785; 603 604-- disable_query_log 605-- disable_result_log 606ANALYZE TABLE t1; 607-- enable_result_log 608-- enable_query_log 609 610EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785; 611 612INSERT INTO t2 SELECT * FROM t1; 613OPTIMIZE TABLE t2; 614 615-- disable_query_log 616-- disable_result_log 617ANALYZE TABLE t2; 618-- enable_result_log 619-- enable_query_log 620 621DROP TABLE t1,t2; 622 623# 624# Bug #28652: assert when alter innodb table operation 625# 626create table t1(a int) engine=innodb; 627alter table t1 comment '123'; 628show create table t1; 629drop table t1; 630 631# 632# Bug #25866: Getting "#HY000 Can't find record in..." on and INSERT 633# 634CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8; 635INSERT INTO t1 VALUES ('uk'),('bg'); 636SELECT * FROM t1 WHERE a = 'uk'; 637DELETE FROM t1 WHERE a = 'uk'; 638SELECT * FROM t1 WHERE a = 'uk'; 639UPDATE t1 SET a = 'us' WHERE a = 'uk'; 640SELECT * FROM t1 WHERE a = 'uk'; 641 642CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB; 643INSERT INTO t2 VALUES ('uk'),('bg'); 644SELECT * FROM t2 WHERE a = 'uk'; 645DELETE FROM t2 WHERE a = 'uk'; 646SELECT * FROM t2 WHERE a = 'uk'; 647INSERT INTO t2 VALUES ('uk'); 648UPDATE t2 SET a = 'us' WHERE a = 'uk'; 649SELECT * FROM t2 WHERE a = 'uk'; 650 651CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM; 652INSERT INTO t3 VALUES ('uk'),('bg'); 653SELECT * FROM t3 WHERE a = 'uk'; 654DELETE FROM t3 WHERE a = 'uk'; 655SELECT * FROM t3 WHERE a = 'uk'; 656INSERT INTO t3 VALUES ('uk'); 657UPDATE t3 SET a = 'us' WHERE a = 'uk'; 658SELECT * FROM t3 WHERE a = 'uk'; 659 660DROP TABLE t1,t2,t3; 661 662# 663# Test bug when trying to drop data file which no InnoDB directory entry 664# 665 666--disable_query_log 667call mtr.add_suppression("InnoDB: Table .*bug29807.*"); 668call mtr.add_suppression("InnoDB: Cannot open table test/bug29807 from"); 669--enable_query_log 670 671create table t1 (a int) engine=innodb; 672let $MYSQLD_DATADIR= `select @@datadir`; 673copy_file $MYSQLD_DATADIR/test/t1.frm $MYSQLD_DATADIR/test/bug29807.frm; 674--error ER_NO_SUCH_TABLE_IN_ENGINE 675select * from bug29807; 676drop table t1; 677drop table bug29807; 678 679 680# 681# Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked 682# 683 684CREATE TABLE t1 (a INT) ENGINE=InnoDB; 685CREATE TABLE t2 (a INT) ENGINE=InnoDB; 686 687CONNECT (c1,localhost,root,,); 688CONNECT (c2,localhost,root,,); 689 690CONNECTION c1; 691SET AUTOCOMMIT=0; 692INSERT INTO t2 VALUES (1); 693 694CONNECTION c2; 695SET AUTOCOMMIT=0; 696SET @old_lock_wait_timeout= @@lock_wait_timeout; 697SET lock_wait_timeout= 1; 698--error ER_LOCK_WAIT_TIMEOUT 699LOCK TABLES t1 READ, t2 READ; 700SET @@lock_wait_timeout= @old_lock_wait_timeout; 701CONNECTION c1; 702COMMIT; 703INSERT INTO t1 VALUES (1); 704 705CONNECTION default; 706SET AUTOCOMMIT=default; 707DISCONNECT c1; 708DISCONNECT c2; 709DROP TABLE t1,t2; 710 711# 712# Bug #25798: a query with forced index merge returns wrong result 713# 714 715CREATE TABLE t1 ( 716 id int NOT NULL auto_increment PRIMARY KEY, 717 b int NOT NULL, 718 c datetime NOT NULL, 719 INDEX idx_b(b), 720 INDEX idx_c(c) 721) ENGINE=InnoDB; 722 723CREATE TABLE t2 ( 724 b int NOT NULL auto_increment PRIMARY KEY, 725 c datetime NOT NULL 726) ENGINE= MyISAM; 727 728INSERT INTO t2(c) VALUES ('2007-01-01'); 729INSERT INTO t2(c) SELECT c FROM t2; 730INSERT INTO t2(c) SELECT c FROM t2; 731INSERT INTO t2(c) SELECT c FROM t2; 732INSERT INTO t2(c) SELECT c FROM t2; 733INSERT INTO t2(c) SELECT c FROM t2; 734INSERT INTO t2(c) SELECT c FROM t2; 735INSERT INTO t2(c) SELECT c FROM t2; 736INSERT INTO t2(c) SELECT c FROM t2; 737INSERT INTO t2(c) SELECT c FROM t2; 738INSERT INTO t2(c) SELECT c FROM t2; 739 740INSERT INTO t1(b,c) SELECT b,c FROM t2; 741UPDATE t2 SET c='2007-01-02'; 742INSERT INTO t1(b,c) SELECT b,c FROM t2; 743UPDATE t2 SET c='2007-01-03'; 744INSERT INTO t1(b,c) SELECT b,c FROM t2; 745 746-- disable_query_log 747-- disable_result_log 748ANALYZE TABLE t1; 749ANALYZE TABLE t2; 750-- enable_result_log 751-- enable_query_log 752 753set @@sort_buffer_size=8192; 754 755SELECT COUNT(*) FROM t1; 756 757--replace_column 9 # 758EXPLAIN 759SELECT COUNT(*) FROM t1 760 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 761SELECT COUNT(*) FROM t1 762 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 763 764--replace_column 9 # 765EXPLAIN 766SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 767 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 768SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 769 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1; 770 771set @@sort_buffer_size=default; 772 773DROP TABLE t1,t2; 774 775# Test of behaviour with CREATE ... SELECT 776# 777 778CREATE TABLE t1 (a int, b int); 779insert into t1 values (1,1),(1,2); 780--error ER_DUP_ENTRY 781CREATE TABLE t2 (primary key (a)) select * from t1; 782# This should give warning 783drop table if exists t2; 784--error ER_DUP_ENTRY 785CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1; 786# This should give warning 787drop table if exists t2; 788CREATE TABLE t2 (a int, b int, primary key (a)); 789BEGIN; 790INSERT INTO t2 values(100,100); 791CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 792SELECT * from t2; 793ROLLBACK; 794SELECT * from t2; 795TRUNCATE table t2; 796--error ER_DUP_ENTRY 797INSERT INTO t2 select * from t1; 798SELECT * from t2; 799drop table t2; 800 801CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a)); 802BEGIN; 803INSERT INTO t2 values(100,100); 804CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 805SELECT * from t2; 806COMMIT; 807BEGIN; 808INSERT INTO t2 values(101,101); 809CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1; 810SELECT * from t2; 811ROLLBACK; 812SELECT * from t2; 813TRUNCATE table t2; 814--error ER_DUP_ENTRY 815INSERT INTO t2 select * from t1; 816SELECT * from t2; 817drop table t1,t2; 818 819# 820# Bug#17530: Incorrect key truncation on table creation caused server crash. 821# 822create table t1(f1 varchar(800) binary not null, key(f1)) 823 character set utf8 collate utf8_general_ci; 824insert into t1 values('aaa'); 825drop table t1; 826 827 828# 829# Bug#22781: SQL_BIG_RESULT fails to influence sort plan 830# 831CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB; 832 833INSERT INTO t1 VALUES ( 1 , 1 , 1); 834INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1; 835INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1; 836INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1; 837INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1; 838INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1; 839INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1; 840INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1; 841 842-- disable_query_log 843-- disable_result_log 844ANALYZE TABLE t1; 845-- enable_result_log 846-- enable_query_log 847 848EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b; 849EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b; 850DROP TABLE t1; 851 852--source include/innodb_rollback_on_timeout.inc 853 854# 855# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build 856# (possible deadlock). 857# 858# The bug is applicable only to a transactoinal table. 859# Cover with tests behavior that no longer causes an 860# assertion. 861# 862--disable_warnings 863drop table if exists t1; 864--enable_warnings 865create table t1 (a int) engine=innodb; 866alter table t1 alter a set default 1; 867drop table t1; 868 869--echo 870--echo Bug#24918 drop table and lock / inconsistent between 871--echo perm and temp tables 872--echo 873--echo Check transactional tables under LOCK TABLES 874--echo 875--disable_warnings 876drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, 877t24918_access; 878--enable_warnings 879create table t24918_access (id int); 880create table t24918 (id int) engine=myisam; 881create temporary table t24918_tmp (id int) engine=myisam; 882create table t24918_trans (id int) engine=innodb; 883create temporary table t24918_trans_tmp (id int) engine=innodb; 884 885lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write; 886drop table t24918; 887--error ER_TABLE_NOT_LOCKED 888select * from t24918_access; 889drop table t24918_trans; 890--error ER_TABLE_NOT_LOCKED 891select * from t24918_access; 892drop table t24918_trans_tmp; 893--error ER_TABLE_NOT_LOCKED 894select * from t24918_access; 895drop table t24918_tmp; 896--error ER_TABLE_NOT_LOCKED 897select * from t24918_access; 898unlock tables; 899 900drop table t24918_access; 901# 902# Bug #28591: MySQL need not sort the records in case of ORDER BY 903# primary_key on InnoDB table 904# 905 906CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB; 907INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2); 908INSERT INTO t1 SELECT a + 8, 2 FROM t1; 909INSERT INTO t1 SELECT a + 16, 1 FROM t1; 910-- disable_query_log 911-- disable_result_log 912ANALYZE TABLE t1; 913-- enable_result_log 914-- enable_query_log 915query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a; 916SELECT * FROM t1 WHERE b=2 ORDER BY a; 917query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; 918SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a; 919query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; 920SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a; 921 922CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c)) 923 ENGINE=InnoDB; 924INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1); 925INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2; 926INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2; 927 928-- disable_query_log 929-- disable_result_log 930ANALYZE TABLE t2; 931-- enable_result_log 932-- enable_query_log 933 934query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a; 935SELECT * FROM t2 WHERE b=1 ORDER BY a; 936query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; 937SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a; 938query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; 939SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a; 940query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; 941SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a; 942 943DROP TABLE t1,t2; 944 945 946# 947# Bug #29644: alter table hangs if records locked in share mode by long 948# running transaction 949# 950 951CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB; 952 953INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8); 954INSERT INTO t1 SELECT a + 8 FROM t1; 955INSERT INTO t1 SELECT a + 16 FROM t1; 956 957DELIMITER |; 958CREATE PROCEDURE p1 () 959BEGIN 960 DECLARE i INT DEFAULT 50; 961 DECLARE cnt INT; 962 # Continue even in the presence of ER_LOCK_DEADLOCK. 963 DECLARE CONTINUE HANDLER FOR 1213 BEGIN END; 964 START TRANSACTION; 965 ALTER TABLE t1 ENGINE=InnoDB; 966 COMMIT; 967 START TRANSACTION; 968 WHILE (i > 0) DO 969 SET i = i - 1; 970 SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE; 971 END WHILE; 972 COMMIT; 973END;| 974 975DELIMITER ;| 976 977CONNECT (con1,localhost,root,,); 978CONNECT (con2,localhost,root,,); 979 980CONNECTION con1; 981SEND CALL p1(); 982CONNECTION con2; 983SEND CALL p1(); 984CONNECTION default; 985CALL p1(); 986 987CONNECTION con1; 988REAP; 989CONNECTION con2; 990REAP; 991CONNECTION default; 992DISCONNECT con1; 993DISCONNECT con2; 994 995DROP PROCEDURE p1; 996DROP TABLE t1; 997 998# 999# Bug #28125: ERROR 2013 when adding index. 1000# 1001create table t1(a text) engine=innodb default charset=utf8; 1002insert into t1 values('aaa'); 1003set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1004alter table t1 add index(a(1024)); 1005show create table t1; 1006drop table t1; 1007 1008# 1009# Bug #28570: handler::index_read() is called with different find_flag when 1010# ORDER BY is used 1011# 1012 1013CREATE TABLE t1 ( 1014 a INT, 1015 b INT, 1016 KEY (b) 1017) ENGINE=InnoDB; 1018 1019INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30); 1020 1021START TRANSACTION; 1022SELECT * FROM t1 WHERE b=20 FOR UPDATE; 1023 1024--connect (conn2, localhost, root,,test) 1025 1026# This statement gives a "failed: 1205: Lock wait timeout exceeded; try 1027# restarting transaction" message when the bug is present. 1028START TRANSACTION; 1029SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE; 1030ROLLBACK; 1031 1032--disconnect conn2 1033--connection default 1034 1035ROLLBACK; 1036DROP TABLE t1; 1037 1038# 1039# Bug#30596: GROUP BY optimization gives wrong result order 1040# 1041CREATE TABLE t1( 1042 a INT, 1043 b INT NOT NULL, 1044 c INT NOT NULL, 1045 d INT, 1046 UNIQUE KEY (c,b) 1047) engine=innodb; 1048 1049INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4); 1050 1051-- disable_query_log 1052-- disable_result_log 1053ANALYZE TABLE t1; 1054-- enable_result_log 1055-- enable_query_log 1056 1057EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d; 1058SELECT c,b,d FROM t1 GROUP BY c,b,d; 1059EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1060SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL; 1061EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d; 1062SELECT c,b,d FROM t1 ORDER BY c,b,d; 1063 1064EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b; 1065SELECT c,b,d FROM t1 GROUP BY c,b; 1066EXPLAIN SELECT c,b FROM t1 GROUP BY c,b; 1067SELECT c,b FROM t1 GROUP BY c,b; 1068 1069DROP TABLE t1; 1070 1071# 1072# Bug #31001: ORDER BY DESC in InnoDB not working 1073# 1074CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB; 1075INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2); 1076 1077-- disable_query_log 1078-- disable_result_log 1079ANALYZE TABLE t1; 1080-- enable_result_log 1081-- enable_query_log 1082 1083#The two queries below should produce different results, but they don't. 1084query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; 1085SELECT * FROM t1 WHERE b=2 ORDER BY a ASC; 1086query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; 1087SELECT * FROM t1 WHERE b=2 ORDER BY a DESC; 1088 1089query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC; 1090SELECT * FROM t1 ORDER BY b ASC, a ASC; 1091query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC; 1092SELECT * FROM t1 ORDER BY b DESC, a DESC; 1093query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC; 1094SELECT * FROM t1 ORDER BY b ASC, a DESC; 1095query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC; 1096SELECT * FROM t1 ORDER BY b DESC, a ASC; 1097 1098DROP TABLE t1; 1099 1100########################################################################### 1101 1102--echo 1103--echo # 1104--echo # Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table. 1105--echo # 1106 1107--echo 1108--echo # - prepare; 1109--echo 1110 1111--disable_warnings 1112DROP TABLE IF EXISTS t1; 1113--enable_warnings 1114 1115--echo 1116 1117CREATE TABLE t1(c INT) 1118 ENGINE = InnoDB 1119 ROW_FORMAT = COMPACT; 1120 1121--echo 1122--echo # - initial check; 1123--echo 1124 1125SELECT table_schema, table_name, row_format 1126FROM INFORMATION_SCHEMA.TABLES 1127WHERE table_schema = DATABASE() AND table_name = 't1'; 1128 1129--echo 1130--echo # - change ROW_FORMAT and check; 1131--echo 1132 1133ALTER TABLE t1 ROW_FORMAT = REDUNDANT; 1134 1135--echo 1136 1137SELECT table_schema, table_name, row_format 1138FROM INFORMATION_SCHEMA.TABLES 1139WHERE table_schema = DATABASE() AND table_name = 't1'; 1140 1141--echo 1142--echo # - that's it, cleanup. 1143--echo 1144 1145DROP TABLE t1; 1146 1147########################################################################### 1148 1149# 1150# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0 1151# 1152create table t1(a char(10) not null, unique key aa(a(1)), 1153 b char(4) not null, unique key bb(b(4))) engine=innodb; 1154desc t1; 1155show create table t1; 1156drop table t1; 1157 1158# 1159# Bug #32815: query with ORDER BY and a possible ref_or_null access 1160# 1161 1162CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB; 1163INSERT INTO t1 VALUES 1164 (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2); 1165 1166-- disable_query_log 1167-- disable_result_log 1168ANALYZE TABLE t1; 1169-- enable_result_log 1170-- enable_query_log 1171 1172EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; 1173SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d; 1174 1175DROP TABLE t1; 1176 1177# 1178# Bug #34223: Assertion failed: (optp->var_type & 127) == 8, 1179# file .\my_getopt.c, line 830 1180# 1181 1182set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment; 1183set global innodb_autoextend_increment=8; 1184set global innodb_autoextend_increment=@my_innodb_autoextend_increment; 1185 1186set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency; 1187set global innodb_commit_concurrency=0; 1188set global innodb_commit_concurrency=@my_innodb_commit_concurrency; 1189 1190# 1191# Bug #37830: ORDER BY ASC/DESC - no difference 1192# 1193 1194CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b)) 1195 ENGINE=InnoDB; 1196 1197INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1); 1198INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1; 1199 1200-- disable_query_log 1201-- disable_result_log 1202ANALYZE TABLE t1; 1203-- enable_result_log 1204-- enable_query_log 1205 1206# should be range access 1207EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; 1208 1209# should produce '8 7 6 5 4' for a 1210SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5; 1211 1212DROP TABLE t1; 1213 1214# 1215# Bug#37284 Crash in Field_string::type() 1216# 1217--disable_warnings 1218DROP TABLE IF EXISTS t1; 1219--enable_warnings 1220CREATE TABLE t1 (a char(50)) ENGINE=InnoDB; 1221CREATE INDEX i1 on t1 (a(3)); 1222SELECT * FROM t1 WHERE a = 'abcde'; 1223DROP TABLE t1; 1224 1225 1226--echo # 1227--echo # BUG #26288: savepoint are not deleted on comit, if the transaction 1228--echo # was otherwise empty 1229--echo # 1230BEGIN; 1231SAVEPOINT s1; 1232COMMIT; 1233--error 1305 1234RELEASE SAVEPOINT s1; 1235 1236BEGIN; 1237SAVEPOINT s2; 1238COMMIT; 1239--error 1305 1240ROLLBACK TO SAVEPOINT s2; 1241 1242BEGIN; 1243SAVEPOINT s3; 1244ROLLBACK; 1245--error 1305 1246RELEASE SAVEPOINT s3; 1247 1248BEGIN; 1249SAVEPOINT s4; 1250ROLLBACK; 1251--error 1305 1252ROLLBACK TO SAVEPOINT s4; 1253 1254# 1255# Bug#39793 Foreign keys not constructed when column has a '#' in a comment or default value 1256# 1257 1258#This statement should be written on a single line for proper testing 1259CREATE 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; 1260SHOW CREATE TABLE t1; 1261DROP TABLE t1; 1262 1263--echo # 1264--echo # Bug #36995: valgrind error in remove_const during subquery executions 1265--echo # 1266 1267create table t1 (a bit(1) not null,b int) engine=myisam; 1268create table t2 (c int) engine=innodb; 1269set @save_optimizer_switch=@@optimizer_switch; 1270set @@optimizer_switch='in_to_exists=on,materialization=off'; 1271explain 1272select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a; 1273set optimizer_switch=@save_optimizer_switch; 1274DROP TABLE t1,t2; 1275 1276--echo End of 5.0 tests 1277 1278# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY 1279# UPDATE": if the row is updated, it's like a regular UPDATE: 1280# LAST_INSERT_ID() is not affected. 1281CREATE TABLE `t2` ( 1282 `k` int(11) NOT NULL auto_increment, 1283 `a` int(11) default NULL, 1284 `c` int(11) default NULL, 1285 PRIMARY KEY (`k`), 1286 UNIQUE KEY `idx_1` (`a`) 1287); 1288insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1289ifnull( c, 12900 ) + 1; 1291insert into t2 ( a ) values ( 7 ) on duplicate key update c = 1292ifnull( c, 12930 ) + 1; 1294select last_insert_id(); 1295select * from t2; 1296insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1297ifnull( c, 12980 ) + 1; 1299select last_insert_id(); 1300# test again when last_insert_id() is 0 initially 1301select last_insert_id(0); 1302insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1303ifnull( c, 13040 ) + 1; 1305select last_insert_id(); 1306select * from t2; 1307 1308# Test of LAST_INSERT_ID() when autogenerated will fail: 1309# last_insert_id() should not change 1310insert ignore into t2 values (null,6,1),(10,8,1); 1311select last_insert_id(); 1312# First and second autogenerated will fail, last_insert_id() should 1313# point to third 1314insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1); 1315select last_insert_id(); 1316select * from t2; 1317 1318# Test of the workaround which enables people to know the id of the 1319# updated row in INSERT ON DUPLICATE KEY UPDATE, by using 1320# LAST_INSERT_ID(autoinc_col) in the UPDATE clause. 1321 1322insert into t2 ( a ) values ( 6 ) on duplicate key update c = 1323ifnull( c, 13240 ) + 1, k=last_insert_id(k); 1325select last_insert_id(); 1326select * from t2; 1327 1328drop table t2; 1329 1330 1331# 1332# Tests for bug #28415 "Some ALTER TABLE statements no longer work 1333# under LOCK TABLES" and some aspects of fast ALTER TABLE behaviour 1334# for transactional tables. 1335# 1336--disable_warnings 1337drop table if exists t1, t2; 1338--enable_warnings 1339create table t1 (i int); 1340alter table t1 modify i int default 1; 1341alter table t1 modify i int default 2, rename t2; 1342lock table t2 write; 1343alter table t2 modify i int default 3; 1344unlock tables; 1345lock table t2 write; 1346alter table t2 modify i int default 4, rename t1; 1347unlock tables; 1348drop table t1; 1349 1350 1351# 1352# Some more tests for ALTER TABLE and LOCK TABLES for transactional tables. 1353# 1354# Table which is altered under LOCK TABLES should stay in list of locked 1355# tables and be available after alter takes place unless ALTER contains 1356# RENAME clause. We should see the new definition of table, of course. 1357# Before 5.1 this behavior was inconsistent across the platforms and 1358# different engines. See also tests in alter_table.test 1359# 1360--disable_warnings 1361drop table if exists t1; 1362--enable_warnings 1363create table t1 (i int); 1364insert into t1 values (); 1365lock table t1 write; 1366# Example of so-called 'fast' ALTER TABLE 1367alter table t1 modify i int default 1; 1368insert into t1 values (); 1369select * from t1; 1370# And now full-blown ALTER TABLE 1371alter table t1 change i c char(10) default "Two"; 1372insert into t1 values (); 1373select * from t1; 1374unlock tables; 1375select * from t1; 1376drop tables t1; 1377 1378# 1379# Bug#29310: An InnoDB table was updated when the data wasn't actually changed. 1380# 1381create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT 1382 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 1383insert into t1(f1) values(1); 1384--replace_column 1 # 1385select @a:=f2 from t1; 1386--sleep 5 1387update t1 set f1=1; 1388--replace_column 1 # 1389select @b:=f2 from t1; 1390select if(@a=@b,"ok","wrong"); 1391--sleep 5 1392insert into t1(f1) values (1) on duplicate key update f1="1"; 1393--replace_column 1 # 1394select @b:=f2 from t1; 1395select if(@a=@b,"ok","wrong"); 1396--sleep 5 1397insert into t1(f1) select f1 from t1 on duplicate key update f1="1"; 1398--replace_column 1 # 1399select @b:=f2 from t1; 1400select if(@a=@b,"ok","wrong"); 1401drop table t1; 1402 1403# 1404# Bug #31310: Locked rows silently skipped in read-committed isolation level. 1405# 1406 1407connect (con1,localhost,root,,); 1408connect (con2,localhost,root,,); 1409SET SESSION AUTOCOMMIT = 0; 1410SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 1411set binlog_format=mixed; 1412connection con1; 1413 1414eval 1415CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256)) 1416ENGINE = $engine_type; 1417INSERT INTO t1 VALUES (1,2); 1418 1419--echo # 1. test for locking: 1420 1421BEGIN; 1422--enable_info 1423UPDATE t1 SET b = 12 WHERE a = 1; 1424--disable_info 1425SELECT * FROM t1; 1426 1427connection con2; 1428 1429--enable_info 1430--disable_abort_on_error 1431--error ER_LOCK_WAIT_TIMEOUT 1432UPDATE t1 SET b = 21 WHERE a = 1; 1433--disable_info 1434 1435connection con1; 1436SELECT * FROM t1; 1437ROLLBACK; 1438 1439connection con2; 1440ROLLBACK; 1441 1442connection con1; 1443 1444--echo # 2. test for serialized update: 1445 1446CREATE TABLE t2 (a INT); 1447 1448TRUNCATE t1; 1449INSERT INTO t1 VALUES (1,'init'); 1450 1451DELIMITER |; 1452CREATE PROCEDURE p1() 1453BEGIN 1454 # retry the UPDATE in case it times out the lock before con1 has time 1455 # to COMMIT. 1456 DECLARE do_retry INT DEFAULT 0; 1457 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1; 1458 retry_loop:LOOP 1459 UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1; 1460 IF do_retry = 0 THEN 1461 LEAVE retry_loop; 1462 END IF; 1463 SET do_retry = 0; 1464 END LOOP; 1465 INSERT INTO t2 VALUES (); 1466END| 1467DELIMITER ;| 1468 1469BEGIN; 1470--enable_info 1471UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1; 1472--disable_info 1473SELECT * FROM t1; 1474 1475connection con2; 1476 1477--send CALL p1; 1478 1479connection con1; 1480SELECT * FROM t1; 1481COMMIT; 1482 1483let $bug31310 = 1; 1484while ($bug31310) 1485{ 1486 let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; 1487} 1488 1489SELECT * FROM t1; 1490 1491connection con2; 1492--reap 1493SELECT * FROM t1; 1494COMMIT; 1495 1496connection con1; 1497 1498--echo # 3. test for updated key column: 1499 1500TRUNCATE t1; 1501TRUNCATE t2; 1502 1503INSERT INTO t1 VALUES (1,'init'); 1504 1505BEGIN; 1506--enable_info 1507UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1; 1508--disable_info 1509SELECT * FROM t1; 1510 1511connection con2; 1512 1513--send CALL p1; 1514 1515connection con1; 1516SELECT * FROM t1; 1517COMMIT; 1518 1519let $bug31310 = 1; 1520while ($bug31310) 1521{ 1522 let $bug31310= `SELECT 1 - COUNT(*) FROM t2`; 1523} 1524 1525SELECT * FROM t1; 1526 1527connection con2; 1528--reap 1529SELECT * FROM t1; 1530 1531--enable_abort_on_error 1532connection default; 1533disconnect con1; 1534disconnect con2; 1535DROP PROCEDURE p1; 1536DROP TABLE t1, t2; 1537# Bug#30747 Create table with identical constraint names behaves incorrectly 1538# 1539 1540if ($test_foreign_keys) 1541{ 1542 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb; 1543 --error ER_WRONG_FK_DEF 1544 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1545 CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; 1546 --error ER_WRONG_FK_DEF 1547 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1548 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb; 1549 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1550 CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION, 1551 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; 1552 ALTER TABLE t2 DROP FOREIGN KEY c2; 1553 DROP TABLE t2; 1554 --error ER_WRONG_FK_DEF 1555 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1556 FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; 1557 --error ER_WRONG_FK_DEF 1558 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1559 FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb; 1560 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d), 1561 CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION, 1562 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION, 1563 FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION, 1564 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb; 1565 SHOW CREATE TABLE t2; 1566 DROP TABLE t2; 1567 DROP TABLE t1; 1568} 1569 1570# 1571# Bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB and 1572# auto_increment keys 1573# 1574create table t1 (a int auto_increment primary key) engine=innodb; 1575set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for 1576alter table t1 order by a; 1577drop table t1; 1578 1579# 1580# Bug #33697: ORDER BY primary key DESC vs. ref access + filesort 1581# (reproduced only with InnoDB tables) 1582# 1583 1584CREATE TABLE t1 1585 (vid integer NOT NULL, 1586 tid integer NOT NULL, 1587 idx integer NOT NULL, 1588 name varchar(128) NOT NULL, 1589 type varchar(128) NULL, 1590 PRIMARY KEY(idx, vid, tid), 1591 UNIQUE(vid, tid, name) 1592) ENGINE=InnoDB; 1593 1594INSERT INTO t1 VALUES 1595 (1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL), 1596 (5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL), 1597 (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL), 1598 (4,1,3,'pk',NULL),(5,1,3,'c2',NULL), 1599 (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL); 1600 1601-- disable_query_log 1602-- disable_result_log 1603ANALYZE TABLE t1; 1604-- enable_result_log 1605-- enable_query_log 1606 1607EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; 1608 1609SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC; 1610 1611DROP TABLE t1; 1612 1613--echo # 1614--echo # Bug #44290: explain crashes for subquery with distinct in 1615--echo # SQL_SELECT::test_quick_select 1616--echo # (reproduced only with InnoDB tables) 1617--echo # 1618 1619eval 1620CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3)) 1621 ENGINE=$engine_type; 1622INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1623 1624-- disable_query_log 1625-- disable_result_log 1626ANALYZE TABLE t1; 1627-- enable_result_log 1628-- enable_query_log 1629 1630SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1631 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1632EXPLAIN 1633SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1634 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1635 1636DROP TABLE t1; 1637 1638eval 1639CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3)) 1640 ENGINE=$engine_type; 1641INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1642 1643-- disable_query_log 1644-- disable_result_log 1645ANALYZE TABLE t1; 1646-- enable_result_log 1647-- enable_query_log 1648 1649SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1650 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1651EXPLAIN 1652SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1653 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1654 1655DROP TABLE t1; 1656 1657eval 1658CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2), 1659 KEY (c3), KEY (c2, c3)) 1660 ENGINE=$engine_type; 1661INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2); 1662 1663-- disable_query_log 1664-- disable_result_log 1665ANALYZE TABLE t1; 1666-- enable_result_log 1667-- enable_query_log 1668 1669SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1670 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1671EXPLAIN 1672SELECT 1 FROM (SELECT COUNT(DISTINCT c1) 1673 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x; 1674 1675DROP TABLE t1; 1676 1677--echo End of 5.1 tests 1678 1679--echo # 1680--echo # Bug#43600: Incorrect type conversion caused wrong result. 1681--echo # 1682CREATE TABLE t1 ( 1683 a int NOT NULL 1684) engine= innodb; 1685 1686CREATE TABLE t2 ( 1687 a int NOT NULL, 1688 b int NOT NULL, 1689 filler char(100) DEFAULT NULL, 1690 KEY a (a,b) 1691) engine= innodb; 1692 1693insert into t1 values (0),(1),(2),(3),(4); 1694insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B; 1695 1696-- disable_query_log 1697-- disable_result_log 1698analyze table t1; 1699analyze table t2; 1700-- enable_result_log 1701-- enable_query_log 1702 1703explain select * from t1, t2 where t2.a=t1.a and t2.b + 1; 1704select * from t1, t2 where t2.a=t1.a and t2.b + 1; 1705 1706drop table t1,t2; 1707--echo # End of test case for the bug#43600 1708 1709--echo # 1710--echo # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE 1711--echo # 1712--echo # Check that a TRUNCATE TABLE statement, needing an exclusive meta 1713--echo # data lock, waits for a shared metadata lock owned by a concurrent 1714--echo # transaction. 1715--echo # 1716 1717eval CREATE TABLE t1 (a INT) ENGINE=$engine_type; 1718INSERT INTO t1 VALUES (1),(2),(3); 1719BEGIN; 1720SELECT * FROM t1 ORDER BY a; 1721connect (con1, localhost, root,,); 1722--send TRUNCATE TABLE t1; 1723connection default; 1724let $wait_condition= SELECT COUNT(*)=1 FROM information_schema.processlist 1725 WHERE state='Waiting for table metadata lock' AND info='TRUNCATE TABLE t1'; 1726--source include/wait_condition.inc 1727SELECT * FROM t1 ORDER BY a; 1728ROLLBACK; 1729connection con1; 1730--echo # Reaping TRUNCATE TABLE 1731--reap 1732SELECT * FROM t1; 1733disconnect con1; 1734connection default; 1735DROP TABLE t1; 1736