path: root/mysql-test/t/merge.test

#
# Test of MERGE TABLES
#

# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

# MERGE tables require MyISAM tables
let $default=`select @@global.storage_engine`;
set global storage_engine=myisam;
set session storage_engine=myisam;

# Clean up resources used in this test case.
--disable_warnings
drop table if exists t1,t2,t3,t4,t5,t6;
drop database if exists mysqltest;
--enable_warnings

let $MYSQLD_DATADIR= `select @@datadir`;

create table t1 (a int not null primary key auto_increment, message char(20));
create table t2 (a int not null primary key auto_increment, message char(20));
INSERT INTO t1 (message) VALUES ("Testing"),("table"),("t1");
INSERT INTO t2 (message) VALUES ("Testing"),("table"),("t2");
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(t1,t2);
select * from t3;
select * from t3 order by a desc;
drop table t3;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
insert into t2 select NULL,message from t1;
insert into t1 select NULL,message from t2;
create table t3 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,test.t2);
explain select * from t3 where a < 10;
explain select * from t3 where a > 10 and a < 20;
select * from t3 where a = 10;
select * from t3 where a < 10;
select * from t3 where a > 10 and a < 20;
explain select a from t3 order by a desc limit 10;
select a from t3 order by a desc limit 10;
select a from t3 order by a desc limit 300,10;
delete from t3 where a=3;
select * from t3 where a < 10;
delete from t3 where a >= 6 and a <= 8;
select * from t3 where a < 10;
update t3 set a=3 where a=9;
select * from t3 where a < 10;
update t3 set a=6 where a=7;
select * from t3 where a < 10;
show create table t3;

# The following should give errors
create table t4 (a int not null, b char(10), key(a)) engine=MERGE UNION=(t1,t2);
--error 1168
select * from t4;
--error 1168
alter table t4 add column c int;
flush tables;
--error 1168
select * from t4;

#
# Test tables in different databases
#
create database mysqltest;
create table mysqltest.t6 (a int not null primary key auto_increment, message char(20));
create table t5 (a int not null, b char(20), key(a)) engine=MERGE UNION=(test.t1,mysqltest.t6);
show create table t5;
alter table t5 engine=myisam;
drop table t5, mysqltest.t6;
drop database mysqltest;

# Because of windows, it's important that we drop the merge tables first!
drop table t4,t3,t1,t2;
  
create table t1 (c char(10)) engine=myisam;
create table t2 (c char(10)) engine=myisam;
create table t3 (c char(10)) union=(t1,t2) engine=merge;
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t1 (c) values ('test1');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
insert into t2 (c) values ('test2');
select * from t3;
select * from t3;
delete from t3 where 1=1;
select * from t3;
select * from t1;
drop table t3,t2,t1;

#
# Test 2
#

CREATE TABLE t1 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t2 (incr int not null, othr int not null, primary key(incr));
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
ENGINE=MERGE UNION=(t1,t2);

SELECT * from t3;

INSERT INTO t1 VALUES ( 1,10),( 3,53),( 5,21),( 7,12),( 9,17);
INSERT INTO t2 VALUES ( 2,24),( 4,33),( 6,41),( 8,26),( 0,32);
INSERT INTO t1 VALUES (11,20),(13,43),(15,11),(17,22),(19,37);
INSERT INTO t2 VALUES (12,25),(14,31),(16,42),(18,27),(10,30);

SELECT * from t3 where incr in (1,2,3,4) order by othr;
alter table t3 UNION=(t1);
select count(*) from t3;
alter table t3 UNION=(t1,t2);
select count(*) from t3;
alter table t3 ENGINE=MYISAM;
select count(*) from t3;

# Test that ALTER TABLE rembers the old UNION

drop table t3;
CREATE TABLE t3 (incr int not null, othr int not null, primary key(incr))
ENGINE=MERGE UNION=(t1,t2);
show create table t3;
alter table t3 drop primary key;
show create table t3;

drop table t3,t2,t1;

#
# Test table without unions
#
create table t1 (a int not null, key(a)) engine=merge;
select * from t1;
drop table t1;

#
# Bug in flush tables combined with MERGE tables
#

create table t1 (a int not null, b int not null, key(a,b));
create table t2 (a int not null, b int not null, key(a,b));
create table t3 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2);
insert into t1 values (1,2),(2,1),(0,0),(4,4),(5,5),(6,6);
insert into t2 values (1,1),(2,2),(0,0),(4,4),(5,5),(6,6);
flush tables;
select * from t3 where a=1 order by b limit 2;
drop table t3,t1,t2;

#
# [phi] testing INSERT_METHOD stuff
#

