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.
13
# Note: The comments/expectations refer to InnoDB.
14
# They might be not valid for other storage engines.
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.
28
eval SET SESSION STORAGE_ENGINE = $engine_type;
31
drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
35
# BUG#16798: Uninitialized row buffer reads in ref-or-null optimizer
36
# (repeatable only w/innodb).
38
c_id int(11) not null default '0',
39
org_id int(11) default null,
40
unique key contacts$c_id (c_id),
41
key contacts$org_id (org_id)
44
(2,null),(120,null),(141,null),(218,7), (128,1),
45
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
46
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
49
slai_id int(11) not null default '0',
50
owner_tbl int(11) default null,
51
owner_id int(11) default null,
52
sla_id int(11) default null,
53
inc_web int(11) default null,
54
inc_email int(11) default null,
55
inc_chat int(11) default null,
56
inc_csr int(11) default null,
57
inc_total int(11) default null,
58
time_billed int(11) default null,
59
activedate timestamp null default null,
60
expiredate timestamp null default null,
61
state int(11) default null,
62
sla_set int(11) default null,
63
unique key t2$slai_id (slai_id),
64
key t2$owner_id (owner_id),
65
key t2$sla_id (sla_id)
67
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
68
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
69
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
73
from t1 c join t2 si on
74
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
75
( si.owner_tbl = 2 and si.owner_id = c.c_id))
77
c.c_id = 218 and expiredate is null;
79
select * from t1 where org_id is null;
81
from t1 c join t2 si on
82
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
83
( si.owner_tbl = 2 and si.owner_id = c.c_id))
85
c.c_id = 218 and expiredate is null;
90
# Bug#17212: results not sorted correctly by ORDER BY when using index
91
# (repeatable only w/innodb because of index props)
93
CREATE TABLE t1 (a int, b int, KEY b (b));
94
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
95
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
96
UNIQUE KEY b (b,c), KEY a (a,b,c));
98
INSERT INTO t1 VALUES (1, 1);
99
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
100
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
102
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
103
INSERT INTO t2 SELECT a + 1, b FROM t2;
104
DELETE FROM t2 WHERE a = 1 AND b < 2;
106
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
107
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
108
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
110
# demonstrate a problem when a must-use-sort table flag
111
# (sort_by_table=1) is being neglected.
112
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
113
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
114
ORDER BY t1.b LIMIT 2;
116
# demonstrate the problem described in the bug report
117
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
118
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
119
ORDER BY t1.b LIMIT 5;
120
DROP TABLE t1, t2, t3;
123
# BUG#21077 (The testcase is not deterministic so correct execution doesn't
124
# prove anything) For proof one should track if sequence of ha_innodb::* func
126
CREATE TABLE `t1` (`id1` INT) ;
127
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
134
UNIQUE (`id2`,`id4`),
138
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
146
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
150
# Bug #13191: INSERT...ON DUPLICATE KEY UPDATE of UTF-8 string fields
151
# used in partial unique indices.
154
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
155
ENGINE=INNODB CHARACTER SET UTF8;
156
INSERT INTO t1 (c1) VALUES ('1a');
158
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
162
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
163
ENGINE=INNODB CHARACTER SET UTF8;
164
INSERT INTO t1 (c1) VALUES ('1a');
166
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
170
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
171
ENGINE=INNODB CHARACTER SET UTF8;
172
INSERT INTO t1 (c1) VALUES ('1a');
174
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
179
# Bug #28272: EXPLAIN for SELECT from an empty InnoDB table
183
a1 decimal(10,0) DEFAULT NULL,
185
a3 time DEFAULT NULL,
187
a5 char(175) DEFAULT NULL,
188
a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
190
INDEX idx (a6,a7(239),a5)
193
EXPLAIN SELECT a4 FROM t1 WHERE
195
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
197
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
198
t.a6=t.a6 AND t1.a6=NULL AND
199
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
204
# Bug #12882 min/max inconsistent on empty table
208
eval create table t1m (a int) engine = $other_engine_type;
209
create table t1i (a int);
210
eval create table t2m (a int) engine = $other_engine_type;
211
create table t2i (a int);
213
insert into t2m values (5);
214
insert into t2i values (5);
216
select 1, min(a) from t1i where a=99;
217
select 1, min(a) from t1i where 1=99;
218
select 1, min(1) from t1i where a=99;
219
select 1, min(1) from t1i where 1=99;
221
select 1, max(a) from t1i where a=99;
222
select 1, max(a) from t1i where 1=99;
223
select 1, max(1) from t1i where a=99;
224
select 1, max(1) from t1i where 1=99;
226
# mixed $engine_type/$other_engine_type test
227
explain select count(*), min(7), max(7) from t1m, t1i;
228
select count(*), min(7), max(7) from t1m, t1i;
230
explain select count(*), min(7), max(7) from t1m, t2i;
231
select count(*), min(7), max(7) from t1m, t2i;
233
explain select count(*), min(7), max(7) from t2m, t1i;
234
select count(*), min(7), max(7) from t2m, t1i;
236
drop table t1m, t1i, t2m, t2i;
239
# Bug #12882: primary key implcitly included in every innodb index
240
# (was part of group_min_max.test)
243
eval create table t1 (
244
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
245
) ENGINE = $other_engine_type;
247
insert into t1 (a1, a2, b, c, d) values
248
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
249
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
250
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
251
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
252
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
253
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
254
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
255
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
256
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
257
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
258
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
259
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
260
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
261
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
262
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
263
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
264
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
265
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
266
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
267
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
268
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
269
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
270
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
271
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
272
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
273
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
274
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
275
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
276
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
277
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
278
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
279
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
282
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 ' '
285
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
287
create index idx12672_0 on t4 (a1);
288
create index idx12672_1 on t4 (a1,a2,b,c);
289
create index idx12672_2 on t4 (a1,a2,b);
292
select distinct a1 from t4 where pk_col not in (1,2,3,4);
298
# BUG#18819: DELETE IGNORE hangs on foreign key parent delete
300
# The bug itself does not relate to InnoDB, but we have to use foreign
301
# keys to reproduce it.
304
DROP TABLE IF EXISTS t2, t1;
307
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
310
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
313
INSERT INTO t1 VALUES (1);
314
INSERT INTO t2 VALUES (1);
316
DELETE IGNORE FROM t1 WHERE i = 1;
318
SELECT * FROM t1, t2;
323
--echo End of 4.1 tests.
327
# Bug #6142: a problem with the empty innodb table
328
# (was part of group_min_max.test)
333
a varchar(30), b varchar(30), primary key(a), key(b)
336
select distinct a from t1;
340
# Bug #9798: group by with rollup
341
# (was part of group_min_max.test)
345
create table t1(a int, key(a));
347
insert into t1 values(1);
348
select a, count(a) from t1 group by a with rollup;
352
# Bug #13293 Wrongly used index results in endless loop.
353
# (was part of group_min_max.test)
355
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
356
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
357
alter table t1 drop primary key, add primary key (f2, f1);
358
explain select distinct f1 a, f1 b from t1;
359
explain select distinct f1, f2 from t1;
363
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
366
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
368
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
369
# CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11),
370
# FOREIGN KEY (fkey) REFERENCES t2(id));
371
if ($test_foreign_keys)
373
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
375
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
376
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
379
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
380
WHERE t1.name LIKE 'A%';
383
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
384
WHERE t1.name LIKE 'A%' OR FALSE;
389
# Bug#26159: crash for a loose scan of a table that has been emptied
394
name varchar(20) NOT NULL,
395
dept varchar(20) NOT NULL,
396
age tinyint(3) unsigned NOT NULL,
400
INSERT INTO t1(id, dept, age, name) VALUES
401
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
402
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
403
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
404
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
406
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
407
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
409
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
410
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
414
--source include/innodb_rollback_on_timeout.inc
417
# Bug #27210: INNODB ON DUPLICATE KEY UPDATE
420
connect (con1,localhost,root,,);
422
drop table if exists `test`;
423
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
424
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
425
ENGINE=InnoDB DEFAULT CHARSET=latin1;
426
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
428
connect (con2,localhost,root,,);
431
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
432
ON DUPLICATE KEY UPDATE `test2` = '1234';
440
--source include/innodb_rollback_on_timeout.inc
443
# Bug #27650: INSERT fails after multi-row INSERT of the form:
444
# INSERT INTO t (id...) VALUES (NULL...) ON DUPLICATE KEY UPDATE id=VALUES(id)
448
id int auto_increment,
450
counter int not null default 1,
455
insert into t1 (id, c) values
458
on duplicate key update id = values(id), counter = counter + 1;
462
insert into t1 (id, c) values
464
on duplicate key update id = values(id), counter = counter + 1;
470
insert into t1 (id, c) values (NULL, 'a');
474
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
475
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
479
insert into t1 (id, c) values (NULL, 'a')
480
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
487
# Bug #28189: optimizer erroniously prefers ref access to range access
488
# for an InnoDB table
492
id int AUTO_INCREMENT PRIMARY KEY,
493
stat_id int NOT NULL,
494
acct_id int DEFAULT NULL,
495
INDEX idx1 (stat_id, acct_id),
500
id int AUTO_INCREMENT PRIMARY KEY,
501
stat_id int NOT NULL,
502
acct_id int DEFAULT NULL,
503
INDEX idx1 (stat_id, acct_id),
507
INSERT INTO t1(stat_id,acct_id) VALUES
508
(1,759), (2,831), (3,785), (4,854), (1,921),
509
(1,553), (2,589), (3,743), (2,827), (2,545),
510
(4,779), (4,783), (1,597), (1,785), (4,832),
511
(1,741), (1,833), (3,788), (2,973), (1,907);
513
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
514
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
515
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
516
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
517
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
518
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
519
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
520
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
521
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
522
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
523
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
524
UPDATE t1 SET acct_id=785
525
WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
528
SELECT COUNT(*) FROM t1;
529
SELECT COUNT(*) FROM t1 WHERE acct_id=785;
531
EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
533
INSERT INTO t2 SELECT * FROM t1;
536
EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
541
# Bug #28652: assert when alter innodb table operation
543
create table t1(a int) engine=innodb;
544
alter table t1 comment '123';
545
show create table t1;
549
# Bug #25866: Getting "#HY000 Can't find record in..." on and INSERT
551
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
552
INSERT INTO t1 VALUES ('uk'),('bg');
553
SELECT * FROM t1 WHERE a = 'uk';
554
DELETE FROM t1 WHERE a = 'uk';
555
SELECT * FROM t1 WHERE a = 'uk';
556
UPDATE t1 SET a = 'us' WHERE a = 'uk';
557
SELECT * FROM t1 WHERE a = 'uk';
559
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
560
INSERT INTO t2 VALUES ('uk'),('bg');
561
SELECT * FROM t2 WHERE a = 'uk';
562
DELETE FROM t2 WHERE a = 'uk';
563
SELECT * FROM t2 WHERE a = 'uk';
564
INSERT INTO t2 VALUES ('uk');
565
UPDATE t2 SET a = 'us' WHERE a = 'uk';
566
SELECT * FROM t2 WHERE a = 'uk';
568
CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
569
INSERT INTO t3 VALUES ('uk'),('bg');
570
SELECT * FROM t3 WHERE a = 'uk';
571
DELETE FROM t3 WHERE a = 'uk';
572
SELECT * FROM t3 WHERE a = 'uk';
573
INSERT INTO t3 VALUES ('uk');
574
UPDATE t3 SET a = 'us' WHERE a = 'uk';
575
SELECT * FROM t3 WHERE a = 'uk';
580
# Test bug when trying to drop data file which no InnoDB directory entry
583
create table t1 (a int) engine=innodb;
584
copy_file $MYSQLTEST_VARDIR/master-data/test/t1.frm $MYSQLTEST_VARDIR/master-data/test/bug29807.frm;
586
select * from bug29807;
590
create table bug29807 (a int);
595
# Bug #29154: LOCK TABLES is not atomic when >1 InnoDB tables are locked
598
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
599
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
601
CONNECT (c1,localhost,root,,);
602
CONNECT (c2,localhost,root,,);
604
--echo switch to connection c1
607
INSERT INTO t2 VALUES (1);
609
--echo switch to connection c2
612
--error ER_LOCK_WAIT_TIMEOUT
613
LOCK TABLES t1 READ, t2 READ;
615
--echo switch to connection c1
618
INSERT INTO t1 VALUES (1);
620
--echo switch to connection default
622
SET AUTOCOMMIT=default;
628
# Bug #25798: a query with forced index merge returns wrong result
632
id int NOT NULL auto_increment PRIMARY KEY,
640
b int NOT NULL auto_increment PRIMARY KEY,
644
INSERT INTO t2(c) VALUES ('2007-01-01');
645
INSERT INTO t2(c) SELECT c FROM t2;
646
INSERT INTO t2(c) SELECT c FROM t2;
647
INSERT INTO t2(c) SELECT c FROM t2;
648
INSERT INTO t2(c) SELECT c FROM t2;
649
INSERT INTO t2(c) SELECT c FROM t2;
650
INSERT INTO t2(c) SELECT c FROM t2;
651
INSERT INTO t2(c) SELECT c FROM t2;
652
INSERT INTO t2(c) SELECT c FROM t2;
653
INSERT INTO t2(c) SELECT c FROM t2;
654
INSERT INTO t2(c) SELECT c FROM t2;
656
INSERT INTO t1(b,c) SELECT b,c FROM t2;
657
UPDATE t2 SET c='2007-01-02';
658
INSERT INTO t1(b,c) SELECT b,c FROM t2;
659
UPDATE t2 SET c='2007-01-03';
660
INSERT INTO t1(b,c) SELECT b,c FROM t2;
662
set @@sort_buffer_size=8192;
664
SELECT COUNT(*) FROM t1;
668
SELECT COUNT(*) FROM t1
669
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
670
SELECT COUNT(*) FROM t1
671
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
675
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
676
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
677
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
678
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
680
set @@sort_buffer_size=default;
684
# Test of behaviour with CREATE ... SELECT
687
CREATE TABLE t1 (a int, b int);
688
insert into t1 values (1,1),(1,2);
690
CREATE TABLE t2 (primary key (a)) select * from t1;
691
# This should give warning
692
drop table if exists t2;
694
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
695
# This should give warning
696
drop table if exists t2;
697
CREATE TABLE t2 (a int, b int, primary key (a));
699
INSERT INTO t2 values(100,100);
701
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
707
INSERT INTO t2 select * from t1;
711
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
713
INSERT INTO t2 values(100,100);
715
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
719
INSERT INTO t2 values(101,101);
721
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
727
INSERT INTO t2 select * from t1;
732
# Bug#17530: Incorrect key truncation on table creation caused server crash.
734
create table t1(f1 varchar(800) binary not null, key(f1))
735
character set utf8 collate utf8_general_ci;
736
insert into t1 values('aaa');
741
# Bug#22781: SQL_BIG_RESULT fails to influence sort plan
743
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
745
INSERT INTO t1 VALUES ( 1 , 1 , 1);
746
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
747
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
748
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
749
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
750
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
751
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
752
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
754
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
755
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
758
--source include/innodb_rollback_on_timeout.inc
761
# Bug#27296 Assertion in ALTER TABLE SET DEFAULT in Linux Debug build
762
# (possible deadlock).
764
# The bug is applicable only to a transactoinal table.
765
# Cover with tests behavior that no longer causes an
769
drop table if exists t1;
771
create table t1 (a int) engine=innodb;
772
alter table t1 alter a set default 1;
776
--echo Bug#24918 drop table and lock / inconsistent between
777
--echo perm and temp tables
779
--echo Check transactional tables under LOCK TABLES
782
drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
785
create table t24918_access (id int);
786
create table t24918 (id int) engine=myisam;
787
create temporary table t24918_tmp (id int) engine=myisam;
788
create table t24918_trans (id int) engine=innodb;
789
create temporary table t24918_trans_tmp (id int) engine=innodb;
791
lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
793
--error ER_TABLE_NOT_LOCKED
794
select * from t24918_access;
795
drop table t24918_trans;
796
--error ER_TABLE_NOT_LOCKED
797
select * from t24918_access;
798
drop table t24918_trans_tmp;
799
--error ER_TABLE_NOT_LOCKED
800
select * from t24918_access;
801
drop table t24918_tmp;
802
--error ER_TABLE_NOT_LOCKED
803
select * from t24918_access;
806
drop table t24918_access;
808
# Bug #28591: MySQL need not sort the records in case of ORDER BY
809
# primary_key on InnoDB table
812
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
813
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
814
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
815
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
816
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
817
SELECT * FROM t1 WHERE b=2 ORDER BY a;
818
query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
819
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
820
query_vertical EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
821
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
823
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
825
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
826
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
827
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
829
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
830
SELECT * FROM t2 WHERE b=1 ORDER BY a;
831
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
832
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
833
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
834
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
835
query_vertical EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
836
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
842
# Bug #28125: ERROR 2013 when adding index.
844
create table t1(a text) engine=innodb default charset=utf8;
845
insert into t1 values('aaa');
846
alter table t1 add index(a(1024));
847
show create table t1;
851
# Bug #28570: handler::index_read() is called with different find_flag when
861
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
864
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
866
--connect (conn2, localhost, root,,test)
868
# This statement gives a "failed: 1205: Lock wait timeout exceeded; try
869
# restarting transaction" message when the bug is present.
871
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
881
# Bug#30596: GROUP BY optimization gives wrong result order
891
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
893
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
894
SELECT c,b,d FROM t1 GROUP BY c,b,d;
895
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
896
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
897
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
898
SELECT c,b,d FROM t1 ORDER BY c,b,d;
900
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
901
SELECT c,b,d FROM t1 GROUP BY c,b;
902
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
903
SELECT c,b FROM t1 GROUP BY c,b;
908
# Bug #31001: ORDER BY DESC in InnoDB not working
910
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
911
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
913
#The two queries below should produce different results, but they don't.
914
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
915
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
916
query_vertical EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
917
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
919
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
920
SELECT * FROM t1 ORDER BY b ASC, a ASC;
921
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
922
SELECT * FROM t1 ORDER BY b DESC, a DESC;
923
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
924
SELECT * FROM t1 ORDER BY b ASC, a DESC;
925
query_vertical EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
926
SELECT * FROM t1 ORDER BY b DESC, a ASC;
930
###########################################################################
934
--echo # Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
942
DROP TABLE IF EXISTS t1;
947
CREATE TABLE t1(c INT)
949
ROW_FORMAT = COMPACT;
952
--echo # - initial check;
955
SELECT table_schema, table_name, row_format
956
FROM INFORMATION_SCHEMA.TABLES
957
WHERE table_schema = DATABASE() AND table_name = 't1';
960
--echo # - change ROW_FORMAT and check;
963
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
967
SELECT table_schema, table_name, row_format
968
FROM INFORMATION_SCHEMA.TABLES
969
WHERE table_schema = DATABASE() AND table_name = 't1';
972
--echo # - that's it, cleanup.
977
###########################################################################
980
# Bug #31137: Assertion failed: primary_key_no == -1 || primary_key_no == 0
982
create table t1(a char(10) not null, unique key aa(a(1)),
983
b char(4) not null, unique key bb(b(4))) engine=innodb;
985
show create table t1;
989
# Bug #32815: query with ORDER BY and a possible ref_or_null access
992
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
993
INSERT INTO t1 VALUES
994
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
996
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
997
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1002
# Bug #34223: Assertion failed: (optp->var_type & 127) == 8,
1003
# file .\my_getopt.c, line 830
1006
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1007
set global innodb_autoextend_increment=8;
1008
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1010
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1011
set global innodb_commit_concurrency=0;
1012
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1014
--echo End of 5.0 tests
1016
# Fix for BUG#19243 "wrong LAST_INSERT_ID() after ON DUPLICATE KEY
1017
# UPDATE": if the row is updated, it's like a regular UPDATE:
1018
# LAST_INSERT_ID() is not affected.
1020
`k` int(11) NOT NULL auto_increment,
1021
`a` int(11) default NULL,
1022
`c` int(11) default NULL,
1024
UNIQUE KEY `idx_1` (`a`)
1026
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1029
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1032
select last_insert_id();
1034
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1037
select last_insert_id();
1038
# test again when last_insert_id() is 0 initially
1039
select last_insert_id(0);
1040
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1043
select last_insert_id();
1046
# Test of LAST_INSERT_ID() when autogenerated will fail:
1047
# last_insert_id() should not change
1048
insert ignore into t2 values (null,6,1),(10,8,1);
1049
select last_insert_id();
1050
# First and second autogenerated will fail, last_insert_id() should
1052
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1053
select last_insert_id();
1056
# Test of the workaround which enables people to know the id of the
1057
# updated row in INSERT ON DUPLICATE KEY UPDATE, by using
1058
# LAST_INSERT_ID(autoinc_col) in the UPDATE clause.
1060
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1062
0 ) + 1, k=last_insert_id(k);
1063
select last_insert_id();
1070
# Tests for bug #28415 "Some ALTER TABLE statements no longer work
1071
# under LOCK TABLES" and some aspects of fast ALTER TABLE behaviour
1072
# for transactional tables.
1075
drop table if exists t1, t2;
1077
create table t1 (i int);
1078
alter table t1 modify i int default 1;
1079
alter table t1 modify i int default 2, rename t2;
1080
lock table t2 write;
1081
alter table t2 modify i int default 3;
1083
lock table t2 write;
1084
alter table t2 modify i int default 4, rename t1;
1090
# Some more tests for ALTER TABLE and LOCK TABLES for transactional tables.
1092
# Table which is altered under LOCK TABLES should stay in list of locked
1093
# tables and be available after alter takes place unless ALTER contains
1094
# RENAME clause. We should see the new definition of table, of course.
1095
# Before 5.1 this behavior was inconsistent across the platforms and
1096
# different engines. See also tests in alter_table.test
1099
drop table if exists t1;
1101
create table t1 (i int);
1102
insert into t1 values ();
1103
lock table t1 write;
1104
# Example of so-called 'fast' ALTER TABLE
1105
alter table t1 modify i int default 1;
1106
insert into t1 values ();
1108
# And now full-blown ALTER TABLE
1109
alter table t1 change i c char(10) default "Two";
1110
insert into t1 values ();
1117
# Bug#29310: An InnoDB table was updated when the data wasn't actually changed.
1119
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1120
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1121
insert into t1(f1) values(1);
1122
--replace_column 1 #
1123
select @a:=f2 from t1;
1126
--replace_column 1 #
1127
select @b:=f2 from t1;
1128
select if(@a=@b,"ok","wrong");
1130
insert into t1(f1) values (1) on duplicate key update f1="1";
1131
--replace_column 1 #
1132
select @b:=f2 from t1;
1133
select if(@a=@b,"ok","wrong");
1135
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1136
--replace_column 1 #
1137
select @b:=f2 from t1;
1138
select if(@a=@b,"ok","wrong");
1141
# Bug#30747 Create table with identical constraint names behaves incorrectly
1144
if ($test_foreign_keys)
1146
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1147
--error ER_WRONG_FK_DEF
1148
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1149
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1150
--error ER_WRONG_FK_DEF
1151
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1152
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1153
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1154
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1155
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1156
ALTER TABLE t2 DROP FOREIGN KEY c2;
1158
--error ER_WRONG_FK_DEF
1159
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1160
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1161
--error ER_WRONG_FK_DEF
1162
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1163
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1164
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1165
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1166
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1167
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1168
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1169
SHOW CREATE TABLE t2;
1175
# Bug #26447: "ALTER TABLE .. ORDER" does not work with InnoDB and
1176
# auto_increment keys
1178
create table t1 (a int auto_increment primary key) engine=innodb;
1179
alter table t1 order by a;
1183
# Bug #33697: ORDER BY primary key DESC vs. ref access + filesort
1184
# (reproduced only with InnoDB tables)
1188
(vid integer NOT NULL,
1189
tid integer NOT NULL,
1190
idx integer NOT NULL,
1191
name varchar(128) NOT NULL,
1192
type varchar(128) NULL,
1193
PRIMARY KEY(idx, vid, tid),
1194
UNIQUE(vid, tid, name)
1197
INSERT INTO t1 VALUES
1198
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1199
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1200
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1201
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1202
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1204
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1206
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1211
# Bug#21704: Renaming column does not update FK definition.
1215
DROP TABLE IF EXISTS t1;
1216
DROP TABLE IF EXISTS t2;
1219
CREATE TABLE t1(id INT PRIMARY KEY)
1223
t1_id INT PRIMARY KEY,
1224
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
1229
--disable_result_log
1230
--error ER_ERROR_ON_RENAME
1231
ALTER TABLE t1 CHANGE id id2 INT;
1239
--echo End of 5.1 tests
added
removed
mysql-test/include/mix1.inc
