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