Debian Codegraph @

mysql-test/suite/innodb/t/innodb_mysql.test

raw
1 # t/innodb_mysql.test
2 #
3 # Last update:
4 # 2006-07-26 ML test refactored (MySQL 5.1)
5 # main testing code t/innodb_mysql.test -> include/mix1.inc
6 #
7
8 #Want to skip this test from daily Valgrind execution.
9 --source include/no_valgrind_without_big.inc
10 # Adding big test option for this test.
11 --source include/big_test.inc
12
13 let $engine_type= InnoDB;
14 let $other_engine_type= MEMORY;
15 # InnoDB does support FOREIGN KEYs
16 let $test_foreign_keys= 1;
17 --source include/mix1.inc
18
19 --echo #
20 --echo # BUG#35850: Performance regression in 5.1.23/5.1.24
21 --echo #
22 create table t1(a int);
23 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
24 create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
25 insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
26 ANALYZE TABLE t2;
27 --echo # this must use key 'a', not PRIMARY:
28 --replace_column 10 #
29 explain select a from t2 where a=b;
30 drop table t1, t2;
31
32 --echo #
33 --echo # Bug #40360: Binlog related errors with binlog off
34 --echo #
35 # This bug is triggered when the binlog format is STATEMENT and the
36 # binary log is turned off. In this case, no error should be shown for
37 # the statement since there are no replication issues.
38 SET @save_log_bin= @@sql_log_bin;
39 SET @save_binlog_format=@@binlog_format;
40 SET @@sql_log_bin = 0;
41
42 SET SESSION BINLOG_FORMAT=STATEMENT;
43 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
44 query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.transaction_isolation;
45 CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
46 INSERT INTO t1 VALUES(1);
47 DROP TABLE t1;
48
49 SET @@sql_log_bin=@save_log_bin;
50 SET @@binlog_format=@save_binlog_format;
51
52 --echo #
53 --echo # Bug#37284 Crash in Field_string::type()
54 --echo #
55 --disable_warnings
56 DROP TABLE IF EXISTS t1;
57 --enable_warnings
58 CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
59 CREATE INDEX i1 on t1 (a(3));
60 SELECT * FROM t1 WHERE a = 'abcde';
61 DROP TABLE t1;
62
63 --echo #
64 --echo # Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
65 --echo # requested column
66 --echo #
67
68 CREATE TABLE foo (a int, b int, c char(10),
69 PRIMARY KEY (c(3)),
70 KEY b (b)
71 ) engine=innodb;
72
73 CREATE TABLE foo2 (a int, b int, c char(10),
74 PRIMARY KEY (c),
75 KEY b (b)
76 ) engine=innodb;
77
78 CREATE TABLE bar (a int, b int, c char(10),
79 PRIMARY KEY (c(3)),
80 KEY b (b)
81 ) engine=myisam;
82
83 INSERT INTO foo VALUES
84 (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
85 (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe'), (5,6,'trondheim'),
86 (6,7,'stockholm'), (7,8,'copenhagen'), (8,9,'helsinki'),
87 (9,10,'reykjavik');
88
89 INSERT INTO bar SELECT * FROM foo;
90 INSERT INTO foo2 SELECT * FROM foo;
91
92 -- disable_result_log
93 ANALYZE TABLE bar;
94 ANALYZE TABLE foo;
95 ANALYZE TABLE foo2;
96 -- enable_result_log
97
98 --query_vertical EXPLAIN SELECT c FROM bar WHERE b>2;
99 --query_vertical EXPLAIN SELECT c FROM foo WHERE b>2;
100 --query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2;
101
102 --query_vertical EXPLAIN SELECT c FROM bar WHERE c>2;
103 --query_vertical EXPLAIN SELECT c FROM foo WHERE c>2;
104 --query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2;
105
106 DROP TABLE foo, bar, foo2;
107
108
109 --echo #
110 --echo # Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
111 --echo #
112
113 --disable_warnings
114 DROP TABLE IF EXISTS t1,t3,t2;
115 DROP FUNCTION IF EXISTS f1;
116 --enable_warnings
117
118 DELIMITER |;
119 CREATE FUNCTION f1() RETURNS VARCHAR(250)
120 BEGIN
121 return 'hhhhhhh' ;
122 END|
123 DELIMITER ;|
124
125 CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
126
127 BEGIN WORK;
128
129 CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
130 CREATE TEMPORARY TABLE t3 LIKE t2;
131
132 INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
133
134 SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
135 PREPARE stmt1 FROM @stmt;
136
137 SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
138 PREPARE stmt3 FROM @stmt;
139
140 EXECUTE stmt1;
141
142 COMMIT;
143
144 DEALLOCATE PREPARE stmt1;
145 DEALLOCATE PREPARE stmt3;
146
147 DROP TABLE t1,t3,t2;
148 DROP FUNCTION f1;
149
150 --echo #
151 --echo # Bug#37016: TRUNCATE TABLE removes some rows but not all
152 --echo #
153
154 --disable_warnings
155 DROP TABLE IF EXISTS t1,t2;
156 --enable_warnings
157
158 CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
159 CREATE TABLE t2 (id INT PRIMARY KEY,
160 t1_id INT, INDEX par_ind (t1_id),
161 FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
162 INSERT INTO t1 VALUES (1),(2);
163 INSERT INTO t2 VALUES (3,2);
164
165 SET AUTOCOMMIT = 0;
166
167 START TRANSACTION;
168 --error ER_TRUNCATE_ILLEGAL_FK
169 TRUNCATE TABLE t1;
170 SELECT * FROM t1;
171 COMMIT;
172 SELECT * FROM t1;
173
174 START TRANSACTION;
175 --error ER_TRUNCATE_ILLEGAL_FK
176 TRUNCATE TABLE t1;
177 SELECT * FROM t1;
178 ROLLBACK;
179 SELECT * FROM t1;
180
181 SET AUTOCOMMIT = 1;
182
183 START TRANSACTION;
184 SELECT * FROM t1;
185 COMMIT;
186
187 --error ER_TRUNCATE_ILLEGAL_FK
188 TRUNCATE TABLE t1;
189 SELECT * FROM t1;
190 DELETE FROM t2 WHERE id = 3;
191
192 START TRANSACTION;
193 SELECT * FROM t1;
194 --error ER_TRUNCATE_ILLEGAL_FK
195 TRUNCATE TABLE t1;
196 ROLLBACK;
197 SELECT * FROM t1;
198 TRUNCATE TABLE t2;
199
200 DROP TABLE t2;
201 DROP TABLE t1;
202
203 --echo #
204 --echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
205 --echo #
206 CREATE TABLE t1 (
207 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
208 PRIMARY KEY (id)
209 ) ENGINE=InnoDB;
210
211 CREATE TABLE t2 (
212 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
213 aid INT UNSIGNED NOT NULL,
214 PRIMARY KEY (id),
215 FOREIGN KEY (aid) REFERENCES t1 (id)
216 ) ENGINE=InnoDB;
217
218 CREATE TABLE t3 (
219 bid INT UNSIGNED NOT NULL,
220 FOREIGN KEY (bid) REFERENCES t2 (id)
221 ) ENGINE=InnoDB;
222
223 CREATE TABLE t4 (
224 a INT
225 ) ENGINE=InnoDB;
226
227 CREATE TABLE t5 (
228 a INT
229 ) ENGINE=InnoDB;
230
231 INSERT INTO t1 (id) VALUES (1);
232 INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
233 INSERT INTO t3 (bid) VALUES (1);
234
235 INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
236 INSERT INTO t5 VALUES (1);
237
238 DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
239
240 --error ER_ROW_IS_REFERENCED_2
241 DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
242 --error ER_ROW_IS_REFERENCED_2
243 DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
244
245 DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
246
247 DROP TABLE t3;
248 DROP TABLE t2;
249 DROP TABLE t1;
250 DROP TABLES t4,t5;
251
252 --echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
253 --echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
254 --echo # transactional tables.
255 --echo #
256 CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
257 CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
258 CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
259 CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
260 FOREIGN KEY (t1i) REFERENCES t1(i))
261 ENGINE=InnoDB;
262 delimiter ||;
263 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
264 BEGIN
265 SET @b:='EXECUTED TRIGGER';
266 INSERT INTO t2 VALUES (@b);
267 SET @a:= error_happens_here;
268 END||
269 delimiter ;||
270
271 SET @b:="";
272 SET @a:="";
273 INSERT INTO t1 VALUES (1),(2),(3),(4);
274 INSERT INTO t3 SELECT * FROM t1;
275 --echo ** An error in a trigger causes rollback of the statement.
276 --error ER_BAD_FIELD_ERROR
277 DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
278 SELECT @a,@b;
279 SELECT * FROM t2;
280 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
281
282 --echo ** Same happens with the IGNORE option
283 --error ER_BAD_FIELD_ERROR
284 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
285 SELECT * FROM t2;
286 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
287
288 --echo **
289 --echo ** The following is an attempt to demonstrate
290 --echo ** error handling inside a row iteration.
291 --echo **
292 DROP TRIGGER trg;
293 DELETE FROM t1;
294 DELETE FROM t2;
295 DELETE FROM t3;
296
297 INSERT INTO t1 VALUES (1),(2),(3),(4);
298 INSERT INTO t3 VALUES (1),(2),(3),(4);
299 INSERT INTO t4 VALUES (3,3),(4,4);
300
301 delimiter ||;
302 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
303 BEGIN
304 SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
305 INSERT INTO t2 VALUES (@b);
306 END||
307 delimiter ;||
308
309 --echo ** DELETE is prevented by foreign key constrains but errors are silenced.
310 --echo ** The AFTER trigger isn't fired.
311 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
312 --echo ** Tables are modified by best effort:
313 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
314 --echo ** The AFTER trigger was only executed on successful rows:
315 SELECT * FROM t2;
316
317 DROP TRIGGER trg;
318
319 --echo **
320 --echo ** Induce an error midway through an AFTER-trigger
321 --echo **
322 DELETE FROM t4;
323 DELETE FROM t1;
324 DELETE FROM t3;
325 INSERT INTO t1 VALUES (1),(2),(3),(4);
326 INSERT INTO t3 VALUES (1),(2),(3),(4);
327 delimiter ||;
328 CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
329 BEGIN
330 SET @a:= @a+1;
331 IF @a > 2 THEN
332 INSERT INTO t4 VALUES (5,5);
333 END IF;
334 END||
335 delimiter ;||
336
337 SET @a:=0;
338 --echo ** Errors in the trigger causes the statement to abort.
339 --error ER_NO_REFERENCED_ROW_2
340 DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
341 SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
342 SELECT * FROM t4;
343
344 DROP TRIGGER trg;
345 DROP TABLE t4;
346 DROP TABLE t1;
347 DROP TABLE t2;
348 DROP TABLE t3;
349
350 --echo #
351 --echo # Bug#43580: Issue with Innodb on multi-table update
352 --echo #
353 CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
354 CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
355
356 CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
357 CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
358
359 INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
360 INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
361
362 INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
363 INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
364
365 # Because t1.a changes and t2.b changes based on t1.a, the result
366 # depends on join order, so STRAIGHT_JOIN is used to have it repeatable.
367 UPDATE t2 straight_join t1 SET t1.a = t1.a + 100, t2.b = t1.a + 10
368 WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
369 --sorted_result
370 SELECT * FROM t2;
371
372 # Because t1.a changes and t2.b changes based on t1.a, the result
373 # depends on join order, so STRAIGHT_JOIN is used to have it repeatable.
374 UPDATE t4 straight_join t3 SET t3.a = t3.a + 100, t4.b = t3.a + 10
375 WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
376 --sorted_result
377 SELECT * FROM t4;
378
379 DROP TABLE t1, t2, t3, t4;
380
381 --echo #
382 --echo # Bug#44886: SIGSEGV in test_if_skip_sort_order() -
383 --echo # uninitialized variable used as subscript
384 --echo #
385
386 CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
387 ENGINE=InnoDB;
388 INSERT INTO t1 VALUES (1,1,1,0);
389
390 CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
391 INSERT INTO t2 VALUES (1,1,2);
392
393 CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
394 INSERT INTO t3 VALUES (1, 1);
395 # Legacy queries below need to turn off ONLY_FULL_GROUP_BY and STRICT mode.
396 SET sql_mode='NO_ENGINE_SUBSTITUTION';
397 SELECT * FROM t1, t2, t3
398 WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
399 GROUP BY t1.b;
400 SET sql_mode=default;
401 DROP TABLE t1, t2, t3;
402
403 --echo #
404 --echo # Bug #45828: Optimizer won't use partial primary key if another
405 --echo # index can prevent filesort
406 --echo #
407
408 # Create the table
409 CREATE TABLE `t1` (
410 c1 int NOT NULL,
411 c2 int NOT NULL,
412 c3 int NOT NULL,
413 PRIMARY KEY (c1,c2),
414 KEY (c3)
415 ) ENGINE=InnoDB;
416
417 # populate with data
418 INSERT INTO t1 VALUES (5,2,1246276747);
419 INSERT INTO t1 VALUES (2,1,1246281721);
420 INSERT INTO t1 VALUES (7,3,1246281756);
421 INSERT INTO t1 VALUES (4,2,1246282139);
422 INSERT INTO t1 VALUES (3,1,1246282230);
423 INSERT INTO t1 VALUES (1,0,1246282712);
424 INSERT INTO t1 VALUES (8,3,1246282765);
425 INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
426 INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
427 INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
428 INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
429 INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
430 INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
431 ANALYZE TABLE t1;
432 # query and no rows will match the c1 condition, whereas all will match c3
433 SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
434
435 # SHOULD use the pk.
436 # index on c3 will be used instead of primary key
437 EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
438
439 # if we force the primary key, we can see the estimate is 1
440 EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
441
442
443 CREATE TABLE t2 (
444 c1 int NOT NULL,
445 c2 int NOT NULL,
446 c3 int NOT NULL,
447 KEY (c1,c2),
448 KEY (c3)
449 ) ENGINE=InnoDB;
450
451 # SHOULD use the pk.
452 # if we switch it from a primary key to a regular index, it works correctly as well
453 ANALYZE TABLE t2;
454 explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
455
456 DROP TABLE t1,t2;
457
458
459 --echo #
460 --echo # 36259: Optimizing with ORDER BY
461 --echo #
462
463 CREATE TABLE t1 (
464 a INT NOT NULL AUTO_INCREMENT,
465 b INT NOT NULL,
466 c INT NOT NULL,
467 d VARCHAR(5),
468 e INT NOT NULL,
469 PRIMARY KEY (a), KEY i2 (b,c,d)
470 ) ENGINE=InnoDB;
471
472 INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
473 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
474 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
475 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
476 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
477 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
478 INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
479 -- disable_result_log
480 ANALYZE TABLE t1;
481 -- enable_result_log
482 EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
483 EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
484 # With 4k pages, the 'rows' column in the output below is either 120 or 138,
485 # not 128 as it is with 8k and 16k. Bug#12602606
486 --replace_column 10 # 11 #
487 EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
488
489 DROP TABLE t1;
490
491 --echo #
492 --echo # Bug #47963: Wrong results when index is used
493 --echo #
494 CREATE TABLE t1(
495 a VARCHAR(5) NOT NULL,
496 b VARCHAR(5) NOT NULL,
497 c DATETIME NOT NULL,
498 KEY (c)
499 ) ENGINE=InnoDB;
500 INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
501 ANALYZE TABLE t1;
502 SELECT * FROM t1 WHERE a = 'TEST' AND
503 c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
504 SELECT * FROM t1 WHERE a = 'TEST' AND
505 c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
506 SELECT * FROM t1 WHERE a = 'TEST' AND
507 c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
508 SELECT * FROM t1 WHERE a = 'TEST' AND
509 c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
510 SELECT * FROM t1 WHERE a = 'TEST' AND
511 c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
512 SELECT * FROM t1 WHERE a = 'TEST' AND
513 c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
514 SELECT * FROM t1 WHERE a = 'TEST' AND
515 c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
516 EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
517 c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
518 DROP TABLE t1;
519
520 --echo #
521 --echo # Bug #46175: NULL read_view and consistent read assertion
522 --echo #
523
524 CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
525 CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
526 INSERT INTO t1 VALUES (),();
527 INSERT INTO t2 VALUES (),();
528 CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
529 WHERE b =(SELECT a FROM t1 LIMIT 1);
530
531 CONNECT (con1, localhost, root,,);
532 CONNECTION default;
533
534 DELIMITER |;
535 CREATE PROCEDURE p1(num INT)
536 BEGIN
537 DECLARE i INT DEFAULT 0;
538 REPEAT
539 SHOW CREATE VIEW v1;
540 SET i:=i+1;
541 UNTIL i>num END REPEAT;
542 END|
543 DELIMITER ;|
544
545 --echo # Should not crash
546 --disable_query_log
547 --disable_result_log
548 --send CALL p1(1000)
549 CONNECTION con1;
550 --echo # Should not crash
551 CALL p1(1000);
552
553 CONNECTION default;
554 --reap
555 --enable_query_log
556 --enable_result_log
557
558 DISCONNECT con1;
559 DROP PROCEDURE p1;
560 DROP VIEW v1;
561 DROP TABLE t1,t2;
562
563
564 --echo #
565 --echo # Bug #49324: more valgrind errors in test_if_skip_sort_order
566 --echo #
567 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
568 --echo # should not cause valgrind warnings
569 SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
570 DROP TABLE t1;
571
572 --echo #
573 --echo # Bug#50843: Filesort used instead of clustered index led to
574 --echo # performance degradation.
575 --echo #
576 create table t1(f1 int not null primary key, f2 int) engine=innodb;
577 create table t2(f1 int not null, key (f1)) engine=innodb;
578 insert into t1 values (1,1),(2,2),(3,3);
579 insert into t2 values (1),(2),(3);
580 -- disable_result_log
581 analyze table t1;
582 analyze table t2;
583 -- enable_result_log
584 explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
585 drop table t1,t2;
586 --echo #
587
588
589 --echo #
590 --echo # Bug #39653: find_shortest_key in sql_select.cc does not consider
591 --echo # clustered primary keys
592 --echo #
593
594 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
595 KEY (b,c)) ENGINE=INNODB;
596
597 INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
598 (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
599 (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
600 (11,11,11,11,11,11);
601
602 ANALYZE TABLE t1;
603
604 --query_vertical EXPLAIN SELECT COUNT(*) FROM t1
605
606 DROP TABLE t1;
607
608 --echo #
609 --echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
610 --echo # corrupt definition at engine
611 --echo #
612
613 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
614 ENGINE=InnoDB;
615
616 ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
617
618 ANALYZE TABLE t1;
619
620 --query_vertical SHOW INDEXES FROM t1;
621
622 DROP TABLE t1;
623
624 --echo #
625 --echo # Bug #53334: wrong result for outer join with impossible ON condition
626 --echo # (see the same test case for MyISAM in join.test)
627 --echo #
628
629 CREATE TABLE t1 (id INT PRIMARY KEY);
630 CREATE TABLE t2 (id INT);
631
632 INSERT INTO t1 VALUES (75);
633 INSERT INTO t1 VALUES (79);
634 INSERT INTO t1 VALUES (78);
635 INSERT INTO t1 VALUES (77);
636 REPLACE INTO t1 VALUES (76);
637 REPLACE INTO t1 VALUES (76);
638 INSERT INTO t1 VALUES (104);
639 INSERT INTO t1 VALUES (103);
640 INSERT INTO t1 VALUES (102);
641 INSERT INTO t1 VALUES (101);
642 INSERT INTO t1 VALUES (105);
643 INSERT INTO t1 VALUES (106);
644 INSERT INTO t1 VALUES (107);
645
646 INSERT INTO t2 VALUES (107),(75),(1000);
647 ANALYZE TABLE t1;
648 ANALYZE TABLE t2;
649
650 SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
651 WHERE t2.id=75 AND t1.id IS NULL;
652 EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
653 WHERE t2.id=75 AND t1.id IS NULL;
654
655 DROP TABLE t1,t2;
656
657 --echo #
658 --echo # Bug#38999 valgrind warnings for update statement in function compare_record()
659 --echo #
660
661 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
662 CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
663 INSERT INTO t1 values (1),(2),(3),(4),(5);
664 INSERT INTO t2 values (1);
665
666 SELECT * FROM t1 WHERE a = 2;
667 UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
668
669 DROP TABLE t1,t2;
670
671 --echo #
672 --echo # Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
673 --echo #
674
675 CREATE TABLE t1 (a INT, b INT, c INT, d INT,
676 PRIMARY KEY(a,b,c), KEY(b,d))
677 ENGINE=InnoDB;
678 INSERT INTO t1 VALUES (0, 77, 1, 3);
679
680 UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
681
682 DROP TABLE t1;
683
684 --echo #
685 --echo # Bug#50389 Using intersect does not return all rows
686 --echo #
687
688 CREATE TABLE t1 (
689 f1 INT(10) NOT NULL,
690 f2 INT(10),
691 f3 INT(10),
692 f4 TINYINT(4),
693 f5 VARCHAR(50),
694 PRIMARY KEY (f1),
695 KEY idx1 (f2,f5,f4),
696 KEY idx2 (f2,f4)
697 ) ENGINE=InnoDB;
698
699 LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
700
701 -- disable_result_log
702 ANALYZE TABLE t1;
703 -- enable_result_log
704
705 SELECT * FROM t1 WHERE f1 IN
706 (3305028,3353871,3772880,3346860,4228206,3336022,
707 3470988,3305175,3329875,3817277,3856380,3796193,
708 3784744,4180925,4559596,3963734,3856391,4494153)
709 AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
710
711 EXPLAIN SELECT * FROM t1 WHERE f1 IN
712 (3305028,3353871,3772880,3346860,4228206,3336022,
713 3470988,3305175,3329875,3817277,3856380,3796193,
714 3784744,4180925,4559596,3963734,3856391,4494153)
715 AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
716
717 DROP TABLE t1;
718
719 --echo #
720 --echo # Bug#51431 Wrong sort order after import of dump file
721 --echo #
722
723 CREATE TABLE t1 (
724 f1 INT(11) NOT NULL,
725 f2 int(11) NOT NULL,
726 f3 int(11) NOT NULL,
727 f4 tinyint(1) NOT NULL,
728 PRIMARY KEY (f1),
729 UNIQUE KEY (f2, f3),
730 KEY (f4)
731 ) ENGINE=InnoDB STATS_PERSISTENT=0;
732
733 INSERT INTO t1 VALUES
734 (1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
735 (6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
736 (16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
737 (21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
738 (26,1,9921,1), (27,1,9922,1);
739
740 FLUSH TABLES;
741 ANALYZE TABLE t1;
742 SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
743 ORDER BY f1 DESC LIMIT 5;
744 EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
745 ORDER BY f4 DESC, f1 DESC LIMIT 5;
746
747 DROP TABLE t1;
748
749
750 --echo #
751 --echo # Bug#54117 crash in thr_multi_unlock, temporary table
752 --echo #
753
754 CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;
755
756 LOCK TABLES t1 READ;
757 ALTER TABLE t1 COMMENT 'test';
758 UNLOCK TABLES;
759
760 DROP TABLE t1;
761
762 --echo #
763 --echo # Bug#55656: mysqldump can be slower after bug #39653 fix
764 --echo #
765
766 CREATE TABLE t1 (a INT , b INT, c INT, d INT,
767 KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB STATS_PERSISTENT=0;
768 INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
769 ANALYZE TABLE t1;
770 --query_vertical EXPLAIN SELECT COUNT(*) FROM t1
771
772 DROP INDEX b ON t1;
773 CREATE INDEX b ON t1(a,b);
774 ANALYZE TABLE t1;
775 --query_vertical EXPLAIN SELECT COUNT(*) FROM t1
776
777 DROP INDEX b ON t1;
778 CREATE INDEX b ON t1(a,b,c);
779 ANALYZE TABLE t1;
780 --query_vertical EXPLAIN SELECT COUNT(*) FROM t1
781
782 DROP INDEX b ON t1;
783 CREATE INDEX b ON t1(a,b,c,d);
784 ANALYZE TABLE t1;
785 --query_vertical EXPLAIN SELECT COUNT(*) FROM t1
786
787 DROP TABLE t1;
788
789 --echo #
790 --echo # Bug#55826: create table .. select crashes with when KILL_BAD_DATA
791 --echo # is returned
792 --echo #
793
794 CREATE TABLE t1(a INT) ENGINE=innodb;
795 INSERT INTO t1 VALUES (0);
796 --error ER_TRUNCATED_WRONG_VALUE
797 CREATE TABLE t2
798 SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
799 DROP TABLE t1;
800
801 --echo #
802 --echo # Bug#56862 Moved to innodb_16k.test
803 --echo #
804 --echo #
805 --echo # Test for bug #39932 "create table fails if column for FK is in different
806 --echo # case than in corr index".
807 --echo #
808 --disable_warnings
809 drop tables if exists t1, t2;
810 --enable_warnings
811 create table t1 (pk int primary key) engine=InnoDB;
812 # Even although the below statement uses uppercased field names in
813 # foreign key definition it still should be able to find explicitly
814 # created supporting index. So it should succeed and should not
815 # create any additional supporting indexes.
816 # TODO: NewDD: Bug 25587256 - NEWDD: FK: NEED TO SET PROPER REFERENCED
817 # COLUMN CASE
818
819 create table t2 (fk int, key x (fk),
820 constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
821 show create table t2;
822 drop table t2, t1;
823
824
825 --echo #
826 --echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
827 --echo # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
828 --echo #
829 --disable_warnings
830 DROP TABLE IF EXISTS t1;
831 --enable_warnings
832 CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
833 INSERT INTO t1 VALUES (1);
834 LOCK TABLES t1 READ;
835 --echo # Even though temporary table was locked for READ we
836 --echo # still allow writes to it to be compatible with MyISAM.
837 --echo # This is possible since due to fact that temporary tables
838 --echo # are specific to connection and therefore locking for them
839 --echo # is irrelevant.
840 UPDATE t1 SET c = 5;
841 UNLOCK TABLES;
842 DROP TEMPORARY TABLE t1;
843
844 --echo # End of 5.1 tests
845
846
847 --echo #
848 --echo # Bug#49604 "6.0 processing compound WHERE clause incorrectly
849 --echo # with Innodb - extra rows"
850 --echo #
851
852 CREATE TABLE t1 (
853 c1 INT NOT NULL,
854 c2 INT,
855 PRIMARY KEY (c1),
856 KEY k1 (c2)
857 ) ENGINE=InnoDB;
858
859 INSERT INTO t1 VALUES (12,1);
860 INSERT INTO t1 VALUES (15,1);
861 INSERT INTO t1 VALUES (16,1);
862 INSERT INTO t1 VALUES (22,1);
863 INSERT INTO t1 VALUES (20,2);
864
865 CREATE TABLE t2 (
866 c1 INT NOT NULL,
867 c2 INT,
868 PRIMARY KEY (c1)
869 ) ENGINE=InnoDB;
870
871 INSERT INTO t2 VALUES (1,2);
872 INSERT INTO t2 VALUES (2,9);
873
874 --sorted_result
875 SELECT STRAIGHT_JOIN t2.c2, t1.c2, t2.c1
876 FROM t1 JOIN t2 ON t1.c2 = t2.c1
877 WHERE t2.c1 IN (2, 1, 6) OR t2.c1 NOT IN (1);
878
879 DROP TABLE t1, t2;
880
881
882 --echo #
883 --echo # Bug#44613 SELECT statement inside FUNCTION takes a shared lock
884 --echo #
885
886 --disable_warnings
887 DROP TABLE IF EXISTS t1;
888 DROP FUNCTION IF EXISTS f1;
889 --enable_warnings
890
891 CREATE TABLE t1(x INT PRIMARY KEY, y INT) ENGINE=innodb;
892 INSERT INTO t1 VALUES (1, 0), (2, 0);
893
894 CREATE FUNCTION f1(z INT) RETURNS INT READS SQL DATA
895 RETURN (SELECT x FROM t1 WHERE x = z);
896
897 --echo # Connection default
898 START TRANSACTION;
899 SELECT f1(1);
900
901 --echo # Connection con2
902 --disable_query_log
903 connect (con2, localhost, root);
904 --enable_query_log
905 START TRANSACTION;
906 SELECT f1(1);
907 # This next statement used to block.
908 UPDATE t1 SET y = 1 WHERE x = 1;
909
910 COMMIT;
911
912 disconnect con2;
913 --source include/wait_until_disconnected.inc
914 --echo # Connection default
915 connection default;
916 COMMIT;
917 DROP TABLE t1;
918 DROP FUNCTION f1;
919 --echo #
920 --echo # Bug#42744: Crash when using a join buffer to join a table with a blob
921 --echo # column and an additional column used for duplicate elimination.
922 --echo #
923
924 CREATE TABLE t1 (a tinyblob) ENGINE=InnoDB;
925 CREATE TABLE t2 (a int PRIMARY KEY, b tinyblob) ENGINE=InnoDB;
926 INSERT INTO t1 VALUES ('1'), (NULL);
927 INSERT INTO t2 VALUES (1, '1');
928 ANALYZE TABLE t1;
929 ANALYZE TABLE t2;
930
931 EXPLAIN
932 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
933
934 SELECT t2.b FROM t1,t2 WHERE t1.a IN (SELECT 1 FROM t2);
935
936 DROP TABLE t1,t2;
937
938 --echo #
939 --echo # Bug#48093: 6.0 Server not processing equivalent IN clauses properly
940 --echo # with Innodb tables
941 --echo #
942
943 CREATE TABLE t1 (
944 i int(11) DEFAULT NULL,
945 v1 varchar(1) DEFAULT NULL,
946 v2 varchar(20) DEFAULT NULL,
947 KEY i (i),
948 KEY v (v1,i)
949 ) ENGINE=innodb;
950
951 INSERT INTO t1 VALUES (1,'f','no');
952 INSERT INTO t1 VALUES (2,'u','yes-u');
953 INSERT INTO t1 VALUES (2,'h','yes-h');
954 INSERT INTO t1 VALUES (3,'d','no');
955 ANALYZE TABLE t1;
956
957 --echo
958 SELECT v2
959 FROM t1
960 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
961
962 --echo
963 --echo # Should not use index_merge
964 EXPLAIN
965 SELECT v2
966 FROM t1
967 WHERE v1 IN ('f', 'd', 'h', 'u' ) AND i = 2;
968
969 DROP TABLE t1;
970
971 --echo #
972 --echo # Bug#54606 innodb fast alter table + pack_keys=0
973 --echo # prevents adding new indexes
974 --echo #
975
976 --disable_warnings
977 DROP TABLE IF EXISTS t1;
978 --enable_warnings
979
980 CREATE TABLE t1 (a INT, b CHAR(9), c INT, key(b))
981 ENGINE=InnoDB
982 PACK_KEYS=0;
983 CREATE INDEX a ON t1 (a);
984 CREATE INDEX c on t1 (c);
985
986 DROP TABLE t1;
987
988
989 --echo #
990 --echo # Additional coverage for refactoring which is made as part
991 --echo # of fix for Bug#27480 "Extend CREATE TEMPORARY TABLES privilege
992 --echo # to allow temp table operations".
993 --echo #
994 --echo # Check that OPTIMIZE table works for temporary InnoDB tables.
995 --disable_warnings
996 DROP TABLE IF EXISTS t1;
997 --enable_warnings
998 CREATE TEMPORARY TABLE t1 (a INT) ENGINE=InnoDB;
999 OPTIMIZE TABLE t1;
1000 DROP TABLE t1;
1001
1002
1003 --echo #
1004 --echo # Bug#11762345 54927: DROPPING AND ADDING AN INDEX IN ONE
1005 --echo # COMMAND CAN FAIL IN INNODB PLUGIN 1.0
1006 --echo #
1007
1008 --disable_warnings
1009 DROP TABLE IF EXISTS t1;
1010 --enable_warnings
1011
1012 CREATE TABLE t1 (id int, a int, b int, PRIMARY KEY (id),
1013 INDEX a (a)) ENGINE=innodb;
1014
1015 ALTER TABLE t1 DROP INDEX a, ADD INDEX a (b, a);
1016 # This used to fail
1017 ALTER TABLE t1 DROP INDEX a, ADD INDEX (a, b);
1018
1019 DROP TABLE t1;
1020
1021
1022 --echo #
1023 --echo # Check the prefix index on BLOB could not be promoted to PRIMARY KEY
1024 --echo #
1025 CREATE TABLE t1 (a INT NOT NULL, d INT NOT NULL, b TEXT NOT NULL, c TEXT,
1026 UNIQUE KEY (b(8) desc, a asc, d)) ENGINE = InnoDB;
1027 SHOW CREATE TABLE t1;
1028 DROP TABLE t1;
1029
1030 --echo #
1031 --echo # Bug#25650516. The problem is the index order for the old and new
1032 --echo # dd::Table are different
1033 --echo #
1034 CREATE TABLE t1 (col1 INT, col2 INT) ENGINE = InnoDB;
1035 ALTER TABLE t1 ADD UNIQUE KEY uidx ( col2 );
1036 ALTER TABLE t1 ADD UNIQUE KEY uidx ( col1 ), RENAME KEY uidx TO new_uidx;
1037 ALTER TABLE t1 ADD PRIMARY KEY ( col1 );
1038 # This will pass different index order for old and new table
1039 ALTER TABLE t1 ALTER COLUMN col1 DROP DEFAULT;
1040 DROP TABLE t1;
1041
1042 --echo #
1043 --echo # BUG 25674492 - INNODB: ASSERTION FAILURE: HA_INNODB.CC:13881
1044 --echo # :DD_TABLE_MATCH(TABLE, DD_TABLE)
1045 --echo #
1046 CREATE TABLE t1 (col1 INT, col4 TEXT) ENGINE = InnoDB ROW_FORMAT = COMPACT;
1047 ALTER TABLE t1 ADD KEY idx ( col1 ), ADD FULLTEXT KEY ftidx ( col4 );
1048 ALTER TABLE t1 DROP KEY ftidx;
1049 TRUNCATE TABLE t1;
1050 DROP TABLE t1;
1051
1052 --echo End of 6.0 tests