path: root/mysql-test/suite/innodb/t/innodb_mysql.test

# 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
-- source include/have_query_cache.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);

CONNECT (con1, localhost, root,,);
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 #39653: find_shortest_key in sql_select.cc does not consider
--echo #             clustered primary keys
--echo #

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT, f INT,
                 KEY (b,c)) ENGINE=INNODB;

INSERT INTO t1 VALUES (1,1,1,1,1,1), (2,2,2,2,2,2), (3,3,3,3,3,3),
                      (4,4,4,4,4,4), (5,5,5,5,5,5), (6,6,6,6,6,6),
		      (7,7,7,7,7,7), (8,8,8,8,8,8), (9,9,9,9,9,9),
		      (11,11,11,11,11,11);

--query_vertical EXPLAIN SELECT COUNT(*) FROM t1

DROP TABLE t1;

--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 #
--echo # Bug#54117 crash in thr_multi_unlock, temporary table
--echo #

CREATE TEMPORARY TABLE t1(a INT) ENGINE = InnoDB;

LOCK TABLES t1 READ;
ALTER TABLE t1 COMMENT 'test';
UNLOCK TABLES;

DROP TABLE t1;


--echo #
--echo # Bug#55826: create table .. select crashes with when KILL_BAD_DATA 
--echo #  is returned
--echo #
CREATE TABLE t1(a INT) ENGINE=innodb;
INSERT INTO t1 VALUES (0);
SET SQL_MODE='STRICT_ALL_TABLES';
--error ER_TRUNCATED_WRONG_VALUE
CREATE TABLE t2 
  SELECT LEAST((SELECT '' FROM t1),NOW()) FROM `t1`;
DROP TABLE t1;  
SET SQL_MODE=DEFAULT;



--echo #
--echo # Bug#55580: segfault in read_view_sees_trx_id
--echo #
CREATE TABLE t1 (a INT) ENGINE=Innodb;
CREATE TABLE t2 (a INT) ENGINE=Innodb;
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 VALUES (1),(2);

connect (con1,localhost,root,,test);
connect (con2,localhost,root,,test);

connection con1;
START TRANSACTION;
SELECT * FROM t2 LOCK IN SHARE MODE;

connection con2;
START TRANSACTION;
SELECT * FROM t1 LOCK IN SHARE MODE;

connection con1;
let $conn_id= `SELECT CONNECTION_ID()`;
--send SELECT * FROM t1 FOR UPDATE

connection con2;
let $wait_timeout= 2;
let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE ID=$conn_id AND STATE='Sending data';
--source include/wait_condition.inc
--echo # should not crash
--error ER_LOCK_DEADLOCK
SELECT * FROM t1 GROUP BY (SELECT a FROM t2 LIMIT 1 FOR UPDATE) + t1.a;

connection default;
disconnect con1;
disconnect con2;

DROP TABLE t1,t2;

--echo #
--echo # Bug#55656: mysqldump can be slower after bug #39653 fix
--echo #

CREATE TABLE t1 (a INT , b INT, c INT, d INT,
  KEY (b), PRIMARY KEY (a,b)) ENGINE=INNODB;
INSERT INTO t1 VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3);
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1

DROP INDEX b ON t1;
CREATE INDEX b ON t1(a,b);
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1

DROP INDEX b ON t1;
CREATE INDEX b ON t1(a,b,c);
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1

DROP INDEX b ON t1;
CREATE INDEX b ON t1(a,b,c,d);
--query_vertical EXPLAIN SELECT COUNT(*) FROM t1

DROP TABLE t1;

--echo #
--echo # Bug#56862 Execution of a query that uses index merge returns a wrong result
--echo #

CREATE TABLE t1 (
  pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a int,
  b int,
  INDEX idx(a))
ENGINE=INNODB;

