summaryrefslogtreecommitdiff
path: root/mysql-test/suite/innodb/t/innodb_mysql.test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb_mysql.test')
-rw-r--r--mysql-test/suite/innodb/t/innodb_mysql.test755
1 files changed, 755 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
new file mode 100644
index 00000000000..8d3af2b3a5d
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -0,0 +1,755 @@
+# t/innodb_mysql.test
+#
+# Last update:
+# 2006-07-26 ML test refactored (MySQL 5.1)
+# main testing code t/innodb_mysql.test -> include/mix1.inc
+#
+
+# Slow test, don't run during staging part
+-- source include/not_staging.inc
+-- source include/have_innodb.inc
+let $engine_type= InnoDB;
+let $other_engine_type= MEMORY;
+# InnoDB does support FOREIGN KEYFOREIGN KEYs
+let $test_foreign_keys= 1;
+set global innodb_support_xa=default;
+set session innodb_support_xa=default;
+--source include/mix1.inc
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+#
+# BUG#35850: Performance regression in 5.1.23/5.1.24
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
+insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
+--echo this must use key 'a', not PRIMARY:
+--replace_column 9 #
+explain select a from t2 where a=b;
+drop table t1, t2;
+
+#
+# Bug #40360: Binlog related errors with binlog off
+#
+# This bug is triggered when the binlog format is STATEMENT and the
+# binary log is turned off. In this case, no error should be shown for
+# the statement since there are no replication issues.
+
+SET SESSION BINLOG_FORMAT=STATEMENT;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation;
+CREATE TABLE t1 ( a INT ) ENGINE=InnoDB;
+INSERT INTO t1 VALUES(1);
+DROP TABLE t1;
+
+#
+# Bug#37284 Crash in Field_string::type()
+#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+CREATE TABLE t1 (a char(50)) ENGINE=InnoDB;
+CREATE INDEX i1 on t1 (a(3));
+SELECT * FROM t1 WHERE a = 'abcde';
+DROP TABLE t1;
+
+#
+# Bug #37742: HA_EXTRA_KEYREAD flag is set when key contains only prefix of
+# requested column
+#
+
+CREATE TABLE foo (a int, b int, c char(10),
+ PRIMARY KEY (c(3)),
+ KEY b (b)
+) engine=innodb;
+
+CREATE TABLE foo2 (a int, b int, c char(10),
+ PRIMARY KEY (c),
+ KEY b (b)
+) engine=innodb;
+
+CREATE TABLE bar (a int, b int, c char(10),
+ PRIMARY KEY (c(3)),
+ KEY b (b)
+) engine=myisam;
+
+INSERT INTO foo VALUES
+ (1,2,'abcdefghij'), (2,3,''), (3,4,'klmnopqrst'),
+ (4,5,'uvwxyz'), (5,6,'meotnsyglt'), (4,5,'asfdewe');
+
+INSERT INTO bar SELECT * FROM foo;
+INSERT INTO foo2 SELECT * FROM foo;
+
+--query_vertical EXPLAIN SELECT c FROM bar WHERE b>2;
+--query_vertical EXPLAIN SELECT c FROM foo WHERE b>2;
+--query_vertical EXPLAIN SELECT c FROM foo2 WHERE b>2;
+
+--query_vertical EXPLAIN SELECT c FROM bar WHERE c>2;
+--query_vertical EXPLAIN SELECT c FROM foo WHERE c>2;
+--query_vertical EXPLAIN SELECT c FROM foo2 WHERE c>2;
+
+DROP TABLE foo, bar, foo2;
+
+
+#
+# Bug#41348: INSERT INTO tbl SELECT * FROM temp_tbl overwrites locking type of temp table
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t3,t2;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+DELIMITER |;
+CREATE FUNCTION f1() RETURNS VARCHAR(250)
+ BEGIN
+ return 'hhhhhhh' ;
+ END|
+DELIMITER ;|
+
+CREATE TABLE t1 (a VARCHAR(20), b VARCHAR(20), c VARCHAR(20)) ENGINE=INNODB;
+
+BEGIN WORK;
+
+CREATE TEMPORARY TABLE t2 (a VARCHAR(20), b VARCHAR(20), c varchar(20)) ENGINE=INNODB;
+CREATE TEMPORARY TABLE t3 LIKE t2;
+
+INSERT INTO t1 VALUES ('a','b',NULL),('c','d',NULL),('e','f',NULL);
+
+SET @stmt := CONCAT('INSERT INTO t2 SELECT tbl.a, tbl.b, f1()',' FROM t1 tbl');
+PREPARE stmt1 FROM @stmt;
+
+SET @stmt := CONCAT('INSERT INTO t3', ' SELECT * FROM t2');
+PREPARE stmt3 FROM @stmt;
+
+EXECUTE stmt1;
+
+COMMIT;
+
+DEALLOCATE PREPARE stmt1;
+DEALLOCATE PREPARE stmt3;
+
+DROP TABLE t1,t3,t2;
+DROP FUNCTION f1;
+
+#
+# Bug#37016: TRUNCATE TABLE removes some rows but not all
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+CREATE TABLE t1 (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=INNODB;
+CREATE TABLE t2 (id INT PRIMARY KEY,
+ t1_id INT, INDEX par_ind (t1_id),
+ FOREIGN KEY (t1_id) REFERENCES t1(id)) ENGINE=INNODB;
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (3,2);
+
+SET AUTOCOMMIT = 0;
+
+START TRANSACTION;
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+COMMIT;
+SELECT * FROM t1;
+
+START TRANSACTION;
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+ROLLBACK;
+SELECT * FROM t1;
+
+SET AUTOCOMMIT = 1;
+
+START TRANSACTION;
+SELECT * FROM t1;
+COMMIT;
+
+--error ER_ROW_IS_REFERENCED_2
+TRUNCATE TABLE t1;
+SELECT * FROM t1;
+DELETE FROM t2 WHERE id = 3;
+
+START TRANSACTION;
+SELECT * FROM t1;
+TRUNCATE TABLE t1;
+ROLLBACK;
+SELECT * FROM t1;
+TRUNCATE TABLE t2;
+
+DROP TABLE t2;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+--echo #
+CREATE TABLE t1 (
+ id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t2 (
+ id INT UNSIGNED NOT NULL AUTO_INCREMENT,
+ aid INT UNSIGNED NOT NULL,
+ PRIMARY KEY (id),
+ FOREIGN KEY (aid) REFERENCES t1 (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t3 (
+ bid INT UNSIGNED NOT NULL,
+ FOREIGN KEY (bid) REFERENCES t2 (id)
+) ENGINE=InnoDB;
+
+CREATE TABLE t4 (
+ a INT
+) ENGINE=InnoDB;
+
+CREATE TABLE t5 (
+ a INT
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id, aid) VALUES (1, 1),(2,1),(3,1),(4,1);
+INSERT INTO t3 (bid) VALUES (1);
+
+INSERT INTO t4 VALUES (1),(2),(3),(4),(5);
+INSERT INTO t5 VALUES (1);
+
+DELETE t5 FROM t4 LEFT JOIN t5 ON t4.a= t5.a;
+
+--error ER_ROW_IS_REFERENCED_2
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+--error ER_ROW_IS_REFERENCED_2
+DELETE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+
+DELETE IGNORE t2, t1 FROM t2 INNER JOIN t1 ON (t2.aid = t1.id) WHERE t2.id = 1;
+
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP TABLES t4,t5;
+
+--echo # Bug#40127 Multiple table DELETE IGNORE hangs on foreign key constraint violation on 5.0
+--echo # Testing for any side effects of IGNORE on AFTER DELETE triggers used with
+--echo # transactional tables.
+--echo #
+CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a VARCHAR(100)) ENGINE=InnoDB;
+CREATE TABLE t3 (i INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t4 (i INT NOT NULL PRIMARY KEY, t1i INT,
+ FOREIGN KEY (t1i) REFERENCES t1(i))
+ ENGINE=InnoDB;
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+ SET @b:='EXECUTED TRIGGER';
+ INSERT INTO t2 VALUES (@b);
+ SET @a:= error_happens_here;
+END||
+delimiter ;||
+
+SET @b:="";
+SET @a:="";
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 SELECT * FROM t1;
+--echo ** An error in a trigger causes rollback of the statement.
+--error ER_BAD_FIELD_ERROR
+DELETE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT @a,@b;
+SELECT * FROM t2;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+
+--echo ** Same happens with the IGNORE option
+--error ER_BAD_FIELD_ERROR
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT * FROM t2;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+
+--echo **
+--echo ** The following is an attempt to demonstrate
+--echo ** error handling inside a row iteration.
+--echo **
+DROP TRIGGER trg;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+TRUNCATE TABLE t3;
+
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+INSERT INTO t4 VALUES (3,3),(4,4);
+
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+ SET @b:= CONCAT('EXECUTED TRIGGER FOR ROW ',CAST(OLD.i AS CHAR));
+ INSERT INTO t2 VALUES (@b);
+END||
+delimiter ;||
+
+--echo ** DELETE is prevented by foreign key constrains but errors are silenced.
+--echo ** The AFTER trigger isn't fired.
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+--echo ** Tables are modified by best effort:
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+--echo ** The AFTER trigger was only executed on successful rows:
+SELECT * FROM t2;
+
+DROP TRIGGER trg;
+
+--echo **
+--echo ** Induce an error midway through an AFTER-trigger
+--echo **
+TRUNCATE TABLE t4;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t3;
+INSERT INTO t1 VALUES (1),(2),(3),(4);
+INSERT INTO t3 VALUES (1),(2),(3),(4);
+delimiter ||;
+CREATE TRIGGER trg AFTER DELETE ON t1 FOR EACH ROW
+BEGIN
+ SET @a:= @a+1;
+ IF @a > 2 THEN
+ INSERT INTO t4 VALUES (5,5);
+ END IF;
+END||
+delimiter ;||
+
+SET @a:=0;
+--echo ** Errors in the trigger causes the statement to abort.
+--error ER_NO_REFERENCED_ROW_2
+DELETE IGNORE t1 FROM t3 LEFT JOIN t1 ON t1.i=t3.i;
+SELECT * FROM t1 LEFT JOIN t3 ON t1.i=t3.i;
+SELECT * FROM t4;
+
+DROP TRIGGER trg;
+DROP TABLE t4;
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+#
+# Bug#43580: Issue with Innodb on multi-table update
+#
+CREATE TABLE t1 (a INT, b INT, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t2 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+
+CREATE TABLE t3 (a INT, b INT KEY, KEY (a)) ENGINE = INNODB;
+CREATE TABLE t4 (a INT KEY, b INT, KEY (b)) ENGINE = INNODB;
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6);
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+INSERT INTO t3 VALUES (1, 101), (2, 102), (3, 103), (4, 104), (5, 105), (6, 106);
+INSERT INTO t4 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
+
+UPDATE t1, t2 SET t1.a = t1.a + 100, t2.b = t1.a + 10
+WHERE t1.a BETWEEN 2 AND 4 AND t2.a = t1.b;
+--sorted_result
+SELECT * FROM t2;
+
+UPDATE t3, t4 SET t3.a = t3.a + 100, t4.b = t3.a + 10
+WHERE t3.a BETWEEN 2 AND 4 AND t4.a = t3.b - 100;
+--sorted_result
+SELECT * FROM t4;
+
+DROP TABLE t1, t2, t3, t4;
+
+--echo #
+--echo # Bug#44886: SIGSEGV in test_if_skip_sort_order() -
+--echo # uninitialized variable used as subscript
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT, d INT, PRIMARY KEY (b), KEY (a,c))
+ ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1,1,1,0);
+
+CREATE TABLE t2 (a INT, b INT, e INT, KEY (e)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES (1,1,2);
+
+CREATE TABLE t3 (a INT, b INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (1, 1);
+
+SELECT * FROM t1, t2, t3
+ WHERE t1.a = t3.a AND (t1.b = t3.b OR t1.d) AND t2.b = t1.b AND t2.e = 2
+ GROUP BY t1.b;
+
+DROP TABLE t1, t2, t3;
+
+--echo #
+--echo # Bug #45828: Optimizer won't use partial primary key if another
+--echo # index can prevent filesort
+--echo #
+
+# Create the table
+CREATE TABLE `t1` (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 int NOT NULL,
+ PRIMARY KEY (c1,c2),
+ KEY (c3)
+) ENGINE=InnoDB;
+
+# populate with data
+INSERT INTO t1 VALUES (5,2,1246276747);
+INSERT INTO t1 VALUES (2,1,1246281721);
+INSERT INTO t1 VALUES (7,3,1246281756);
+INSERT INTO t1 VALUES (4,2,1246282139);
+INSERT INTO t1 VALUES (3,1,1246282230);
+INSERT INTO t1 VALUES (1,0,1246282712);
+INSERT INTO t1 VALUES (8,3,1246282765);
+INSERT INTO t1 SELECT c1+10,c2+10,c3+10 FROM t1;
+INSERT INTO t1 SELECT c1+100,c2+100,c3+100 from t1;
+INSERT INTO t1 SELECT c1+1000,c2+1000,c3+1000 from t1;
+INSERT INTO t1 SELECT c1+10000,c2+10000,c3+10000 from t1;
+INSERT INTO t1 SELECT c1+100000,c2+100000,c3+100000 from t1;
+INSERT INTO t1 SELECT c1+1000000,c2+1000000,c3+1000000 from t1;
+
+# query and no rows will match the c1 condition, whereas all will match c3
+SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# SHOULD use the pk.
+# index on c3 will be used instead of primary key
+EXPLAIN SELECT * FROM t1 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+# if we force the primary key, we can see the estimate is 1
+EXPLAIN SELECT * FROM t1 FORCE INDEX (PRIMARY) WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+
+CREATE TABLE t2 (
+ c1 int NOT NULL,
+ c2 int NOT NULL,
+ c3 int NOT NULL,
+ KEY (c1,c2),
+ KEY (c3)
+) ENGINE=InnoDB;
+
+# SHOULD use the pk.
+# if we switch it from a primary key to a regular index, it works correctly as well
+explain SELECT * FROM t2 WHERE c1 = 99999999 AND c3 > 1 ORDER BY c3;
+
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # 36259: Optimizing with ORDER BY
+--echo #
+
+CREATE TABLE t1 (
+ a INT NOT NULL AUTO_INCREMENT,
+ b INT NOT NULL,
+ c INT NOT NULL,
+ d VARCHAR(5),
+ e INT NOT NULL,
+ PRIMARY KEY (a), KEY i2 (b,c,d)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 (b,c,d,e) VALUES (1,1,'a',1), (2,2,'b',2);
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+INSERT INTO t1 (b,c,d,e) SELECT RAND()*10000, RAND()*10000, d, e FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE b=1 AND c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(i2) WHERE b=1 and c=1 ORDER BY a;
+EXPLAIN SELECT * FROM t1 FORCE INDEX(PRIMARY) WHERE b=1 AND c=1 ORDER BY a;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #47963: Wrong results when index is used
+--echo #
+CREATE TABLE t1(
+ a VARCHAR(5) NOT NULL,
+ b VARCHAR(5) NOT NULL,
+ c DATETIME NOT NULL,
+ KEY (c)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES('TEST', 'TEST', '2009-10-09 00:00:00');
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00.0';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.0' AND c <= '2009-10-09 00:00:00';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00' AND c <= '2009-10-09 00:00:00.0';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.000' AND c <= '2009-10-09 00:00:00.000';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.00' AND c <= '2009-10-09 00:00:00.001';
+SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+EXPLAIN SELECT * FROM t1 WHERE a = 'TEST' AND
+ c >= '2009-10-09 00:00:00.001' AND c <= '2009-10-09 00:00:00.00';
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #46175: NULL read_view and consistent read assertion
+--echo #
+
+CREATE TABLE t1(a CHAR(13),KEY(a)) ENGINE=innodb;
+CREATE TABLE t2(b DATETIME,KEY(b)) ENGINE=innodb;
+INSERT INTO t1 VALUES (),();
+INSERT INTO t2 VALUES (),();
+CREATE OR REPLACE VIEW v1 AS SELECT 1 FROM t2
+ WHERE b =(SELECT a FROM t1 LIMIT 1);
+
+--disable_query_log
+--disable_result_log
+CONNECT (con1, localhost, root,,);
+--enable_query_log
+--enable_result_log
+CONNECTION default;
+
+DELIMITER |;
+CREATE PROCEDURE p1(num INT)
+BEGIN
+ DECLARE i INT DEFAULT 0;
+ REPEAT
+ SHOW CREATE VIEW v1;
+ SET i:=i+1;
+ UNTIL i>num END REPEAT;
+END|
+DELIMITER ;|
+
+--echo # Should not crash
+--disable_query_log
+--disable_result_log
+--send CALL p1(1000)
+CONNECTION con1;
+--echo # Should not crash
+CALL p1(1000);
+
+CONNECTION default;
+--reap
+--enable_query_log
+--enable_result_log
+
+DISCONNECT con1;
+DROP PROCEDURE p1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
+
+--echo #
+--echo # Bug #49324: more valgrind errors in test_if_skip_sort_order
+--echo #
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=innodb ;
+--echo #should not cause valgrind warnings
+SELECT 1 FROM t1 JOIN t1 a USING(a) GROUP BY t1.a,t1.a;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#50843: Filesort used instead of clustered index led to
+--echo # performance degradation.
+--echo #
+create table t1(f1 int not null primary key, f2 int) engine=innodb;
+create table t2(f1 int not null, key (f1)) engine=innodb;
+insert into t1 values (1,1),(2,2),(3,3);
+insert into t2 values (1),(2),(3);
+explain select t1.* from t1 left join t2 using(f1) group by t1.f1;
+drop table t1,t2;
+--echo #
+
+
+--echo #
+--echo # Bug #49838: DROP INDEX and ADD UNIQUE INDEX for same index may
+--echo # corrupt definition at engine
+--echo #
+
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, KEY k (a,b))
+ ENGINE=InnoDB;
+
+ALTER TABLE t1 DROP INDEX k, ADD UNIQUE INDEX k (a,b);
+
+--query_vertical SHOW INDEXES FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #53334: wrong result for outer join with impossible ON condition
+--echo # (see the same test case for MyISAM in join.test)
+--echo #
+
+create table t1 (id int primary key);
+create table t2 (id int);
+
+insert into t1 values (75);
+insert into t1 values (79);
+insert into t1 values (78);
+insert into t1 values (77);
+replace into t1 values (76);
+replace into t1 values (76);
+insert into t1 values (104);
+insert into t1 values (103);
+insert into t1 values (102);
+insert into t1 values (101);
+insert into t1 values (105);
+insert into t1 values (106);
+insert into t1 values (107);
+
+insert into t2 values (107),(75),(1000);
+
+select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+ where t2.id=75 and t1.id is null;
+explain select t1.id,t2.id from t2 left join t1 on t1.id>=74 and t1.id<=0
+ where t2.id=75 and t1.id is null;
+
+drop table t1,t2;
+
+--echo #
+--echo # Bug #47453: InnoDB incorrectly changes TIMESTAMP columns when
+--echo # JOINed during an UPDATE
+--echo #
+
+CREATE TABLE t1 (d INT) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT, b INT,
+ c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
+ ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
+
+--echo set up our data elements
+INSERT INTO t1 (d) VALUES (1);
+INSERT INTO t2 (a,b) VALUES (1,1);
+SELECT SECOND(c) INTO @bug47453 FROM t2;
+
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+
+SELECT SLEEP(1);
+
+UPDATE t1 JOIN t2 ON d=a SET b=1 WHERE a=1;
+
+--echo #should be 0
+SELECT SECOND(c)-@bug47453 FROM t1 JOIN t2 ON d=a;
+
+DROP TABLE t1, t2;
+
+--echo #
+--echo # Bug#38999 valgrind warnings for update statement in function compare_record()
+--echo #
+
+CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
+CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB;
+INSERT INTO t1 values (1),(2),(3),(4),(5);
+INSERT INTO t2 values (1);
+
+SELECT * FROM t1 WHERE a = 2;
+UPDATE t1,t2 SET t1.a = t1.a + 100 WHERE t1.a = 1;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug #53334: wrong result for outer join with impossible ON condition
+--echo # (see the same test case for MyISAM in join.test)
+--echo #
+
+CREATE TABLE t1 (id INT PRIMARY KEY);
+CREATE TABLE t2 (id INT);
+
+INSERT INTO t1 VALUES (75);
+INSERT INTO t1 VALUES (79);
+INSERT INTO t1 VALUES (78);
+INSERT INTO t1 VALUES (77);
+REPLACE INTO t1 VALUES (76);
+REPLACE INTO t1 VALUES (76);
+INSERT INTO t1 VALUES (104);
+INSERT INTO t1 VALUES (103);
+INSERT INTO t1 VALUES (102);
+INSERT INTO t1 VALUES (101);
+INSERT INTO t1 VALUES (105);
+INSERT INTO t1 VALUES (106);
+INSERT INTO t1 VALUES (107);
+
+INSERT INTO t2 VALUES (107),(75),(1000);
+
+SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
+ WHERE t2.id=75 AND t1.id IS NULL;
+EXPLAIN SELECT t1.id,t2.id FROM t2 LEFT JOIN t1 ON t1.id>=74 AND t1.id<=0
+ WHERE t2.id=75 AND t1.id IS NULL;
+
+DROP TABLE t1,t2;
+
+--echo #
+--echo # Bug #53830: !table || (!table->read_set || bitmap_is_set(table->read_set, field_index))
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, c INT, d INT,
+ PRIMARY KEY(a,b,c), KEY(b,d))
+ ENGINE=InnoDB;
+INSERT INTO t1 VALUES (0, 77, 1, 3);
+
+UPDATE t1 SET d = 0 WHERE b = 77 AND c = 25;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#50389 Using intersect does not return all rows
+--echo #
+
+CREATE TABLE t1 (
+ f1 INT(10) NOT NULL,
+ f2 INT(10),
+ f3 INT(10),
+ f4 TINYINT(4),
+ f5 VARCHAR(50),
+ PRIMARY KEY (f1),
+ KEY idx1 (f2,f5,f4),
+ KEY idx2 (f2,f4)
+) ENGINE=InnoDB;
+
+LOAD DATA INFILE '../../std_data/intersect-bug50389.tsv' INTO TABLE t1;
+
+SELECT * FROM t1 WHERE f1 IN
+(3305028,3353871,3772880,3346860,4228206,3336022,
+ 3470988,3305175,3329875,3817277,3856380,3796193,
+ 3784744,4180925,4559596,3963734,3856391,4494153)
+AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
+
+EXPLAIN SELECT * FROM t1 WHERE f1 IN
+(3305028,3353871,3772880,3346860,4228206,3336022,
+ 3470988,3305175,3329875,3817277,3856380,3796193,
+ 3784744,4180925,4559596,3963734,3856391,4494153)
+AND f5 = 'abcdefghijklmnopwrst' AND f2 = 1221457 AND f4 = 0 ;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#51431 Wrong sort order after import of dump file
+--echo #
+
+CREATE TABLE t1 (
+ f1 INT(11) NOT NULL,
+ f2 int(11) NOT NULL,
+ f3 int(11) NOT NULL,
+ f4 tinyint(1) NOT NULL,
+ PRIMARY KEY (f1),
+ UNIQUE KEY (f2, f3),
+ KEY (f4)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),
+(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),
+(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),
+(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),
+(26,1,9921,1), (27,1,9922,1);
+
+FLUSH TABLES;
+
+SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+EXPLAIN SELECT * FROM t1 WHERE f2 = 1 AND f4 = TRUE
+ORDER BY f1 DESC LIMIT 5;
+
+DROP TABLE t1;
+
+--echo End of 5.1 tests
View Lorry Controller Status