# first testing of common stuff with new parameters
create table t1 (a int not null, b int not null auto_increment, primary key(a,b));
create table t2 (a int not null, b int not null auto_increment, primary key(a,b));
create table t3 (a int not null, b int not null, key(a,b)) UNION=(t1,t2) INSERT_METHOD=NO;
create table t4 (a int not null, b int not null, key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=NO;
create table t5 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST;
create table t6 (a int not null, b int not null auto_increment, primary key(a,b)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
show create table t3;
show create table t4;
show create table t5;
show create table t6;
insert into t1 values (1,NULL),(1,NULL),(1,NULL),(1,NULL);
insert into t2 values (2,NULL),(2,NULL),(2,NULL),(2,NULL);
select * from t3 order by b,a limit 3;
select * from t4 order by b,a limit 3;
select * from t5 order by b,a limit 3,3;
select * from t6 order by b,a limit 6,3;
# now testing inserts and where the data gets written
insert into t5 values (5,1),(5,2);
insert into t6 values (6,1),(6,2);
select * from t1 order by a,b;
select * from t2 order by a,b;
select * from t4 order by a,b;
# preperation for next test
insert into t3 values (3,1),(3,2),(3,3),(3,4);
select * from t3 order by a,b;
# now testing whether options are kept by alter table
alter table t4 UNION=(t1,t2,t3);
show create table t4;
select * from t4 order by a,b;
# testing switching off insert method and inserts again
alter table t4 INSERT_METHOD=FIRST;
show create table t4;
insert into t4 values (4,1),(4,2);
select * from t1 order by a,b;
select * from t2 order by a,b;
select * from t3 order by a,b;
select * from t4 order by a,b;
select * from t5 order by a,b;
# auto_increment
select 1;
insert into t5 values (1,NULL),(5,NULL);
insert into t6 values (2,NULL),(6,NULL);
select * from t1 order by a,b;
select * from t2 order by a,b;
select * from t5 order by a,b;
select * from t6 order by a,b;
insert into t1 values (99,NULL);
select * from t4 where a+0 > 90;
# bug#4008 - cannot determine a unique key that caused "dupl. key error"
--error ER_DUP_ENTRY
insert t5 values (1,1);
--error ER_DUP_ENTRY
insert t6 values (2,1);
insert t5 values (1,1) on duplicate key update b=b+10;
insert t6 values (2,1) on duplicate key update b=b+20;
select * from t5 where a < 3;
drop table t6, t5, t4, t3, t2, t1;

CREATE TABLE t1 (  a int(11) NOT NULL default '0',  b int(11) NOT NULL default '0',  PRIMARY KEY  (a,b)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,1), (2,1);
CREATE TABLE t2 (  a int(11) NOT NULL default '0',  b int(11) NOT NULL default '0',  PRIMARY KEY  (a,b)) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,2), (2,2);
CREATE TABLE t3 (  a int(11) NOT NULL default '0',  b int(11) NOT NULL default '0',  KEY a (a,b)) ENGINE=MRG_MyISAM UNION=(t1,t2);
select max(b) from t3 where a = 2;
select max(b) from t1 where a = 2;
drop table t3,t1,t2;

#
# temporary merge tables
#
create table t1 (a int not null);
create table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
create temporary table t4 (a int not null);
create temporary table t5 (a int not null);
insert into t4 values (1);
insert into t5 values (2);
create temporary table t6 (a int not null) ENGINE=MERGE UNION=(t4,t5);
select * from t6;
drop table t6, t3, t1, t2, t4, t5;
#
# Bug#19627 - temporary merge table locking
# MERGE table and its children must match in temporary type.
# Forbid temporary merge on non-temporary children: shown above.
# Forbid non-temporary merge on temporary children:
create temporary table t1 (a int not null);
create temporary table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
--error ER_WRONG_MRG_TABLE
select * from t3;
drop table t3, t2, t1;
# Forbid children mismatch in temporary:
create table t1 (a int not null);
create temporary table t2 (a int not null);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
--error ER_WRONG_MRG_TABLE
select * from t3;
drop table t3;
create temporary table t3 (a int not null) ENGINE=MERGE UNION=(t1,t2);
select * from t3;
drop table t3, t2, t1;
--echo # CREATE...SELECT is not implemented for MERGE tables.
CREATE TEMPORARY TABLE t1 (c1 INT NOT NULL);
CREATE TEMPORARY TABLE t2 (c1 INT NOT NULL);
CREATE TABLE t3 (c1 INT NOT NULL);
INSERT INTO t3 VALUES (3), (33);
LOCK TABLES t3 READ;
--error ER_WRONG_OBJECT
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
  INSERT_METHOD=LAST SELECT * FROM t3;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t4;
UNLOCK TABLES;
CREATE TEMPORARY TABLE t4 (c1 INT NOT NULL) ENGINE=MERGE UNION=(t1,t2)
  INSERT_METHOD=LAST;
INSERT INTO t4 SELECT * FROM t3;
--echo # Alter temporary MERGE table.
ALTER TABLE t4 UNION=(t1);
LOCK TABLES t4 WRITE;
--echo # Alter temporary MERGE table under LOCk tables.
ALTER TABLE t4 UNION=(t1,t2);
UNLOCK TABLES;
--echo # MERGE table and function.
CREATE FUNCTION f1 () RETURNS INT RETURN (SELECT max(c1) FROM t3);
SELECT * FROM t4 WHERE c1 < f1();
DROP FUNCTION f1;
DROP TABLE t4, t3, t2, t1;

#
# testing merge::records_in_range and optimizer
#

CREATE TABLE t1 (
  fileset_id tinyint(3) unsigned NOT NULL default '0',
  file_code varchar(32) NOT NULL default '',
  fileset_root_id tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (fileset_id,file_code),
  KEY files (fileset_id,fileset_root_id)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (2, '0000000111', 1), (2, '0000000112', 1), (2, '0000000113', 1),
(2, '0000000114', 1), (2, '0000000115', 1), (2, '0000000116', 1), (2, '0000000117', 1),
(2, '0000000118', 1), (2, '0000000119', 1), (2, '0000000120', 1);
CREATE TABLE t2 (
  fileset_id tinyint(3) unsigned NOT NULL default '0',
  file_code varchar(32) NOT NULL default '',
  fileset_root_id tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (fileset_id,file_code),
  KEY files (fileset_id,fileset_root_id)
) ENGINE=MRG_MyISAM UNION=(t1);

EXPLAIN SELECT * FROM t2 IGNORE INDEX (files) WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
EXPLAIN SELECT * FROM t1 WHERE fileset_id = 2
AND file_code BETWEEN '0000000115' AND '0000000120' LIMIT 1;
EXPLAIN SELECT * FROM t2 WHERE fileset_id = 2
AND file_code = '0000000115' LIMIT 1;
DROP TABLE t2, t1;

#
# Test of ORDER BY DESC on key (Bug #515)
#

create table t1 (x int, y int, index xy(x, y));
create table t2 (x int, y int, index xy(x, y));
create table t3 (x int, y int, index xy(x, y)) engine=merge union=(t1,t2);
insert into t1 values(1, 2);
insert into t2 values(1, 3);
select * from t3 where x = 1 and y < 5 order by y;
# Bug is that followng query returns empty set while it must be same as above
select * from t3 where x = 1 and y < 5 order by y desc;
drop table t1,t2,t3;

#
# Bug#5232: CREATE TABLE ... SELECT
#

create table t1 (a int);
create table t2 (a int);
insert into t1 values (0);
insert into t2 values (1);
--error ER_WRONG_OBJECT
create table t3 engine=merge union=(t1, t2) select * from t1;
--error ER_WRONG_OBJECT
create table t3 engine=merge union=(t1, t2) select * from t2;
--error ER_WRONG_OBJECT
create table t3 engine=merge union=(t1, t2) select (select max(a) from t2);
drop table t1, t2;

#
# Bug#9112 - Merge table with composite index producing invalid results with some queries
# This test case will fail only without the bugfix and some
# non-deterministic circumstances. It depends on properly initialized
# "un-initialized" memory. At the time it happens with a standard
# non-debug build. But there is no guarantee that this will be always so.
#
create table t1 (
 a double(14,4),
 b varchar(10),
 index (a,b)
) engine=merge union=(t2,t3);

create table t2 (
 a double(14,4),
 b varchar(10),
 index (a,b)
) engine=myisam;

create table t3 (
 a double(14,4),
 b varchar(10),
 index (a,b)
) engine=myisam;

insert into t2 values ( null, '');
insert into t2 values ( 9999999999.999, '');
insert into t3 select * from t2;
select min(a), max(a) from t1;
flush tables;
select min(a), max(a) from t1;
drop table t1, t2, t3;
# BUG#6699 : no sorting on 'ref' retrieval 
create table t1 (a int,b int,c int, index (a,b,c));
create table t2 (a int,b int,c int, index (a,b,c));
create table t3 (a int,b int,c int, index (a,b,c))
  engine=merge union=(t1 ,t2);
insert into t1 (a,b,c) values (1,1,0),(1,2,0);
insert into t2 (a,b,c) values (1,1,1),(1,2,1);

explain select a,b,c from t3 force index (a) where a=1 order by a,b,c;
select a,b,c from t3 force index (a) where a=1 order by a,b,c;

# this actually wasn't affected:
explain select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;
select a,b,c from t3 force index (a) where a=1 order by a desc, b desc, c desc;

# BUG#7377 SHOW index on MERGE table crashes debug server
show index from t3;

drop table t1, t2, t3;

#
# Bug#10400 - Improperly-defined MERGE table crashes with INSERT ... ON DUPLICATE KEY UPDATE
#
CREATE TABLE t1 ( a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(10), UNIQUE (b) )
  ENGINE=MyISAM;
CREATE TABLE t2 ( a INT AUTO_INCREMENT, b VARCHAR(10), INDEX (a), INDEX (b) )
  ENGINE=MERGE UNION (t1) INSERT_METHOD=FIRST;
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=2;
INSERT INTO t2 (b) VALUES (1) ON DUPLICATE KEY UPDATE b=3;
SELECT b FROM t2;
DROP TABLE t1, t2;


#
# BUG#5390 - problems with merge tables
# Problem #1: INSERT...SELECT
#
#drop table if exists t1, t2, t3;
create table t1(a int);
create table t2(a int);
insert into t1 values (1);
insert into t2 values (2);
create table t3 (a int) engine=merge union=(t1, t2) insert_method=first;
select * from t3;
#
insert t2 select * from t2;
select * from t2;
#
insert t3 select * from t1;
select * from t3;
#
insert t1 select * from t3;
select * from t1;
select * from t2;
select * from t3;
check table t1, t2;
drop table t1, t2, t3;

#
# BUG#21617 - crash when selecting from merge table with inconsistent
# indexes
#
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(2),(1);
CREATE TABLE t2(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM t2 WHERE a=2;
DROP TABLE t1, t2;

#
# BUG#10974 - No error message if merge table based on union of innodb,
# memory
#
CREATE TABLE t1(a INT) ENGINE=MEMORY;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM t2;
DROP TABLE t1, t2;
CREATE TABLE t2(a INT) ENGINE=MERGE UNION=(t3);
--error ER_WRONG_MRG_TABLE
SELECT * FROM t2;
DROP TABLE t2;

#
# Underlying table definition conformance tests.
#
CREATE TABLE t1(a INT, b TEXT);
CREATE TABLE tm1(a TEXT, b INT) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM tm1;
DROP TABLE t1, tm1;

CREATE TABLE t1(a SMALLINT, b SMALLINT);
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM tm1;
DROP TABLE t1, tm1;

CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(a, b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM tm1;
DROP TABLE t1, tm1;

CREATE TABLE t1(a SMALLINT, b SMALLINT, KEY(b));
CREATE TABLE tm1(a SMALLINT, b SMALLINT, KEY(a)) ENGINE=MERGE UNION=(t1);
--error 1168
SELECT * FROM tm1;
DROP TABLE t1, tm1;


# BUG#26881 - Large MERGE tables report incorrect specification when no
#             differences in tables
#
CREATE TABLE t1(c1 VARCHAR(1));
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
DROP TABLE t1, m1;

CREATE TABLE t1(c1 VARCHAR(4), c2 TINYINT, c3 TINYINT, c4 TINYINT,
                c5 TINYINT, c6 TINYINT, c7 TINYINT, c8 TINYINT, c9 TINYINT);
CREATE TABLE m1 LIKE t1;
ALTER TABLE m1 ENGINE=MERGE UNION=(t1);
SELECT * FROM m1;
DROP TABLE t1, m1;

#
# BUG#24342 - Incorrect results with query over MERGE table
#
CREATE TABLE t1 (a VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci,
                 b INT, INDEX(a,b));
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
ALTER TABLE t3 ENGINE=MERGE UNION=(t1,t2);
INSERT INTO t1 VALUES ('ss',1);
INSERT INTO t2 VALUES ('ss',2),(0xDF,2);
SELECT COUNT(*) FROM t3 WHERE a=0xDF AND b=2;
DROP TABLE t1,t2,t3;

# End of 4.1 tests

#
# BUG#19648 - Merge table does not work with bit types
#
create table t1 (b bit(1));
create table t2 (b bit(1));
create table tm (b bit(1)) engine = merge union = (t1,t2);
select * from tm;
drop table tm, t1, t2;

#
# Bug #17766: The server accepts to create MERGE tables which cannot work
#
create table t1 (a int) insert_method = last engine = merge;
--error ER_OPEN_AS_READONLY
insert into t1 values (1);
create table t2 (a int) engine = myisam;
alter table t1 union (t2);
insert into t1 values (1);
alter table t1 insert_method = no;
--error ER_OPEN_AS_READONLY
insert into t1 values (1);
drop table t2;
drop table t1;

#
# BUG#26976 - Missing table in merge not noted in related error msg + SHOW
#             CREATE TABLE fails
#
CREATE TABLE tm1(a INT) ENGINE=MERGE UNION=(t1, t2);
--error ER_WRONG_MRG_TABLE
SELECT * FROM tm1;
CHECK TABLE tm1;
CREATE TABLE t1(a INT);
--error ER_WRONG_MRG_TABLE
SELECT * FROM tm1;
CHECK TABLE tm1;
CREATE TABLE t2(a BLOB);
--error ER_WRONG_MRG_TABLE
SELECT * FROM tm1;
CHECK TABLE tm1;
ALTER TABLE t2 MODIFY a INT;
SELECT * FROM tm1;
CHECK TABLE tm1;
DROP TABLE tm1, t1, t2;

#
# Bug#15522 - create ... select and with merge tables
#
# This was fixed together with Bug#20662 (Infinite loop in CREATE TABLE
# IF NOT EXISTS ... SELECT with locked tables).
# The new behavior for MERGE tables is consistent with the
# CREATE TABLE SELECT behavior for ordinary tables.
#
CREATE TABLE t1(c1 INT);
CREATE TABLE t2 (c1 INT) ENGINE=MERGE UNION=(t1) INSERT_METHOD=FIRST;
# After WL#5370, it just generates a warning that the table already exists
CREATE TABLE IF NOT EXISTS t1 SELECT * FROM t2;
DROP TABLE t1, t2;

#
# Bug #28837: MyISAM storage engine error (134) doing delete with self-join
#

CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM;
CREATE TABLE t2 LIKE t1;

INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4);
INSERT INTO t1 SELECT * FROM t2;
INSERT INTO t1 SELECT * FROM t2;

CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE
                                                                UNION(t1);

SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
SELECT * FROM t3;
DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref;
SELECT * FROM t3;

DROP TABLE t1, t2, t3;

#
# BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed
#
CREATE TABLE t1(a INT);
CREATE TABLE m1(a INT) ENGINE=MERGE;
SHOW CREATE TABLE m1;
DROP TABLE m1;
CREATE TABLE m1(a INT) ENGINE=MERGE UNION=();
SHOW CREATE TABLE m1;
ALTER TABLE m1 UNION=(t1);
ALTER TABLE m1 UNION=();
SHOW CREATE TABLE m1;
DROP TABLE t1, m1;

#
# BUG#32047 - 'Spurious' errors while opening MERGE tables
#
CREATE TABLE t1(a INT);
CREATE TABLE t2(a VARCHAR(10));
CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(t1, t2);
CREATE TABLE m2(a INT) ENGINE=MERGE UNION=(t1);
SELECT * FROM t1;
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE t1, t2, m1, m2;

--echo End of 5.0 tests


#
# Bug #8306: TRUNCATE leads to index corruption 
#
create table t1 (c1 int, index(c1));
create table t2 (c1 int, index(c1)) engine=merge union=(t1);
insert into t1 values (1);
# Close all tables.
flush tables;
# Open t2 and (implicitly) t1.
select * from t2;
# Truncate after flush works (unless another threads reopens t2 in between).
flush tables;
truncate table t1;
insert into t1 values (1);
# Close all tables.
flush tables;
# Open t2 and (implicitly) t1.
select * from t2;
# Truncate t1, wich was not recognized as open without the bugfix.
# After fix for Bug#8306 and before fix for Bug#26379,
# it should fail with a table-in-use error message, otherwise succeed.
truncate table t1;
# The insert used to fail on the crashed table.
insert into t1 values (1);
drop table t1,t2;
--echo #
--echo # Extra tests for TRUNCATE.
--echo #
--echo # Truncate MERGE table.
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1));
CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
TRUNCATE TABLE t3;
SELECT * FROM t3;
--echo #
--echo # Truncate child table.
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
--echo #
--echo # Truncate MERGE table under locked tables.
LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
INSERT INTO t1 VALUES (1);
TRUNCATE TABLE t3;
SELECT * FROM t3;
UNLOCK TABLES;
SELECT * FROM t1;
SELECT * FROM t2;
--echo #
--echo # Truncate child table under locked tables.
LOCK TABLE t1 WRITE, t2 WRITE, t3 WRITE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
UNLOCK TABLES;
DROP TABLE t1, t2, t3;
--echo #
--echo # Truncate temporary MERGE table.
CREATE TEMPORARY TABLE t1 (c1 INT, INDEX(c1));
CREATE TEMPORARY TABLE t2 (c1 INT, INDEX(c1));
CREATE TEMPORARY TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2);
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
TRUNCATE TABLE t3;
SELECT * FROM t3;
--echo #
--echo # Truncate temporary child table.
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
--echo #
--echo # Truncate temporary MERGE table under locked tables.
INSERT INTO t1 VALUES (1);
CREATE TABLE t4 (c1 INT, INDEX(c1));
LOCK TABLE t4 WRITE;
TRUNCATE TABLE t3;
SELECT * FROM t3;
SELECT * FROM t1;
SELECT * FROM t2;
--echo #
--echo # Truncate temporary child table under locked tables.
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
TRUNCATE TABLE t1;
SELECT * FROM t3;
SELECT * FROM t1;
SELECT * FROM t2;
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;

