4
# 2006-07-26 ML test refactored (MySQL 5.1)
5
# main testing code t/innodb_mysql.test -> include/mix1.inc
8
-- source include/have_innodb.inc
9
let $engine_type= InnoDB;
10
let $other_engine_type= MEMORY;
11
# InnoDB does support FOREIGN KEYFOREIGN KEYs
12
let $test_foreign_keys= 1;
13
set global innodb_support_xa=default;
14
set session innodb_support_xa=default;
15
--source include/mix1.inc
18
drop table if exists t1, t2, t3;
21
# BUG#35850: Performance regression in 5.1.23/5.1.24
23
create table t1(a int);
24
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
25
create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
26
insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
27
--echo this must use key 'a', not PRIMARY:
29
explain select a from t2 where a=b;
33
# Bug #40360: Binlog related errors with binlog off
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.
39
SET SESSION BINLOG_FORMAT=STATEMENT;
40
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
41
query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
42
CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
43
INSERT INTO t1 VALUES(1);
47
# Bug#37284 Crash in Field_string::type()
50
DROP TABLE IF EXISTS t1;
52
CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
53
CREATE INDEX i1 on t1 (a(3));
54
SELECT * FROM t1 WHERE a = 'abcde';
58
# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
62
CREATE TABLE foo (a int, b int, c char(10),
67
CREATE TABLE foo2 (a int, b int, c char(10),
72
CREATE TABLE bar (a int, b int, c char(10),
77
INSERT INTO foo VALUES
78
(1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
79
(4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
81
INSERT INTO bar SELECT * FROM foo;
82
INSERT INTO foo2 SELECT * FROM foo;
84
--query_vertical EXPLAIN SELECT c FROM bar WHERE b>2;
85
--query_vertical EXPLAIN SELECT c FROM foo WHERE b>2;
86
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2;
88
--query_vertical EXPLAIN SELECT c FROM bar WHERE c>2;
89
--query_vertical EXPLAIN SELECT c FROM foo WHERE c>2;
90
--query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2;
92
DROP TABLE foo, bar, foo2;
96
# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
100
DROP TABLE IF EXISTS t1,t3,t2;
101
DROP FUNCTION IF EXISTS f1;
105
CREATE FUNCTION f1() RETURNS VARCHAR(250)
111
CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
115
CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
116
CREATE TEMPORARY TABLE t3 LIKE t2;
118
INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
120
SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
121
PREPARE stmt1 FROM @stmt;
123
SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
124
PREPARE stmt3 FROM @stmt;
130
DEALLOCATE PREPARE stmt1;
131
DEALLOCATE PREPARE stmt3;
137
# Bug#37016: TRUNCATE TABLE removes some rows but not all
141
DROP TABLE IF EXISTS t1,t2;
144
CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
145
CREATE TABLE t2 (id INT PRIMARY KEY,
146
t1_id INT, INDEX par_ind (t1_id),
147
FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
148
INSERT INTO t1 VALUES (1),(2);
149
INSERT INTO t2 VALUES (3,2);
154
--error ER_ROW_IS_REFERENCED_2
161
--error ER_ROW_IS_REFERENCED_2
173
--error ER_ROW_IS_REFERENCED_2
176
DELETE FROM t2 WHERE id = 3;
189
--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
192
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
197
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
198
aid INT UNSIGNED NOT NULL,
200
FOREIGN KEY (aid) REFERENCES t1 (id)
204
bid INT UNSIGNED NOT NULL,
205
FOREIGN KEY (bid) REFERENCES t2 (id)
216
INSERT INTO t1 (id) VALUES (1);
217
INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
218
INSERT INTO t3 (bid) VALUES (1);
220
INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
221
INSERT INTO t5 VALUES (1);
223
DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
225
--error ER_ROW_IS_REFERENCED_2
226
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
227
--error ER_ROW_IS_REFERENCED_2
228
DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
230
DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
237
--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
238
--echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
239
--echo # transactional tables.
241
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
242
CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
243
CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
244
CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
245
FOREIGN KEY (t1i) REFERENCES t1(i))
248
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
250
SET @b:='EXECUTED TRIGGER';
251
INSERT INTO t2 VALUES (@b);
252
SET @a:= error_happens_here;
258
INSERT INTO t1 VALUES (1),(2),(3),(4);
259
INSERT INTO t3 SELECT * FROM t1;
260
--echo ** An error in a trigger causes rollback of the statement.
261
--error ER_BAD_FIELD_ERROR
262
DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
265
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
267
--echo ** Same happens with the IGNORE option
268
--error ER_BAD_FIELD_ERROR
269
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
271
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
274
--echo ** The following is an attempt to demonstrate
275
--echo ** error handling inside a row iteration.
282
INSERT INTO t1 VALUES (1),(2),(3),(4);
283
INSERT INTO t3 VALUES (1),(2),(3),(4);
284
INSERT INTO t4 VALUES (3,3),(4,4);
287
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
289
SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
290
INSERT INTO t2 VALUES (@b);
294
--echo ** DELETE is prevented by foreign key constrains but errors are silenced.
295
--echo ** The AFTER trigger isn't fired.
296
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
297
--echo ** Tables are modified by best effort:
298
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
299
--echo ** The AFTER trigger was only executed on successful rows:
305
--echo ** Induce an error midway through an AFTER-trigger
310
INSERT INTO t1 VALUES (1),(2),(3),(4);
311
INSERT INTO t3 VALUES (1),(2),(3),(4);
313
CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
317
INSERT INTO t4 VALUES (5,5);
323
--echo ** Errors in the trigger causes the statement to abort.
324
--error ER_NO_REFERENCED_ROW_2
325
DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
326
SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
336
# Bug#43580: Issue with Innodb on multi-table update
338
CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
339
CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
341
CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
342
CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
344
INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
345
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
347
INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
348
INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
350
UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10
351
WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
355
UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
356
WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
360
DROP TABLE t1, t2, t3, t4;
363
--echo # Bug#44886: SIGSEGV in test_if_skip_sort_order() -
364
--echo # uninitialized variable used as subscript
367
CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
369
INSERT INTO t1 VALUES (1,1,1,0);
371
CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
372
INSERT INTO t2 VALUES (1,1,2);
374
CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
375
INSERT INTO t3 VALUES (1, 1);
377
SELECT * FROM t1, t2, t3
378
WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
381
DROP TABLE t1, t2, t3;
384
--echo # Bug #45828: Optimizer won't use partial primary key if another
385
--echo # index can prevent filesort
398
INSERT INTO t1 VALUES (5,2,1246276747);
399
INSERT INTO t1 VALUES (2,1,1246281721);
400
INSERT INTO t1 VALUES (7,3,1246281756);
401
INSERT INTO t1 VALUES (4,2,1246282139);
402
INSERT INTO t1 VALUES (3,1,1246282230);
403
INSERT INTO t1 VALUES (1,0,1246282712);
404
INSERT INTO t1 VALUES (8,3,1246282765);
405
INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
406
INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
407
INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
408
INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
409
INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
410
INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
412
# query and no rows will match the c1 condition, whereas all will match c3
413
SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
416
# index on c3 will be used instead of primary key
417
EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
419
# if we force the primary key, we can see the estimate is 1
420
EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
432
# if we switch it from a primary key to a regular index, it works correctly as well
433
explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
439
--echo # 36259: Optimizing with ORDER BY
443
a INT NOT NULL AUTO_INCREMENT,
448
PRIMARY KEY (a), KEY i2 (b,c,d)
451
INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
452
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
453
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
454
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
455
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
456
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
457
INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
458
EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
459
EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
460
EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
465
--echo # Bug #47963: Wrong results when index is used
468
a VARCHAR(5) NOT NULL,
469
b VARCHAR(5) NOT NULL,
473
INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
474
SELECT * FROM t1 WHERE a = 'TEST' AND
475
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
476
SELECT * FROM t1 WHERE a = 'TEST' AND
477
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
478
SELECT * FROM t1 WHERE a = 'TEST' AND
479
c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
480
SELECT * FROM t1 WHERE a = 'TEST' AND
481
c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
482
SELECT * FROM t1 WHERE a = 'TEST' AND
483
c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
484
SELECT * FROM t1 WHERE a = 'TEST' AND
485
c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
486
SELECT * FROM t1 WHERE a = 'TEST' AND
487
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
488
EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
489
c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
493
--echo # Bug #46175: NULL read_view and consistent read assertion
496
CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
497
CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
498
INSERT INTO t1 VALUES (),();
499
INSERT INTO t2 VALUES (),();
500
CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
501
WHERE b =(SELECT a FROM t1 LIMIT 1);
505
CONNECT (con1, localhost, root,,);
511
CREATE PROCEDURE p1(num INT)
513
DECLARE i INT DEFAULT 0;
517
UNTIL i>num END REPEAT;
521
--echo # Should not crash
526
--echo # Should not crash
541
--echo # Bug #49324: more valgrind errors in test_if_skip_sort_order
543
CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
544
--echo #should not cause valgrind warnings
545
SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
549
--echo # Bug#50843: Filesort used instead of clustered index led to
550
--echo # performance degradation.
552
create table t1(f1 int not null primary key, f2 int) engine=innodb;
553
create table t2(f1 int not null, key (f1)) engine=innodb;
554
insert into t1 values (1,1),(2,2),(3,3);
555
insert into t2 values (1),(2),(3);
556
explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
561
--echo End of 5.1 tests