Debian Codegraph @

mysql-test/suite/innodb/r/innodb_mysql.result

raw
1 SET SESSION DEFAULT_STORAGE_ENGINE = InnoDB;
2 drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
3 drop procedure if exists p1;
4 create table t1 (
5 c_id int(11) not null default '0',
6 org_id int(11) default null,
7 unique key contacts$c_id (c_id),
8 key contacts$org_id (org_id)
9 );
10 insert into t1 values
11 (2,null),(120,null),(141,null),(218,7), (128,1),
12 (151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
13 (246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
14 create table t2 (
15 slai_id int(11) not null default '0',
16 owner_tbl int(11) default null,
17 owner_id int(11) default null,
18 sla_id int(11) default null,
19 inc_web int(11) default null,
20 inc_email int(11) default null,
21 inc_chat int(11) default null,
22 inc_csr int(11) default null,
23 inc_total int(11) default null,
24 time_billed int(11) default null,
25 activedate timestamp null default null,
26 expiredate timestamp null default null,
27 state int(11) default null,
28 sla_set int(11) default null,
29 unique key t2$slai_id (slai_id),
30 key t2$owner_id (owner_id),
31 key t2$sla_id (sla_id)
32 );
33 insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
34 (1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
35 (8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
36 flush tables;
37 select si.slai_id
38 from t1 c join t2 si on
39 ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
40 ( si.owner_tbl = 2 and si.owner_id = c.c_id))
41 where
42 c.c_id = 218 and expiredate is null;
43 slai_id
44 12
45 select * from t1 where org_id is null;
46 c_id org_id
47 2 NULL
48 120 NULL
49 141 NULL
50 select si.slai_id
51 from t1 c join t2 si on
52 ((si.owner_tbl = 3 and si.owner_id = c.org_id) or
53 ( si.owner_tbl = 2 and si.owner_id = c.c_id))
54 where
55 c.c_id = 218 and expiredate is null;
56 slai_id
57 12
58 drop table t1, t2;
59 CREATE TABLE t1 (a int, b int, KEY b (b));
60 CREATE TABLE t2 (a int, b int, PRIMARY KEY (a,b));
61 CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY (a),
62 UNIQUE KEY b (b,c), KEY a (a,b,c));
63 INSERT INTO t1 VALUES (1, 1);
64 INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
65 INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
66 INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
67 INSERT INTO t2 SELECT a + 1, b FROM t2;
68 DELETE FROM t2 WHERE a = 1 AND b < 2;
69 INSERT INTO t3 VALUES (1,1,1),(2,1,2);
70 INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
71 INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
72 SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
73 t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
74 ORDER BY t1.b LIMIT 2;
75 b a
76 1 1
77 2 2
78 SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
79 t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
80 ORDER BY t1.b LIMIT 5;
81 b a
82 1 1
83 2 2
84 2 2
85 3 3
86 3 3
87 DROP TABLE t1, t2, t3;
88 CREATE TABLE `t1` (`id1` INT) ;
89 INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
90 CREATE TABLE `t2` (
91 `id1` INT,
92 `id2` INT NOT NULL,
93 `id3` INT,
94 `id4` INT NOT NULL,
95 UNIQUE (`id2`,`id4`),
96 KEY (`id1`)
97 );
98 INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
99 (1,1,1,0),
100 (1,1,2,1),
101 (5,1,2,2),
102 (6,1,2,3),
103 (1,2,2,2),
104 (1,2,1,1);
105 SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
106 id1
107 2
108 DROP TABLE t1, t2;
109 create table t1 (c1 int) engine=innodb;
110 connect con1,localhost,root,,;
111 connect con2,localhost,root,,;
112 connection con2;
113 handler t1 open;
114 handler t1 read first;
115 c1
116 disconnect con2;
117 connection con1;
118 Before and after comparison
119 0
120 connection default;
121 drop table t1;
122 disconnect con1;
123 CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
124 ENGINE=INNODB CHARACTER SET UTF8;
125 INSERT INTO t1 (c1) VALUES ('1a');
126 SELECT * FROM t1;
127 c1 cnt
128 1a 1
129 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
130 SELECT * FROM t1;
131 c1 cnt
132 1a 2
133 DROP TABLE t1;
134 CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
135 ENGINE=INNODB CHARACTER SET UTF8;
136 INSERT INTO t1 (c1) VALUES ('1a');
137 SELECT * FROM t1;
138 c1 cnt
139 1a 1
140 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
141 SELECT * FROM t1;
142 c1 cnt
143 1a 2
144 DROP TABLE t1;
145 CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
146 ENGINE=INNODB CHARACTER SET UTF8;
147 INSERT INTO t1 (c1) VALUES ('1a');
148 SELECT * FROM t1;
149 c1 cnt
150 1a 1
151 INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
152 SELECT * FROM t1;
153 c1 cnt
154 1a 2
155 DROP TABLE t1;
156 CREATE TABLE t1 (
157 a1 decimal(10,0) DEFAULT NULL,
158 a2 blob,
159 a3 time DEFAULT NULL,
160 a4 blob,
161 a5 char(175) DEFAULT NULL,
162 a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
163 a7 tinyblob,
164 INDEX idx (a6,a7(239),a5)
165 ) ENGINE=InnoDB;
166 EXPLAIN SELECT a4 FROM t1 WHERE
167 a6=NULL AND
168 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
171 EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
172 t.a6=t.a6 AND t1.a6=NULL AND
173 t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
174 id select_type table type possible_keys key key_len ref rows Extra
175 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
176 DROP TABLE t1;
177 create table t1m (a int) engine = MEMORY;
178 create table t1i (a int);
179 create table t2m (a int) engine = MEMORY;
180 create table t2i (a int);
181 insert into t2m values (5);
182 insert into t2i values (5);
183 select min(a) from t1i;
184 min(a)
185 NULL
186 select min(7) from t1i;
187 min(7)
188 NULL
189 select min(7) from DUAL;
190 min(7)
191 7
192 explain select min(7) from t2i join t1i;
193 id select_type table type possible_keys key key_len ref rows Extra
194 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
195 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
196 select min(7) from t2i join t1i;
197 min(7)
198 NULL
199 select max(a) from t1i;
200 max(a)
201 NULL
202 select max(7) from t1i;
203 max(7)
204 NULL
205 select max(7) from DUAL;
206 max(7)
207 7
208 explain select max(7) from t2i join t1i;
209 id select_type table type possible_keys key key_len ref rows Extra
210 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
211 1 SIMPLE t1i ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join)
212 select max(7) from t2i join t1i;
213 max(7)
214 NULL
215 select 1, min(a) from t1i where a=99;
216 1 min(a)
217 1 NULL
218 select 1, min(a) from t1i where 1=99;
219 1 min(a)
220 1 NULL
221 select 1, min(1) from t1i where a=99;
222 1 min(1)
223 1 NULL
224 select 1, min(1) from t1i where 1=99;
225 1 min(1)
226 1 NULL
227 select 1, max(a) from t1i where a=99;
228 1 max(a)
229 1 NULL
230 select 1, max(a) from t1i where 1=99;
231 1 max(a)
232 1 NULL
233 select 1, max(1) from t1i where a=99;
234 1 max(1)
235 1 NULL
236 select 1, max(1) from t1i where 1=99;
237 1 max(1)
238 1 NULL
239 explain select count(*), min(7), max(7) from t1m, t1i;
240 id select_type table type possible_keys key key_len ref rows Extra
241 1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found
242 1 SIMPLE t1i ALL NULL NULL NULL NULL 1
243 select count(*), min(7), max(7) from t1m, t1i;
244 count(*) min(7) max(7)
245 0 NULL NULL
246 explain select count(*), min(7), max(7) from t1m, t2i;
247 id select_type table type possible_keys key key_len ref rows Extra
248 1 SIMPLE t1m system NULL NULL NULL NULL 0 Const row not found
249 1 SIMPLE t2i ALL NULL NULL NULL NULL 1
250 select count(*), min(7), max(7) from t1m, t2i;
251 count(*) min(7) max(7)
252 0 NULL NULL
253 explain select count(*), min(7), max(7) from t2m, t1i;
254 id select_type table type possible_keys key key_len ref rows Extra
255 1 SIMPLE t2m system NULL NULL NULL NULL 1
256 1 SIMPLE t1i ALL NULL NULL NULL NULL 1
257 select count(*), min(7), max(7) from t2m, t1i;
258 count(*) min(7) max(7)
259 0 NULL NULL
260 drop table t1m, t1i, t2m, t2i;
261 create table t1 (
262 a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
263 ) ENGINE = MEMORY;
264 insert into t1 (a1, a2, b, c, d) values
265 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
266 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
267 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
268 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
269 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
270 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
271 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
272 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
273 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
274 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
275 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
276 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
277 ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
278 ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
279 ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
280 ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
281 ('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
282 ('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
283 ('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
284 ('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
285 ('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
286 ('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
287 ('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
288 ('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
289 ('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
290 ('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
291 ('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
292 ('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
293 ('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
294 ('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
295 ('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
296 ('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
297 create table t4 (
298 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 ' '
299 );
300 insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
301 create index idx12672_0 on t4 (a1);
302 create index idx12672_1 on t4 (a1,a2,b,c);
303 create index idx12672_2 on t4 (a1,a2,b);
304 analyze table t4;
305 Table Op Msg_type Msg_text
306 test.t4 analyze status Engine-independent statistics collected
307 test.t4 analyze status OK
308 select distinct a1 from t4 where pk_col not in (1,2,3,4);
309 a1
310 a
311 b
312 c
313 d
314 drop table t1,t4;
315 DROP TABLE IF EXISTS t2, t1;
316 CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
317 CREATE TABLE t2 (
318 i INT NOT NULL,
319 FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
320 ) ENGINE= InnoDB;
321 INSERT INTO t1 VALUES (1);
322 INSERT INTO t2 VALUES (1);
323 DELETE IGNORE FROM t1 WHERE i = 1;
324 Warnings:
325 Warning 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)
326 SELECT * FROM t1, t2;
327 i i
328 1 1
329 DROP TABLE t2, t1;
330 End of 4.1 tests.
331 create table t1 (
332 a varchar(30), b varchar(30), primary key(a), key(b)
333 );
334 select distinct a from t1;
335 a
336 drop table t1;
337 create table t1(a int, key(a));
338 insert into t1 values(1);
339 select a, count(a) from t1 group by a with rollup;
340 a count(a)
341 1 1
342 NULL 1
343 drop table t1;
344 create table t1 (f1 int, f2 char(1), primary key(f1,f2)) stats_persistent=0;
345 insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
346 alter table t1 drop primary key, add primary key (f2, f1);
347 explain select distinct f1 a, f1 b from t1;
348 id select_type table type possible_keys key key_len ref rows Extra
349 1 SIMPLE t1 ALL NULL NULL NULL NULL 4 Using temporary
350 explain select distinct f1, f2 from t1;
351 id select_type table type possible_keys key key_len ref rows Extra
352 1 SIMPLE t1 ALL NULL NULL NULL NULL 4
353 drop table t1;
354 CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
355 INDEX (name));
356 CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
357 ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
358 INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
359 INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
360 EXPLAIN
361 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
362 WHERE t1.name LIKE 'A%';
363 id select_type table type possible_keys key key_len ref rows Extra
364 1 SIMPLE t1 range PRIMARY,name name 83 NULL 2 Using where; Using index
365 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
366 EXPLAIN
367 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
368 WHERE t1.name LIKE 'A%' OR FALSE;
369 id select_type table type possible_keys key key_len ref rows Extra
370 1 SIMPLE t1 range PRIMARY,name name 83 NULL 2 Using where; Using index
371 1 SIMPLE t2 ref fkey fkey 5 test.t1.id 1 Using index
372 DROP TABLE t1,t2;
373 CREATE TABLE t1 (
374 id int NOT NULL,
375 name varchar(20) NOT NULL,
376 dept varchar(20) NOT NULL,
377 age tinyint(3) unsigned NOT NULL,
378 PRIMARY KEY (id),
379 INDEX (name,dept)
380 ) ENGINE=InnoDB STATS_PERSISTENT=0;
381 INSERT INTO t1(id, dept, age, name) VALUES
382 (3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
383 (3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
384 (4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
385 (4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
386 set
387 @tmp_uss=@@use_stat_tables,
388 @tmp_occ=@@optimizer_use_condition_selectivity;
389 set
390 use_stat_tables='preferably',
391 optimizer_use_condition_selectivity=4;
392 analyze table t1 persistent for all;
393 Table Op Msg_type Msg_text
394 test.t1 analyze status Engine-independent statistics collected
395 test.t1 analyze status OK
396 flush tables;
397 EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
398 id select_type table type possible_keys key key_len ref rows Extra
399 1 SIMPLE t1 range name name 164 NULL 2 Using where; Using index for group-by
400 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
401 name dept
402 rs5 cs10
403 rs5 cs9
404 DELETE FROM t1;
405 # Masking (#) number in "rows" column of the following EXPLAIN output, as it may vary (bug#47746).
406 EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
407 id select_type table type possible_keys key key_len ref rows Extra
408 1 SIMPLE t1 range name name 164 NULL # Using where; Using index for group-by
409 SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
410 name dept
411 DROP TABLE t1;
412 set
413 use_stat_tables=@tmp_uss,
414 optimizer_use_condition_selectivity=@tmp_occ;
415 drop table if exists t1;
416 show variables like 'innodb_rollback_on_timeout';
417 Variable_name Value
418 innodb_rollback_on_timeout OFF
419 create table t1 (a int unsigned not null primary key) engine = innodb;
420 insert into t1 values (1);
421 commit;
422 connect con1,localhost,root,,;
423 connect con2,localhost,root,,;
424 connection con2;
425 begin work;
426 insert into t1 values (2);
427 select * from t1;
428 a
429 1
430 2
431 connection con1;
432 begin work;
433 insert into t1 values (5);
434 insert into t1 values (2);
435 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
436 select * from t1;
437 a
438 1
439 5
440 commit;
441 connection con2;
442 select * from t1;
443 a
444 1
445 2
446 commit;
447 connection default;
448 select * from t1;
449 a
450 1
451 2
452 5
453 drop table t1;
454 disconnect con1;
455 disconnect con2;
456 set @save_qcache_size=@@global.query_cache_size;
457 set @save_qcache_type=@@global.query_cache_type;
458 set global query_cache_size=10*1024*1024;
459 set global query_cache_type=1;
460 connect con1,localhost,root,,;
461 connection con1;
462 drop table if exists `test`;
463 Warnings:
464 Note 1051 Unknown table 'test.test'
465 CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
466 `test2` varchar(4) NOT NULL,PRIMARY KEY (`test1`))
467 ENGINE=InnoDB DEFAULT CHARSET=latin1;
468 INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
469 disconnect con1;
470 connect con2,localhost,root,,;
471 connection con2;
472 select * from test;
473 test1 test2
474 tes 5678
475 INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
476 ON DUPLICATE KEY UPDATE `test2` = '1234';
477 select * from test;
478 test1 test2
479 tes 1234
480 flush tables;
481 select * from test;
482 test1 test2
483 tes 1234
484 disconnect con2;
485 connection default;
486 drop table test;
487 set global query_cache_type=@save_qcache_type;
488 set global query_cache_size=@save_qcache_size;
489 drop table if exists t1;
490 show variables like 'innodb_rollback_on_timeout';
491 Variable_name Value
492 innodb_rollback_on_timeout OFF
493 create table t1 (a int unsigned not null primary key) engine = innodb;
494 insert into t1 values (1);
495 commit;
496 connect con1,localhost,root,,;
497 connect con2,localhost,root,,;
498 connection con2;
499 begin work;
500 insert into t1 values (2);
501 select * from t1;
502 a
503 1
504 2
505 connection con1;
506 begin work;
507 insert into t1 values (5);
508 insert into t1 values (2);
509 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
510 select * from t1;
511 a
512 1
513 5
514 commit;
515 connection con2;
516 select * from t1;
517 a
518 1
519 2
520 commit;
521 connection default;
522 select * from t1;
523 a
524 1
525 2
526 5
527 drop table t1;
528 disconnect con1;
529 disconnect con2;
530 create table t1(
531 id int auto_increment,
532 c char(1) not null,
533 counter int not null default 1,
534 primary key (id),
535 unique key (c)
536 ) engine=innodb;
537 insert into t1 (id, c) values
538 (NULL, 'a'),
539 (NULL, 'a')
540 on duplicate key update id = values(id), counter = counter + 1;
541 select * from t1;
542 id c counter
543 2 a 2
544 insert into t1 (id, c) values
545 (NULL, 'b')
546 on duplicate key update id = values(id), counter = counter + 1;
547 select * from t1;
548 id c counter
549 2 a 2
550 3 b 1
551 truncate table t1;
552 insert into t1 (id, c) values (NULL, 'a');
553 select * from t1;
554 id c counter
555 1 a 1
556 insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
557 on duplicate key update id = values(id), c = values(c), counter = counter + 1;
558 select * from t1;
559 id c counter
560 1 a 1
561 3 b 2
562 insert into t1 (id, c) values (NULL, 'a')
563 on duplicate key update id = values(id), c = values(c), counter = counter + 1;
564 select * from t1;
565 id c counter
566 3 b 2
567 4 a 2
568 drop table t1;
569 CREATE TABLE t1(
570 id int AUTO_INCREMENT PRIMARY KEY,
571 stat_id int NOT NULL,
572 acct_id int DEFAULT NULL,
573 INDEX idx1 (stat_id, acct_id),
574 INDEX idx2 (acct_id)
575 ) ENGINE=MyISAM;
576 CREATE TABLE t2(
577 id int AUTO_INCREMENT PRIMARY KEY,
578 stat_id int NOT NULL,
579 acct_id int DEFAULT NULL,
580 INDEX idx1 (stat_id, acct_id),
581 INDEX idx2 (acct_id)
582 ) ENGINE=InnoDB STATS_PERSISTENT=0;
583 INSERT INTO t1(stat_id,acct_id) VALUES
584 (1,759), (2,831), (3,785), (4,854), (1,921),
585 (1,553), (2,589), (3,743), (2,827), (2,545),
586 (4,779), (4,783), (1,597), (1,785), (4,832),
587 (1,741), (1,833), (3,788), (2,973), (1,907);
588 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
589 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
590 INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
591 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
592 Warnings:
593 Warning 1365 Division by 0
594 Warning 1365 Division by 0
595 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
596 Warnings:
597 Warning 1365 Division by 0
598 Warning 1365 Division by 0
599 Warning 1365 Division by 0
600 Warning 1365 Division by 0
601 Warning 1365 Division by 0
602 Warning 1365 Division by 0
603 Warning 1365 Division by 0
604 Warning 1365 Division by 0
605 Warning 1365 Division by 0
606 Warning 1365 Division by 0
607 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
608 Warnings:
609 Warning 1365 Division by 0
610 Warning 1365 Division by 0
611 Warning 1365 Division by 0
612 Warning 1365 Division by 0
613 Warning 1365 Division by 0
614 Warning 1365 Division by 0
615 Warning 1365 Division by 0
616 Warning 1365 Division by 0
617 Warning 1365 Division by 0
618 Warning 1365 Division by 0
619 Warning 1365 Division by 0
620 Warning 1365 Division by 0
621 Warning 1365 Division by 0
622 Warning 1365 Division by 0
623 Warning 1365 Division by 0
624 Warning 1365 Division by 0
625 Warning 1365 Division by 0
626 Warning 1365 Division by 0
627 Warning 1365 Division by 0
628 Warning 1365 Division by 0
629 Warning 1365 Division by 0
630 Warning 1365 Division by 0
631 Warning 1365 Division by 0
632 Warning 1365 Division by 0
633 Warning 1365 Division by 0
634 Warning 1365 Division by 0
635 Warning 1365 Division by 0
636 Warning 1365 Division by 0
637 Warning 1365 Division by 0
638 Warning 1365 Division by 0
639 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
640 Warnings:
641 Warning 1365 Division by 0
642 Warning 1365 Division by 0
643 Warning 1365 Division by 0
644 Warning 1365 Division by 0
645 Warning 1365 Division by 0
646 Warning 1365 Division by 0
647 Warning 1365 Division by 0
648 Warning 1365 Division by 0
649 Warning 1365 Division by 0
650 Warning 1365 Division by 0
651 Warning 1365 Division by 0
652 Warning 1365 Division by 0
653 Warning 1365 Division by 0
654 Warning 1365 Division by 0
655 Warning 1365 Division by 0
656 Warning 1365 Division by 0
657 Warning 1365 Division by 0
658 Warning 1365 Division by 0
659 Warning 1365 Division by 0
660 Warning 1365 Division by 0
661 Warning 1365 Division by 0
662 Warning 1365 Division by 0
663 Warning 1365 Division by 0
664 Warning 1365 Division by 0
665 Warning 1365 Division by 0
666 Warning 1365 Division by 0
667 Warning 1365 Division by 0
668 Warning 1365 Division by 0
669 Warning 1365 Division by 0
670 Warning 1365 Division by 0
671 Warning 1365 Division by 0
672 Warning 1365 Division by 0
673 Warning 1365 Division by 0
674 Warning 1365 Division by 0
675 Warning 1365 Division by 0
676 Warning 1365 Division by 0
677 Warning 1365 Division by 0
678 Warning 1365 Division by 0
679 Warning 1365 Division by 0
680 Warning 1365 Division by 0
681 Warning 1365 Division by 0
682 Warning 1365 Division by 0
683 Warning 1365 Division by 0
684 Warning 1365 Division by 0
685 Warning 1365 Division by 0
686 Warning 1365 Division by 0
687 Warning 1365 Division by 0
688 Warning 1365 Division by 0
689 Warning 1365 Division by 0
690 Warning 1365 Division by 0
691 Warning 1365 Division by 0
692 Warning 1365 Division by 0
693 Warning 1365 Division by 0
694 Warning 1365 Division by 0
695 Warning 1365 Division by 0
696 Warning 1365 Division by 0
697 Warning 1365 Division by 0
698 Warning 1365 Division by 0
699 Warning 1365 Division by 0
700 Warning 1365 Division by 0
701 Warning 1365 Division by 0
702 Warning 1365 Division by 0
703 Warning 1365 Division by 0
704 Warning 1365 Division by 0
705 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
706 Warnings:
707 Warning 1365 Division by 0
708 Warning 1365 Division by 0
709 Warning 1365 Division by 0
710 Warning 1365 Division by 0
711 Warning 1365 Division by 0
712 Warning 1365 Division by 0
713 Warning 1365 Division by 0
714 Warning 1365 Division by 0
715 Warning 1365 Division by 0
716 Warning 1365 Division by 0
717 Warning 1365 Division by 0
718 Warning 1365 Division by 0
719 Warning 1365 Division by 0
720 Warning 1365 Division by 0
721 Warning 1365 Division by 0
722 Warning 1365 Division by 0
723 Warning 1365 Division by 0
724 Warning 1365 Division by 0
725 Warning 1365 Division by 0
726 Warning 1365 Division by 0
727 Warning 1365 Division by 0
728 Warning 1365 Division by 0
729 Warning 1365 Division by 0
730 Warning 1365 Division by 0
731 Warning 1365 Division by 0
732 Warning 1365 Division by 0
733 Warning 1365 Division by 0
734 Warning 1365 Division by 0
735 Warning 1365 Division by 0
736 Warning 1365 Division by 0
737 Warning 1365 Division by 0
738 Warning 1365 Division by 0
739 Warning 1365 Division by 0
740 Warning 1365 Division by 0
741 Warning 1365 Division by 0
742 Warning 1365 Division by 0
743 Warning 1365 Division by 0
744 Warning 1365 Division by 0
745 Warning 1365 Division by 0
746 Warning 1365 Division by 0
747 Warning 1365 Division by 0
748 Warning 1365 Division by 0
749 Warning 1365 Division by 0
750 Warning 1365 Division by 0
751 Warning 1365 Division by 0
752 Warning 1365 Division by 0
753 Warning 1365 Division by 0
754 Warning 1365 Division by 0
755 Warning 1365 Division by 0
756 Warning 1365 Division by 0
757 Warning 1365 Division by 0
758 Warning 1365 Division by 0
759 Warning 1365 Division by 0
760 Warning 1365 Division by 0
761 Warning 1365 Division by 0
762 Warning 1365 Division by 0
763 Warning 1365 Division by 0
764 Warning 1365 Division by 0
765 Warning 1365 Division by 0
766 Warning 1365 Division by 0
767 Warning 1365 Division by 0
768 Warning 1365 Division by 0
769 Warning 1365 Division by 0
770 Warning 1365 Division by 0
771 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
772 Warnings:
773 Warning 1365 Division by 0
774 Warning 1365 Division by 0
775 Warning 1365 Division by 0
776 Warning 1365 Division by 0
777 Warning 1365 Division by 0
778 Warning 1365 Division by 0
779 Warning 1365 Division by 0
780 Warning 1365 Division by 0
781 Warning 1365 Division by 0
782 Warning 1365 Division by 0
783 Warning 1365 Division by 0
784 Warning 1365 Division by 0
785 Warning 1365 Division by 0
786 Warning 1365 Division by 0
787 Warning 1365 Division by 0
788 Warning 1365 Division by 0
789 Warning 1365 Division by 0
790 Warning 1365 Division by 0
791 Warning 1365 Division by 0
792 Warning 1365 Division by 0
793 Warning 1365 Division by 0
794 Warning 1365 Division by 0
795 Warning 1365 Division by 0
796 Warning 1365 Division by 0
797 Warning 1365 Division by 0
798 Warning 1365 Division by 0
799 Warning 1365 Division by 0
800 Warning 1365 Division by 0
801 Warning 1365 Division by 0
802 Warning 1365 Division by 0
803 Warning 1365 Division by 0
804 Warning 1365 Division by 0
805 Warning 1365 Division by 0
806 Warning 1365 Division by 0
807 Warning 1365 Division by 0
808 Warning 1365 Division by 0
809 Warning 1365 Division by 0
810 Warning 1365 Division by 0
811 Warning 1365 Division by 0
812 Warning 1365 Division by 0
813 Warning 1365 Division by 0
814 Warning 1365 Division by 0
815 Warning 1365 Division by 0
816 Warning 1365 Division by 0
817 Warning 1365 Division by 0
818 Warning 1365 Division by 0
819 Warning 1365 Division by 0
820 Warning 1365 Division by 0
821 Warning 1365 Division by 0
822 Warning 1365 Division by 0
823 Warning 1365 Division by 0
824 Warning 1365 Division by 0
825 Warning 1365 Division by 0
826 Warning 1365 Division by 0
827 Warning 1365 Division by 0
828 Warning 1365 Division by 0
829 Warning 1365 Division by 0
830 Warning 1365 Division by 0
831 Warning 1365 Division by 0
832 Warning 1365 Division by 0
833 Warning 1365 Division by 0
834 Warning 1365 Division by 0
835 Warning 1365 Division by 0
836 Warning 1365 Division by 0
837 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
838 Warnings:
839 Warning 1365 Division by 0
840 Warning 1365 Division by 0
841 Warning 1365 Division by 0
842 Warning 1365 Division by 0
843 Warning 1365 Division by 0
844 Warning 1365 Division by 0
845 Warning 1365 Division by 0
846 Warning 1365 Division by 0
847 Warning 1365 Division by 0
848 Warning 1365 Division by 0
849 Warning 1365 Division by 0
850 Warning 1365 Division by 0
851 Warning 1365 Division by 0
852 Warning 1365 Division by 0
853 Warning 1365 Division by 0
854 Warning 1365 Division by 0
855 Warning 1365 Division by 0
856 Warning 1365 Division by 0
857 Warning 1365 Division by 0
858 Warning 1365 Division by 0
859 Warning 1365 Division by 0
860 Warning 1365 Division by 0
861 Warning 1365 Division by 0
862 Warning 1365 Division by 0
863 Warning 1365 Division by 0
864 Warning 1365 Division by 0
865 Warning 1365 Division by 0
866 Warning 1365 Division by 0
867 Warning 1365 Division by 0
868 Warning 1365 Division by 0
869 Warning 1365 Division by 0
870 Warning 1365 Division by 0
871 Warning 1365 Division by 0
872 Warning 1365 Division by 0
873 Warning 1365 Division by 0
874 Warning 1365 Division by 0
875 Warning 1365 Division by 0
876 Warning 1365 Division by 0
877 Warning 1365 Division by 0
878 Warning 1365 Division by 0
879 Warning 1365 Division by 0
880 Warning 1365 Division by 0
881 Warning 1365 Division by 0
882 Warning 1365 Division by 0
883 Warning 1365 Division by 0
884 Warning 1365 Division by 0
885 Warning 1365 Division by 0
886 Warning 1365 Division by 0
887 Warning 1365 Division by 0
888 Warning 1365 Division by 0
889 Warning 1365 Division by 0
890 Warning 1365 Division by 0
891 Warning 1365 Division by 0
892 Warning 1365 Division by 0
893 Warning 1365 Division by 0
894 Warning 1365 Division by 0
895 Warning 1365 Division by 0
896 Warning 1365 Division by 0
897 Warning 1365 Division by 0
898 Warning 1365 Division by 0
899 Warning 1365 Division by 0
900 Warning 1365 Division by 0
901 Warning 1365 Division by 0
902 Warning 1365 Division by 0
903 INSERT IGNORE INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
904 Warnings:
905 Warning 1365 Division by 0
906 Warning 1365 Division by 0
907 Warning 1365 Division by 0
908 Warning 1365 Division by 0
909 Warning 1365 Division by 0
910 Warning 1365 Division by 0
911 Warning 1365 Division by 0
912 Warning 1365 Division by 0
913 Warning 1365 Division by 0
914 Warning 1365 Division by 0
915 Warning 1365 Division by 0
916 Warning 1365 Division by 0
917 Warning 1365 Division by 0
918 Warning 1365 Division by 0
919 Warning 1365 Division by 0
920 Warning 1365 Division by 0
921 Warning 1365 Division by 0
922 Warning 1365 Division by 0
923 Warning 1365 Division by 0
924 Warning 1365 Division by 0
925 Warning 1365 Division by 0
926 Warning 1365 Division by 0
927 Warning 1365 Division by 0
928 Warning 1365 Division by 0
929 Warning 1365 Division by 0
930 Warning 1365 Division by 0
931 Warning 1365 Division by 0
932 Warning 1365 Division by 0
933 Warning 1365 Division by 0
934 Warning 1365 Division by 0
935 Warning 1365 Division by 0
936 Warning 1365 Division by 0
937 Warning 1365 Division by 0
938 Warning 1365 Division by 0
939 Warning 1365 Division by 0
940 Warning 1365 Division by 0
941 Warning 1365 Division by 0
942 Warning 1365 Division by 0
943 Warning 1365 Division by 0
944 Warning 1365 Division by 0
945 Warning 1365 Division by 0
946 Warning 1365 Division by 0
947 Warning 1365 Division by 0
948 Warning 1365 Division by 0
949 Warning 1365 Division by 0
950 Warning 1365 Division by 0
951 Warning 1365 Division by 0
952 Warning 1365 Division by 0
953 Warning 1365 Division by 0
954 Warning 1365 Division by 0
955 Warning 1365 Division by 0
956 Warning 1365 Division by 0
957 Warning 1365 Division by 0
958 Warning 1365 Division by 0
959 Warning 1365 Division by 0
960 Warning 1365 Division by 0
961 Warning 1365 Division by 0
962 Warning 1365 Division by 0
963 Warning 1365 Division by 0
964 Warning 1365 Division by 0
965 Warning 1365 Division by 0
966 Warning 1365 Division by 0
967 Warning 1365 Division by 0
968 Warning 1365 Division by 0
969 UPDATE t1 SET acct_id=785
970 WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
971 OPTIMIZE TABLE t1;
972 Table Op Msg_type Msg_text
973 test.t1 optimize status OK
974 SELECT COUNT(*) FROM t1;
975 COUNT(*)
976 40960
977 SELECT COUNT(*) FROM t1 WHERE acct_id=785;
978 COUNT(*)
979 8702
980 EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
981 id select_type table type possible_keys key key_len ref rows Extra
982 1 SIMPLE t1 range idx1,idx2 idx1 9 NULL 2 Using where; Using index
983 INSERT INTO t2 SELECT * FROM t1;
984 OPTIMIZE TABLE t2;
985 Table Op Msg_type Msg_text
986 test.t2 optimize note Table does not support optimize, doing recreate + analyze instead
987 test.t2 optimize status OK
988 DROP TABLE t1,t2;
989 create table t1(a int) engine=innodb;
990 alter table t1 comment '123';
991 show create table t1;
992 Table Create Table
993 t1 CREATE TABLE `t1` (
994 `a` int(11) DEFAULT NULL
995 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci COMMENT='123'
996 drop table t1;
997 CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
998 INSERT INTO t1 VALUES ('uk'),('bg');
999 SELECT * FROM t1 WHERE a = 'uk';
1000 a
1001 uk
1002 DELETE FROM t1 WHERE a = 'uk';
1003 SELECT * FROM t1 WHERE a = 'uk';
1004 a
1005 UPDATE t1 SET a = 'us' WHERE a = 'uk';
1006 SELECT * FROM t1 WHERE a = 'uk';
1007 a
1008 CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
1009 INSERT INTO t2 VALUES ('uk'),('bg');
1010 SELECT * FROM t2 WHERE a = 'uk';
1011 a
1012 uk
1013 DELETE FROM t2 WHERE a = 'uk';
1014 SELECT * FROM t2 WHERE a = 'uk';
1015 a
1016 INSERT INTO t2 VALUES ('uk');
1017 UPDATE t2 SET a = 'us' WHERE a = 'uk';
1018 SELECT * FROM t2 WHERE a = 'uk';
1019 a
1020 CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
1021 INSERT INTO t3 VALUES ('uk'),('bg');
1022 SELECT * FROM t3 WHERE a = 'uk';
1023 a
1024 uk
1025 DELETE FROM t3 WHERE a = 'uk';
1026 SELECT * FROM t3 WHERE a = 'uk';
1027 a
1028 INSERT INTO t3 VALUES ('uk');
1029 UPDATE t3 SET a = 'us' WHERE a = 'uk';
1030 SELECT * FROM t3 WHERE a = 'uk';
1031 a
1032 DROP TABLE t1,t2,t3;
1033 create table t1 (a int) engine=innodb;
1034 select * from bug29807;
1035 ERROR 42S02: Table 'test.bug29807' doesn't exist in engine
1036 drop table t1;
1037 drop table bug29807;
1038 Warnings:
1039 Warning 1932 Table 'test.bug29807' doesn't exist in engine
1040 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
1041 CREATE TABLE t2 (a INT) ENGINE=InnoDB;
1042 CONNECT c1,localhost,root,,;
1043 CONNECT c2,localhost,root,,;
1044 connection c1;
1045 SET AUTOCOMMIT=0;
1046 INSERT INTO t2 VALUES (1);
1047 connection c2;
1048 SET AUTOCOMMIT=0;
1049 SET @old_lock_wait_timeout= @@lock_wait_timeout;
1050 SET lock_wait_timeout= 1;
1051 LOCK TABLES t1 READ, t2 READ;
1052 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1053 SET @@lock_wait_timeout= @old_lock_wait_timeout;
1054 connection c1;
1055 COMMIT;
1056 INSERT INTO t1 VALUES (1);
1057 connection default;
1058 SET AUTOCOMMIT=default;
1059 disconnect c1;
1060 disconnect c2;
1061 DROP TABLE t1,t2;
1062 CREATE TABLE t1 (
1063 id int NOT NULL auto_increment PRIMARY KEY,
1064 b int NOT NULL,
1065 c datetime NOT NULL,
1066 INDEX idx_b(b),
1067 INDEX idx_c(c)
1068 ) ENGINE=InnoDB;
1069 CREATE TABLE t2 (
1070 b int NOT NULL auto_increment PRIMARY KEY,
1071 c datetime NOT NULL
1072 ) ENGINE= MyISAM;
1073 INSERT INTO t2(c) VALUES ('2007-01-01');
1074 INSERT INTO t2(c) SELECT c FROM t2;
1075 INSERT INTO t2(c) SELECT c FROM t2;
1076 INSERT INTO t2(c) SELECT c FROM t2;
1077 INSERT INTO t2(c) SELECT c FROM t2;
1078 INSERT INTO t2(c) SELECT c FROM t2;
1079 INSERT INTO t2(c) SELECT c FROM t2;
1080 INSERT INTO t2(c) SELECT c FROM t2;
1081 INSERT INTO t2(c) SELECT c FROM t2;
1082 INSERT INTO t2(c) SELECT c FROM t2;
1083 INSERT INTO t2(c) SELECT c FROM t2;
1084 INSERT INTO t1(b,c) SELECT b,c FROM t2;
1085 UPDATE t2 SET c='2007-01-02';
1086 INSERT INTO t1(b,c) SELECT b,c FROM t2;
1087 UPDATE t2 SET c='2007-01-03';
1088 INSERT INTO t1(b,c) SELECT b,c FROM t2;
1089 set @@sort_buffer_size=8192;
1090 SELECT COUNT(*) FROM t1;
1091 COUNT(*)
1092 3072
1093 EXPLAIN
1094 SELECT COUNT(*) FROM t1
1095 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
1096 id select_type table type possible_keys key key_len ref rows Extra
1097 1 SIMPLE t1 ALL idx_b,idx_c NULL NULL NULL # Using where
1098 SELECT COUNT(*) FROM t1
1099 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
1100 COUNT(*)
1101 3072
1102 EXPLAIN
1103 SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
1104 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
1105 id select_type table type possible_keys key key_len ref rows Extra
1106 1 SIMPLE t1 index_merge idx_b,idx_c idx_c,idx_b 5,4 NULL # Using sort_union(idx_c,idx_b); Using where
1107 SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
1108 WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
1109 COUNT(*)
1110 3072
1111 set @@sort_buffer_size=default;
1112 DROP TABLE t1,t2;
1113 CREATE TABLE t1 (a int, b int);
1114 insert into t1 values (1,1),(1,2);
1115 CREATE TABLE t2 (primary key (a)) select * from t1;
1116 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1117 drop table if exists t2;
1118 Warnings:
1119 Note 1051 Unknown table 'test.t2'
1120 CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
1121 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1122 drop table if exists t2;
1123 Warnings:
1124 Note 1051 Unknown table 'test.t2'
1125 CREATE TABLE t2 (a int, b int, primary key (a));
1126 BEGIN;
1127 INSERT INTO t2 values(100,100);
1128 CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1129 Warnings:
1130 Note 1050 Table 't2' already exists
1131 SELECT * from t2;
1132 a b
1133 100 100
1134 ROLLBACK;
1135 SELECT * from t2;
1136 a b
1137 100 100
1138 TRUNCATE table t2;
1139 INSERT INTO t2 select * from t1;
1140 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1141 SELECT * from t2;
1142 a b
1143 drop table t2;
1144 CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
1145 BEGIN;
1146 INSERT INTO t2 values(100,100);
1147 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1148 Warnings:
1149 Note 1050 Table 't2' already exists
1150 SELECT * from t2;
1151 a b
1152 100 100
1153 COMMIT;
1154 BEGIN;
1155 INSERT INTO t2 values(101,101);
1156 CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1157 Warnings:
1158 Note 1050 Table 't2' already exists
1159 SELECT * from t2;
1160 a b
1161 100 100
1162 101 101
1163 ROLLBACK;
1164 SELECT * from t2;
1165 a b
1166 100 100
1167 TRUNCATE table t2;
1168 INSERT INTO t2 select * from t1;
1169 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
1170 SELECT * from t2;
1171 a b
1172 drop table t1,t2;
1173 create table t1(f1 varchar(800) binary not null, key(f1))
1174 character set utf8 collate utf8_general_ci;
1175 insert into t1 values('aaa');
1176 drop table t1;
1177 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
1178 INSERT INTO t1 VALUES ( 1 , 1 , 1);
1179 INSERT INTO t1 SELECT a + 1 , MOD(a + 1 , 20), 1 FROM t1;
1180 INSERT INTO t1 SELECT a + 2 , MOD(a + 2 , 20), 1 FROM t1;
1181 INSERT INTO t1 SELECT a + 4 , MOD(a + 4 , 20), 1 FROM t1;
1182 INSERT INTO t1 SELECT a + 8 , MOD(a + 8 , 20), 1 FROM t1;
1183 INSERT INTO t1 SELECT a + 16, MOD(a + 16, 20), 1 FROM t1;
1184 INSERT INTO t1 SELECT a + 32, MOD(a + 32, 20), 1 FROM t1;
1185 INSERT INTO t1 SELECT a + 64, MOD(a + 64, 20), 1 FROM t1;
1186 EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
1187 id select_type table type possible_keys key key_len ref rows Extra
1188 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using temporary; Using filesort
1189 EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
1190 id select_type table type possible_keys key key_len ref rows Extra
1191 1 SIMPLE t1 ALL NULL NULL NULL NULL 128 Using filesort
1192 DROP TABLE t1;
1193 drop table if exists t1;
1194 show variables like 'innodb_rollback_on_timeout';
1195 Variable_name Value
1196 innodb_rollback_on_timeout OFF
1197 create table t1 (a int unsigned not null primary key) engine = innodb;
1198 insert into t1 values (1);
1199 commit;
1200 connect con1,localhost,root,,;
1201 connect con2,localhost,root,,;
1202 connection con2;
1203 begin work;
1204 insert into t1 values (2);
1205 select * from t1;
1206 a
1207 1
1208 2
1209 connection con1;
1210 begin work;
1211 insert into t1 values (5);
1212 insert into t1 values (2);
1213 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1214 select * from t1;
1215 a
1216 1
1217 5
1218 commit;
1219 connection con2;
1220 select * from t1;
1221 a
1222 1
1223 2
1224 commit;
1225 connection default;
1226 select * from t1;
1227 a
1228 1
1229 2
1230 5
1231 drop table t1;
1232 disconnect con1;
1233 disconnect con2;
1234 drop table if exists t1;
1235 create table t1 (a int) engine=innodb;
1236 alter table t1 alter a set default 1;
1237 drop table t1;
1238
1239 Bug#24918 drop table and lock / inconsistent between
1240 perm and temp tables
1241
1242 Check transactional tables under LOCK TABLES
1243
1244 drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp,
1245 t24918_access;
1246 create table t24918_access (id int);
1247 create table t24918 (id int) engine=myisam;
1248 create temporary table t24918_tmp (id int) engine=myisam;
1249 create table t24918_trans (id int) engine=innodb;
1250 create temporary table t24918_trans_tmp (id int) engine=innodb;
1251 lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
1252 drop table t24918;
1253 select * from t24918_access;
1254 ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
1255 drop table t24918_trans;
1256 select * from t24918_access;
1257 ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
1258 drop table t24918_trans_tmp;
1259 select * from t24918_access;
1260 ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
1261 drop table t24918_tmp;
1262 select * from t24918_access;
1263 ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
1264 unlock tables;
1265 drop table t24918_access;
1266 CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
1267 INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
1268 INSERT INTO t1 SELECT a + 8, 2 FROM t1;
1269 INSERT INTO t1 SELECT a + 16, 1 FROM t1;
1270 EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
1271 id 1
1272 select_type SIMPLE
1273 table t1
1274 type ref
1275 possible_keys bkey
1276 key bkey
1277 key_len 5
1278 ref const
1279 rows 16
1280 Extra Using where; Using index
1281 SELECT * FROM t1 WHERE b=2 ORDER BY a;
1282 a b
1283 1 2
1284 2 2
1285 3 2
1286 4 2
1287 5 2
1288 6 2
1289 7 2
1290 8 2
1291 9 2
1292 10 2
1293 11 2
1294 12 2
1295 13 2
1296 14 2
1297 15 2
1298 16 2
1299 EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
1300 id 1
1301 select_type SIMPLE
1302 table t1
1303 type index
1304 possible_keys bkey
1305 key PRIMARY
1306 key_len 4
1307 ref NULL
1308 rows 32
1309 Extra Using where
1310 SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
1311 a b
1312 1 2
1313 2 2
1314 3 2
1315 4 2
1316 5 2
1317 6 2
1318 7 2
1319 8 2
1320 9 2
1321 10 2
1322 11 2
1323 12 2
1324 13 2
1325 14 2
1326 15 2
1327 16 2
1328 17 1
1329 18 1
1330 19 1
1331 20 1
1332 21 1
1333 22 1
1334 23 1
1335 24 1
1336 25 1
1337 26 1
1338 27 1
1339 28 1
1340 29 1
1341 30 1
1342 31 1
1343 32 1
1344 EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
1345 id 1
1346 select_type SIMPLE
1347 table t1
1348 type range
1349 possible_keys bkey
1350 key bkey
1351 key_len 5
1352 ref NULL
1353 rows 32
1354 Extra Using where; Using index
1355 SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
1356 a b
1357 17 1
1358 18 1
1359 19 1
1360 20 1
1361 21 1
1362 22 1
1363 23 1
1364 24 1
1365 25 1
1366 26 1
1367 27 1
1368 28 1
1369 29 1
1370 30 1
1371 31 1
1372 32 1
1373 1 2
1374 2 2
1375 3 2
1376 4 2
1377 5 2
1378 6 2
1379 7 2
1380 8 2
1381 9 2
1382 10 2
1383 11 2
1384 12 2
1385 13 2
1386 14 2
1387 15 2
1388 16 2
1389 CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
1390 ENGINE=InnoDB;
1391 INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
1392 INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
1393 INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
1394 EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
1395 id 1
1396 select_type SIMPLE
1397 table t2
1398 type index
1399 possible_keys bkey
1400 key PRIMARY
1401 key_len 4
1402 ref NULL
1403 rows 16
1404 Extra Using where
1405 SELECT * FROM t2 WHERE b=1 ORDER BY a;
1406 a b c
1407 1 1 1
1408 2 1 1
1409 3 1 1
1410 4 1 1
1411 5 1 1
1412 6 1 1
1413 7 1 1
1414 8 1 1
1415 9 1 1
1416 10 1 1
1417 11 1 1
1418 12 1 1
1419 13 1 1
1420 14 1 1
1421 15 1 1
1422 16 1 1
1423 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1424 id 1
1425 select_type SIMPLE
1426 table t2
1427 type ref
1428 possible_keys bkey
1429 key bkey
1430 key_len 10
1431 ref const,const
1432 rows 16
1433 Extra Using where; Using index
1434 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
1435 a b c
1436 1 1 1
1437 2 1 1
1438 3 1 1
1439 4 1 1
1440 5 1 1
1441 6 1 1
1442 7 1 1
1443 8 1 1
1444 9 1 1
1445 10 1 1
1446 11 1 1
1447 12 1 1
1448 13 1 1
1449 14 1 1
1450 15 1 1
1451 16 1 1
1452 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1453 id 1
1454 select_type SIMPLE
1455 table t2
1456 type ref
1457 possible_keys bkey
1458 key bkey
1459 key_len 10
1460 ref const,const
1461 rows 16
1462 Extra Using where; Using index
1463 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
1464 a b c
1465 1 1 1
1466 2 1 1
1467 3 1 1
1468 4 1 1
1469 5 1 1
1470 6 1 1
1471 7 1 1
1472 8 1 1
1473 9 1 1
1474 10 1 1
1475 11 1 1
1476 12 1 1
1477 13 1 1
1478 14 1 1
1479 15 1 1
1480 16 1 1
1481 EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1482 id 1
1483 select_type SIMPLE
1484 table t2
1485 type ref
1486 possible_keys bkey
1487 key bkey
1488 key_len 10
1489 ref const,const
1490 rows 16
1491 Extra Using where; Using index
1492 SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
1493 a b c
1494 1 1 1
1495 2 1 1
1496 3 1 1
1497 4 1 1
1498 5 1 1
1499 6 1 1
1500 7 1 1
1501 8 1 1
1502 9 1 1
1503 10 1 1
1504 11 1 1
1505 12 1 1
1506 13 1 1
1507 14 1 1
1508 15 1 1
1509 16 1 1
1510 DROP TABLE t1,t2;
1511 CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
1512 INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
1513 INSERT INTO t1 SELECT a + 8 FROM t1;
1514 INSERT INTO t1 SELECT a + 16 FROM t1;
1515 CREATE PROCEDURE p1 ()
1516 BEGIN
1517 DECLARE i INT DEFAULT 50;
1518 DECLARE cnt INT;
1519 # Continue even in the presence of ER_LOCK_DEADLOCK.
1520 DECLARE CONTINUE HANDLER FOR 1213 BEGIN END;
1521 START TRANSACTION;
1522 ALTER TABLE t1 ENGINE=InnoDB;
1523 COMMIT;
1524 START TRANSACTION;
1525 WHILE (i > 0) DO
1526 SET i = i - 1;
1527 SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
1528 END WHILE;
1529 COMMIT;
1530 END;|
1531 CONNECT con1,localhost,root,,;
1532 CONNECT con2,localhost,root,,;
1533 connection con1;
1534 CALL p1();
1535 connection con2;
1536 CALL p1();
1537 connection default;
1538 CALL p1();
1539 connection con1;
1540 connection con2;
1541 connection default;
1542 disconnect con1;
1543 disconnect con2;
1544 DROP PROCEDURE p1;
1545 DROP TABLE t1;
1546 create table t1(a text) engine=innodb default charset=utf8;
1547 insert into t1 values('aaa');
1548 set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
1549 alter table t1 add index(a(1024));
1550 show create table t1;
1551 Table Create Table
1552 t1 CREATE TABLE `t1` (
1553 `a` text DEFAULT NULL,
1554 KEY `a` (`a`(1024))
1555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
1556 drop table t1;
1557 CREATE TABLE t1 (
1558 a INT,
1559 b INT,
1560 KEY (b)
1561 ) ENGINE=InnoDB;
1562 INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
1563 START TRANSACTION;
1564 SELECT * FROM t1 WHERE b=20 FOR UPDATE;
1565 a b
1566 2 20
1567 connect conn2, localhost, root,,test;
1568 START TRANSACTION;
1569 SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
1570 a b
1571 1 10
1572 2 10
1573 ROLLBACK;
1574 disconnect conn2;
1575 connection default;
1576 ROLLBACK;
1577 DROP TABLE t1;
1578 CREATE TABLE t1(
1579 a INT,
1580 b INT NOT NULL,
1581 c INT NOT NULL,
1582 d INT,
1583 UNIQUE KEY (c,b)
1584 ) engine=innodb;
1585 INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
1586 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
1587 id select_type table type possible_keys key key_len ref rows Extra
1588 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1589 SELECT c,b,d FROM t1 GROUP BY c,b,d;
1590 c b d
1591 1 1 50
1592 3 1 4
1593 3 2 40
1594 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1595 id select_type table type possible_keys key key_len ref rows Extra
1596 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1597 SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
1598 c b d
1599 1 1 50
1600 3 1 4
1601 3 2 40
1602 EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
1603 id select_type table type possible_keys key key_len ref rows Extra
1604 1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort
1605 SELECT c,b,d FROM t1 ORDER BY c,b,d;
1606 c b d
1607 1 1 50
1608 3 1 4
1609 3 2 40
1610 EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
1611 id select_type table type possible_keys key key_len ref rows Extra
1612 1 SIMPLE t1 index NULL c 8 NULL 3
1613 SELECT c,b,d FROM t1 GROUP BY c,b;
1614 c b d
1615 1 1 50
1616 3 1 4
1617 3 2 40
1618 EXPLAIN SELECT c,b FROM t1 GROUP BY c,b;
1619 id select_type table type possible_keys key key_len ref rows Extra
1620 1 SIMPLE t1 index NULL c 8 NULL 3
1621 SELECT c,b FROM t1 GROUP BY c,b;
1622 c b
1623 1 1
1624 3 1
1625 3 2
1626 DROP TABLE t1;
1627 CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
1628 INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
1629 EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1630 id 1
1631 select_type SIMPLE
1632 table t1
1633 type ref
1634 possible_keys b
1635 key b
1636 key_len 5
1637 ref const
1638 rows 2
1639 Extra Using where; Using index
1640 SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
1641 a b
1642 2 2
1643 3 2
1644 EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1645 id 1
1646 select_type SIMPLE
1647 table t1
1648 type ref
1649 possible_keys b
1650 key b
1651 key_len 5
1652 ref const
1653 rows 2
1654 Extra Using where; Using index
1655 SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
1656 a b
1657 3 2
1658 2 2
1659 EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
1660 id 1
1661 select_type SIMPLE
1662 table t1
1663 type index
1664 possible_keys NULL
1665 key b
1666 key_len 5
1667 ref NULL
1668 rows 3
1669 Extra Using index
1670 SELECT * FROM t1 ORDER BY b ASC, a ASC;
1671 a b
1672 1 1
1673 2 2
1674 3 2
1675 EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
1676 id 1
1677 select_type SIMPLE
1678 table t1
1679 type index
1680 possible_keys NULL
1681 key b
1682 key_len 5
1683 ref NULL
1684 rows 3
1685 Extra Using index
1686 SELECT * FROM t1 ORDER BY b DESC, a DESC;
1687 a b
1688 3 2
1689 2 2
1690 1 1
1691 EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
1692 id 1
1693 select_type SIMPLE
1694 table t1
1695 type index
1696 possible_keys NULL
1697 key b
1698 key_len 5
1699 ref NULL
1700 rows 3
1701 Extra Using index; Using filesort
1702 SELECT * FROM t1 ORDER BY b ASC, a DESC;
1703 a b
1704 1 1
1705 3 2
1706 2 2
1707 EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
1708 id 1
1709 select_type SIMPLE
1710 table t1
1711 type index
1712 possible_keys NULL
1713 key b
1714 key_len 5
1715 ref NULL
1716 rows 3
1717 Extra Using index; Using filesort
1718 SELECT * FROM t1 ORDER BY b DESC, a ASC;
1719 a b
1720 2 2
1721 3 2
1722 1 1
1723 DROP TABLE t1;
1724
1725 #
1726 # Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
1727 #
1728
1729 # - prepare;
1730
1731 DROP TABLE IF EXISTS t1;
1732
1733 CREATE TABLE t1(c INT)
1734 ENGINE = InnoDB
1735 ROW_FORMAT = COMPACT;
1736
1737 # - initial check;
1738
1739 SELECT table_schema, table_name, row_format
1740 FROM INFORMATION_SCHEMA.TABLES
1741 WHERE table_schema = DATABASE() AND table_name = 't1';
1742 table_schema table_name row_format
1743 test t1 Compact
1744
1745 # - change ROW_FORMAT and check;
1746
1747 ALTER TABLE t1 ROW_FORMAT = REDUNDANT;
1748
1749 SELECT table_schema, table_name, row_format
1750 FROM INFORMATION_SCHEMA.TABLES
1751 WHERE table_schema = DATABASE() AND table_name = 't1';
1752 table_schema table_name row_format
1753 test t1 Redundant
1754
1755 # - that's it, cleanup.
1756
1757 DROP TABLE t1;
1758 create table t1(a char(10) not null, unique key aa(a(1)),
1759 b char(4) not null, unique key bb(b(4))) engine=innodb;
1760 desc t1;
1761 Field Type Null Key Default Extra
1762 a char(10) NO UNI NULL
1763 b char(4) NO PRI NULL
1764 show create table t1;
1765 Table Create Table
1766 t1 CREATE TABLE `t1` (
1767 `a` char(10) NOT NULL,
1768 `b` char(4) NOT NULL,
1769 UNIQUE KEY `bb` (`b`),
1770 UNIQUE KEY `aa` (`a`(1))
1771 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1772 drop table t1;
1773 CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
1774 INSERT INTO t1 VALUES
1775 (191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
1776 EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1777 id select_type table type possible_keys key key_len ref rows Extra
1778 1 SIMPLE t1 ref_or_null idx idx 5 const 3 Using index condition; Using filesort
1779 SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
1780 id type d
1781 191 member 1
1782 NULL member 3
1783 NULL member 4
1784 DROP TABLE t1;
1785 set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
1786 set global innodb_autoextend_increment=8;
1787 set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
1788 #
1789 # Bug #37830: ORDER BY ASC/DESC - no difference
1790 #
1791 CREATE TABLE t1 (a int, b int, c int, PRIMARY KEY (a), KEY t1_b (b))
1792 ENGINE=InnoDB;
1793 INSERT INTO t1 (a,b,c) VALUES (1,1,1), (2,1,1), (3,1,1), (4,1,1), (100,2,2);
1794 INSERT INTO t1 (a,b,c) SELECT a+4,b,c FROM t1;
1795 EXPLAIN SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
1796 id select_type table type possible_keys key key_len ref rows Extra
1797 1 SIMPLE t1 ref t1_b t1_b 5 const 8 Using where
1798 SELECT a, b, c FROM t1 WHERE b = 1 ORDER BY a DESC LIMIT 5;
1799 a b c
1800 8 1 1
1801 7 1 1
1802 6 1 1
1803 5 1 1
1804 4 1 1
1805 DROP TABLE t1;
1806 DROP TABLE IF EXISTS t1;
1807 CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
1808 CREATE INDEX i1 on t1 (a(3));
1809 SELECT * FROM t1 WHERE a = 'abcde';
1810 a
1811 DROP TABLE t1;
1812 #
1813 # BUG #26288: savepoint are not deleted on comit, if the transaction
1814 # was otherwise empty
1815 #
1816 BEGIN;
1817 SAVEPOINT s1;
1818 COMMIT;
1819 RELEASE SAVEPOINT s1;
1820 ERROR 42000: SAVEPOINT s1 does not exist
1821 BEGIN;
1822 SAVEPOINT s2;
1823 COMMIT;
1824 ROLLBACK TO SAVEPOINT s2;
1825 ERROR 42000: SAVEPOINT s2 does not exist
1826 BEGIN;
1827 SAVEPOINT s3;
1828 ROLLBACK;
1829 RELEASE SAVEPOINT s3;
1830 ERROR 42000: SAVEPOINT s3 does not exist
1831 BEGIN;
1832 SAVEPOINT s4;
1833 ROLLBACK;
1834 ROLLBACK TO SAVEPOINT s4;
1835 ERROR 42000: SAVEPOINT s4 does not exist
1836 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;
1837 SHOW CREATE TABLE t1;
1838 Table Create Table
1839 t1 CREATE TABLE `t1` (
1840 `f1` int(11) NOT NULL COMMENT 'My ID#',
1841 `f2` int(11) DEFAULT NULL,
1842 `f3` char(10) DEFAULT 'My ID#',
1843 PRIMARY KEY (`f1`),
1844 KEY `f2_ref` (`f2`),
1845 CONSTRAINT `f2_ref` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
1846 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
1847 DROP TABLE t1;
1848 #
1849 # Bug #36995: valgrind error in remove_const during subquery executions
1850 #
1851 create table t1 (a bit(1) not null,b int) engine=myisam;
1852 create table t2 (c int) engine=innodb;
1853 set @save_optimizer_switch=@@optimizer_switch;
1854 set @@optimizer_switch='in_to_exists=on,materialization=off';
1855 explain
1856 select b from t1 where a not in (select max(b) from t1,t2 group by a) group by a;
1857 id select_type table type possible_keys key key_len ref rows Extra
1858 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
1859 2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 0 Const row not found
1860 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1
1861 set optimizer_switch=@save_optimizer_switch;
1862 DROP TABLE t1,t2;
1863 End of 5.0 tests
1864 CREATE TABLE `t2` (
1865 `k` int(11) NOT NULL auto_increment,
1866 `a` int(11) default NULL,
1867 `c` int(11) default NULL,
1868 PRIMARY KEY (`k`),
1869 UNIQUE KEY `idx_1` (`a`)
1870 );
1871 insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1872 ifnull( c,
1873 0 ) + 1;
1874 insert into t2 ( a ) values ( 7 ) on duplicate key update c =
1875 ifnull( c,
1876 0 ) + 1;
1877 select last_insert_id();
1878 last_insert_id()
1879 2
1880 select * from t2;
1881 k a c
1882 1 6 NULL
1883 2 7 NULL
1884 insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1885 ifnull( c,
1886 0 ) + 1;
1887 select last_insert_id();
1888 last_insert_id()
1889 2
1890 select last_insert_id(0);
1891 last_insert_id(0)
1892 0
1893 insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1894 ifnull( c,
1895 0 ) + 1;
1896 select last_insert_id();
1897 last_insert_id()
1898 0
1899 select * from t2;
1900 k a c
1901 1 6 2
1902 2 7 NULL
1903 insert ignore into t2 values (null,6,1),(10,8,1);
1904 Warnings:
1905 Warning 1062 Duplicate entry '6' for key 'idx_1'
1906 select last_insert_id();
1907 last_insert_id()
1908 0
1909 insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
1910 Warnings:
1911 Warning 1062 Duplicate entry '6' for key 'idx_1'
1912 Warning 1062 Duplicate entry '8' for key 'idx_1'
1913 select last_insert_id();
1914 last_insert_id()
1915 11
1916 select * from t2;
1917 k a c
1918 1 6 2
1919 2 7 NULL
1920 10 8 1
1921 11 15 1
1922 12 20 1
1923 insert into t2 ( a ) values ( 6 ) on duplicate key update c =
1924 ifnull( c,
1925 0 ) + 1, k=last_insert_id(k);
1926 select last_insert_id();
1927 last_insert_id()
1928 1
1929 select * from t2;
1930 k a c
1931 1 6 3
1932 2 7 NULL
1933 10 8 1
1934 11 15 1
1935 12 20 1
1936 drop table t2;
1937 drop table if exists t1, t2;
1938 create table t1 (i int);
1939 alter table t1 modify i int default 1;
1940 alter table t1 modify i int default 2, rename t2;
1941 lock table t2 write;
1942 alter table t2 modify i int default 3;
1943 unlock tables;
1944 lock table t2 write;
1945 alter table t2 modify i int default 4, rename t1;
1946 unlock tables;
1947 drop table t1;
1948 drop table if exists t1;
1949 create table t1 (i int);
1950 insert into t1 values ();
1951 lock table t1 write;
1952 alter table t1 modify i int default 1;
1953 insert into t1 values ();
1954 select * from t1;
1955 i
1956 NULL
1957 1
1958 alter table t1 change i c char(10) default "Two";
1959 insert into t1 values ();
1960 select * from t1;
1961 c
1962 NULL
1963 1
1964 Two
1965 unlock tables;
1966 select * from t1;
1967 c
1968 NULL
1969 1
1970 Two
1971 drop tables t1;
1972 create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
1973 CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
1974 insert into t1(f1) values(1);
1975 select @a:=f2 from t1;
1976 @a:=f2
1977 #
1978 update t1 set f1=1;
1979 select @b:=f2 from t1;
1980 @b:=f2
1981 #
1982 select if(@a=@b,"ok","wrong");
1983 if(@a=@b,"ok","wrong")
1984 ok
1985 insert into t1(f1) values (1) on duplicate key update f1="1";
1986 select @b:=f2 from t1;
1987 @b:=f2
1988 #
1989 select if(@a=@b,"ok","wrong");
1990 if(@a=@b,"ok","wrong")
1991 ok
1992 insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
1993 select @b:=f2 from t1;
1994 @b:=f2
1995 #
1996 select if(@a=@b,"ok","wrong");
1997 if(@a=@b,"ok","wrong")
1998 ok
1999 drop table t1;
2000 connect con1,localhost,root,,;
2001 connect con2,localhost,root,,;
2002 SET SESSION AUTOCOMMIT = 0;
2003 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2004 set binlog_format=mixed;
2005 connection con1;
2006 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
2007 ENGINE = InnoDB;
2008 INSERT INTO t1 VALUES (1,2);
2009 # 1. test for locking:
2010 BEGIN;
2011 UPDATE t1 SET b = 12 WHERE a = 1;
2012 affected rows: 1
2013 info: Rows matched: 1 Changed: 1 Warnings: 0
2014 SELECT * FROM t1;
2015 a b
2016 1 12
2017 connection con2;
2018 UPDATE t1 SET b = 21 WHERE a = 1;
2019 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
2020 connection con1;
2021 SELECT * FROM t1;
2022 a b
2023 1 12
2024 ROLLBACK;
2025 connection con2;
2026 ROLLBACK;
2027 connection con1;
2028 # 2. test for serialized update:
2029 CREATE TABLE t2 (a INT);
2030 TRUNCATE t1;
2031 INSERT INTO t1 VALUES (1,'init');
2032 CREATE PROCEDURE p1()
2033 BEGIN
2034 # retry the UPDATE in case it times out the lock before con1 has time
2035 # to COMMIT.
2036 DECLARE do_retry INT DEFAULT 0;
2037 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET do_retry = 1;
2038 retry_loop:LOOP
2039 UPDATE t1 SET b = CONCAT(b, '+con2') WHERE a = 1;
2040 IF do_retry = 0 THEN
2041 LEAVE retry_loop;
2042 END IF;
2043 SET do_retry = 0;
2044 END LOOP;
2045 INSERT INTO t2 VALUES ();
2046 END|
2047 BEGIN;
2048 UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
2049 affected rows: 1
2050 info: Rows matched: 1 Changed: 1 Warnings: 0
2051 SELECT * FROM t1;
2052 a b
2053 1 init+con1
2054 connection con2;
2055 CALL p1;;
2056 connection con1;
2057 SELECT * FROM t1;
2058 a b
2059 1 init+con1
2060 COMMIT;
2061 SELECT * FROM t1;
2062 a b
2063 1 init+con1
2064 connection con2;
2065 SELECT * FROM t1;
2066 a b
2067 1 init+con1+con2
2068 COMMIT;
2069 connection con1;
2070 # 3. test for updated key column:
2071 TRUNCATE t1;
2072 TRUNCATE t2;
2073 INSERT INTO t1 VALUES (1,'init');
2074 BEGIN;
2075 UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
2076 affected rows: 1
2077 info: Rows matched: 1 Changed: 1 Warnings: 0
2078 SELECT * FROM t1;
2079 a b
2080 2 init+con1
2081 connection con2;
2082 CALL p1;;
2083 connection con1;
2084 SELECT * FROM t1;
2085 a b
2086 2 init+con1
2087 COMMIT;
2088 SELECT * FROM t1;
2089 a b
2090 2 init+con1
2091 connection con2;
2092 SELECT * FROM t1;
2093 a b
2094 2 init+con1
2095 connection default;
2096 disconnect con1;
2097 disconnect con2;
2098 DROP PROCEDURE p1;
2099 DROP TABLE t1, t2;
2100 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
2101 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2102 CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
2103 ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
2104 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2105 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
2106 ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
2107 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2108 CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
2109 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
2110 ALTER TABLE t2 DROP FOREIGN KEY c2;
2111 DROP TABLE t2;
2112 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2113 FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
2114 ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
2115 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2116 FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
2117 ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
2118 CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
2119 CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
2120 CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
2121 FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
2122 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
2123 SHOW CREATE TABLE t2;
2124 Table Create Table
2125 t2 CREATE TABLE `t2` (
2126 `c` int(11) NOT NULL,
2127 `d` int(11) NOT NULL,
2128 PRIMARY KEY (`c`,`d`),
2129 CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
2130 CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
2131 CONSTRAINT `f3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
2132 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
2133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
2134 DROP TABLE t2;
2135 DROP TABLE t1;
2136 create table t1 (a int auto_increment primary key) engine=innodb;
2137 set statement sql_mode = 'NO_ENGINE_SUBSTITUTION' for
2138 alter table t1 order by a;
2139 Warnings:
2140 Warning 1105 ORDER BY ignored as there is a user-defined clustered index in the table 't1'
2141 drop table t1;
2142 CREATE TABLE t1
2143 (vid integer NOT NULL,
2144 tid integer NOT NULL,
2145 idx integer NOT NULL,
2146 name varchar(128) NOT NULL,
2147 type varchar(128) NULL,
2148 PRIMARY KEY(idx, vid, tid),
2149 UNIQUE(vid, tid, name)
2150 ) ENGINE=InnoDB;
2151 INSERT INTO t1 VALUES
2152 (1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
2153 (5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
2154 (4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
2155 (4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
2156 (2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
2157 EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
2158 id select_type table type possible_keys key key_len ref rows Extra
2159 1 SIMPLE t1 index NULL PRIMARY 12 NULL 16 Using where
2160 SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
2161 vid tid idx name type
2162 3 1 4 c_extra NULL
2163 3 1 3 c2 NULL
2164 3 1 2 c1 NULL
2165 3 1 1 pk NULL
2166 DROP TABLE t1;
2167 #
2168 # Bug #44290: explain crashes for subquery with distinct in
2169 # SQL_SELECT::test_quick_select
2170 # (reproduced only with InnoDB tables)
2171 #
2172 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, KEY (c3), KEY (c2, c3))
2173 ENGINE=InnoDB;
2174 INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
2175 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2176 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2177 1
2178 1
2179 EXPLAIN
2180 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2181 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2182 id select_type table type possible_keys key key_len ref rows Extra
2183 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2184 2 DERIVED t1 ref c3,c2 c3 5 const 2 Using where
2185 DROP TABLE t1;
2186 CREATE TABLE t1 (c1 REAL, c2 REAL, c3 REAL, KEY (c3), KEY (c2, c3))
2187 ENGINE=InnoDB;
2188 INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
2189 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2190 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2191 1
2192 1
2193 EXPLAIN
2194 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2195 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2196 id select_type table type possible_keys key key_len ref rows Extra
2197 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2198 2 DERIVED t1 ref c3,c2 c3 9 const 2 Using where
2199 DROP TABLE t1;
2200 CREATE TABLE t1 (c1 DECIMAL(12,2), c2 DECIMAL(12,2), c3 DECIMAL(12,2),
2201 KEY (c3), KEY (c2, c3))
2202 ENGINE=InnoDB;
2203 INSERT INTO t1 VALUES (1,1,1), (1,1,1), (1,1,2), (1,1,1), (1,1,2);
2204 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2205 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2206 1
2207 1
2208 EXPLAIN
2209 SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
2210 FROM t1 WHERE c2 IN (1, 1) AND c3 = 2 GROUP BY c2) x;
2211 id select_type table type possible_keys key key_len ref rows Extra
2212 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2
2213 2 DERIVED t1 ref c3,c2 c3 7 const 2 Using where
2214 DROP TABLE t1;
2215 End of 5.1 tests
2216 #
2217 # Bug#43600: Incorrect type conversion caused wrong result.
2218 #
2219 CREATE TABLE t1 (
2220 a int NOT NULL
2221 ) engine= innodb;
2222 CREATE TABLE t2 (
2223 a int NOT NULL,
2224 b int NOT NULL,
2225 filler char(100) DEFAULT NULL,
2226 KEY a (a,b)
2227 ) engine= innodb;
2228 insert into t1 values (0),(1),(2),(3),(4);
2229 insert into t2 select A.a + 10 *B.a, 1, 'filler' from t1 A, t1 B;
2230 explain select * from t1, t2 where t2.a=t1.a and t2.b + 1;
2231 id select_type table type possible_keys key key_len ref rows Extra
2232 1 SIMPLE t1 ALL NULL NULL NULL NULL 5
2233 1 SIMPLE t2 ref a a 4 test.t1.a 1 Using index condition
2234 select * from t1, t2 where t2.a=t1.a and t2.b + 1;
2235 a a b filler
2236 0 0 1 filler
2237 1 1 1 filler
2238 2 2 1 filler
2239 3 3 1 filler
2240 4 4 1 filler
2241 drop table t1,t2;
2242 # End of test case for the bug#43600
2243 #
2244 # Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
2245 #
2246 # Check that a TRUNCATE TABLE statement, needing an exclusive meta
2247 # data lock, waits for a shared metadata lock owned by a concurrent
2248 # transaction.
2249 #
2250 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
2251 INSERT INTO t1 VALUES (1),(2),(3);
2252 BEGIN;
2253 SELECT * FROM t1 ORDER BY a;
2254 a
2255 1
2256 2
2257 3
2258 connect con1, localhost, root,,;
2259 TRUNCATE TABLE t1;;
2260 connection default;
2261 SELECT * FROM t1 ORDER BY a;
2262 a
2263 1
2264 2
2265 3
2266 ROLLBACK;
2267 connection con1;
2268 # Reaping TRUNCATE TABLE
2269 SELECT * FROM t1;
2270 a
2271 disconnect con1;
2272 connection default;
2273 DROP TABLE t1;
2274 drop table if exists t1, t2, t3;
2275 #
2276 # BUG#35850: Performance regression in 5.1.23/5.1.24
2277 #
2278 create table t1(a int);
2279 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
2280 create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
2281 insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
2282 # this must use key 'a', not PRIMARY:
2283 explain select a from t2 where a=b;
2284 id select_type table type possible_keys key key_len ref rows Extra
2285 1 SIMPLE t2 index NULL a 10 NULL # Using where; Using index
2286 drop table t1, t2;
2287 #
2288 # Bug #40360: Binlog related errors with binlog off
2289 #
2290 SET SESSION BINLOG_FORMAT=STATEMENT;
2291 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
2292 select @@session.sql_log_bin, @@session.binlog_format, @@session.transaction_isolation;
2293 @@session.sql_log_bin 1
2294 @@session.binlog_format STATEMENT
2295 @@session.transaction_isolation READ-COMMITTED
2296 CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
2297 INSERT INTO t1 VALUES(1);
2298 DROP TABLE t1;
2299 #
2300 # Bug#37284 Crash in Field_string::type()
2301 #
2302 DROP TABLE IF EXISTS t1;
2303 CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
2304 CREATE INDEX i1 on t1 (a(3));
2305 SELECT * FROM t1 WHERE a = 'abcde';
2306 a
2307 DROP TABLE t1;
2308 #
2309 # Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
2310 # requested column
2311 #
2312 CREATE TABLE foo (a int, b int, c char(10),
2313 PRIMARY KEY (c(3)),
2314 KEY b (b)
2315 ) engine=innodb;
2316 CREATE TABLE foo2 (a int, b int, c char(10),
2317 PRIMARY KEY (c),
2318 KEY b (b)
2319 ) engine=innodb;
2320 CREATE TABLE bar (a int, b int, c char(10),
2321 PRIMARY KEY (c(3)),
2322 KEY b (b)
2323 ) engine=myisam;
2324 INSERT INTO foo VALUES
2325 (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
2326 (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
2327 INSERT INTO bar SELECT * FROM foo;
2328 INSERT INTO foo2 SELECT * FROM foo;
2329 ANALYZE TABLE bar;
2330 ANALYZE TABLE foo;
2331 ANALYZE TABLE foo2;
2332 EXPLAIN SELECT c FROM bar WHERE b>2;;
2333 id 1
2334 select_type SIMPLE
2335 table bar
2336 type range
2337 possible_keys b
2338 key b
2339 key_len 5
2340 ref NULL
2341 rows 5
2342 Extra Using index condition
2343 EXPLAIN SELECT c FROM foo WHERE b>2;;
2344 id 1
2345 select_type SIMPLE
2346 table foo
2347 type range
2348 possible_keys b
2349 key b
2350 key_len 5
2351 ref NULL
2352 rows 5
2353 Extra Using index condition
2354 EXPLAIN SELECT c FROM foo2 WHERE b>2;;
2355 id 1
2356 select_type SIMPLE
2357 table foo2
2358 type range
2359 possible_keys b
2360 key b
2361 key_len 5
2362 ref NULL
2363 rows 5
2364 Extra Using where; Using index
2365 EXPLAIN SELECT c FROM bar WHERE c>2;;
2366 id 1
2367 select_type SIMPLE
2368 table bar
2369 type ALL
2370 possible_keys PRIMARY
2371 key NULL
2372 key_len NULL
2373 ref NULL
2374 rows 6
2375 Extra Using where
2376 Warnings:
2377 Level Note
2378 Code 1105
2379 Message Cannot use key `PRIMARY` part[0] for lookup: `test`.`bar`.`c` of type `char` > "2" of type `int`
2380 EXPLAIN SELECT c FROM foo WHERE c>2;;
2381 id 1
2382 select_type SIMPLE
2383 table foo
2384 type ALL
2385 possible_keys PRIMARY
2386 key NULL
2387 key_len NULL
2388 ref NULL
2389 rows 6
2390 Extra Using where
2391 Warnings:
2392 Level Note
2393 Code 1105
2394 Message Cannot use key `PRIMARY` part[0] for lookup: `test`.`foo`.`c` of type `char` > "2" of type `int`
2395 EXPLAIN SELECT c FROM foo2 WHERE c>2;;
2396 id 1
2397 select_type SIMPLE
2398 table foo2
2399 type index
2400 possible_keys PRIMARY
2401 key b
2402 key_len 5
2403 ref NULL
2404 rows 6
2405 Extra Using where; Using index
2406 Warnings:
2407 Level Note
2408 Code 1105
2409 Message Cannot use key `PRIMARY` part[0] for lookup: `test`.`foo2`.`c` of type `char` > "2" of type `int`
2410 DROP TABLE foo, bar, foo2;
2411 #
2412 # Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
2413 #
2414 DROP TABLE IF EXISTS t1,t3,t2;
2415 DROP FUNCTION IF EXISTS f1;
2416 CREATE FUNCTION f1() RETURNS VARCHAR(250)
2417 BEGIN
2418 return 'hhhhhhh' ;
2419 END|
2420 CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
2421 BEGIN WORK;
2422 CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
2423 CREATE TEMPORARY TABLE t3 LIKE t2;
2424 INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
2425 SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
2426 PREPARE stmt1 FROM @stmt;
2427 SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
2428 PREPARE stmt3 FROM @stmt;
2429 EXECUTE stmt1;
2430 COMMIT;
2431 DEALLOCATE PREPARE stmt1;
2432 DEALLOCATE PREPARE stmt3;
2433 DROP TABLE t1,t3,t2;
2434 DROP FUNCTION f1;
2435 #
2436 # Bug#37016: TRUNCATE TABLE removes some rows but not all
2437 #
2438 DROP TABLE IF EXISTS t1,t2;
2439 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
2440 CREATE TABLE t2 (id INT PRIMARY KEY,
2441 t1_id INT, INDEX par_ind (t1_id),
2442 FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
2443 INSERT INTO t1 VALUES (1),(2);
2444 INSERT INTO t2 VALUES (3,2);
2445 SET AUTOCOMMIT = 0;
2446 START TRANSACTION;
2447 TRUNCATE TABLE t1;
2448 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
2449 SELECT * FROM t1;
2450 id
2451 1
2452 2
2453 COMMIT;
2454 SELECT * FROM t1;
2455 id
2456 1
2457 2
2458 START TRANSACTION;
2459 TRUNCATE TABLE t1;
2460 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
2461 SELECT * FROM t1;
2462 id
2463 1
2464 2
2465 ROLLBACK;
2466 SELECT * FROM t1;
2467 id
2468 1
2469 2
2470 SET AUTOCOMMIT = 1;
2471 START TRANSACTION;
2472 SELECT * FROM t1;
2473 id
2474 1
2475 2
2476 COMMIT;
2477 TRUNCATE TABLE t1;
2478 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
2479 SELECT * FROM t1;
2480 id
2481 1
2482 2
2483 DELETE FROM t2 WHERE id = 3;
2484 START TRANSACTION;
2485 SELECT * FROM t1;
2486 id
2487 1
2488 2
2489 TRUNCATE TABLE t1;
2490 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t1_id`) REFERENCES `test`.`t1` (`id`))
2491 ROLLBACK;
2492 SELECT * FROM t1;
2493 id
2494 1
2495 2
2496 TRUNCATE TABLE t2;
2497 DROP TABLE t2;
2498 DROP TABLE t1;
2499 #
2500 # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
2501 #
2502 CREATE TABLE t1 (
2503 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
2504 PRIMARY KEY (id)
2505 ) ENGINE=InnoDB;
2506 CREATE TABLE t2 (
2507 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
2508 aid INT UNSIGNED NOT NULL,
2509 PRIMARY KEY (id),
2510 FOREIGN KEY (aid) REFERENCES t1 (id)
2511 ) ENGINE=InnoDB;
2512 CREATE TABLE t3 (
2513 bid INT UNSIGNED NOT NULL,
2514 FOREIGN KEY (bid) REFERENCES t2 (id)
2515 ) ENGINE=InnoDB;
2516 CREATE TABLE t4 (
2517 a INT
2518 ) ENGINE=InnoDB;
2519 CREATE TABLE t5 (
2520 a INT
2521 ) ENGINE=InnoDB;
2522 INSERT INTO t1 (id) VALUES (1);
2523 INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
2524 INSERT INTO t3 (bid) VALUES (1);
2525 INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
2526 INSERT INTO t5 VALUES (1);
2527 DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
2528 DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
2529 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`))
2530 DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
2531 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`))
2532 DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
2533 DROP TABLE t3;
2534 DROP TABLE t2;
2535 DROP TABLE t1;
2536 DROP TABLES t4,t5;
2537 # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
2538 # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
2539 # transactional tables.
2540 #
2541 CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
2542 CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
2543 CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
2544 CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
2545 FOREIGN KEY (t1i) REFERENCES t1(i))
2546 ENGINE=InnoDB;
2547 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2548 BEGIN
2549 SET @b:='EXECUTED TRIGGER';
2550 INSERT INTO t2 VALUES (@b);
2551 SET @a:= error_happens_here;
2552 END||
2553 SET @b:="";
2554 SET @a:="";
2555 INSERT INTO t1 VALUES (1),(2),(3),(4);
2556 INSERT INTO t3 SELECT * FROM t1;
2557 ** An error in a trigger causes rollback of the statement.
2558 DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2559 ERROR 42S22: Unknown column 'error_happens_here' in 'SET'
2560 SELECT @a,@b;
2561 @a @b
2562 EXECUTED TRIGGER
2563 SELECT * FROM t2;
2564 a
2565 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2566 i i
2567 1 1
2568 2 2
2569 3 3
2570 4 4
2571 ** Same happens with the IGNORE option
2572 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2573 ERROR 42S22: Unknown column 'error_happens_here' in 'SET'
2574 SELECT * FROM t2;
2575 a
2576 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2577 i i
2578 1 1
2579 2 2
2580 3 3
2581 4 4
2582 **
2583 ** The following is an attempt to demonstrate
2584 ** error handling inside a row iteration.
2585 **
2586 DROP TRIGGER trg;
2587 DELETE FROM t1;
2588 DELETE FROM t2;
2589 DELETE FROM t3;
2590 INSERT INTO t1 VALUES (1),(2),(3),(4);
2591 INSERT INTO t3 VALUES (1),(2),(3),(4);
2592 INSERT INTO t4 VALUES (3,3),(4,4);
2593 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2594 BEGIN
2595 SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
2596 INSERT INTO t2 VALUES (@b);
2597 END||
2598 ** DELETE is prevented by foreign key constrains but errors are silenced.
2599 ** The AFTER trigger isn't fired.
2600 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2601 ** Tables are modified by best effort:
2602 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2603 i i
2604 3 3
2605 4 4
2606 ** The AFTER trigger was only executed on successful rows:
2607 SELECT * FROM t2;
2608 a
2609 EXECUTED TRIGGER FOR ROW 1
2610 EXECUTED TRIGGER FOR ROW 2
2611 DROP TRIGGER trg;
2612 **
2613 ** Induce an error midway through an AFTER-trigger
2614 **
2615 DELETE FROM t4;
2616 DELETE FROM t1;
2617 DELETE FROM t3;
2618 INSERT INTO t1 VALUES (1),(2),(3),(4);
2619 INSERT INTO t3 VALUES (1),(2),(3),(4);
2620 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
2621 BEGIN
2622 SET @a:= @a+1;
2623 IF @a > 2 THEN
2624 INSERT INTO t4 VALUES (5,5);
2625 END IF;
2626 END||
2627 SET @a:=0;
2628 ** Errors in the trigger causes the statement to abort.
2629 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
2630 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`))
2631 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
2632 i i
2633 1 1
2634 2 2
2635 3 3
2636 4 4
2637 SELECT * FROM t4;
2638 i t1i
2639 DROP TRIGGER trg;
2640 DROP TABLE t4;
2641 DROP TABLE t1;
2642 DROP TABLE t2;
2643 DROP TABLE t3;
2644 #
2645 # Bug#43580: Issue with Innodb on multi-table update
2646 #
2647 CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
2648 CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
2649 CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
2650 CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
2651 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
2652 INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2653 INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
2654 INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
2655 UPDATE t2 straight_join t1 SET t1.a = t1.a + 100, t2.b = t1.a + 10
2656 WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
2657 SELECT * FROM t2;
2658 a b
2659 1 1
2660 2 12
2661 3 13
2662 4 14
2663 5 5
2664 UPDATE t4 straight_join t3 SET t3.a = t3.a + 100, t4.b = t3.a + 10
2665 WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
2666 SELECT * FROM t4;
2667 a b
2668 1 1
2669 2 12
2670 3 13
2671 4 14
2672 5 5
2673 DROP TABLE t1, t2, t3, t4;
2674 #
2675 # Bug#44886: SIGSEGV in test_if_skip_sort_order() -
2676 # uninitialized variable used as subscript
2677 #
2678 CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
2679 ENGINE=InnoDB;
2680 INSERT INTO t1 VALUES (1,1,1,0);
2681 CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
2682 INSERT INTO t2 VALUES (1,1,2);
2683 CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
2684 INSERT INTO t3 VALUES (1, 1);
2685 SELECT * FROM t1, t2, t3
2686 WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
2687 GROUP BY t1.b;
2688 a b c d a b e a b
2689 1 1 1 0 1 1 2 1 1
2690 DROP TABLE t1, t2, t3;
2691 #
2692 # Bug #45828: Optimizer won't use partial primary key if another
2693 # index can prevent filesort
2694 #
2695 CREATE TABLE `t1` (
2696 c1 int NOT NULL,
2697 c2 int NOT NULL,
2698 c3 int NOT NULL,
2699 PRIMARY KEY (c1,c2),
2700 KEY (c3)
2701 ) ENGINE=InnoDB;
2702 INSERT INTO t1 VALUES (5,2,1246276747);
2703 INSERT INTO t1 VALUES (2,1,1246281721);
2704 INSERT INTO t1 VALUES (7,3,1246281756);
2705 INSERT INTO t1 VALUES (4,2,1246282139);
2706 INSERT INTO t1 VALUES (3,1,1246282230);
2707 INSERT INTO t1 VALUES (1,0,1246282712);
2708 INSERT INTO t1 VALUES (8,3,1246282765);
2709 INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
2710 INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
2711 INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
2712 INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
2713 INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
2714 INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
2715 ANALYZE TABLE t1;
2716 Table Op Msg_type Msg_text
2717 test.t1 analyze status Engine-independent statistics collected
2718 test.t1 analyze status OK
2719 SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2720 c1 c2 c3
2721 EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2722 id select_type table type possible_keys key key_len ref rows Extra
2723 1 SIMPLE t1 ref PRIMARY,c3 PRIMARY 4 const 1 Using where; Using filesort
2724 EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2725 id select_type table type possible_keys key key_len ref rows Extra
2726 1 SIMPLE t1 ref PRIMARY PRIMARY 4 const 1 Using where; Using filesort
2727 CREATE TABLE t2 (
2728 c1 int NOT NULL,
2729 c2 int NOT NULL,
2730 c3 int NOT NULL,
2731 KEY (c1,c2),
2732 KEY (c3)
2733 ) ENGINE=InnoDB;
2734 explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
2735 id select_type table type possible_keys key key_len ref rows Extra
2736 1 SIMPLE t2 ref c1,c3 c1 4 const 1 Using where; Using filesort
2737 DROP TABLE t1,t2;
2738 #
2739 # 36259: Optimizing with ORDER BY
2740 #
2741 CREATE TABLE t1 (
2742 a INT NOT NULL AUTO_INCREMENT,
2743 b INT NOT NULL,
2744 c INT NOT NULL,
2745 d VARCHAR(5),
2746 e INT NOT NULL,
2747 PRIMARY KEY (a), KEY i2 (b,c,d)
2748 ) ENGINE=InnoDB;
2749 INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
2750 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2751 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2752 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2753 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2754 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2755 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
2756 ANALYZE TABLE t1;
2757 EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
2758 id select_type table type possible_keys key key_len ref rows Extra
2759 1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
2760 EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
2761 id select_type table type possible_keys key key_len ref rows Extra
2762 1 SIMPLE t1 ref i2 i2 8 const,const 1 Using where; Using filesort
2763 EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
2764 id select_type table type possible_keys key key_len ref rows Extra
2765 1 SIMPLE t1 index NULL PRIMARY 4 NULL {checked} Using where
2766 DROP TABLE t1;
2767 #
2768 # Bug #47963: Wrong results when index is used
2769 #
2770 CREATE TABLE t1(
2771 a VARCHAR(5) NOT NULL,
2772 b VARCHAR(5) NOT NULL,
2773 c DATETIME NOT NULL,
2774 KEY (c)
2775 ) ENGINE=InnoDB;
2776 INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
2777 SELECT * FROM t1 WHERE a = 'TEST' AND
2778 c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
2779 a b c
2780 TEST TEST 2009-10-09 00:00:00
2781 SELECT * FROM t1 WHERE a = 'TEST' AND
2782 c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
2783 a b c
2784 TEST TEST 2009-10-09 00:00:00
2785 SELECT * FROM t1 WHERE a = 'TEST' AND
2786 c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
2787 a b c
2788 TEST TEST 2009-10-09 00:00:00
2789 SELECT * FROM t1 WHERE a = 'TEST' AND
2790 c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
2791 a b c
2792 TEST TEST 2009-10-09 00:00:00
2793 SELECT * FROM t1 WHERE a = 'TEST' AND
2794 c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
2795 a b c
2796 TEST TEST 2009-10-09 00:00:00
2797 SELECT * FROM t1 WHERE a = 'TEST' AND
2798 c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
2799 a b c
2800 TEST TEST 2009-10-09 00:00:00
2801 SELECT * FROM t1 WHERE a = 'TEST' AND
2802 c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
2803 a b c
2804 EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
2805 c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
2806 id select_type table type possible_keys key key_len ref rows Extra
2807 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
2808 DROP TABLE t1;
2809 #
2810 # Bug #46175: NULL read_view and consistent read assertion
2811 #
2812 CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
2813 CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
2814 INSERT INTO t1 VALUES (),();
2815 INSERT INTO t2 VALUES (),();
2816 CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
2817 WHERE b =(SELECT a FROM t1 LIMIT 1);
2818 CONNECT con1, localhost, root,,;
2819 connection default;
2820 CREATE PROCEDURE p1(num INT)
2821 BEGIN
2822 DECLARE i INT DEFAULT 0;
2823 REPEAT
2824 SHOW CREATE VIEW v1;
2825 SET i:=i+1;
2826 UNTIL i>num END REPEAT;
2827 END|
2828 # Should not crash
2829 # Should not crash
2830 disconnect con1;
2831 DROP PROCEDURE p1;
2832 DROP VIEW v1;
2833 DROP TABLE t1,t2;
2834 #
2835 # Bug #49324: more valgrind errors in test_if_skip_sort_order
2836 #
2837 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
2838 # should not cause valgrind warnings
2839 SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
2840 1
2841 DROP TABLE t1;
2842 #
2843 # Bug#50843: Filesort used instead of clustered index led to
2844 # performance degradation.
2845 #
2846 create table t1(f1 int not null primary key, f2 int) engine=innodb;
2847 create table t2(f1 int not null, key (f1)) engine=innodb;
2848 insert into t1 values (1,1),(2,2),(3,3);
2849 insert into t2 values (1),(2),(3);
2850 analyze table t1;
2851 analyze table t2;
2852 explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
2853 id select_type table type possible_keys key key_len ref rows Extra
2854 1 SIMPLE t1 index NULL PRIMARY 4 NULL 3
2855 1 SIMPLE t2 ref f1 f1 4 test.t1.f1 1 Using index
2856 drop table t1,t2;
2857 #
2858 #
2859 # Bug #39653: find_shortest_key in sql_select.cc does not consider
2860 # clustered primary keys
2861 #
2862 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
2863 KEY (b,c)) ENGINE=INNODB;
2864 INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
2865 (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
2866 (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
2867 (11,11,11,11,11,11);
2868 ANALYZE TABLE t1;
2869 Table Op Msg_type Msg_text
2870 test.t1 analyze status Engine-independent statistics collected
2871 test.t1 analyze status OK
2872 EXPLAIN SELECT COUNT(*) FROM t1;
2873 id 1
2874 select_type SIMPLE
2875 table t1
2876 type index
2877 possible_keys NULL
2878 key b
2879 key_len 10
2880 ref NULL
2881 rows 10
2882 Extra Using index
2883 DROP TABLE t1;
2884 #
2885 # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
2886 # corrupt definition at engine
2887 #
2888 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
2889 ENGINE=InnoDB;
2890 ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
2891 SHOW INDEXES FROM t1;;
2892 Table t1
2893 Non_unique 0
2894 Key_name k
2895 Seq_in_index 1
2896 Column_name a
2897 Collation A
2898 Cardinality 0
2899 Sub_part NULL
2900 Packed NULL
2901 Null
2902 Index_type BTREE
2903 Comment
2904 Index_comment
2905 Ignored NO
2906 Table t1
2907 Non_unique 0
2908 Key_name k
2909 Seq_in_index 2
2910 Column_name b
2911 Collation A
2912 Cardinality 0
2913 Sub_part NULL
2914 Packed NULL
2915 Null
2916 Index_type BTREE
2917 Comment
2918 Index_comment
2919 Ignored NO
2920 DROP TABLE t1;
2921 #
2922 # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when
2923 # JOINed during an UPDATE
2924 #
2925 CREATE TABLE t1 (d INT) ENGINE=InnoDB;
2926 CREATE TABLE t2 (a INT, b INT,
2927 c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
2928 ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
2929 # set up our data elements
2930 INSERT INTO t1 (d) VALUES (1);
2931 INSERT INTO t2 (a,b) VALUES (1,1);
2932 SELECT SECOND(c) INTO @bug47453 FROM t2;
2933 SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2934 SECOND(c)-@bug47453
2935 0
2936 UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
2937 SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2938 SECOND(c)-@bug47453
2939 0
2940 SELECT SLEEP(1);
2941 SLEEP(1)
2942 0
2943 UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
2944 # should be 0
2945 SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
2946 SECOND(c)-@bug47453
2947 0
2948 DROP TABLE t1, t2;
2949 #
2950 # Bug #53334: wrong result for outer join with impossible ON condition
2951 # (see the same test case for MyISAM in join.test)
2952 #
2953 CREATE TABLE t1 (id INT PRIMARY KEY);
2954 CREATE TABLE t2 (id INT);
2955 INSERT INTO t1 VALUES (75);
2956 INSERT INTO t1 VALUES (79);
2957 INSERT INTO t1 VALUES (78);
2958 INSERT INTO t1 VALUES (77);
2959 REPLACE INTO t1 VALUES (76);
2960 REPLACE INTO t1 VALUES (76);
2961 INSERT INTO t1 VALUES (104);
2962 INSERT INTO t1 VALUES (103);
2963 INSERT INTO t1 VALUES (102);
2964 INSERT INTO t1 VALUES (101);
2965 INSERT INTO t1 VALUES (105);
2966 INSERT INTO t1 VALUES (106);
2967 INSERT INTO t1 VALUES (107);
2968 INSERT INTO t2 VALUES (107),(75),(1000);
2969 SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
2970 WHERE t2.id=75 AND t1.id IS NULL;
2971 id id
2972 NULL 75
2973 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
2974 WHERE t2.id=75 AND t1.id IS NULL;
2975 id select_type table type possible_keys key key_len ref rows Extra
2976 1 SIMPLE t1 const PRIMARY NULL NULL NULL 0 Impossible ON condition
2977 1 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where
2978 DROP TABLE t1,t2;
2979 #
2980 # Bug#38999 valgrind warnings for update statement in function compare_record()
2981 #
2982 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
2983 CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
2984 INSERT INTO t1 values (1),(2),(3),(4),(5);
2985 INSERT INTO t2 values (1);
2986 SELECT * FROM t1 WHERE a = 2;
2987 a
2988 2
2989 UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
2990 DROP TABLE t1,t2;
2991 #
2992 # Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
2993 #
2994 CREATE TABLE t1 (a INT, b INT, c INT, d INT,
2995 PRIMARY KEY(a,b,c), KEY(b,d))
2996 ENGINE=InnoDB;
2997 INSERT INTO t1 VALUES (0, 77, 1, 3);
2998 UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
2999 DROP TABLE t1;
3000 #
3001 # Bug#50389 Using intersect does not return all rows
3002 #
3003 CREATE TABLE t1 (
3004 f1 INT(10) NOT NULL,
3005 f2 INT(10),
3006 f3 INT(10),
3007 f4 TINYINT(4),
3008 f5 VARCHAR(50),
3009 PRIMARY KEY (f1),
3010 KEY idx1 (f2,f5,f4),
3011 KEY idx2 (f2,f4)
3012 ) ENGINE=InnoDB;
3013 LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
3014 ANALYZE TABLE t1;
3015 set @tmp_innodb_mysql= @@optimizer_switch;
3016 set optimizer_switch='extended_keys=off';
3017 SELECT * FROM t1 WHERE f1 IN
3018 (3305028,3353871,3772880,3346860,4228206,3336022,
3019 3470988,3305175,3329875,3817277,3856380,3796193,
3020 3784744,4180925,4559596,3963734,3856391,4494153)
3021 AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
3022 f1 f2 f3 f4 f5
3023 3305175 1221457 0 0 abcdefghijklmnopwrst
3024 3329875 1221457 1382427 0 abcdefghijklmnopwrst
3025 3336022 1221457 0 0 abcdefghijklmnopwrst
3026 3346860 1221457 0 0 abcdefghijklmnopwrst
3027 3772880 1221457 0 0 abcdefghijklmnopwrst
3028 3784744 1221457 1382427 0 abcdefghijklmnopwrst
3029 3796193 1221457 0 0 abcdefghijklmnopwrst
3030 4228206 1221457 0 0 abcdefghijklmnopwrst
3031 4494153 1221457 0 0 abcdefghijklmnopwrst
3032 4559596 1221457 0 0 abcdefghijklmnopwrst
3033 EXPLAIN SELECT * FROM t1 WHERE f1 IN
3034 (3305028,3353871,3772880,3346860,4228206,3336022,
3035 3470988,3305175,3329875,3817277,3856380,3796193,
3036 3784744,4180925,4559596,3963734,3856391,4494153)
3037 AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
3038 id select_type table type possible_keys key key_len ref rows Extra
3039 1 SIMPLE t1 index_merge PRIMARY,idx1,idx2 idx2,idx1,PRIMARY 7,210,4 NULL 1 Using intersect(idx2,idx1,PRIMARY); Using where
3040 set optimizer_switch=@tmp_innodb_mysql;
3041 DROP TABLE t1;
3042 #
3043 # Bug#51431 Wrong sort order after import of dump file
3044 #
3045 CREATE TABLE t1 (
3046 f1 INT(11) NOT NULL,
3047 f2 int(11) NOT NULL,
3048 f3 int(11) NOT NULL,
3049 f4 tinyint(1) NOT NULL,
3050 PRIMARY KEY (f1),
3051 UNIQUE KEY (f2, f3),
3052 KEY (f4)
3053 ) ENGINE=InnoDB STATS_PERSISTENT=0;
3054 INSERT INTO t1 VALUES
3055 (1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
3056 (6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
3057 (16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
3058 (21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
3059 (26,1,9921,1), (27,1,9922,1);
3060 FLUSH TABLES;
3061 SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
3062 ORDER BY f1 DESC LIMIT 5;
3063 f1 f2 f3 f4
3064 27 1 9922 1
3065 26 1 9921 1
3066 25 1 9920 1
3067 24 1 9919 1
3068 23 1 9918 1
3069 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
3070 ORDER BY f1 DESC LIMIT 5;
3071 id select_type table type possible_keys key key_len ref rows Extra
3072 1 SIMPLE t1 index f2,f4 PRIMARY 4 NULL 5 Using where
3073 DROP TABLE t1;
3074 #
3075 # Bug#54117 crash in thr_multi_unlock, temporary table
3076 #
3077 CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;
3078 LOCK TABLES t1 READ;
3079 ALTER TABLE t1 COMMENT 'test';
3080 UNLOCK TABLES;
3081 DROP TABLE t1;
3082 #
3083 # Bug#55656: mysqldump can be slower after bug #39653 fix
3084 #
3085 CREATE TABLE t1 (a INT , b INT, c INT, d INT,
3086 KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB STATS_PERSISTENT=0;
3087 INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
3088 EXPLAIN SELECT COUNT(*) FROM t1;
3089 id 1
3090 select_type SIMPLE
3091 table t1
3092 type index
3093 possible_keys NULL
3094 key b
3095 key_len 4
3096 ref NULL
3097 rows 3
3098 Extra Using index
3099 DROP INDEX b ON t1;
3100 CREATE INDEX b ON t1(a,b);
3101 EXPLAIN SELECT COUNT(*) FROM t1;
3102 id 1
3103 select_type SIMPLE
3104 table t1
3105 type index
3106 possible_keys NULL
3107 key b
3108 key_len 8
3109 ref NULL
3110 rows 3
3111 Extra Using index
3112 DROP INDEX b ON t1;
3113 CREATE INDEX b ON t1(a,b,c);
3114 EXPLAIN SELECT COUNT(*) FROM t1;
3115 id 1
3116 select_type SIMPLE
3117 table t1
3118 type index
3119 possible_keys NULL
3120 key b
3121 key_len 13
3122 ref NULL
3123 rows 3
3124 Extra Using index
3125 DROP INDEX b ON t1;
3126 CREATE INDEX b ON t1(a,b,c,d);
3127 EXPLAIN SELECT COUNT(*) FROM t1;
3128 id 1
3129 select_type SIMPLE
3130 table t1
3131 type index
3132 possible_keys NULL
3133 key b
3134 key_len 18
3135 ref NULL
3136 rows 3
3137 Extra Using index
3138 DROP TABLE t1;
3139 #
3140 # Bug#55826: create table .. select crashes with when KILL_BAD_DATA
3141 # is returned
3142 #
3143 CREATE TABLE t1(a INT) ENGINE=innodb;
3144 INSERT INTO t1 VALUES (0);
3145 SET SQL_MODE='STRICT_ALL_TABLES';
3146 CREATE TABLE t2
3147 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
3148 ERROR 22007: Truncated incorrect datetime value: ''
3149 DROP TABLE t1;
3150 SET SQL_MODE=DEFAULT;
3151 #
3152 # Bug#56862 Execution of a query that uses index merge returns a wrong result
3153 #
3154 CREATE TABLE t1 (
3155 pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
3156 a int,
3157 b int,
3158 INDEX idx(a))
3159 ENGINE=INNODB;
3160 INSERT INTO t1(a,b) VALUES
3161 (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
3162 (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
3163 (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
3164 (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
3165 INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
3166 INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
3167 INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
3168 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3169 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3170 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3171 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3172 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3173 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3174 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3175 INSERT INTO t1(a,b) SELECT a,b FROM t1;
3176 INSERT INTO t1 VALUES (1000000, 0, 0);
3177 set @optimizer_switch_saved=@@optimizer_switch;
3178 SET SESSION optimizer_switch='derived_merge=off';
3179 SET SESSION sort_buffer_size = 1024*36;
3180 EXPLAIN
3181 SELECT COUNT(*) FROM
3182 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
3183 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
3184 id select_type table type possible_keys key key_len ref rows Extra
3185 1 PRIMARY <derived2> ALL NULL NULL NULL NULL #
3186 2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL # Using sort_union(idx,PRIMARY); Using where
3187 SELECT COUNT(*) FROM
3188 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
3189 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
3190 COUNT(*)
3191 1537
3192 set @@optimizer_switch=@optimizer_switch_saved;
3193 SET SESSION sort_buffer_size = DEFAULT;
3194 DROP TABLE t1;
3195 #
3196 # Test for bug #39932 "create table fails if column for FK is in different
3197 # case than in corr index".
3198 #
3199 drop tables if exists t1, t2;
3200 create table t1 (pk int primary key) engine=InnoDB;
3201 create table t2 (fk int, key x (fk),
3202 constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
3203 show create table t2;
3204 Table Create Table
3205 t2 CREATE TABLE `t2` (
3206 `fk` int(11) DEFAULT NULL,
3207 KEY `x` (`fk`),
3208 CONSTRAINT `x` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
3209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
3210 drop table t2, t1;
3211 #
3212 # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
3213 # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
3214 #
3215 DROP TABLE IF EXISTS t1;
3216 CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
3217 INSERT INTO t1 VALUES (1);
3218 LOCK TABLES t1 READ;
3219 # Even though temporary table was locked for READ we
3220 # still allow writes to it to be compatible with MyISAM.
3221 # This is possible since due to fact that temporary tables
3222 # are specific to connection and therefore locking for them
3223 # is irrelevant.
3224 UPDATE t1 SET c = 5;
3225 UNLOCK TABLES;
3226 DROP TEMPORARY TABLE t1;
3227 # End of 5.1 tests
3228 #
3229 # Bug#49604 "6.0 processing compound WHERE clause incorrectly
3230 # with Innodb - extra rows"
3231 #
3232 CREATE TABLE t1 (
3233 c1 INT NOT NULL,
3234 c2 INT,
3235 PRIMARY KEY (c1),
3236 KEY k1 (c2)
3237 ) ENGINE=InnoDB;
3238 INSERT INTO t1 VALUES (12,1);
3239 INSERT INTO t1 VALUES (15,1);
3240 INSERT INTO t1 VALUES (16,1);
3241 INSERT INTO t1 VALUES (22,1);
3242 INSERT INTO t1 VALUES (20,2);
3243 CREATE TABLE t2 (
3244 c1 INT NOT NULL,
3245 c2 INT,
3246 PRIMARY KEY (c1)
3247 ) ENGINE=InnoDB;
3248 INSERT INTO t2 VALUES (1,2);
3249 INSERT INTO t2 VALUES (2,9);
3250 SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1
3251 FROM t1 JOIN t2 ON t1.c2 = t2.c1
3252 WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1);
3253 c2 c2 c1
3254 2 1 1
3255 2 1 1
3256 2 1 1
3257 2 1 1
3258 9 2 2
3259 DROP TABLE t1, t2;
3260 #
3261 # Bug#44613 SELECT statement inside FUNCTION takes a shared lock
3262 #
3263 DROP TABLE IF EXISTS t1;
3264 DROP FUNCTION IF EXISTS f1;
3265 CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb;
3266 INSERT INTO t1 VALUES (1, 0), (2, 0);
3267 CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA
3268 RETURN (SELECT x FROM t1 WHERE x = z);
3269 START TRANSACTION;
3270 SELECT f1(1);
3271 f1(1)
3272 1
3273 START TRANSACTION;
3274 SELECT f1(1);
3275 f1(1)
3276 1
3277 UPDATE t1 SET y = 1 WHERE x = 1;
3278 COMMIT;
3279 disconnect con2;
3280 connection default;
3281 COMMIT;
3282 DROP TABLE t1;
3283 DROP FUNCTION f1;
3284 #
3285 # Bug#42744: Crash when using a join buffer to join a table with a blob
3286 # column and an additional column used for duplicate elimination.
3287 #
3288 CREATE TABLE t1 (a tinyblob) ENGINE=InnoDB;
3289 CREATE TABLE t2 (a int PRIMARY KEY, b tinyblob) ENGINE=InnoDB;
3290 INSERT INTO t1 VALUES ('1'), (NULL);
3291 INSERT INTO t2 VALUES (1, '1');
3292 EXPLAIN
3293 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
3294 id select_type table type possible_keys key key_len ref rows Extra
3295 1 PRIMARY t2 ALL NULL NULL NULL NULL 1
3296 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3297 1 PRIMARY t2 ALL NULL NULL NULL NULL 1 FirstMatch(t1); Using join buffer (incremental, BNL join)
3298 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
3299 b
3300 1
3301 DROP TABLE t1,t2;
3302 #
3303 # Bug#48093: 6.0 Server not processing equivalent IN clauses properly
3304 # with Innodb tables
3305 #
3306 CREATE TABLE t1 (
3307 i int(11) DEFAULT NULL,
3308 v1 varchar(1) DEFAULT NULL,
3309 v2 varchar(20) DEFAULT NULL,
3310 KEY i (i),
3311 KEY v (v1,i)
3312 ) ENGINE=innodb;
3313 INSERT INTO t1 VALUES (1,'f','no');
3314 INSERT INTO t1 VALUES (2,'u','yes-u');
3315 INSERT INTO t1 VALUES (2,'h','yes-h');
3316 INSERT INTO t1 VALUES (3,'d','no');
3317
3318 SELECT v2
3319 FROM t1
3320 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
3321 v2
3322 yes-u
3323 yes-h
3324
3325 # Should not use index_merge
3326 EXPLAIN
3327 SELECT v2
3328 FROM t1
3329 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
3330 id select_type table type possible_keys key key_len ref rows Extra
3331 1 SIMPLE t1 ref i,v i 5 const 2 Using where
3332 DROP TABLE t1;
3333 #
3334 # Bug#54606 innodb fast alter table + pack_keys=0
3335 # prevents adding new indexes
3336 #
3337 DROP TABLE IF EXISTS t1;
3338 CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b))
3339 ENGINE=InnoDB
3340 PACK_KEYS=0;
3341 CREATE INDEX a ON t1 (a);
3342 CREATE INDEX c on t1 (c);
3343 DROP TABLE t1;
3344 #
3345 # Additional coverage for refactoring which is made as part
3346 # of fix for Bug#27480 "Extend CREATE TEMPORARY TABLES privilege
3347 # to allow temp table operations".
3348 #
3349 # Check that OPTIMIZE table works for temporary InnoDB tables.
3350 DROP TABLE IF EXISTS t1;
3351 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=InnoDB;
3352 OPTIMIZE TABLE t1;
3353 Table Op Msg_type Msg_text
3354 test.t1 optimize note Table does not support optimize, doing recreate + analyze instead
3355 test.t1 optimize status OK
3356 DROP TABLE t1;
3357 #
3358 # Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE
3359 # COMMAND CAN FAIL IN INNODB PLUGIN 1.0
3360 #
3361 DROP TABLE IF EXISTS t1;
3362 CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id),
3363 INDEX a (a)) ENGINE=innodb;
3364 ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a);
3365 ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b);
3366 DROP TABLE t1;
3367 End of 6.0 tests