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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
drop table if exists t0,t1,t2,t3,t4;
drop table if exists t0,t5,t6,t7,t8,t9,t1_1,t1_2,t9_1,t9_2;
create table t0 SELECT 1,"table 1";
create table t2 SELECT 2,"table 2";
create table t3 SELECT 3,"table 3";
rename table t0 to t1;
rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
select * from t1;
3 table 3
3 table 3
rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
rename table t3 to t4, t2 to t3, t1 to t2, t4 to t1;
select * from t1;
1 table 1
1 table 1
rename table t1 to t2;
ERROR 42S01: Table 't2' already exists
rename table t1 to t1;
ERROR 42S01: Table 't1' already exists
rename table t3 to t4, t2 to t3, t1 to t2, t4 to t2;
ERROR 42S01: Table 't2' already exists
show tables like "t_";
Tables_in_test (t_)
t1
t2
t3
rename table t3 to t1, t2 to t3, t1 to t2, t4 to t1;
ERROR 42S01: Table 't1' already exists
rename table t3 to t4, t5 to t3, t1 to t2, t4 to t1;
ERROR 42S02: Table 'test.t5' doesn't exist
select * from t1;
1 table 1
1 table 1
select * from t2;
2 table 2
2 table 2
select * from t3;
3 table 3
3 table 3
drop table if exists t1,t2,t3,t4;
Warnings:
Note 1051 Unknown table 'test.t4'
connect con1,localhost,root,,;
connect con2,localhost,root,,;
connection con1;
CREATE TABLE t1 (a int);
CREATE TABLE t3 (a int);
connection con2;
FLUSH TABLES WITH READ LOCK;
connection con1;
RENAME TABLE t1 TO t2, t3 to t4;
connection con2;
show tables;
Tables_in_test
t1
t3
UNLOCK TABLES;
connection con1;
connection con2;
show tables;
Tables_in_test
t2
t4
drop table t2, t4;
disconnect con2;
disconnect con1;
connection default;
End of 4.1 tests
#
# Bug#14959: "ALTER TABLE isn't able to rename a view"
# Bug#53976: "ALTER TABLE RENAME is allowed on views
# (not documented, broken)"
#
create table t1(f1 int);
create view v1 as select * from t1;
alter table v1 rename to v2;
ERROR HY000: 'test.v1' is not of type 'BASE TABLE'
drop view v1;
drop table t1;
End of 5.0 tests
CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TABLE t2 (a INT);
CREATE OR REPLACE TEMPORARY TABLE t1_tmp (b INT);
CREATE OR REPLACE TEMPORARY TABLE t2_tmp (b INT);
rename table t1 to t2;
ERROR 42S01: Table 't2' already exists
rename table t1 to tmp, tmp to t2;
ERROR 42S01: Table 't2' already exists
rename table t1_tmp to t2_tmp;
ERROR 42S01: Table 't2_tmp' already exists
rename table t1_tmp to tmp, tmp to t2_tmp;
ERROR 42S01: Table 't2_tmp' already exists
show create table t1_tmp;
Table Create Table
t1_tmp CREATE TEMPORARY TABLE `t1_tmp` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
show create table t2_tmp;
Table Create Table
t2_tmp CREATE TEMPORARY TABLE `t2_tmp` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
rename table t1 to t1_tmp;
rename table t2_tmp to t2;
rename table t2 to tmp, tmp to t2;
rename table t1_tmp to tmp, tmp to t1_tmp;
show tables;
Tables_in_test
t2
t1_tmp
t1_tmp
t2
SHOW CREATE TABLE t1_tmp;
Table Create Table
t1_tmp CREATE TEMPORARY TABLE `t1_tmp` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1_tmp;
SHOW CREATE TABLE t1_tmp;
Table Create Table
t1_tmp CREATE TABLE `t1_tmp` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t1_tmp;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TEMPORARY TABLE `t2` (
`b` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t2;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
drop table t2;
CREATE TABLE t1 (a INT);
insert into t1 values (1);
CREATE TEMPORARY TABLE t1 (b INT);
insert into t1 values (2);
RENAME TABLE t1 TO tmp, t1 TO t2;
select * from tmp;
b
2
select * from t2;
a
1
drop table tmp,t2;
create table t1 (a int) engine=memory;
rename table t1 to non_existent.t2;
ERROR 42000: Unknown database 'non_existent'
drop table t1;
#
# Test rename IF EXISTS
#
rename table if exists t1 to t2;
Warnings:
Note 1146 Table 'test.t1' doesn't exist
alter table if exists t1 rename to t2;
Warnings:
Error 1146 Table 'test.t1' doesn't exist
create table t2 (a int);
alter table if exists t1 rename to t2;
Warnings:
Error 1146 Table 'test.t1' doesn't exist
rename table if exists t1 to t2;
Warnings:
Note 1146 Table 'test.t1' doesn't exist
create table t1 (a int);
rename table if exists t1 to t2;
ERROR 42S01: Table 't2' already exists
alter table if exists t1 rename to t2;
ERROR 42S01: Table 't2' already exists
drop table t2;
rename table if exists t1 to t2;
alter table if exists t2 rename to t1;
drop table t1;
|