#
# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
# Preparation
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
connection default;
#
# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
# Problem #1
# A thread trying to lock a MERGE table performed busy waiting while
# REPAIR TABLE or a similar table administration task was ongoing on one or
# more of its MyISAM tables.
# To allow for observability it was necessary to enter a multi-second sleep
# in mysql_admin_table() after remove_table_from_cache(), which comes after
# mysql_abort_lock(). The sleep faked a long running operation. One could
# watch a high CPU load during the sleep time.
# The problem was that mysql_abort_lock() upgrades the write lock to
# TL_WRITE_ONLY. This lock type persisted until the final unlock at the end
# of the administration task. The effect of TL_WRITE_ONLY is to reject any
# attempt to lock the table. The trying thread must close the table and wait
# until it is no longer used. Unfortunately there is no way to detect that
# one of the MyISAM tables of a MERGE table is in use. When trying to lock
# the MERGE table, all MyISAM tables are locked. If one fails on
# TL_WRITE_ONLY, all locks are aborted and wait_for_tables() is entered.
# But this doesn't see the MERGE table as used, so it seems appropriate to
# retry a lock...
#
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
send REPAIR TABLE t1;
    connection con1;
    sleep 1; # let repair run into its sleep
    INSERT INTO t2 VALUES (1);
connection default;
reap;
DROP TABLE t1, t2;
#
# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
# Problem #2
# A thread trying to lock a MERGE table performed busy waiting until all
# threads that did REPAIR TABLE or similar table administration tasks on
# one or more of its MyISAM tables in LOCK TABLES segments did
# UNLOCK TABLES.
# The difference against problem #1 is that the busy waiting took place
# *after* the administration task. It was terminated by UNLOCK TABLES only.
#
# This is the same test case as for
# Bug#26867 - LOCK TABLES REPAIR + merge table result in memory/cpu hogging
#
#
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1) INSERT_METHOD= LAST;
LOCK TABLE t1 WRITE;
    connection con1;
    send INSERT INTO t2 VALUES (1);
connection default;
sleep 1; # Let INSERT go into thr_multi_lock().
REPAIR TABLE t1;
sleep 2; # con1 performs busy waiting during this sleep.
UNLOCK TABLES;
    connection con1;
    reap;
connection default;
DROP TABLE t1, t2;
#
# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
# Problem #3
# Two FLUSH TABLES within a LOCK TABLES segment could invalidate the lock.
# This did *not* require a MERGE table.
# To increase reproducibility it was necessary to enter a sleep of 2 seconds
# at the end of wait_for_tables() after unlock of LOCK_open. In 5.0 and 5.1
# the sleep must be inserted in open_and_lock_tables() after open_tables()
# instead. wait_for_tables() is not used in this case.
# The problem was that FLUSH TABLES releases LOCK_open while having unlocked
# and closed all tables. When this happened while a thread was in the loop in
# mysql_lock_tables() right after wait_for_tables() and before retrying to
# lock, the thread got the lock.  (Translate to similar code places in 5.0
# and 5.1). And it did not notice that the table needed a refresh. So it
# executed its statement on the table.
# The first FLUSH TABLES kicked the INSERT out of thr_multi_lock() and let
# it wait in wait_for_tables(). (open_table() in 5.0 and 5.1). The second
# FLUSH TABLES must happen while the INSERT was on its way from
# wait_for_tables() to the next call of thr_multi_lock(). This needed to be
# supported by a sleep to make it repeatable.
#
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM;
LOCK TABLE t1 WRITE;
    connection con1;
    send INSERT INTO t1 VALUES (1);
connection default;
sleep 1; # Let INSERT go into thr_multi_lock().
FLUSH TABLES;
sleep 1; # Let INSERT go through wait_for_tables() where it sleeps.
FLUSH TABLES;
# This should give no result. But it will with sleep(2) at the right place.
SELECT * FROM t1;
UNLOCK TABLES;
    connection con1;
    reap;
connection default;
DROP TABLE t1;
#
# Bug#26379 - Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table
# Cleanup
disconnect con1;
disconnect con2;
#
--echo #
--echo # Extra tests for Bug#26379 - Combination of FLUSH TABLE and
--echo #                             REPAIR TABLE corrupts a MERGE table
#
--echo #
--echo # CREATE ... SELECT is disabled for MERGE tables.
--echo #
CREATE TABLE t1(c1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
--error ER_OPEN_AS_READONLY
CREATE TABLE t3 ENGINE=MRG_MYISAM INSERT_METHOD=LAST SELECT * FROM t2;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t3;
--error ER_WRONG_OBJECT
CREATE TABLE t3 ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST
  SELECT * FROM t2;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t3;
DROP TABLE t1, t2;
#
--echo #
--echo # Bug#37371 "CREATE TABLE LIKE merge loses UNION parameter"
--echo # Demonstrate that this is no longer the case.
--echo #
--echo # 1. Create like.
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE t3 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
CREATE TABLE t4 LIKE t3;
SHOW CREATE TABLE t4;
INSERT INTO t4 VALUES (4);
DROP TABLE t4;
--echo #
--echo # 1. Create like with locked tables.
LOCK TABLES t3 WRITE, t2 WRITE, t1 WRITE;
--error ER_TABLE_NOT_LOCKED
CREATE TABLE t4 LIKE t3;
--error ER_TABLE_NOT_LOCKED
SHOW CREATE TABLE t4;
--error ER_TABLE_NOT_LOCKED
INSERT INTO t4 VALUES (4);
--echo # Temporary tables can be created in spite of LOCK TABLES.
--echo # If the temporary MERGE table uses the locked children only,
--echo # it can even be used.
CREATE TEMPORARY TABLE t4 LIKE t3;
SHOW CREATE TABLE t4;
INSERT INTO t4 VALUES (4);
UNLOCK TABLES;
INSERT INTO t4 VALUES (4);
DROP TABLE t4;
#
--echo #
--echo # Rename child.
--echo #
--echo # 1. Normal rename of non-MERGE table.
CREATE TABLE t4 (c1 INT);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
RENAME TABLE t4 TO t5;
SELECT * FROM t5 ORDER BY c1;
RENAME TABLE t5 TO t4;
SELECT * FROM t4 ORDER BY c1;
DROP TABLE t4;
--echo #
--echo # 2. Normal rename.
SELECT * FROM t3 ORDER BY c1;
RENAME TABLE t2 TO t5;
--error ER_WRONG_MRG_TABLE
SELECT * FROM t3 ORDER BY c1;
RENAME TABLE t5 TO t2;
SELECT * FROM t3 ORDER BY c1;
--echo #
--echo # 3. Normal rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
SELECT * FROM t3 ORDER BY c1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
RENAME TABLE t2 TO t5;
SELECT * FROM t3 ORDER BY c1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
RENAME TABLE t5 TO t2;
SELECT * FROM t3 ORDER BY c1;
UNLOCK TABLES;
--echo #
--echo # 4. Alter table rename.
ALTER TABLE t2 RENAME TO t5;
--error ER_WRONG_MRG_TABLE
SELECT * FROM t3 ORDER BY c1;
ALTER TABLE t5 RENAME TO t2;
SELECT * FROM t3 ORDER BY c1;
--echo #
--echo # 5. Alter table rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
ALTER TABLE t2 RENAME TO t5;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t3 ORDER BY c1;
--error ER_TABLE_NOT_LOCKED
ALTER TABLE t5 RENAME TO t2;
UNLOCK TABLES;
ALTER TABLE t5 RENAME TO t2;
SELECT * FROM t3 ORDER BY c1;
#
--echo #
--echo # Rename parent.
--echo #
--echo # 1. Normal rename with locked tables.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE;
SELECT * FROM t3 ORDER BY c1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
RENAME TABLE t3 TO t5;
SELECT * FROM t3 ORDER BY c1;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
RENAME TABLE t5 TO t3;
SELECT * FROM t3 ORDER BY c1;
--echo #
--echo # 5. Alter table rename with locked tables.
ALTER TABLE t3 RENAME TO t5;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t5 ORDER BY c1;
--error ER_TABLE_NOT_LOCKED
ALTER TABLE t5 RENAME TO t3;
UNLOCK TABLES;
ALTER TABLE t5 RENAME TO t3;
SELECT * FROM t3 ORDER BY c1;
DROP TABLE t1, t2, t3;
#
--echo #
--echo # Drop locked tables.
--echo #
--echo # 1. Drop parent.
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t1 VALUES (1);
DROP TABLE t2;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t2;
SELECT * FROM t1;
UNLOCK TABLES;
--echo # 2. Drop child.
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
LOCK TABLES t1 WRITE, t2 WRITE;
INSERT INTO t1 VALUES (1);
DROP TABLE t1;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t2;
--error ER_TABLE_NOT_LOCKED
SELECT * FROM t1;
UNLOCK TABLES;
DROP TABLE t2;
#
--echo #
--echo # ALTER TABLE. Change child list.
--echo #
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1));
CREATE TABLE t3 (c1 INT, INDEX(c1));
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t3,t2)
  INSERT_METHOD=LAST;
