1
set global innodb_support_xa=default;
2
set session innodb_support_xa=default;
3
SET SESSION STORAGE_ENGINE = InnoDB;
4
drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
5
drop procedure if exists p1;
7
c_id int(11) not null default '0',
8
org_id int(11) default null,
9
unique key contacts$c_id (c_id),
10
key contacts$org_id (org_id)
13
(2,null),(120,null),(141,null),(218,7), (128,1),
14
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
15
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
17
slai_id int(11) not null default '0',
18
owner_tbl int(11) default null,
19
owner_id int(11) default null,
20
sla_id int(11) default null,
21
inc_web int(11) default null,
22
inc_email int(11) default null,
23
inc_chat int(11) default null,
24
inc_csr int(11) default null,
25
inc_total int(11) default null,
26
time_billed int(11) default null,
27
activedate timestamp null default null,
28
expiredate timestamp null default null,
29
state int(11) default null,
30
sla_set int(11) default null,
31
unique key t2$slai_id (slai_id),
32
key t2$owner_id (owner_id),
33
key t2$sla_id (sla_id)
35
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
36
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
37
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
40
from t1 c join t2 si on
41
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
42
( si.owner_tbl = 2 and si.owner_id = c.c_id))
44
c.c_id = 218 and expiredate is null;
47
select * from t1 where org_id is null;
53
from t1 c join t2 si on
54
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
55
( si.owner_tbl = 2 and si.owner_id = c.c_id))
57
c.c_id = 218 and expiredate is null;
61
CREATE TABLE t1 (a int, b int, KEY b (b));
62
CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
63
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
64
UNIQUE KEY b (b,c), KEY a (a,b,c));
65
INSERT INTO t1 VALUES (1, 1);
66
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
67
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
68
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
69
INSERT INTO t2 SELECT a + 1, b FROM t2;
70
DELETE FROM t2 WHERE a = 1 AND b < 2;
71
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
72
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
73
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
74
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
75
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
76
ORDER BY t1.b LIMIT 2;
80
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
81
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
82
ORDER BY t1.b LIMIT 5;
89
DROP TABLE t1, t2, t3;
90
CREATE TABLE `t1` (`id1` INT) ;
91
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
100
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
107
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
111
create table t1 (c1 int) engine=innodb;
113
handler t1 read first;
115
Before and after comparison
118
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
119
ENGINE=INNODB CHARACTER SET UTF8;
120
INSERT INTO t1 (c1) VALUES ('1a');
124
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
129
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
130
ENGINE=INNODB CHARACTER SET UTF8;
131
INSERT INTO t1 (c1) VALUES ('1a');
135
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
140
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
141
ENGINE=INNODB CHARACTER SET UTF8;
142
INSERT INTO t1 (c1) VALUES ('1a');
146
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
152
a1 decimal(10,0) DEFAULT NULL,
154
a3 time DEFAULT NULL,
156
a5 char(175) DEFAULT NULL,
157
a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
159
INDEX idx (a6,a7(239),a5)
161
EXPLAIN SELECT a4 FROM t1 WHERE
163
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
164
id select_type table type possible_keys key key_len ref rows Extra
165
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
166
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
167
t.a6=t.a6 AND t1.a6=NULL AND
168
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
169
id select_type table type possible_keys key key_len ref rows Extra
170
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
172
create table t1m (a int) engine = MEMORY;
173
create table t1i (a int);
174
create table t2m (a int) engine = MEMORY;
175
create table t2i (a int);
176
insert into t2m values (5);
177
insert into t2i values (5);
178
select min(a) from t1i;
181
select min(7) from t1i;
184
select min(7) from DUAL;
187
explain select min(7) from t2i join t1i;
188
id select_type table type possible_keys key key_len ref rows Extra
189
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
190
1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
191
select min(7) from t2i join t1i;
194
select max(a) from t1i;
197
select max(7) from t1i;
200
select max(7) from DUAL;
203
explain select max(7) from t2i join t1i;
204
id select_type table type possible_keys key key_len ref rows Extra
205
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
206
1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer
207
select max(7) from t2i join t1i;
210
select 1, min(a) from t1i where a=99;
213
select 1, min(a) from t1i where 1=99;
216
select 1, min(1) from t1i where a=99;
219
select 1, min(1) from t1i where 1=99;
222
select 1, max(a) from t1i where a=99;
225
select 1, max(a) from t1i where 1=99;
228
select 1, max(1) from t1i where a=99;
231
select 1, max(1) from t1i where 1=99;
234
explain select count(*), min(7), max(7) from t1m, t1i;
235
id select_type table type possible_keys key key_len ref rows Extra
236
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
237
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
238
select count(*), min(7), max(7) from t1m, t1i;
239
count(*) min(7) max(7)
241
explain select count(*), min(7), max(7) from t1m, t2i;
242
id select_type table type possible_keys key key_len ref rows Extra
243
1 SIMPLE t1m system NULL NULL NULL NULL 0 const row not found
244
1 SIMPLE t2i ALL NULL NULL NULL NULL 1
245
select count(*), min(7), max(7) from t1m, t2i;
246
count(*) min(7) max(7)
248
explain select count(*), min(7), max(7) from t2m, t1i;
249
id select_type table type possible_keys key key_len ref rows Extra
250
1 SIMPLE t2m system NULL NULL NULL NULL 1
251
1 SIMPLE t1i ALL NULL NULL NULL NULL 1
252
select count(*), min(7), max(7) from t2m, t1i;
253
count(*) min(7) max(7)
255
drop table t1m, t1i, t2m, t2i;
257
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
259
insert into t1 (a1, a2, b, c, d) values
260
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
261
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
262
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
263
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
264
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
265
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
266
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
267
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
268
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
269
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
270
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
271
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
272
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
273
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
274
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
275
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
276
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
277
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
278
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
279
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
280
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
281
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
282
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
283
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
284
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
285
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
286
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
287
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
288
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
289
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
290
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
291
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
293
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 ' '
295
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
296
create index idx12672_0 on t4 (a1);
297
create index idx12672_1 on t4 (a1,a2,b,c);
298
create index idx12672_2 on t4 (a1,a2,b);
300
Table Op Msg_type Msg_text
301
test.t4 analyze status OK
302
select distinct a1 from t4 where pk_col not in (1,2,3,4);
309
DROP TABLE IF EXISTS t2, t1;
310
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
313
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
315
INSERT INTO t1 VALUES (1);
316
INSERT INTO t2 VALUES (1);
317
DELETE IGNORE FROM t1 WHERE i = 1;
319
Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)
320
SELECT * FROM t1, t2;
326
a varchar(30), b varchar(30), primary key(a), key(b)
328
select distinct a from t1;
331
create table t1(a int, key(a));
332
insert into t1 values(1);
333
select a, count(a) from t1 group by a with rollup;
338
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
339
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
340
alter table t1 drop primary key, add primary key (f2, f1);
341
explain select distinct f1 a, f1 b from t1;
342
id select_type table type possible_keys key key_len ref rows Extra
343
1 SIMPLE t1 index NULL PRIMARY 5 NULL 4 Using index; Using temporary
344
explain select distinct f1, f2 from t1;
345
id select_type table type possible_keys key key_len ref rows Extra
346
1 SIMPLE t1 range NULL PRIMARY 5 NULL 3 Using index for group-by; Using temporary
348
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
350
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
351
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
352
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
353
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
355
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
356
WHERE t1.name LIKE 'A%';
357
id select_type table type possible_keys key key_len ref rows Extra
358
1 SIMPLE t1 index PRIMARY,name name 23 NULL 3 Using where; Using index
359
1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using where; Using index
361
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
362
WHERE t1.name LIKE 'A%' OR FALSE;
363
id select_type table type possible_keys key key_len ref rows Extra
364
1 SIMPLE t2 index NULL fkey 5 NULL 5 Using index
365
1 SIMPLE t1 eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 Using where
369
name varchar(20) NOT NULL,
370
dept varchar(20) NOT NULL,
371
age tinyint(3) unsigned NOT NULL,
375
INSERT INTO t1(id, dept, age, name) VALUES
376
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
377
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
378
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
379
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
380
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
381
id select_type table type possible_keys key key_len ref rows Extra
382
1 SIMPLE t1 range name name 44 NULL 2 Using where; Using index for group-by
383
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
388
# Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
389
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
390
id select_type table type possible_keys key key_len ref rows Extra
391
1 SIMPLE t1 range name name 44 NULL # Using where; Using index for group-by
392
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
395
drop table if exists t1;
396
show variables like 'innodb_rollback_on_timeout';
398
innodb_rollback_on_timeout OFF
399
create table t1 (a int unsigned not null primary key) engine = innodb;
400
insert into t1 values (1);
403
insert into t1 values (2);
409
insert into t1 values (5);
414
insert into t1 values (2);
415
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
432
set @save_qcache_size=@@global.query_cache_size;
433
set @save_qcache_type=@@global.query_cache_type;
434
set global query_cache_size=10*1024*1024;
435
set global query_cache_type=1;
436
drop table if exists `test`;
438
Note 1051 Unknown table 'test'
439
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
440
`test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
441
ENGINE=InnoDB DEFAULT CHARSET=latin1;
442
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
446
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
447
ON DUPLICATE KEY UPDATE `test2` = '1234';
456
set global query_cache_type=@save_qcache_type;
457
set global query_cache_size=@save_qcache_size;
458
drop table if exists t1;
459
show variables like 'innodb_rollback_on_timeout';
461
innodb_rollback_on_timeout OFF
462
create table t1 (a int unsigned not null primary key) engine = innodb;
463
insert into t1 values (1);
466
insert into t1 values (2);
472
insert into t1 values (5);
477
insert into t1 values (2);
478
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
496
id int auto_increment,
498
counter int not null default 1,
502
insert into t1 (id, c) values
505
on duplicate key update id = values(id), counter = counter + 1;
509
insert into t1 (id, c) values
511
on duplicate key update id = values(id), counter = counter + 1;
517
insert into t1 (id, c) values (NULL, 'a');
521
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
522
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
527
insert into t1 (id, c) values (NULL, 'a')
528
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
535
id int AUTO_INCREMENT PRIMARY KEY,
536
stat_id int NOT NULL,
537
acct_id int DEFAULT NULL,
538
INDEX idx1 (stat_id, acct_id),
542
id int AUTO_INCREMENT PRIMARY KEY,
543
stat_id int NOT NULL,
544
acct_id int DEFAULT NULL,
545
INDEX idx1 (stat_id, acct_id),
548
INSERT INTO t1(stat_id,acct_id) VALUES
549
(1,759), (2,831), (3,785), (4,854), (1,921),
550
(1,553), (2,589), (3,743), (2,827), (2,545),
551
(4,779), (4,783), (1,597), (1,785), (4,832),
552
(1,741), (1,833), (3,788), (2,973), (1,907);
553
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
554
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
555
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
556
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
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
UPDATE t1 SET acct_id=785
565
WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
567
Table Op Msg_type Msg_text
568
test.t1 optimize status OK
569
SELECT COUNT(*) FROM t1;
572
SELECT COUNT(*) FROM t1 WHERE acct_id=785;
575
EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
576
id select_type table type possible_keys key key_len ref rows Extra
577
1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
578
INSERT INTO t2 SELECT * FROM t1;
580
Table Op Msg_type Msg_text
581
test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
582
test.t2 optimize status OK
583
EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
584
id select_type table type possible_keys key key_len ref rows Extra
585
1 SIMPLE t2 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
587
create table t1(a int) engine=innodb;
588
alter table t1 comment '123';
589
show create table t1;
591
t1 CREATE TABLE `t1` (
592
`a` int(11) DEFAULT NULL
593
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123'
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';
600
DELETE FROM t1 WHERE a = 'uk';
601
SELECT * FROM t1 WHERE a = 'uk';
603
UPDATE t1 SET a = 'us' WHERE a = 'uk';
604
SELECT * FROM t1 WHERE a = 'uk';
606
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
607
INSERT INTO t2 VALUES ('uk'),('bg');
608
SELECT * FROM t2 WHERE a = 'uk';
611
DELETE FROM t2 WHERE a = 'uk';
612
SELECT * FROM t2 WHERE a = 'uk';
614
INSERT INTO t2 VALUES ('uk');
615
UPDATE t2 SET a = 'us' WHERE a = 'uk';
616
SELECT * FROM t2 WHERE a = 'uk';
618
CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
619
INSERT INTO t3 VALUES ('uk'),('bg');
620
SELECT * FROM t3 WHERE a = 'uk';
623
DELETE FROM t3 WHERE a = 'uk';
624
SELECT * FROM t3 WHERE a = 'uk';
626
INSERT INTO t3 VALUES ('uk');
627
UPDATE t3 SET a = 'us' WHERE a = 'uk';
628
SELECT * FROM t3 WHERE a = 'uk';
631
create table t1 (a int) engine=innodb;
632
select * from bug29807;
633
ERROR 42S02: Table 'test.bug29807' doesn't exist
636
ERROR 42S02: Unknown table 'bug29807'
637
create table bug29807 (a int);
639
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
640
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
641
switch to connection c1
643
INSERT INTO t2 VALUES (1);
644
switch to connection c2
646
LOCK TABLES t1 READ, t2 READ;
647
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
648
switch to connection c1
650
INSERT INTO t1 VALUES (1);
651
switch to connection default
652
SET AUTOCOMMIT=default;
655
id int NOT NULL auto_increment PRIMARY KEY,
662
b int NOT NULL auto_increment PRIMARY KEY,
665
INSERT INTO t2(c) VALUES ('2007-01-01');
666
INSERT INTO t2(c) SELECT c FROM t2;
667
INSERT INTO t2(c) SELECT c FROM t2;
668
INSERT INTO t2(c) SELECT c FROM t2;
669
INSERT INTO t2(c) SELECT c FROM t2;
670
INSERT INTO t2(c) SELECT c FROM t2;
671
INSERT INTO t2(c) SELECT c FROM t2;
672
INSERT INTO t2(c) SELECT c FROM t2;
673
INSERT INTO t2(c) SELECT c FROM t2;
674
INSERT INTO t2(c) SELECT c FROM t2;
675
INSERT INTO t2(c) SELECT c FROM t2;
676
INSERT INTO t1(b,c) SELECT b,c FROM t2;
677
UPDATE t2 SET c='2007-01-02';
678
INSERT INTO t1(b,c) SELECT b,c FROM t2;
679
UPDATE t2 SET c='2007-01-03';
680
INSERT INTO t1(b,c) SELECT b,c FROM t2;
681
set @@sort_buffer_size=8192;
683
Warning 1292 Truncated incorrect sort_buffer_size value: '8192'
684
SELECT COUNT(*) FROM t1;
688
SELECT COUNT(*) FROM t1
689
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
690
id select_type table type possible_keys key key_len ref rows Extra
691
1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
692
SELECT COUNT(*) FROM t1
693
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
697
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
698
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
699
id select_type table type possible_keys key key_len ref rows Extra
700
1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 8,4 NULL # Using sort_union(idx_c,idx_b); Using where
701
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
702
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
705
set @@sort_buffer_size=default;
707
CREATE TABLE t1 (a int, b int);
708
insert into t1 values (1,1),(1,2);
709
CREATE TABLE t2 (primary key (a)) select * from t1;
710
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
711
drop table if exists t2;
713
Note 1051 Unknown table 't2'
714
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
715
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
716
drop table if exists t2;
718
Note 1051 Unknown table 't2'
719
CREATE TABLE t2 (a int, b int, primary key (a));
721
INSERT INTO t2 values(100,100);
722
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
723
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
732
INSERT INTO t2 select * from t1;
733
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
737
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
739
INSERT INTO t2 values(100,100);
740
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
741
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
747
INSERT INTO t2 values(101,101);
748
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
749
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
759
INSERT INTO t2 select * from t1;
760
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
764
create table t1(f1 varchar(800) binary not null, key(f1))
765
character set utf8 collate utf8_general_ci;
767
Warning 1071 Specified key was too long; max key length is 767 bytes
768
insert into t1 values('aaa');
770
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
771
INSERT INTO t1 VALUES ( 1 , 1 , 1);
772
INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
773
INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
774
INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
775
INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
776
INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
777
INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
778
INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
779
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
780
id select_type table type possible_keys key key_len ref rows Extra
781
1 SIMPLE t1 index NULL b 5 NULL 128
782
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
783
id select_type table type possible_keys key key_len ref rows Extra
784
1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
786
drop table if exists t1;
787
show variables like 'innodb_rollback_on_timeout';
789
innodb_rollback_on_timeout OFF
790
create table t1 (a int unsigned not null primary key) engine = innodb;
791
insert into t1 values (1);
794
insert into t1 values (2);
800
insert into t1 values (5);
805
insert into t1 values (2);
806
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
823
drop table if exists t1;
824
create table t1 (a int) engine=innodb;
825
alter table t1 alter a set default 1;
828
Bug#24918 drop table and lock / inconsistent between
831
Check transactional tables under LOCK TABLES
833
drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
835
create table t24918_access (id int);
836
create table t24918 (id int) engine=myisam;
837
create temporary table t24918_tmp (id int) engine=myisam;
838
create table t24918_trans (id int) engine=innodb;
839
create temporary table t24918_trans_tmp (id int) engine=innodb;
840
lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
842
select * from t24918_access;
843
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
844
drop table t24918_trans;
845
select * from t24918_access;
846
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
847
drop table t24918_trans_tmp;
848
select * from t24918_access;
849
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
850
drop table t24918_tmp;
851
select * from t24918_access;
852
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
854
drop table t24918_access;
855
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
856
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
857
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
858
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
859
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
869
Extra Using where; Using index
870
SELECT * FROM t1 WHERE b=2 ORDER BY a;
888
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
898
Extra Using where; Using index; Using filesort
899
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
933
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
943
Extra Using where; Using index
944
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
978
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
980
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
981
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
982
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
983
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
993
Extra Using where; Using index; Using filesort
994
SELECT * FROM t2 WHERE b=1 ORDER BY a;
1012
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1022
Extra Using where; Using index
1023
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1041
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1051
Extra Using where; Using index
1052
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1070
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1080
Extra Using where; Using index
1081
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1100
CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
1101
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
1102
INSERT INTO t1 SELECT a + 8 FROM t1;
1103
INSERT INTO t1 SELECT a + 16 FROM t1;
1104
CREATE PROCEDURE p1 ()
1106
DECLARE i INT DEFAULT 50;
1109
ALTER TABLE t1 ENGINE=InnoDB;
1114
SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
1123
create table t1(a text) engine=innodb default charset=utf8;
1124
insert into t1 values('aaa');
1125
alter table t1 add index(a(1024));
1127
Warning 1071 Specified key was too long; max key length is 767 bytes
1128
Warning 1071 Specified key was too long; max key length is 767 bytes
1129
show create table t1;
1131
t1 CREATE TABLE `t1` (
1134
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1141
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
1143
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
1147
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
1161
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1162
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1163
id select_type table type possible_keys key key_len ref rows Extra
1164
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1165
SELECT c,b,d FROM t1 GROUP BY c,b,d;
1170
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1171
id select_type table type possible_keys key key_len ref rows Extra
1172
1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1173
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1178
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1179
id select_type table type possible_keys key key_len ref rows Extra
1180
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1181
SELECT c,b,d FROM t1 ORDER BY c,b,d;
1186
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1187
id select_type table type possible_keys key key_len ref rows Extra
1188
1 SIMPLE t1 index NULL c 8 NULL 3
1189
SELECT c,b,d FROM t1 GROUP BY c,b;
1194
EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1195
id select_type table type possible_keys key key_len ref rows Extra
1196
1 SIMPLE t1 index NULL c 8 NULL 3 Using index
1197
SELECT c,b FROM t1 GROUP BY c,b;
1203
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1204
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1205
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1215
Extra Using where; Using index
1216
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1220
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1230
Extra Using where; Using index
1231
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1235
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1246
SELECT * FROM t1 ORDER BY b ASC, a ASC;
1251
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1262
SELECT * FROM t1 ORDER BY b DESC, a DESC;
1267
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1277
Extra Using index; Using filesort
1278
SELECT * FROM t1 ORDER BY b ASC, a DESC;
1283
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1293
Extra Using index; Using filesort
1294
SELECT * FROM t1 ORDER BY b DESC, a ASC;
1302
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1307
DROP TABLE IF EXISTS t1;
1309
CREATE TABLE t1(c INT)
1311
ROW_FORMAT = COMPACT;
1315
SELECT table_schema, table_name, row_format
1316
FROM INFORMATION_SCHEMA.TABLES
1317
WHERE table_schema = DATABASE() AND table_name = 't1';
1318
table_schema table_name row_format
1321
# - change ROW_FORMAT and check;
1323
ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1325
SELECT table_schema, table_name, row_format
1326
FROM INFORMATION_SCHEMA.TABLES
1327
WHERE table_schema = DATABASE() AND table_name = 't1';
1328
table_schema table_name row_format
1331
# - that's it, cleanup.
1334
create table t1(a char(10) not null, unique key aa(a(1)),
1335
b char(4) not null, unique key bb(b(4))) engine=innodb;
1337
Field Type Null Key Default Extra
1338
a char(10) NO UNI NULL
1339
b char(4) NO PRI NULL
1340
show create table t1;
1342
t1 CREATE TABLE `t1` (
1343
`a` char(10) NOT NULL,
1344
`b` char(4) NOT NULL,
1345
UNIQUE KEY `bb` (`b`),
1346
UNIQUE KEY `aa` (`a`(1))
1347
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1349
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1350
INSERT INTO t1 VALUES
1351
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1352
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1353
id select_type table type possible_keys key key_len ref rows Extra
1354
1 SIMPLE t1 ALL idx NULL NULL NULL 4 Using where; Using filesort
1355
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1361
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1362
set global innodb_autoextend_increment=8;
1363
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1364
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
1365
set global innodb_commit_concurrency=0;
1366
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
1367
CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b))
1369
INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1);
1370
INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
1371
EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
1372
id select_type table type possible_keys key key_len ref rows Extra
1373
1 SIMPLE t1 index t1_b PRIMARY 4 NULL 8 Using where
1374
SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
1382
DROP TABLE IF EXISTS t1;
1383
CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
1384
CREATE INDEX i1 on t1 (a(3));
1385
SELECT * FROM t1 WHERE a = 'abcde';
1389
# BUG #26288: savepoint are not deleted on comit, if the transaction
1390
# was otherwise empty
1395
RELEASE SAVEPOINT s1;
1396
ERROR 42000: SAVEPOINT s1 does not exist
1400
ROLLBACK TO SAVEPOINT s2;
1401
ERROR 42000: SAVEPOINT s2 does not exist
1405
RELEASE SAVEPOINT s3;
1406
ERROR 42000: SAVEPOINT s3 does not exist
1410
ROLLBACK TO SAVEPOINT s4;
1411
ERROR 42000: SAVEPOINT s4 does not exist
1412
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;
1413
SHOW CREATE TABLE t1;
1415
t1 CREATE TABLE `t1` (
1416
`f1` int(11) NOT NULL COMMENT 'My ID#',
1417
`f2` int(11) DEFAULT NULL,
1418
`f3` char(10) DEFAULT 'My ID#',
1420
KEY `f2_ref` (`f2`),
1421
CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
1422
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1425
# Bug #36995: valgrind error in remove_const during subquery executions
1427
create table t1 (a bit(1) not null,b int) engine=myisam;
1428
create table t2 (c int) engine=innodb;
1430
select b from t1 where a not in (select b from t1,t2 group by a) group by a;
1431
id select_type table type possible_keys key key_len ref rows Extra
1432
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1433
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 const row not found
1434
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
1438
`k` int(11) NOT NULL auto_increment,
1439
`a` int(11) default NULL,
1440
`c` int(11) default NULL,
1442
UNIQUE KEY `idx_1` (`a`)
1444
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1447
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1450
select last_insert_id();
1457
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1460
select last_insert_id();
1463
select last_insert_id(0);
1466
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1469
select last_insert_id();
1476
insert ignore into t2 values (null,6,1),(10,8,1);
1477
select last_insert_id();
1480
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1481
select last_insert_id();
1491
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1493
0 ) + 1, k=last_insert_id(k);
1494
select last_insert_id();
1505
drop table if exists t1, t2;
1506
create table t1 (i int);
1507
alter table t1 modify i int default 1;
1508
alter table t1 modify i int default 2, rename t2;
1509
lock table t2 write;
1510
alter table t2 modify i int default 3;
1512
lock table t2 write;
1513
alter table t2 modify i int default 4, rename t1;
1516
drop table if exists t1;
1517
create table t1 (i int);
1518
insert into t1 values ();
1519
lock table t1 write;
1520
alter table t1 modify i int default 1;
1521
insert into t1 values ();
1526
alter table t1 change i c char(10) default "Two";
1527
insert into t1 values ();
1540
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1541
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1542
insert into t1(f1) values(1);
1543
select @a:=f2 from t1;
1547
select @b:=f2 from t1;
1550
select if(@a=@b,"ok","wrong");
1551
if(@a=@b,"ok","wrong")
1553
insert into t1(f1) values (1) on duplicate key update f1="1";
1554
select @b:=f2 from t1;
1557
select if(@a=@b,"ok","wrong");
1558
if(@a=@b,"ok","wrong")
1560
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1561
select @b:=f2 from t1;
1564
select if(@a=@b,"ok","wrong");
1565
if(@a=@b,"ok","wrong")
1568
SET SESSION AUTOCOMMIT = 0;
1569
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1570
set binlog_format=mixed;
1571
# Switch to connection con1
1572
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
1574
INSERT INTO t1 VALUES (1,2);
1575
# 1. test for locking:
1577
UPDATE t1 SET b = 12 WHERE a = 1;
1579
info: Rows matched: 1 Changed: 1 Warnings: 0
1583
# Switch to connection con2
1584
UPDATE t1 SET b = 21 WHERE a = 1;
1585
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1586
# Switch to connection con1
1591
# 2. test for serialized update:
1592
CREATE TABLE t2 (a INT);
1594
INSERT INTO t1 VALUES (1,'init');
1595
CREATE PROCEDURE p1()
1597
UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
1598
INSERT INTO t2 VALUES ();
1601
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
1603
info: Rows matched: 1 Changed: 1 Warnings: 0
1607
# Switch to connection con2
1609
# Switch to connection con1
1617
# Switch to connection con2
1621
# Switch to connection con1
1622
# 3. test for updated key column:
1625
INSERT INTO t1 VALUES (1,'init');
1627
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
1629
info: Rows matched: 1 Changed: 1 Warnings: 0
1633
# Switch to connection con2
1635
# Switch to connection con1
1643
# Switch to connection con2
1649
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
1650
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1651
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1652
ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
1653
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1654
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
1655
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
1656
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1657
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1658
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1659
ALTER TABLE t2 DROP FOREIGN KEY c2;
1661
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1662
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1663
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
1664
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1665
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
1666
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
1667
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
1668
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
1669
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1670
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
1671
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
1672
SHOW CREATE TABLE t2;
1674
t2 CREATE TABLE `t2` (
1675
`c` int(11) NOT NULL,
1676
`d` int(11) NOT NULL,
1677
PRIMARY KEY (`c`,`d`),
1678
CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
1679
CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1680
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
1681
CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
1682
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1685
create table t1 (a int auto_increment primary key) engine=innodb;
1686
alter table t1 order by a;
1688
Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
1691
(vid integer NOT NULL,
1692
tid integer NOT NULL,
1693
idx integer NOT NULL,
1694
name varchar(128) NOT NULL,
1695
type varchar(128) NULL,
1696
PRIMARY KEY(idx, vid, tid),
1697
UNIQUE(vid, tid, name)
1699
INSERT INTO t1 VALUES
1700
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
1701
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
1702
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
1703
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
1704
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
1705
EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1706
id select_type table type possible_keys key key_len ref rows Extra
1707
1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where
1708
SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
1709
vid tid idx name type
1716
# Bug #44290: explain crashes for subquery with distinct in
1717
# SQL_SELECT::test_quick_select
1718
# (reproduced only with InnoDB tables)
1720
CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3))
1722
INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
1723
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1724
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1728
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1729
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1730
id select_type table type possible_keys key key_len ref rows Extra
1731
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1732
2 DERIVED t1 index c3,c2 c2 10 NULL 5
1734
CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
1736
INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
1737
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1738
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1742
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1743
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1744
id select_type table type possible_keys key key_len ref rows Extra
1745
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1746
2 DERIVED t1 index c3,c2 c2 18 NULL 5
1748
CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2),
1749
KEY (c3), KEY (c2, c3))
1751
INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
1752
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1753
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1757
SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
1758
FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
1759
id select_type table type possible_keys key key_len ref rows Extra
1760
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1761
2 DERIVED t1 index c3,c2 c2 14 NULL 5
1764
drop table if exists t1, t2, t3;
1765
create table t1(a int);
1766
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
1767
create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
1768
insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
1769
this must use key 'a', not PRIMARY:
1770
explain select a from t2 where a=b;
1771
id select_type table type possible_keys key key_len ref rows Extra
1772
1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index
1774
SET SESSION BINLOG_FORMAT=STATEMENT;
1775
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
1776
select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
1777
@@session.sql_log_bin 1
1778
@@session.binlog_format STATEMENT
1779
@@session.tx_isolation READ-COMMITTED
1780
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
1781
INSERT INTO t1 VALUES(1);
1783
DROP TABLE IF EXISTS t1;
1784
CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
1785
CREATE INDEX i1 on t1 (a(3));
1786
SELECT * FROM t1 WHERE a = 'abcde';
1789
CREATE TABLE foo (a int, b int, c char(10),
1793
CREATE TABLE foo2 (a int, b int, c char(10),
1797
CREATE TABLE bar (a int, b int, c char(10),
1801
INSERT INTO foo VALUES
1802
(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
1803
(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
1804
INSERT INTO bar SELECT * FROM foo;
1805
INSERT INTO foo2 SELECT * FROM foo;
1806
EXPLAIN SELECT c FROM bar WHERE b>2;;
1817
EXPLAIN SELECT c FROM foo WHERE b>2;;
1828
EXPLAIN SELECT c FROM foo2 WHERE b>2;;
1838
Extra Using where; Using index
1839
EXPLAIN SELECT c FROM bar WHERE c>2;;
1844
possible_keys PRIMARY
1850
EXPLAIN SELECT c FROM foo WHERE c>2;;
1855
possible_keys PRIMARY
1861
EXPLAIN SELECT c FROM foo2 WHERE c>2;;
1866
possible_keys PRIMARY
1871
Extra Using where; Using index
1872
DROP TABLE foo, bar, foo2;
1873
DROP TABLE IF EXISTS t1,t3,t2;
1874
DROP FUNCTION IF EXISTS f1;
1875
CREATE FUNCTION f1() RETURNS VARCHAR(250)
1879
CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
1881
CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
1882
CREATE TEMPORARY TABLE t3 LIKE t2;
1883
INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
1884
SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
1885
PREPARE stmt1 FROM @stmt;
1886
SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
1887
PREPARE stmt3 FROM @stmt;
1890
DEALLOCATE PREPARE stmt1;
1891
DEALLOCATE PREPARE stmt3;
1892
DROP TABLE t1,t3,t2;
1894
DROP TABLE IF EXISTS t1,t2;
1895
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
1896
CREATE TABLE t2 (id INT PRIMARY KEY,
1897
t1_id INT, INDEX par_ind (t1_id),
1898
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
1899
INSERT INTO t1 VALUES (1),(2);
1900
INSERT INTO t2 VALUES (3,2);
1904
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
1916
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
1934
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `t1` (`id`))
1939
DELETE FROM t2 WHERE id = 3;
1953
# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
1956
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
1960
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
1961
aid INT UNSIGNED NOT NULL,
1963
FOREIGN KEY (aid) REFERENCES t1 (id)
1966
bid INT UNSIGNED NOT NULL,
1967
FOREIGN KEY (bid) REFERENCES t2 (id)
1975
INSERT INTO t1 (id) VALUES (1);
1976
INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
1977
INSERT INTO t3 (bid) VALUES (1);
1978
INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
1979
INSERT INTO t5 VALUES (1);
1980
DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
1981
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
1982
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
1983
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
1984
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`bid`) REFERENCES `t2` (`id`))
1985
DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
1990
# Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
1991
# Testing for any side effects of IGNORE on AFTER DELETE triggers used with
1992
# transactional tables.
1994
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1995
CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
1996
CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
1997
CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
1998
FOREIGN KEY (t1i) REFERENCES t1(i))
2000
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2002
SET @b:='EXECUTED TRIGGER';
2003
INSERT INTO t2 VALUES (@b);
2004
SET @a:= error_happens_here;
2008
INSERT INTO t1 VALUES (1),(2),(3),(4);
2009
INSERT INTO t3 SELECT * FROM t1;
2010
** An error in a trigger causes rollback of the statement.
2011
DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2012
ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
2018
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2024
** Same happens with the IGNORE option
2025
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2026
ERROR 42S22: Unknown column 'error_happens_here' in 'field list'
2029
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2036
** The following is an attempt to demonstrate
2037
** error handling inside a row iteration.
2043
INSERT INTO t1 VALUES (1),(2),(3),(4);
2044
INSERT INTO t3 VALUES (1),(2),(3),(4);
2045
INSERT INTO t4 VALUES (3,3),(4,4);
2046
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2048
SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
2049
INSERT INTO t2 VALUES (@b);
2051
** DELETE is prevented by foreign key constrains but errors are silenced.
2052
** The AFTER trigger isn't fired.
2053
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2054
** Tables are modified by best effort:
2055
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2059
** The AFTER trigger was only executed on successful rows:
2062
EXECUTED TRIGGER FOR ROW 1
2063
EXECUTED TRIGGER FOR ROW 2
2066
** Induce an error midway through an AFTER-trigger
2071
INSERT INTO t1 VALUES (1),(2),(3),(4);
2072
INSERT INTO t3 VALUES (1),(2),(3),(4);
2073
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2077
INSERT INTO t4 VALUES (5,5);
2081
** Errors in the trigger causes the statement to abort.
2082
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2083
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t4`, CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`t1i`) REFERENCES `t1` (`i`))
2084
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2097
CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
2098
CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
2099
CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
2100
CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
2101
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
2102
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2103
INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
2104
INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2105
UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10
2106
WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
2114
UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
2115
WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
2123
DROP TABLE t1, t2, t3, t4;
2125
# Bug#44886: SIGSEGV in test_if_skip_sort_order() -
2126
# uninitialized variable used as subscript
2128
CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
2130
INSERT INTO t1 VALUES (1,1,1,0);
2131
CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
2132
INSERT INTO t2 VALUES (1,1,2);
2133
CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
2134
INSERT INTO t3 VALUES (1, 1);
2135
SELECT * FROM t1, t2, t3
2136
WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
2140
DROP TABLE t1, t2, t3;
2142
# Bug #45828: Optimizer won't use partial primary key if another
2143
# index can prevent filesort
2149
PRIMARY KEY (c1,c2),
2152
INSERT INTO t1 VALUES (5,2,1246276747);
2153
INSERT INTO t1 VALUES (2,1,1246281721);
2154
INSERT INTO t1 VALUES (7,3,1246281756);
2155
INSERT INTO t1 VALUES (4,2,1246282139);
2156
INSERT INTO t1 VALUES (3,1,1246282230);
2157
INSERT INTO t1 VALUES (1,0,1246282712);
2158
INSERT INTO t1 VALUES (8,3,1246282765);
2159
INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
2160
INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
2161
INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
2162
INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
2163
INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
2164
INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
2165
SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2167
EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2168
id select_type table type possible_keys key key_len ref rows Extra
2169
1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort
2170
EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2171
id select_type table type possible_keys key key_len ref rows Extra
2172
1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort
2180
explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2181
id select_type table type possible_keys key key_len ref rows Extra
2182
1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort
2185
# 36259: Optimizing with ORDER BY
2188
a INT NOT NULL AUTO_INCREMENT,
2193
PRIMARY KEY (a), KEY i2 (b,c,d)
2195
INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
2196
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2197
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2198
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2199
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2200
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2201
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2202
EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
2203
id select_type table type possible_keys key key_len ref rows Extra
2204
1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
2205
EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
2206
id select_type table type possible_keys key key_len ref rows Extra
2207
1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
2208
EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
2209
id select_type table type possible_keys key key_len ref rows Extra
2210
1 SIMPLE t1 index NULL PRIMARY 4 NULL 128 Using where
2213
# Bug #47963: Wrong results when index is used
2216
a VARCHAR(5) NOT NULL,
2217
b VARCHAR(5) NOT NULL,
2218
c DATETIME NOT NULL,
2221
INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
2222
SELECT * FROM t1 WHERE a = 'TEST' AND
2223
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
2225
TEST TEST 2009-10-09 00:00:00
2226
SELECT * FROM t1 WHERE a = 'TEST' AND
2227
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
2229
TEST TEST 2009-10-09 00:00:00
2230
SELECT * FROM t1 WHERE a = 'TEST' AND
2231
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
2233
TEST TEST 2009-10-09 00:00:00
2234
SELECT * FROM t1 WHERE a = 'TEST' AND
2235
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
2237
TEST TEST 2009-10-09 00:00:00
2238
SELECT * FROM t1 WHERE a = 'TEST' AND
2239
c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
2241
TEST TEST 2009-10-09 00:00:00
2242
SELECT * FROM t1 WHERE a = 'TEST' AND
2243
c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
2245
TEST TEST 2009-10-09 00:00:00
2246
SELECT * FROM t1 WHERE a = 'TEST' AND
2247
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
2249
EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
2250
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
2251
id select_type table type possible_keys key key_len ref rows Extra
2252
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2255
# Bug #46175: NULL read_view and consistent read assertion
2257
CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
2258
CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
2259
INSERT INTO t1 VALUES (),();
2260
INSERT INTO t2 VALUES (),();
2261
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
2262
WHERE b =(SELECT a FROM t1 LIMIT 1);
2263
CREATE PROCEDURE p1(num INT)
2265
DECLARE i INT DEFAULT 0;
2267
SHOW CREATE VIEW v1;
2269
UNTIL i>num END REPEAT;
2277
# Bug #49324: more valgrind errors in test_if_skip_sort_order
2279
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
2280
#should not cause valgrind warnings
2281
SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
2285
# Bug#50843: Filesort used instead of clustered index led to
2286
# performance degradation.
2288
create table t1(f1 int not null primary key, f2 int) engine=innodb;
2289
create table t2(f1 int not null, key (f1)) engine=innodb;
2290
insert into t1 values (1,1),(2,2),(3,3);
2291
insert into t2 values (1),(2),(3);
2292
explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
2293
id select_type table type possible_keys key key_len ref rows Extra
2294
1 SIMPLE t1 index NULL PRIMARY 4 NULL 3
2295
1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index
2299
# Bug #39653: find_shortest_key in sql_select.cc does not consider
2300
# clustered primary keys
2302
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
2303
KEY (b,c)) ENGINE=INNODB;
2304
INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
2305
(4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
2306
(7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
2307
(11,11,11,11,11,11);
2308
EXPLAIN SELECT COUNT(*) FROM t1;
2321
# Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
2322
# corrupt definition at engine
2324
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
2326
ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
2327
SHOW INDEXES FROM t1;;
2354
# Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when
2355
# JOINed during an UPDATE
2357
CREATE TABLE t1 (d INT) ENGINE=InnoDB;
2358
CREATE TABLE t2 (a INT, b INT,
2359
c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
2360
ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
2361
set up our data elements
2362
INSERT INTO t1 (d) VALUES (1);
2363
INSERT INTO t2 (a,b) VALUES (1,1);
2364
SELECT SECOND(c) INTO @bug47453 FROM t2;
2365
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2368
UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
2369
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2375
UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
2377
SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2382
# Bug #53334: wrong result for outer join with impossible ON condition
2383
# (see the same test case for MyISAM in join.test)
2385
CREATE TABLE t1 (id INT PRIMARY KEY);
2386
CREATE TABLE t2 (id INT);
2387
INSERT INTO t1 VALUES (75);
2388
INSERT INTO t1 VALUES (79);
2389
INSERT INTO t1 VALUES (78);
2390
INSERT INTO t1 VALUES (77);
2391
REPLACE INTO t1 VALUES (76);
2392
REPLACE INTO t1 VALUES (76);
2393
INSERT INTO t1 VALUES (104);
2394
INSERT INTO t1 VALUES (103);
2395
INSERT INTO t1 VALUES (102);
2396
INSERT INTO t1 VALUES (101);
2397
INSERT INTO t1 VALUES (105);
2398
INSERT INTO t1 VALUES (106);
2399
INSERT INTO t1 VALUES (107);
2400
INSERT INTO t2 VALUES (107),(75),(1000);
2401
SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
2402
WHERE t2.id=75 AND t1.id IS NULL;
2405
EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
2406
WHERE t2.id=75 AND t1.id IS NULL;
2407
id select_type table type possible_keys key key_len ref rows Extra
2408
1 SIMPLE t1 const PRIMARY NULL NULL NULL 1 Impossible ON condition
2409
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
2412
# Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
2414
CREATE TABLE t1 (a INT, b INT, c INT, d INT,
2415
PRIMARY KEY(a,b,c), KEY(b,d))
2417
INSERT INTO t1 VALUES (0, 77, 1, 3);
2418
UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
added
removed
mysql-test/r/innodb_mysql.result
