Debian Codegraph @

mysql-test/suite/atomic/rename_combinations.result

raw
1 create database test2;
2 #
3 # Testing rename error in different places
4 #
5 create table t1 (a int);
6 create table t2 (b int);
7 create table t3 (c int);
8 create table t4 (d int);
9 insert into t1 values(1);
10 insert into t2 values(2);
11 insert into t3 values(3);
12 insert into t4 values(4);
13 create temporary table tmp1 (a int);
14 create temporary table tmp2 (b int);
15 create temporary table tmp3 (c int);
16 create temporary table tmp4 (d int);
17 insert into tmp1 values(11);
18 insert into tmp2 values(22);
19 insert into tmp3 values(33);
20 insert into tmp4 values(44);
21 rename table t3 to t4, t1 to t5, t2 to t1, t5 to t2;
22 ERROR 42S01: Table 't4' already exists
23 rename table t1 to t5, t3 to t4, t2 to t1, t5 to t2;
24 ERROR 42S01: Table 't4' already exists
25 rename table t1 to t5, t2 to t1, t3 to t4, t5 to t2;
26 ERROR 42S01: Table 't4' already exists
27 rename table t1 to t5, t2 to t1, t5 to t2, t3 to t4;
28 ERROR 42S01: Table 't4' already exists
29 # Try failed rename using two databases
30 rename table test.t1 to test2.t5, test.t2 to test.t1, t5 to test.t2;
31 ERROR 42S02: Table 'test.t5' doesn't exist
32 select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
33 t1.a+t2.b+t3.c+t4.d
34 10
35 select * from t5;
36 ERROR 42S02: Table 'test.t5' doesn't exist
37 #
38 # Testing rename error in different places with temporary tables
39 #
40 rename table tmp3 to tmp4, tmp1 to t5, tmp2 to tmp1, t5 to tmp1;
41 ERROR 42S01: Table 'tmp4' already exists
42 rename table tmp1 to t5, tmp3 to tmp4, tmp2 to tmp1, t5 to tmp1;
43 ERROR 42S01: Table 'tmp4' already exists
44 rename table tmp1 to t5, tmp2 to tmp1, tmp3 to tmp4, t5 to tmp1;
45 ERROR 42S01: Table 'tmp4' already exists
46 rename table tmp1 to t5, tmp2 to tmp1, t5 to tmp1, tmp3 to tmp4;
47 ERROR 42S01: Table 'tmp1' already exists
48 select tmp1.a+tmp2.b+tmp3.c+tmp4.d from tmp1,tmp2,tmp3,tmp4;
49 tmp1.a+tmp2.b+tmp3.c+tmp4.d
50 110
51 select * from t5;
52 ERROR 42S02: Table 'test.t5' doesn't exist
53 #
54 # Testing combinations of rename normal and temporary tables
55 #
56 rename table t1 to t5, t2 to t1, t5 to t2, tmp3 to tmp4, tmp1 to t5, tmp2 to tmp1, t5 to tmp1;
57 ERROR 42S01: Table 'tmp4' already exists
58 rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp3 to tmp4, tmp2 to tmp1, t5 to tmp1;
59 ERROR 42S01: Table 'tmp4' already exists
60 rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp2 to tmp1, tmp3 to tmp4, t5 to tmp1;
61 ERROR 42S01: Table 'tmp4' already exists
62 rename table t1 to t5, t2 to t1, t5 to t2, tmp1 to t5, tmp2 to tmp1, t5 to tmp1, t3 to t4;
63 ERROR 42S01: Table 'tmp1' already exists
64 rename table t1 to t5, tmp2 to tmp5, t2 to t1, tmp2 to tmp1, t5 to t2, tmp5 to tmp1, t8 to t9;
65 ERROR 42S02: Table 'test.tmp2' doesn't exist
66 select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
67 t1.a+t2.b+t3.c+t4.d
68 10
69 select tmp1.a+tmp2.b+tmp3.c+tmp4.d from tmp1,tmp2,tmp3,tmp4;
70 tmp1.a+tmp2.b+tmp3.c+tmp4.d
71 110
72 drop table tmp1,tmp2,tmp3,tmp4;
73 #
74 # Similar tests with triggers
75 #
76 create trigger t1_trg before insert on t1 for each row
77 begin
78 if isnull(new.a) then
79 set new.a:= 10;
80 end if;
81 end|
82 create trigger t2_trg before insert on t2 for each row
83 begin
84 if isnull(new.b) then
85 set new.b:= 100;
86 end if;
87 end|
88 create trigger t3_trg before insert on t3 for each row
89 begin
90 if isnull(new.c) then
91 set new.c:= 1000;
92 end if;
93 end|
94 rename table t3 to t4, t1 to t5, t2 to t1, t5 to t2;
95 ERROR 42S01: Table 't4' already exists
96 rename table t1 to t5, t3 to t4, t2 to t1, t5 to t2;
97 ERROR 42S01: Table 't4' already exists
98 rename table t1 to t5, t2 to t1, t3 to t4, t5 to t2;
99 ERROR 42S01: Table 't4' already exists
100 rename table t1 to t5, t2 to t1, t5 to t2, t3 to t4;
101 ERROR 42S01: Table 't4' already exists
102 # Test of move table between databases
103 rename table t4 to test2.t5, t2 to t4, test2.t5 to t2, t1 to test2.t6;
104 ERROR HY000: Trigger in wrong schema
105 show triggers;
106 Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
107 t1_trg INSERT t1 begin
108 if isnull(new.a) then
109 set new.a:= 10;
110 end if;
111 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
112 t2_trg INSERT t2 begin
113 if isnull(new.b) then
114 set new.b:= 100;
115 end if;
116 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
117 t3_trg INSERT t3 begin
118 if isnull(new.c) then
119 set new.c:= 1000;
120 end if;
121 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
122 select t1.a+t2.b+t3.c+t4.d from t1,t2,t3,t4;
123 t1.a+t2.b+t3.c+t4.d
124 10
125 insert into t1 values(null);
126 insert into t2 values(null);
127 insert into t3 values(null);
128 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);
129 (select sum(t1.a) from t1)+ (select sum(t2.b) from t2) + (select sum(t3.c) from t3)+ (select sum(t4.d) from t4)
130 1120
131 drop trigger t1_trg;
132 drop trigger t2_trg;
133 drop trigger t3_trg;
134 #
135 # Test with views
136 #
137 create view v1 as select * from t1;
138 create view v2 as select * from t2;
139 create view v3 as select * from t3;
140 create view v4 as select * from t4;
141 rename table v3 to v4, v1 to t5, v2 to v1, t5 to v2;
142 ERROR 42S01: Table 'v4' already exists
143 rename table v1 to t5, v3 to v4, v2 to v1, t5 to v2;
144 ERROR 42S01: Table 'v4' already exists
145 rename table v1 to t5, v2 to v1, v3 to v4, t5 to v2;
146 ERROR 42S01: Table 'v4' already exists
147 rename table v1 to t5, v2 to v1, t5 to v2, v3 to v4;
148 ERROR 42S01: Table 'v4' already exists
149 # Try failed rename using two databases
150 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;
151 ERROR HY000: Changing schema from 'test' to 'test2' is not allowed
152 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);
153 (select sum(v1.a) from v1)+ (select sum(v2.b) from v2) + (select sum(v3.c) from v3)+ (select sum(v4.d) from v4)
154 1120
155 drop view v1,v2,v3,v4;
156 drop table t1, t2, t3, t4;
157 drop database test2;