--echo # Shrink child list.
ALTER TABLE t4 UNION=(t3);
SHOW CREATE TABLE t4;
SELECT * FROM t4 ORDER BY c1;
--echo # Extend child list.
ALTER TABLE t4 UNION=(t3,t2);
SHOW CREATE TABLE t4;
SELECT * FROM t4 ORDER BY c1;
#
--echo #
--echo # ALTER TABLE under LOCK TABLES. Change child list.
--echo #
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE;
--echo # Shrink child list.
--error ER_LOCK_OR_ACTIVE_TRANSACTION
ALTER TABLE t4 UNION=(t3);
--echo # Extend child list within locked tables.
--error ER_LOCK_OR_ACTIVE_TRANSACTION
ALTER TABLE t4 UNION=(t3,t2);
--echo # Extend child list beyond locked tables.
--error ER_LOCK_OR_ACTIVE_TRANSACTION
ALTER TABLE t4 UNION=(t3,t2,t1);
SHOW CREATE TABLE t4;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
DROP TABLE t4;
#
--echo #
--echo # ALTER TABLE under LOCK TABLES. Grave change, table re-creation.
--echo #
CREATE TABLE t4 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
  INSERT_METHOD=LAST;
--echo # Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
ALTER TABLE t4 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 DROP INDEX c1, ADD UNIQUE INDEX (c1);
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;
#
--echo #
--echo # ALTER TABLE under LOCK TABLES. Simple change, no re-creation.
--echo #
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE t3 (c1 INT);
CREATE TABLE t4 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3)
  INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
INSERT INTO t3 VALUES (3);
--echo # Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
ALTER TABLE t4 ALTER COLUMN c1 SET DEFAULT 44;
SELECT * FROM t4 ORDER BY c1;
ALTER TABLE t2 ALTER COLUMN c1 SET DEFAULT 22;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
#
--echo #
--echo # FLUSH TABLE under LOCK TABLES.
--echo #
--echo # Lock parent first and then children.
LOCK TABLES t4 WRITE, t3 WRITE, t2 WRITE, t1 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock children first and then parent.
LOCK TABLES t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
--echo # Lock parent between children.
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
FLUSH TABLE t4;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLE t2;
SELECT * FROM t4 ORDER BY c1;
FLUSH TABLES;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
#
--echo #
--echo # Triggers
--echo #
--echo # Trigger on parent
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t4_ai1 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
SELECT * FROM t4 ORDER BY c1;
DROP TRIGGER t4_ai1;
CHECK TABLE t3;
--echo # Trigger on parent under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t4_ai2 AFTER INSERT ON t4 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
SELECT * FROM t4 ORDER BY c1;
DROP TRIGGER t4_ai2;
UNLOCK TABLES;
CHECK TABLE t3;
--echo #
--echo # Trigger on child
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t3_ai3 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
INSERT INTO t3 VALUES (33);
SELECT @a;
SELECT * FROM t4 ORDER BY c1;
DROP TRIGGER t3_ai3;
CHECK TABLE t3;
--echo # Trigger on child under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t3_ai4 AFTER INSERT ON t3 FOR EACH ROW SET @a=1;
SET @a=0;
INSERT INTO t4 VALUES (4);
SELECT @a;
INSERT INTO t3 VALUES (33);
SELECT @a;
SELECT * FROM t4 ORDER BY c1;
DELETE FROM t4 WHERE c1 = 33;
DROP TRIGGER t3_ai4;
UNLOCK TABLES;
CHECK TABLE t3;
--echo #
--echo # Trigger with table use on child
DELETE FROM t4 WHERE c1 = 4;
CREATE TRIGGER t3_ai5 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
SELECT COUNT(*) FROM t2;
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
INSERT INTO t3 VALUES (33);
SELECT * FROM t4 ORDER BY c1;
DELETE FROM t4 WHERE c1 = 22;
DELETE FROM t4 WHERE c1 = 33;
DROP TRIGGER t3_ai5;
UNLOCK TABLES;
CHECK TABLE t2,t3; 
--echo # Trigger with table use on child under LOCK TABLES
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CREATE TRIGGER t3_ai6 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
INSERT INTO t3 VALUES (33);
SELECT * FROM t4 ORDER BY c1;
DROP TRIGGER t3_ai6;
UNLOCK TABLES;
check table t2,t3,t4;
DELETE FROM t4 WHERE c1 = 22;
DELETE FROM t4 WHERE c1 = 33;
--echo # Trigger with table use on child under different LOCK TABLES
DELETE FROM t4 WHERE c1 = 4;
LOCK TABLES t4 WRITE,t3 WRITE, t2 WRITE, t1 WRITE;
CREATE TRIGGER t3_ai7 AFTER INSERT ON t3 FOR EACH ROW INSERT INTO t2 VALUES(22);
INSERT INTO t4 VALUES (4);
SELECT * FROM t4 ORDER BY c1;
INSERT INTO t3 VALUES (33);
SELECT * FROM t4 ORDER BY c1;
DROP TRIGGER t3_ai7;
UNLOCK TABLES;
check table t2,t3,t4;
DELETE FROM t4 WHERE c1 = 22;
DELETE FROM t4 WHERE c1 = 33;
#
--echo #
--echo # Repair
--echo #
REPAIR TABLE t4;
REPAIR TABLE t2;
SELECT * FROM t4 ORDER BY c1;
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
REPAIR TABLE t4;
REPAIR TABLE t2;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
#
--echo #
--echo # Optimize
--echo #
OPTIMIZE TABLE t4;
OPTIMIZE TABLE t2;
SELECT * FROM t4 ORDER BY c1;
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
OPTIMIZE TABLE t4;
OPTIMIZE TABLE t2;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
#
--echo #
--echo # Checksum
--echo #
CHECKSUM TABLE t4;
CHECKSUM TABLE t2;
SELECT * FROM t4 ORDER BY c1;
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
CHECKSUM TABLE t4;
CHECKSUM TABLE t2;
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
#
--echo #
--echo # Insert delayed
--echo #
# See also Bug#26464 - insert delayed + update + merge = corruption
# Succeeds in embedded server - is converted to normal insert
# Fails in normal server, ps-protocol - not supported by engine
# Fails in normal server, normal protocol - not a base table
--error 0, ER_DELAYED_NOT_SUPPORTED, ER_WRONG_OBJECT
INSERT DELAYED INTO t4 VALUES(44);
# Get rid of row in embedded server
DELETE FROM t4 WHERE c1 = 44;
INSERT DELAYED INTO t3 VALUES(33);
  let $wait_cmd= SHOW STATUS LIKE 'Not_flushed_delayed_rows';
  let $run= query_get_value($wait_cmd, Value, 1);
  while ($run)
  {
    let $run= query_get_value($wait_cmd, Value, 1);
  }
SELECT * FROM t4 ORDER BY c1;
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED
INSERT DELAYED INTO t4 VALUES(444);
--error ER_DELAYED_INSERT_TABLE_LOCKED, ER_DELAYED_NOT_SUPPORTED
INSERT DELAYED INTO t3 VALUES(333);
SELECT * FROM t4 ORDER BY c1;
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4;
#
--echo #
--echo # Recursive inclusion of merge tables in their union clauses.
--echo #
CREATE TABLE t1 (c1 INT, INDEX(c1));
CREATE TABLE t2 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TABLE t3 (c1 INT, INDEX(c1)) ENGINE=MRG_MYISAM UNION=(t2,t1)
  INSERT_METHOD=LAST;
ALTER TABLE t2 UNION=(t3,t1);
--error ER_ADMIN_WRONG_MRG_TABLE
SELECT * FROM t2;
DROP TABLE t1, t2, t3;


#
# Bug#25038 - Waiting TRUNCATE
# Truncate failed with error message when table was in use by MERGE.
#
# Show that truncate of child table after use of parent table works.
CREATE TABLE t1 (c1 INT) ENGINE= MyISAM; 
CREATE TABLE t2 (c1 INT) ENGINE= MyISAM; 
CREATE TABLE t3 (c1 INT) ENGINE= MRG_MYISAM UNION= (t1, t2); 
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2);
SELECT * FROM t3;
TRUNCATE TABLE t1;
SELECT * FROM t3;
DROP TABLE t1, t2, t3;
#
# Show that truncate of child table waits while parent table is used.
# (test partly borrowed from count_distinct3.)
CREATE TABLE t1 (id INTEGER, grp TINYINT, id_rev INTEGER);
SET @rnd_max= 2147483647;
let $1 = 10;
--disable_query_log
begin;
while ($1)
{
  SET @rnd= RAND();
  SET @id = CAST(@rnd * @rnd_max AS UNSIGNED);
  SET @id_rev= @rnd_max - @id;
  SET @grp= CAST(127.0 * @rnd AS UNSIGNED); 
  INSERT INTO t1 (id, grp, id_rev) VALUES (@id, @grp, @id_rev); 
  dec $1;
}
commit;
--enable_query_log
set @@read_buffer_size=2*1024*1024;
CREATE TABLE t2 SELECT * FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
INSERT INTO t2 (id, grp, id_rev) SELECT id, grp, id_rev FROM t1;
INSERT INTO t1 (id, grp, id_rev) SELECT id, grp, id_rev FROM t2;
CREATE TABLE t3 (id INTEGER, grp TINYINT, id_rev INTEGER)
  ENGINE= MRG_MYISAM UNION= (t1, t2);
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;
SELECT COUNT(*) FROM t3;
connect (con1,localhost,root,,);
    # As t3 contains random numbers, results are different from test to test. 
    # That's okay, because we test only that select doesn't yield an
    # error. Note, that --disable_result_log doesn't suppress error output.
    --disable_result_log
    send SELECT COUNT(DISTINCT a1.id) FROM t3 AS a1, t3 AS a2
      WHERE a1.id = a2.id GROUP BY a2.grp;
connection default;
sleep 1;
TRUNCATE TABLE t1;
    connection con1;
    reap;
    --enable_result_log
    disconnect con1;
connection default;
SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;
SELECT COUNT(*) FROM t3;
DROP TABLE t1, t2, t3;

#
# Bug#25700 - merge base tables get corrupted by optimize/analyze/repair table
#
# Using FLUSH TABLES before REPAIR.
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
INSERT INTO t2 VALUES (1);
SELECT * FROM t2;
LOCK TABLES t2 WRITE, t1 WRITE;
FLUSH TABLES;
REPAIR TABLE t1;
CHECK TABLE t1;
REPAIR TABLE t1;
UNLOCK TABLES;
CHECK TABLE t1 EXTENDED;
#
# Not using FLUSH TABLES before REPAIR.
LOCK TABLES t2 WRITE, t1 WRITE;
REPAIR TABLE t1;
CHECK TABLE t1;
REPAIR TABLE t1;
UNLOCK TABLES;
CHECK TABLE t1 EXTENDED;
DROP TABLE t1, t2;