INSERT INTO t1(a,b) VALUES
  (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
  (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
  (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
  (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1(a,b) SELECT a,b FROM t1;
INSERT INTO t1 VALUES (1000000, 0, 0);

SET SESSION sort_buffer_size = 1024*36;

EXPLAIN
SELECT COUNT(*) FROM
  (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
     WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

SELECT COUNT(*) FROM
  (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
     WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;

SET SESSION sort_buffer_size = DEFAULT;

DROP TABLE t1;

--echo #
--echo # ALTER TABLE IGNORE didn't ignore duplicates for unique add index
--echo #

create table t1 (a int primary key, b int) engine = innodb;
insert into t1 values (1,1),(2,1);
alter ignore table t1 add unique `main` (b);
select * from t1;
drop table t1;

--echo End of 5.1 tests

--echo #
--echo # Test for bug #39932 "create table fails if column for FK is in different
--echo #                      case than in corr index".
--echo #
--disable_warnings
drop tables if exists t1, t2;
--enable_warnings
create table t1 (pk int primary key) engine=InnoDB;
--echo # Even although the below statement uses uppercased field names in
--echo # foreign key definition it still should be able to find explicitly
--echo # created supporting index. So it should succeed and should not
--echo # create any additional supporting indexes.
create table t2 (fk int, key x (fk),
                 constraint x foreign key (FK) references t1 (PK)) engine=InnoDB;
show create table t2;
drop table t2, t1;

--echo #
--echo # Bug #663818: wrong result when BNLH is used 
--echo #

CREATE TABLE t1(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
  (1), (2), (11), (12), (13), (14),
  (15), (16), (17), (18), (19);
CREATE TABLE t2(pk int NOT NULL PRIMARY KEY) ENGINE=InnoDB;
INSERT INTO t2 VALUES 
  (1), (10), (11), (12), (13), (14),
  (15), (16), (17), (18), (19), (20), (21);

SET SESSION join_buffer_size=10000;

SET SESSION join_cache_level=3;
EXPLAIN
SELECT t1.pk FROM t1,t2 
  WHERE t1.pk = t2.pk AND t2.pk <> 8;
SELECT t1.pk FROM t1,t2 
  WHERE t1.pk = t2.pk AND t2.pk <> 8;

SET SESSION join_cache_level=1;
EXPLAIN
SELECT t1.pk FROM t1,t2 
  WHERE t1.pk = t2.pk AND t2.pk <> 8;
SELECT t1.pk FROM t1,t2 
  WHERE t1.pk = t2.pk AND t2.pk <> 8;

DROP TABLE t1,t2;

SET SESSION join_cache_level=DEFAULT;
SET SESSION join_buffer_size=DEFAULT;

--echo #
--echo # Bug#668644: HAVING + ORDER BY
--echo #

CREATE TABLE t1 (
  pk int  NOT NULL PRIMARY KEY, i int DEFAULT NULL,
  INDEX idx (i)
) ENGINE=INNODB;
INSERT INTO t1 VALUES
  (6,-1636630528),(2,-1097924608),(1,6),(3,6),(4,1148715008),(5,1541734400);

CREATE TABLE t2 (
  i int DEFAULT NULL,
  pk int NOT NULL PRIMARY KEY,
  INDEX idx (i)
) ENGINE= INNODB;
INSERT INTO t2 VALUES
  (-1993998336,20),(-1036582912,1),(-733413376,5),(-538247168,16),
  (-514260992,4),(-249561088,9),(1,2),(1,6),(2,10),(2,19),(4,17),
  (5,14),(5,15),(6,8),(7,13),(8,18),(9,11),(9,12),(257425408,7),
  (576061440,3);

EXPLAIN
SELECT t1 .i AS f FROM t1, t2
  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
  HAVING f > 7
  ORDER BY f;
SELECT t1 .i AS f FROM t1, t2
  WHERE t2.i = t1.pk AND t1.pk BETWEEN 0 AND 224
  HAVING f > 7
  ORDER BY f;

DROP TABLE t1, t2;

--echo #
--echo # Test for bug #56619 - Assertion failed during
--echo # ALTER TABLE RENAME, DISABLE KEYS
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
--enable_warnings
CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb;
--disable_warnings
ALTER TABLE t1 RENAME TO t2, DISABLE KEYS;
DROP TABLE IF EXISTS t1, t2;
--enable_warnings

--echo #
--echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::
--echo #                           UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK".
--echo #
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB;
INSERT INTO t1 VALUES (1);
LOCK TABLES t1 READ;
--echo # Even though temporary table was locked for READ we
--echo # still allow writes to it to be compatible with MyISAM.
--echo # This is possible since due to fact that temporary tables
--echo # are specific to connection and therefore locking for them
--echo # is irrelevant.
UPDATE t1 SET c = 5;
UNLOCK TABLES;
DROP TEMPORARY TABLE t1;

--echo End of 5.1 tests

--echo #
--echo # Bug#702322: HAVING with two ANDed predicates + ORDER BY
--echo #

CREATE TABLE t1 (pk int PRIMARY KEY, a int, KEY (a)) ENGINE=InnoDB;
CREATE TABLE t2 (a int, KEY (a)) ENGINE=InnoDB;

INSERT INTO t1 VALUES
  (18,0),(9,10),(8,11),(2,15),(7,19),(1,20);

SET SESSION join_cache_level = 0;

EXPLAIN 
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a 
  WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11
  ORDER BY t1.a;
SELECT t1.a FROM t1 LEFT JOIN t2 ON t1.pk = t2.a 
  WHERE t1.pk >= 6 HAVING t1.a<> 0 AND t1.a <> 11
  ORDER BY t1.a;
  
DROP TABLE t1,t2;

--echo End of 5.3 tests