1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
#
# Test of MERGE TABLES with MyISAM memory mapping enabled (--myisam-use-mmap)
#
# 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, m1, m2;
--enable_warnings
####################
## No locked tables.
####################
#
# INSERT-SELECT with no TEMPORARY table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
SELECT * FROM t2;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY select table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY insert table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY both tables.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
DROP TABLE m2, m1, t2, t1;
####################
## With LOCK TABLES.
####################
#
# INSERT-SELECT with no TEMPORARY table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
LOCK TABLE m1 WRITE, m2 WRITE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
SELECT * FROM t2;
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY select table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
LOCK TABLE m1 WRITE, m2 WRITE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY insert table.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
LOCK TABLE m1 WRITE, m2 WRITE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
#
# INSERT-SELECT with TEMPORARY both tables.
#
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE TEMPORARY TABLE m1 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
CREATE TEMPORARY TABLE m2 (c1 INT) ENGINE=MRG_MyISAM UNION=(t1,t2)
INSERT_METHOD=LAST;
LOCK TABLE m1 WRITE, m2 WRITE;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (2), (3), (4);
INSERT INTO m2 SELECT * FROM m1;
SELECT * FROM m2;
UNLOCK TABLES;
DROP TABLE m2, m1, t2, t1;
--echo End of 6.0 tests
--disable_result_log
--disable_query_log
eval SET GLOBAL storage_engine = $default;
--enable_result_log
--enable_query_log
|