#
# Bug#26377 - Deadlock with MERGE and FLUSH TABLE
#
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
# Lock t1 first. This did always work.
LOCK TABLES t1 WRITE, m1 WRITE;
FLUSH TABLE t1;
UNLOCK TABLES;
DROP TABLE m1, t1;
#
CREATE TABLE t1 ( a INT ) ENGINE=MyISAM;
CREATE TABLE m1 ( a INT ) ENGINE=MRG_MYISAM UNION=(t1);
# Lock m1 first. This did deadlock.
LOCK TABLES m1 WRITE, t1 WRITE;
FLUSH TABLE t1;
UNLOCK TABLES;
DROP TABLE m1, t1;

#
# Bug#27660 - Falcon: merge table possible
#
# Normal MyISAM MERGE operation.
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE= MyISAM;
CREATE TABLE t2 (c1 INT, c2 INT) ENGINE= MyISAM;
CREATE TABLE t3 (c1 INT, c2 INT) ENGINE= MRG_MYISAM UNION(t1, t2);
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t2 VALUES (2, 2);
SELECT * FROM t3;
# Try an unsupported engine.
ALTER TABLE t1 ENGINE= MEMORY;
INSERT INTO t1 VALUES (0, 0);
# Before fixing, this succeeded, but (0, 0) was missing.
--error 1168
SELECT * FROM t3;
DROP TABLE t1, t2, t3;

#
# Bug#30275 - Merge tables: flush tables or unlock tables causes server to crash
#
CREATE TABLE t1 (c1 INT, KEY(c1));
CREATE TABLE t2 (c1 INT, KEY(c1)) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=FIRST;
LOCK TABLE t1 WRITE, t2 WRITE;
FLUSH TABLES t2, t1;
OPTIMIZE TABLE t1;
FLUSH TABLES t1;
UNLOCK TABLES;
#
FLUSH TABLES;
INSERT INTO t1 VALUES (1);
LOCK TABLE t1 WRITE, t2 WRITE;
FLUSH TABLES t2, t1;
OPTIMIZE TABLE t1;
FLUSH TABLES t1;
UNLOCK TABLES;
DROP TABLE t1, t2;

#
# Test derived from test program for
# Bug#30273 - merge tables: Can't lock file (errno: 155)
#
CREATE TABLE t1 (ID INT) ENGINE=MYISAM;
CREATE TABLE m1 (ID INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
INSERT INTO t1 VALUES ();
INSERT INTO m1 VALUES ();
LOCK TABLE t1 WRITE, m1 WRITE;
FLUSH TABLES m1, t1;
OPTIMIZE TABLE t1;
FLUSH TABLES m1, t1;
UNLOCK TABLES;
DROP TABLE t1, m1;

#
# Bug#35068 - Assertion fails when reading from i_s.tables
#             and there is incorrect merge table
#
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=FIRST;
--replace_column 8 # 9 # 10 # 11 # 12 # 13 # 14 # 15 # 16 # 17 # 19 # 20 #
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA = 'test' and TABLE_NAME='tm1';

DROP TABLE tm1;

#
# Bug#36006 - Optimizer does table scan for select count(*)
#
CREATE TABLE t1(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t2(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t3(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2)) ENGINE=MYISAM;
CREATE TABLE t4(C1 INT, C2 INT, KEY C1(C1), KEY C2(C2))
  ENGINE=MRG_MYISAM UNION=(t1, t2, t3);
INSERT INTO t1 VALUES (1,1), (1,2),(1,3), (1,4);
INSERT INTO t2 VALUES (2,1), (2,2),(2,3), (2,4);
INSERT INTO t3 VALUES (3,1), (3,2),(3,3), (3,4);
EXPLAIN SELECT COUNT(*) FROM t1;
EXPLAIN SELECT COUNT(*) FROM t4;
DROP TABLE t1, t2, t3, t4;

#
# BUG#39185 - Cardinality for merge tables calculated incorrectly.
#
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES(0),(1),(2),(3),(4);
ANALYZE TABLE t1;
CREATE TABLE m1(a INT, KEY(a)) ENGINE=MERGE UNION=(t1);
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 
SELECT CARDINALITY FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='m1'; 
DROP TABLE t1, m1;

--echo #
--echo # Bug #40675 MySQL 5.1 crash with index merge algorithm and Merge tables
--echo #

--echo # create MYISAM table t1 and insert values into it
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(1);

--echo # create MYISAM table t2 and insert values into it
CREATE TABLE t2(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b));
INSERT INTO t2(a,b) VALUES
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),(0,0),
(1,2);

--echo # Create the merge table t3
CREATE TABLE t3(a INT, b INT, dummy CHAR(16) DEFAULT '', KEY(a), KEY(b))
ENGINE=MERGE UNION=(t2) INSERT_METHOD=FIRST;

--echo # Lock tables t1 and t3 for write
LOCK TABLES t1 WRITE, t3 WRITE;

--echo # Insert values into the merge table t3
INSERT INTO t3(a,b) VALUES(1,2);

--echo # select from the join of t2 and t3 (The merge table)
SELECT t3.a FROM t1,t3 WHERE t3.b=2 AND t3.a=1;

--echo # Unlock the tables
UNLOCK TABLES;

--echo # drop the created tables
DROP TABLE t1, t2, t3;

#
# Bug #41305 server crashes when inserting duplicate row into a merge table
#
--echo # insert duplicate value in child table while merge table doesn't have key
create table t1 (
        col1 int(10),
        primary key (col1)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE m1 (
  col1 int(10) NOT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(t1);

insert into m1 (col1) values (1);
--error ER_DUP_ENTRY
insert into m1 (col1) values (1);

drop table m1, t1;

--echo #
--echo # Bug#45800 crash when replacing into a merge table and there is a duplicate
--echo #

--echo # Replace duplicate value in child table when merge table doesn't have key
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1  VALUES (666);
SELECT * FROM m1;
--echo # insert the duplicate value into the merge table
REPLACE INTO m1 VALUES (666);
SELECT * FROM m1;
DROP TABLE m1, t1;

--echo # Insert... on duplicate key update (with duplicate values in the table)
CREATE TABLE t1 (c1 INT PRIMARY KEY) ENGINE=MyISAM;
CREATE TABLE m1 (c1 INT NOT NULL) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1  VALUES (666);
SELECT * FROM m1;
--echo # insert the duplicate value into the merge table
INSERT INTO m1 VALUES (666) ON DUPLICATE KEY UPDATE c1=c1+1;
SELECT * FROM m1;
DROP TABLE m1, t1;

--echo # Insert duplicate value on MERGE table, where, MERGE has a key but MyISAM has more keys
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1), UNIQUE (c2));
CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c1)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
INSERT INTO m1 VALUES (1,2);
--echo # insert the duplicate value into the merge table
--error ER_DUP_ENTRY
INSERT INTO m1 VALUES (3,2);
DROP TABLE m1,t1;

--echo # Try to define MERGE and MyISAM with keys on different columns
CREATE TABLE t1 (c1 INT, c2 INT, UNIQUE (c1));
CREATE TABLE m1 (c1 INT, c2 INT, UNIQUE (c2)) ENGINE=MRG_MyISAM INSERT_METHOD=LAST UNION=(t1);
--echo # Try accessing the merge table for inserts (error occurs)
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (1,2);
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (1,4);
DROP TABLE m1,t1;

#
#Bug #44040   MySQL allows creating a MERGE table upon VIEWs but crashes 
#when using it
#

