release/src/router/mysql/mysql-test/include/mix1.inc

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