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