CREATE TABLE t1 (
        col1 INT(10)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE VIEW v1 as SELECT * FROM t1;
CREATE TABLE m1 (
        col1 INT(10)
)ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(v1);

--echo #Select should detect that the child table is a view and fail.
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;

DROP VIEW v1;
DROP TABLE m1, t1;

--echo #
--echo # Bug #45796: invalid memory reads and writes when altering merge and 
--echo #             base tables
--echo #

CREATE TABLE t1(c1 INT) ENGINE=MyISAM;
CREATE TABLE m1(c1 INT) ENGINE=MERGE UNION=(t1);
ALTER TABLE m1 ADD INDEX idx_c1(c1);
# Open the MERGE table and allocate buffers based on children's definition.
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
# Change the child table definition.
ALTER TABLE t1 ADD INDEX idx_c1(c1);
# Check that old buffers are not reused
SELECT * FROM m1;

DROP TABLE m1;
DROP TABLE t1;

--echo #
--echo # Bug45781 infinite hang/crash in "opening tables" after handler tries to
--echo #          open merge table
--echo #

--disable_warnings
DROP TABLE IF EXISTS m1,t1;
--enable_warnings

CREATE TABLE t1(a int)engine=myisam;
CREATE TABLE t2(a int)engine=myisam;
CREATE TABLE t3(a int)engine=myisam;
CREATE TABLE t4(a int)engine=myisam;
CREATE TABLE t5(a int)engine=myisam;
CREATE TABLE t6(a int)engine=myisam;
CREATE TABLE t7(a int)engine=myisam;
CREATE TABLE m1(a int)engine=merge union=(t1,t2,t3,t4,t5,t6,t7);
SELECT 1 FROM m1;
--error ER_ILLEGAL_HA
HANDLER m1 OPEN;
DROP TABLE m1,t1,t2,t3,t4,t5,t6,t7;
--error ER_NO_SUCH_TABLE
SELECT 1 FROM m1; # Should not hang!

--echo #
--echo # Bug #46614: Assertion in show_create_trigger()
--echo #
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
CREATE TABLE t3(a int) ENGINE = MERGE UNION(t1, t2);
CREATE TRIGGER tr1 AFTER INSERT ON t3 FOR EACH ROW CALL foo();
SHOW CREATE TRIGGER tr1;
DROP TRIGGER tr1;
DROP TABLE t1, t2, t3;

--echo #
--echo # BUG#48265 - MRG_MYISAM problem (works in 5.0.85, does't work in 5.1.40)
--echo #
CREATE DATABASE `test/1`;

CREATE TABLE `test/1`.`t/1`(a INT);
CREATE TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
SELECT * FROM m1;
SHOW CREATE TABLE m1;
DROP TABLE m1;

CREATE TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
SELECT * FROM `test/1`.m1;
SHOW CREATE TABLE `test/1`.m1;
DROP TABLE `test/1`.m1;
DROP TABLE `test/1`.`t/1`;

CREATE TEMPORARY TABLE `test/1`.`t/1`(a INT);
CREATE TEMPORARY TABLE m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
SELECT * FROM m1;
SHOW CREATE TABLE m1;
DROP TABLE m1;

CREATE TEMPORARY TABLE `test/1`.m1(a INT) ENGINE=MERGE UNION=(`test/1`.`t/1`);
SELECT * FROM `test/1`.m1;
SHOW CREATE TABLE `test/1`.m1;
DROP TABLE `test/1`.m1;
DROP TABLE `test/1`.`t/1`;

DROP DATABASE `test/1`;

# Test compatibility. Use '@' instead of '/' (was not allowed in 5.0)

CREATE TABLE `t@1`(a INT);
copy_file std_data/bug48265.frm $MYSQLD_DATADIR/test/m1.frm;
write_file $MYSQLD_DATADIR/test/m1.MRG;
t@1
EOF
SELECT * FROM m1;
SHOW CREATE TABLE m1;
DROP TABLE `t@1`;

CREATE DATABASE `test@1`;
CREATE TABLE `test@1`.`t@1`(a INT);
FLUSH TABLE m1;
remove_file $MYSQLD_DATADIR/test/m1.MRG;
write_file $MYSQLD_DATADIR/test/m1.MRG;
./test@1/t@1
EOF
SELECT * FROM m1;
SHOW CREATE TABLE m1;
DROP TABLE m1;
DROP TABLE `test@1`.`t@1`;
DROP DATABASE `test@1`;

--echo #
--echo # Bug#51494c rash with join, explain and 'sounds like' operator
--echo #

CREATE TABLE t1 (a INT) ENGINE=MYISAM;
INSERT INTO t1 VALUES(1);

CREATE TABLE t2 (b INT NOT NULL,c INT,d INT,e BLOB NOT NULL,
KEY idx0 (d, c)) ENGINE=MERGE;

EXPLAIN SELECT * FROM t1 NATURAL RIGHT JOIN
t2 WHERE b SOUNDS LIKE e AND d = 1;

DROP TABLE t2, t1;

--echo #
--echo # Bug#46339 - crash on REPAIR TABLE merge table USE_FRM
--echo #
--disable_warnings
DROP TABLE IF EXISTS m1, t1;
--enable_warnings
#
# Test derived from a proposal of Shane Bester.
#
CREATE TABLE t1 (c1 INT) ENGINE=MYISAM;
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1) INSERT_METHOD=LAST;
#
# REPAIR ... USE_FRM with LOCK TABLES.
#
LOCK TABLE m1 READ;
REPAIR TABLE m1 USE_FRM;
UNLOCK TABLES;
#
# REPAIR ... USE_FRM without LOCK TABLES.
#
# This statement crashed the server (Bug#46339).
#
REPAIR TABLE m1 USE_FRM;
#
DROP TABLE m1,t1;
#
# Test derived from a proposal of Matthias Leich.
#
# Base table is missing.
#
CREATE TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
#
# This statement crashed the server (Bug#46339).
#
REPAIR TABLE m1 USE_FRM;
#
# Create base table.
#
CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
#
# This statement crashed the server (Bug#46339).
#
REPAIR TABLE m1 USE_FRM;
#
# Normal repair as reference.
#
REPAIR TABLE m1;
#
# Cleanup.
#
DROP TABLE m1, t1;
#
# Same with temporary tables.
#
# Base table is missing.
#
CREATE TEMPORARY TABLE m1 (f1 BIGINT) ENGINE=MRG_MyISAM UNION(t1);
#
# This statement crashed the server (Bug#46339).
#
REPAIR TABLE m1 USE_FRM;
#
# Create base table.
#
CREATE TEMPORARY TABLE t1 (f1 BIGINT) ENGINE=MyISAM;
#
# This statement crashed the server (Bug#46339).
#
REPAIR TABLE m1 USE_FRM;
#
# Normal repair as reference.
#
REPAIR TABLE m1;
#
# Cleanup.
#
DROP TABLE m1, t1;

#
#  MDEV-5266 MySQL:57657 - Temporary MERGE table with temporary underlying is broken by ALTER
#
create temporary table t1_temp(i int);
create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last;
alter table tm_temp_temp insert_method=first;
check table tm_temp_temp;
drop temporary table t1_temp, tm_temp_temp;

--echo End of 5.1 tests

--echo # 
--echo # MDEV-4277: Crash inside mi_killed_in_mariadb() with myisammrg
--echo # 
create table t1 (a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2 ( i int ) engine=myisam;
insert into t2 values (1),(2);

create table t3 (a int, b int, filler char(100), key(a), key(b)) engine=myisam;
create table t4 like t3;
insert into t3 
  select A.a+10*B.a+100*C.a, 
         A.a+10*B.a+100*C.a,
         'filler-data-FILLER-DATA-qqq' 
  from t1 A, t1 B, t1 C where C.a < 5;
insert into t4
  select A.a+10*B.a+100*C.a, 
         A.a+10*B.a+100*C.a,
         'filler-data-FILLER-DATA-qqq' 
  from t1 A, t1 B, t1 C where C.a >= 5;

create table t5 like t3; 
alter table t5 engine=merge;
alter table t5 union(t3, t4);

update t5 set b=999, a=999 where b>950;

explain 
select * from t2, t5 where t5.a=999 and t5.b=999;
select * from t2, t5 where t5.a=999 and t5.b=999;

drop table t5;
drop table t1,t2,t3,t4;

--echo End of 5.3 tests

#
# BUG#35274 - merge table doesn't need any base tables, gives error 124 when
#             key accessed
#
CREATE TABLE t1(a INT, KEY(a)) ENGINE=merge;
SELECT MAX(a) FROM t1;
DROP TABLE t1;

--echo #
--echo # An additional test case for Bug#27430 Crash in subquery code
--echo # when in PS and table DDL changed after PREPARE
--echo #
--echo # Test merge table with too many merge children.
--echo #
--disable_warnings
drop table if exists t_parent;
--enable_warnings
set @save_table_definition_cache=@@global.table_definition_cache;
--echo #
--echo # Set @@global.table_definition_cache to minimum
--echo #
set @@global.table_definition_cache=400;
set @a=null;
let $1 = 400;
--echo #
--echo # Create 400 merge children
--echo #
--disable_query_log
while ($1)
{
--disable_warnings
  eval drop table if exists t$1;
--enable_warnings
  eval create table t$1 (a int) engine=myisam;
  eval set @a=ifnull(concat(@a, ", ", "t$1"), "t$1");
  dec $1;
}
--enable_query_log
set @a=concat("create table t_parent (a int) union(", @a,
              ") insert_method=first engine=mrg_myisam");
prepare stmt from @a;
execute stmt;
prepare stmt from "select * from t_parent";
--error ER_NEED_REPREPARE
execute stmt;
--error ER_NEED_REPREPARE
execute stmt;
--error ER_NEED_REPREPARE
execute stmt;
deallocate prepare stmt;
--echo #
--echo # Create merge parent 
--echo #

--echo #
--echo # Cleanup
--echo #
let $1 = 400;
--disable_query_log
while ($1)
{
  eval drop table t$1;
  dec $1;
}
--enable_query_log
drop table t_parent;
set @@global.table_definition_cache=@save_table_definition_cache;

#
# WL#4144 - Lock MERGE engine children
#
# Test DATA/INDEX DIRECTORY
#
--disable_warnings
DROP DATABASE IF EXISTS mysql_test1;
--enable_warnings
CREATE DATABASE mysql_test1;
--disable_query_log
# data/index directory don't work in HAVE_purify builds. Disable
# build-dependent warnings.
--disable_warnings
--echo CREATE TABLE t1 ... DATA DIRECTORY=... INDEX DIRECTORY=...
eval CREATE TABLE t1 (c1 INT)
  DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp';
--echo CREATE TABLE mysql_test1.t2 ... DATA DIRECTORY=... INDEX DIRECTORY=...
eval CREATE TABLE mysql_test1.t2 (c1 INT)
  DATA DIRECTORY='$MYSQLTEST_VARDIR/tmp'
  INDEX DIRECTORY='$MYSQLTEST_VARDIR/tmp';
--enable_query_log
--enable_warnings
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,mysql_test1.t2)
  INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO mysql_test1.t2 VALUES (2);
SELECT * FROM m1;
#--copy_file $MYSQLTEST_DATADIR/test/m1.MRG /tmp/mysql-test-m1.MRG
DROP TABLE t1, mysql_test1.t2, m1;
DROP DATABASE mysql_test1;
#
# Review detected Crash #1. Detaching main tables while in sub statement.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
INSERT INTO t1 (c1) VALUES (1);
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2) INSERT_METHOD=FIRST;
CREATE TABLE t3 (c1 INT);
INSERT INTO t3 (c1) VALUES (1);
CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t3);
CREATE VIEW v1 AS SELECT foo.c1 c1, f1() c2, bar.c1 c3, f1() c4
                    FROM tm1 foo, tm1 bar, t3;
SELECT * FROM v1;
DROP FUNCTION f1;
DROP VIEW v1;
DROP TABLE tm1, t1, t2, t3;
#
# Review detected Crash #2. Trying to attach temporary table twice.
#
CREATE TEMPORARY TABLE t1 (c1 INT);
CREATE TEMPORARY TABLE t2 (c1 INT);
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2)
  INSERT_METHOD=FIRST;
CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM tm1);
INSERT INTO tm1 (c1) VALUES (1);
SELECT f1() FROM (SELECT 1) AS c1;
DROP FUNCTION f1;
DROP TABLE tm1, t1, t2;
#
# Review suggested test. DDL in a stored function.
#
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  CREATE TEMPORARY TABLE t1 (c1 INT);
  CREATE TEMPORARY TABLE t2 (c1 INT);
  CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2);
  INSERT INTO t1 (c1) VALUES (1);
  RETURN (SELECT MAX(c1) FROM tm1);
END|
DELIMITER ;|
SELECT f1() FROM (SELECT 1 UNION SELECT 1) c1;
DROP FUNCTION f1;
DROP TABLE tm1, t1, t2;
#
# Base table. No LOCK TABLES, no functions/triggers.
#
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
DROP TABLE tm1, t1;
#
# Base table. No LOCK TABLES, sub-statement that is run inside a function.
#
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO tm1 VALUES (1);
  RETURN (SELECT MAX(c1) FROM tm1);
END|
DELIMITER ;|
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
SELECT f1();
DROP FUNCTION f1;
DROP TABLE tm1, t1;
#
# Base table. LOCK TABLES, no functions/triggers.
#
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
LOCK TABLE tm1 WRITE;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
DROP TABLE tm1, t1;
#
# Base table. LOCK TABLES, sub-statement that is run inside a function.
#
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO tm1 VALUES (1);
  RETURN (SELECT MAX(c1) FROM tm1);
END|
DELIMITER ;|
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
LOCK TABLE tm1 WRITE;
SELECT f1();
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE tm1, t1;
#
# Base table. LOCK TABLES statement that locks a table that has a trigger
# that inserts into a merge table, so an attempt is made to lock tables
# of a sub-statement.
#
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
CREATE TRIGGER t2_ai AFTER INSERT ON t2
  FOR EACH ROW INSERT INTO tm1 VALUES(11);
