Debian Codegraph @

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

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