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
|
create database test2;
#
# Testing rename error in different places
#
create table t1 (a int);
create table t2 (b int);
create table t3 (c int);
create table t4 (d int);
insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
insert into t4 values(4);
create temporary table tmp1 (a int);
create temporary table tmp2 (b int);
create temporary table tmp3 (c int);
create temporary table tmp4 (d int);
insert into tmp1 values(11);
insert into tmp2 values(22);
insert into tmp3 values(33);
insert into tmp4 values(44);
rename table t3 to t4, t1 to t5, t2 to t1, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t3 to t4, t2 to t1, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t2 to t1, t3 to t4, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t2 to t1, t5 to t2, t3 to t4;
ERROR 42S01: Table 't4' already exists
# Try failed rename using two databases
rename table test.t1 to test2.t5, test.t2 to test.t1, t5 to test.t2;
ERROR 42S02: Table 'test.t5' doesn't exist
select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
t1.a+t2.b+t3.c+t4.d
10
select * from t5;
ERROR 42S02: Table 'test.t5' doesn't exist
#
# Testing rename error in different places with temporary tables
#
rename table tmp3 to tmp4, tmp1 to t5, tmp2 to tmp1, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table tmp1 to t5, tmp3 to tmp4, tmp2 to tmp1, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table tmp1 to t5, tmp2 to tmp1, tmp3 to tmp4, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table tmp1 to t5, tmp2 to tmp1, t5 to tmp1, tmp3 to tmp4;
ERROR 42S01: Table 'tmp1' already exists
select tmp1.a+tmp2.b+tmp3.c+tmp4.d from tmp1,tmp2,tmp3,tmp4;
tmp1.a+tmp2.b+tmp3.c+tmp4.d
110
select * from t5;
ERROR 42S02: Table 'test.t5' doesn't exist
#
# Testing combinations of rename normal and temporary tables
#
rename table t1 to t5, t2 to t1, t5 to t2, tmp3 to tmp4, tmp1 to t5, tmp2 to tmp1, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp3 to tmp4, tmp2 to tmp1, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp2 to tmp1, tmp3 to tmp4, t5 to tmp1;
ERROR 42S01: Table 'tmp4' already exists
rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp2 to tmp1, t5 to tmp1, t3 to t4;
ERROR 42S01: Table 'tmp1' already exists
rename table t1 to t5, tmp2 to tmp5, t2 to t1, tmp2 to tmp1, t5 to t2, tmp5 to tmp1, t8 to t9;
ERROR 42S02: Table 'test.tmp2' doesn't exist
select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
t1.a+t2.b+t3.c+t4.d
10
select tmp1.a+tmp2.b+tmp3.c+tmp4.d from tmp1,tmp2,tmp3,tmp4;
tmp1.a+tmp2.b+tmp3.c+tmp4.d
110
drop table tmp1,tmp2,tmp3,tmp4;
#
# Similar tests with triggers
#
create trigger t1_trg before insert on t1 for each row
begin
if isnull(new.a) then
set new.a:= 10;
end if;
end|
create trigger t2_trg before insert on t2 for each row
begin
if isnull(new.b) then
set new.b:= 100;
end if;
end|
create trigger t3_trg before insert on t3 for each row
begin
if isnull(new.c) then
set new.c:= 1000;
end if;
end|
rename table t3 to t4, t1 to t5, t2 to t1, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t3 to t4, t2 to t1, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t2 to t1, t3 to t4, t5 to t2;
ERROR 42S01: Table 't4' already exists
rename table t1 to t5, t2 to t1, t5 to t2, t3 to t4;
ERROR 42S01: Table 't4' already exists
# Test of move table between databases
rename table t4 to test2.t5, t2 to t4, test2.t5 to t2, t1 to test2.t6;
ERROR HY000: Trigger in wrong schema
show triggers;
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
t1_trg INSERT t1 begin
if isnull(new.a) then
set new.a:= 10;
end if;
end BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
t2_trg INSERT t2 begin
if isnull(new.b) then
set new.b:= 100;
end if;
end BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
t3_trg INSERT t3 begin
if isnull(new.c) then
set new.c:= 1000;
end if;
end BEFORE # STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION root@localhost latin1 latin1_swedish_ci utf8mb4_uca1400_ai_ci
select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
t1.a+t2.b+t3.c+t4.d
10
insert into t1 values(null);
insert into t2 values(null);
insert into t3 values(null);
select (select sum(t1.a) from t1)+ (select sum(t2.b) from t2) + (select sum(t3.c) from t3)+ (select sum(t4.d) from t4);
(select sum(t1.a) from t1)+ (select sum(t2.b) from t2) + (select sum(t3.c) from t3)+ (select sum(t4.d) from t4)
1120
drop trigger t1_trg;
drop trigger t2_trg;
drop trigger t3_trg;
#
# Test with views
#
create view v1 as select * from t1;
create view v2 as select * from t2;
create view v3 as select * from t3;
create view v4 as select * from t4;
rename table v3 to v4, v1 to t5, v2 to v1, t5 to v2;
ERROR 42S01: Table 'v4' already exists
rename table v1 to t5, v3 to v4, v2 to v1, t5 to v2;
ERROR 42S01: Table 'v4' already exists
rename table v1 to t5, v2 to v1, v3 to v4, t5 to v2;
ERROR 42S01: Table 'v4' already exists
rename table v1 to t5, v2 to v1, t5 to v2, v3 to v4;
ERROR 42S01: Table 'v4' already exists
# Try failed rename using two databases
rename table test.v1 to test.v5, test.v2 to test.v1, test.v3 to test2.v2, non_existing_view to another_non_existing_view;
ERROR HY000: Changing schema from 'test' to 'test2' is not allowed
select (select sum(v1.a) from v1)+ (select sum(v2.b) from v2) + (select sum(v3.c) from v3)+ (select sum(v4.d) from v4);
(select sum(v1.a) from v1)+ (select sum(v2.b) from v2) + (select sum(v3.c) from v3)+ (select sum(v4.d) from v4)
1120
drop view v1,v2,v3,v4;
drop table t1, t2, t3, t4;
drop database test2;
|