LOCK TABLE t2 WRITE;
INSERT INTO t2 VALUES (2);
SELECT * FROM tm1;
SELECT * FROM t2;
UNLOCK TABLES;
DROP TRIGGER t2_ai;
DROP TABLE tm1, t1, t2;
#
# Temporary. No LOCK TABLES, no functions/triggers.
#
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
DROP TABLE tm1, t1;
#
# Temporary. No LOCK TABLES, sub-statement that is run inside a function.
#
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO tm1 VALUES (1);
  RETURN (SELECT MAX(c1) FROM tm1);
END|
DELIMITER ;|
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
SELECT f1();
DROP FUNCTION f1;
DROP TABLE tm1, t1;
#
# Temporary. LOCK TABLES, no functions/triggers.
#
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
LOCK TABLE t9 WRITE;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
DROP TABLE tm1, t1, t9;
#
# Temporary. LOCK TABLES, sub-statement that is run inside a function.
#
DELIMITER |;
CREATE FUNCTION f1() RETURNS INT
BEGIN
  INSERT INTO tm1 VALUES (1);
  RETURN (SELECT MAX(c1) FROM tm1);
END|
DELIMITER ;|
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TABLE t9 (c1 INT) ENGINE=MyISAM;
LOCK TABLE t9 WRITE;
SELECT f1();
UNLOCK TABLES;
DROP FUNCTION f1;
DROP TABLE tm1, t1, t9;
#
# Temporary. LOCK TABLES statement that locks a table that has a trigger
# that inserts into a merge table, so an attempt is made to lock tables
# of a sub-statement.
#
CREATE TEMPORARY TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
CREATE TRIGGER t2_ai AFTER INSERT ON t2
  FOR EACH ROW INSERT INTO tm1 VALUES(11);
LOCK TABLE t2 WRITE;
INSERT INTO t2 VALUES (2);
SELECT * FROM tm1;
SELECT * FROM t2;
UNLOCK TABLES;
DROP TRIGGER t2_ai;
DROP TABLE tm1, t1, t2;
--echo #
--echo # Don't allow an update of a MERGE child in a trigger
--echo # if the table's already being modified by the main
--echo # statement.
--echo #
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
  FOR EACH ROW INSERT INTO t1 VALUES(11);
LOCK TABLE tm1 WRITE, t1 WRITE;
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
LOCK TABLE t1 WRITE, tm1 WRITE;
--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
DROP TRIGGER tm1_ai;
DROP TABLE tm1, t1;

--echo #
--echo # Don't select MERGE child when trying to get a prelocked table.
--echo #
--echo # Due to a limitation demonstrated by the previous test
--echo # we can no longer use a write-locked prelocked table.
--echo # The test is kept for historical purposes.
--echo #
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1)
  INSERT_METHOD=LAST;
CREATE TRIGGER tm1_ai AFTER INSERT ON tm1
  FOR EACH ROW SELECT max(c1) FROM t1 INTO @var;
LOCK TABLE tm1 WRITE, t1 WRITE;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
LOCK TABLE t1 WRITE, tm1 WRITE;
INSERT INTO tm1 VALUES (1);
SELECT * FROM tm1;
UNLOCK TABLES;
DROP TRIGGER tm1_ai;
DROP TABLE tm1, t1;

# Don't resurrect chopped off prelocked tables.
# The problem is not visible by test results; only by debugging.
#
CREATE TABLE t1 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t3 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t4 (c1 INT) ENGINE=MyISAM;
CREATE TABLE t5 (c1 INT) ENGINE=MyISAM;
CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5)
  INSERT_METHOD=LAST;
CREATE TRIGGER t2_au AFTER UPDATE ON t2
  FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var;
CREATE FUNCTION f1() RETURNS INT
  RETURN (SELECT MAX(c1) FROM t4);
LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE;
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t3 VALUES(3);
INSERT INTO t4 VALUES(4);
INSERT INTO t5 VALUES(5);
    connect (con1,localhost,root,,);
    send UPDATE t2, tm1 SET t2.c1=f1();
connection default;
# Force reopen in other thread.
#sleep 1;
FLUSH TABLES;
#sleep 1;
FLUSH TABLES;
#sleep 1;
UNLOCK TABLES;
    connection con1;
    reap;
    disconnect con1;
connection default;
SELECT * FROM tm1;
DROP TRIGGER t2_au;
DROP FUNCTION f1;
DROP TABLE tm1, t1, t2, t3, t4, t5;

--echo #
--echo # Bug#47633 - assert in ha_myisammrg::info during OPTIMIZE
--echo #
CREATE TEMPORARY TABLE t1 (c1 INT);
ALTER TABLE t1 ENGINE=MERGE UNION(t_not_exists,t1);
OPTIMIZE TABLE t1;
DROP TABLE t1;

--echo #
--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
--echo # More tests with TEMPORARY MERGE table and permanent children.
--echo # First without locked tables.
--echo #
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3, t4, m1, m2;
--enable_warnings
#
--echo #
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE m1;
SELECT * FROM m1;
INSERT INTO t1 VALUES (111, 121);
INSERT INTO m1 VALUES (211, 221);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
#
--echo #
ALTER TABLE m1 RENAME m2;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
#
--echo #
CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
--error ER_TABLE_EXISTS_ERROR
ALTER TABLE m2 RENAME m1;
DROP TABLE m1;
ALTER TABLE m2 RENAME m1;
SHOW CREATE TABLE m1;
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 ADD COLUMN c3 INT;
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (212, 222, 232);
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
ALTER TABLE t1 ADD COLUMN c3 INT;
ALTER TABLE t2 ADD COLUMN c3 INT;
INSERT INTO m1 VALUES (212, 222, 232);
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 DROP COLUMN c3;
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (213, 223);
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
ALTER TABLE t1 DROP COLUMN c3;
ALTER TABLE t2 DROP COLUMN c3;
INSERT INTO m1 VALUES (213, 223);
SELECT * FROM m1;
#
--echo #
CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
ALTER TABLE m1 UNION=(t1,t2,t3);
INSERT INTO m1 VALUES (311, 321);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
#
--echo #
CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
ALTER TABLE m1 UNION=(t1,t2,t3,t4);
INSERT INTO m1 VALUES (411, 421);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
#
--echo #
ALTER TABLE m1 ENGINE=MyISAM;
SHOW CREATE TABLE m1;
INSERT INTO m1 VALUES (511, 521);
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
#
--echo #
CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (611, 621);
SELECT * FROM m1;
DROP TABLE t1;
SELECT * FROM m1;
#
#
--echo #
--echo #
SHOW CREATE TABLE m1;
#
--echo #
CREATE TABLE m2 SELECT * FROM m1;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST;
--error ER_WRONG_MRG_TABLE
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
--error ER_WRONG_OBJECT
CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST SELECT * FROM m1;
#
--echo #
--error ER_WRONG_OBJECT
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST SELECT * FROM m1;
#
--echo #
CREATE TABLE m2 LIKE m1;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TEMPORARY TABLE m2 LIKE m1;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST;
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
#
#
--echo #
--echo #
LOCK TABLE m1 WRITE, m2 WRITE;
SELECT * FROM m1,m2 WHERE m1.c1=m2.c1;
UNLOCK TABLES;
#
DROP TABLE t1, t2, t3, t4, m1, m2;
#
#
#
--echo #
--echo # Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
--echo # More tests with TEMPORARY MERGE table and permanent children.
--echo # (continued) Now the same with locked table.
--echo #
CREATE TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TABLE t2 (c1 INT, c2 INT) ENGINE=MyISAM;
CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE m1;
SELECT * FROM m1;
INSERT INTO t1 VALUES (111, 121);
INSERT INTO m1 VALUES (211, 221);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
#
--echo #
LOCK TABLE m1 WRITE, t1 WRITE, t2 WRITE;
#
--echo #
ALTER TABLE m1 RENAME m2;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
#
--echo #
CREATE TEMPORARY TABLE m1 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
--error ER_TABLE_EXISTS_ERROR
ALTER TABLE m2 RENAME m1;
DROP TABLE m1;
ALTER TABLE m2 RENAME m1;
SHOW CREATE TABLE m1;
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 ADD COLUMN c3 INT;
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (212, 222, 232);
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
ALTER TABLE t1 ADD COLUMN c3 INT;
ALTER TABLE t2 ADD COLUMN c3 INT;
INSERT INTO m1 VALUES (212, 222, 232);
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 DROP COLUMN c3;
--error ER_WRONG_MRG_TABLE
INSERT INTO m1 VALUES (213, 223);
--error ER_WRONG_MRG_TABLE
SELECT * FROM m1;
ALTER TABLE t1 DROP COLUMN c3;
ALTER TABLE t2 DROP COLUMN c3;
INSERT INTO m1 VALUES (213, 223);
SELECT * FROM m1;
#
--echo #
UNLOCK TABLES;
CREATE TABLE t3 (c1 INT, c2 INT) ENGINE=MyISAM;
ALTER TABLE m1 UNION=(t1,t2,t3);
LOCK TABLE m1 WRITE;
INSERT INTO m1 VALUES (311, 321);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
#
--echo #
CREATE TEMPORARY TABLE t4 (c1 INT, c2 INT) ENGINE=MyISAM;
ALTER TABLE m1 UNION=(t1,t2,t3,t4);
INSERT INTO m1 VALUES (411, 421);
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
#
--echo #
ALTER TABLE m1 ENGINE=MyISAM;
SHOW CREATE TABLE m1;
INSERT INTO m1 VALUES (511, 521);
SELECT * FROM m1;
#
--echo #
ALTER TABLE m1 ENGINE=MRG_MyISAM UNION=(t1,t2)
  INSERT_METHOD=LAST;
SELECT * FROM m1;
SELECT * FROM t1;
SELECT * FROM t2;
#
--echo #
CREATE TEMPORARY TABLE t1 (c1 INT, c2 INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (611, 621);
SELECT * FROM m1;
DROP TABLE t1;
SELECT * FROM m1;
#
#
--echo #
--echo #
SHOW CREATE TABLE m1;
--error ER_TABLE_NOT_LOCKED
CREATE TABLE m2 SELECT * FROM m1;
#
--echo #
CREATE TEMPORARY TABLE m2 SELECT * FROM m1;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST;
SELECT * FROM m2;
LOCK TABLE m1 WRITE, m2 WRITE;
UNLOCK TABLES;
DROP TABLE m2;
LOCK TABLE m1 WRITE;
#
--echo #
--echo # ER_TABLE_NOT_LOCKED is returned in ps-protocol
--error ER_WRONG_OBJECT, ER_TABLE_NOT_LOCKED
CREATE TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST SELECT * FROM m1;
#
--echo #
--error ER_WRONG_OBJECT
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST SELECT * FROM m1;
#
--echo #
CREATE TEMPORARY TABLE m2 LIKE m1;
SHOW CREATE TABLE m2;
LOCK TABLE m1 WRITE, m2 WRITE;
SHOW CREATE TABLE m2;
SELECT * FROM m2;
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2;
#
--echo #
CREATE TEMPORARY TABLE m2 (c1 INT, c2 INT) ENGINE=MRG_MyISAM UNION=(t3,t4)
  INSERT_METHOD=LAST;
LOCK TABLE m1 WRITE, m2 WRITE;
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
#
--echo #
UNLOCK TABLES;
DROP TABLE t1, t2, t3, t4, m1, m2;

--echo #
--echo # Bug47098 assert in MDL_context::destroy on HANDLER
--echo #          <damaged merge table> OPEN
--echo #
--echo # Test that merge tables are closed correctly when opened using 
--echo # HANDLER ... OPEN.
--echo # The general case.
--disable_warnings
DROP TABLE IF EXISTS t1, t2, t3;
--enable_warnings
--echo # Connection con1.
connect (con1,localhost,root,,);
CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int);
CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
START TRANSACTION;
--error ER_ILLEGAL_HA
HANDLER t3 OPEN;
DROP TABLE t1, t2, t3;
--echo # Connection default.
connection default;
--echo # Disconnecting con1, all mdl_tickets must have been released.
disconnect con1;
--echo # The bug-specific case.
--echo # Connection con1.
connect (con1,localhost,root,,);
CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int);
CREATE TABLE t3 (c1 int) ENGINE = MERGE UNION (t1,t2);
DROP TABLE t2;
START TRANSACTION;
--error ER_WRONG_MRG_TABLE
HANDLER t3 OPEN;
DROP TABLE t1, t3;
--echo # Connection default.
connection default;
--echo # Disconnecting con1, all mdl_tickets must have been released.
disconnect con1;

--echo #
--echo # A test case for Bug#47648   main.merge fails sporadically
--echo #
--echo # Make sure we correctly maintain lex->query_tables_last_own.
--echo #
create table t1 (c1 int not null);
create table t2 (c1 int not null);
create table t3 (c1 int not null);

create function f1 () returns int return (select max(c1) from t3);

create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ;

select * from t4 where c1 < f1();
prepare stmt from "select * from t4 where c1 < f1()";
execute stmt;
execute stmt;
execute stmt;
drop function f1;
--error ER_SP_DOES_NOT_EXIST
execute stmt;
--error ER_SP_DOES_NOT_EXIST
execute stmt;
drop table t4, t3, t2, t1;


--echo #
--echo # Bug#51240 ALTER TABLE of a locked MERGE table fails
--echo #

--disable_warnings
DROP TABLE IF EXISTS m1, t1;
--enable_warnings

CREATE TABLE t1 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
LOCK TABLE m1 WRITE;
# This used to cause an error.
ALTER TABLE m1 ADD INDEX (c1);

UNLOCK TABLES;
DROP TABLE m1, t1;

--echo #
--echo # Locking the merge table won't implicitly lock children.
--echo #
CREATE TABLE t1 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1);
LOCK TABLE m1 WRITE;
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
ALTER TABLE t1 ADD INDEX (c1);
LOCK TABLE m1 WRITE, t1 WRITE;
ALTER TABLE t1 ADD INDEX (c1);
UNLOCK TABLES;
DROP TABLE m1, t1;

--echo #
--echo # Test for bug #37371 "CREATE TABLE LIKE merge loses UNION parameter"
--echo #
--disable_warnings
drop tables if exists t1, m1, m2;
--enable_warnings
create table t1 (i int) engine=myisam;
create table m1 (i int) engine=mrg_myisam union=(t1) insert_method=first;
create table m2 like m1;
--echo # Table definitions should match
show create table m1;
show create table m2;
drop tables m1, m2, t1;

--echo #
--echo # Test case for Bug#54811 "Assert in mysql_lock_have_duplicate()"
--echo # Check that unique_table() works correctly for merge tables.
--echo #
--disable_warnings
drop table if exists t1, t2, t3, m1, m2;
--enable_warnings
create table t1 (a int);
create table t2 (a int);
create table t3 (b int);
create view v1 as select * from t3,t1;
create table m1 (a int) engine=merge union (t1, t2) insert_method=last;
create table m2 (a int) engine=merge union (t1, t2) insert_method=first;
create temporary table tmp (b int);
insert into tmp (b) values (1);

insert into t1 (a) values (1);
insert into t3 (b) values (1);
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from m1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from m2));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t2));

--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t3, m1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t3, m2));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t3, t1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from t3, t2));

--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from tmp, m1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from tmp, m2));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from tmp, t1));
--error ER_UPDATE_TABLE_USED
insert into m1 (a) values ((select max(a) from tmp, t2));
 
--error ER_VIEW_PREVENT_UPDATE
insert into m1 (a) values ((select max(a) from v1));
--error ER_VIEW_PREVENT_UPDATE
insert into m1 (a) values ((select max(a) from tmp, v1));


--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from m1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from m2));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t2));

--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t3, m1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t3, m2));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t3, t1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from t3, t2));

--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from tmp, m1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from tmp, m2));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from tmp, t1));
--error ER_UPDATE_TABLE_USED
update m1 set a = ((select max(a) from tmp, t2));
 
--error ER_VIEW_PREVENT_UPDATE
update m1 set a = ((select max(a) from v1));
--error ER_VIEW_PREVENT_UPDATE
update m1 set a = ((select max(a) from tmp, v1));


--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from m1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from m2);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t2);

--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t3, m1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t3, m2);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t3, t1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from t3, t2);

--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from tmp, m1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from tmp, m2);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from tmp, t1);
--error ER_UPDATE_TABLE_USED
delete from m1 where a = (select max(a) from tmp, t2);
 
--error ER_VIEW_PREVENT_UPDATE
delete from m1 where a = (select max(a) from v1);
--error ER_VIEW_PREVENT_UPDATE
delete from m1 where a = (select max(a) from tmp, v1);

drop view v1;
drop temporary table tmp;
drop table t1, t2, t3, m1, m2;


--echo #
--echo # Bug#56494 Segfault in upgrade_shared_lock_to_exclusive() for
--echo #           REPAIR of merge table
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1, t2, t_not_exists;
--enable_warnings

CREATE TABLE t1(a INT);
ALTER TABLE t1 engine= MERGE UNION (t_not_exists);
# This caused the segfault
ANALYZE TABLE t1;
CHECK TABLE t1;
CHECKSUM TABLE t1;
OPTIMIZE TABLE t1;
REPAIR TABLE t1;

# This caused an assert
REPAIR TABLE t1 USE_FRM;

DROP TABLE t1;
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT) engine= MERGE UNION (t1);
# This caused an assert
REPAIR TABLE t2 USE_FRM;

DROP TABLE t1, t2;


--echo #
--echo # Bug#57002 Assert in upgrade_shared_lock_to_exclusive()
--echo #           for ALTER TABLE + MERGE tables
--echo #

--disable_warnings
DROP TABLE IF EXISTS t1, m1;
--enable_warnings

CREATE TABLE t1(a INT) engine=myisam;
CREATE TABLE m1(a INT) engine=merge UNION(t1);
LOCK TABLES t1 READ, m1 WRITE;

# This caused an assert
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
ALTER TABLE t1 engine=myisam;

UNLOCK TABLES;
DROP TABLE m1, t1;


--echo #
--echo # Test for bug #11754210 - "45777: CHECK TABLE DOESN'T SHOW ALL
--echo #                           PROBLEMS FOR MERGE TABLE COMPLIANCE IN 5.1"
--echo #
--disable_warnings
drop tables if exists t1, t2, t3, t4, m1;
--enable_warnings
create table t1(id int) engine=myisam;
create view t3 as select 1 as id;
create table t4(id int) engine=memory;
create table m1(id int) engine=merge union=(t1,t2,t3,t4);
--error ER_WRONG_MRG_TABLE
select * from m1;
--echo # The below CHECK and REPAIR TABLE statements should
--echo # report all problems with underlying tables: 
--echo # - absence of 't2',
--echo # - missing base table for 't3',
--echo # - wrong engine of 't4'.
check table m1;
repair table m1;
--echo # Clean-up.
drop tables m1, t1, t4;
drop view t3;


--echo End of 5.5 tests


--echo #
--echo # Additional coverage for refactoring which is made as part
--echo # of fix for bug #27480 "Extend CREATE TEMPORARY TABLES privilege
--echo # to allow temp table operations".
--echo # 
--echo # Check that prelocking works correctly for various variants of
--echo # merge tables.
--disable_warnings
drop table if exists t1, t2, m1;
drop function if exists f1;
--enable_warnings
create table t1 (j int);
insert into t1 values (1);
create function f1() returns int return (select count(*) from m1);
create temporary table t2 (a int) engine=myisam;
insert into t2 values (1);
create temporary table m1 (a int) engine=merge union=(t2);
select f1() from t1;
drop tables t2, m1;
create table t2 (a int) engine=myisam;
insert into t2 values (1);
create table m1 (a int) engine=merge union=(t2);
select f1() from t1;
drop table m1;
create temporary table m1 (a int) engine=merge union=(t2);
select f1() from t1;
drop tables t1, t2, m1;
drop function f1;
--echo #
--echo # Check that REPAIR/CHECK and CHECKSUM statements work correctly
--echo # for various variants of merge tables.
create table t1 (a int) engine=myisam;
insert into t1 values (1);
create table m1 (a int) engine=merge union=(t1);
check table m1;
repair table m1;
checksum table m1;
drop tables t1, m1;
create temporary table t1 (a int) engine=myisam;
insert into t1 values (1);
create temporary table m1 (a int) engine=merge union=(t1);
check table m1;
repair table m1;
checksum table m1;
drop tables t1, m1;
create table t1 (a int) engine=myisam;
insert into t1 values (1);
create temporary table m1 (a int) engine=merge union=(t1);
check table m1;
repair table m1;
checksum table m1;
drop tables t1, m1;

# Check effect of Bug#27480-preliminary patch:
# a merge-table with non-existing children, opened from a prelocked list.

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS m1;
DROP TRIGGER IF EXISTS trg1;
DROP TABLE IF EXISTS q1;
DROP TABLE IF EXISTS q2;
--enable_warnings

CREATE TABLE t1(a INT);
CREATE TABLE m1(a INT) ENGINE = MERGE UNION (q1, q2);

CREATE TRIGGER trg1 BEFORE DELETE ON t1
FOR EACH ROW
  INSERT INTO m1 VALUES (1);

--error ER_WRONG_MRG_TABLE
DELETE FROM t1;

DROP TRIGGER trg1;
DROP TABLE t1;
DROP TABLE m1;

--disable_result_log
--disable_query_log
eval set global storage_engine=$default;
--enable_result_log
--enable_query_log

# Check that all connections opened by test cases in this file are really
# gone so execution of other tests won't be affected by their presence.
--source include/wait_until_count_sessions.inc