Debian Codegraph @

mysql-test/suite/innodb/r/dml_operations_temp_table.result

raw
1 use test;
2 drop table if exists t;
3 create temporary table t (
4 i int,
5 f float,
6 primary key pk_index(i),
7 index sec_index(f)
8 ) engine = innodb;
9 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
10 rollback;
11 select * from t;
12 i f
13 10 2.2
14 20 3.3
15 30 4.4
16 40 5.5
17 update t set i = 50 where i = 40;
18 update t set i = 60 where i = 30;
19 rollback;
20 select * from t;
21 i f
22 10 2.2
23 20 3.3
24 50 5.5
25 60 4.4
26 delete from t where i = 10;
27 delete from t where f < 3.4;
28 rollback;
29 select * from t;
30 i f
31 50 5.5
32 60 4.4
33 delete from t where i = 10;
34 delete from t;
35 select * from t;
36 i f
37 drop table if exists t;
38 create temporary table t (
39 i int,
40 f float,
41 primary key pk_index(i),
42 index sec_index(f)
43 ) engine = innodb;
44 begin;
45 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
46 select * from t;
47 i f
48 10 2.2
49 20 3.3
50 30 4.4
51 40 5.5
52 commit;
53 select * from t;
54 i f
55 10 2.2
56 20 3.3
57 30 4.4
58 40 5.5
59 rollback;
60 select * from t;
61 i f
62 10 2.2
63 20 3.3
64 30 4.4
65 40 5.5
66 drop table if exists t;
67 create temporary table t (
68 i int,
69 f float,
70 primary key pk_index(i),
71 index sec_index(f)
72 ) engine = innodb;
73 begin;
74 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
75 select * from t;
76 i f
77 10 2.2
78 20 3.3
79 30 4.4
80 40 5.5
81 rollback;
82 select * from t;
83 i f
84 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
85 begin;
86 delete from t;
87 select * from t;
88 i f
89 rollback;
90 select * from t;
91 i f
92 10 2.2
93 20 3.3
94 30 4.4
95 40 5.5
96 begin;
97 update t set i = 50 where i = 40;
98 update t set i = 60 where i = 30;
99 select * from t;
100 i f
101 10 2.2
102 20 3.3
103 50 5.5
104 60 4.4
105 rollback;
106 select * from t;
107 i f
108 10 2.2
109 20 3.3
110 30 4.4
111 40 5.5
112 drop table if exists t;
113 create temporary table t (
114 i int,
115 f float,
116 primary key pk_index(i),
117 index sec_index(f)
118 ) engine = innodb;
119 start transaction;
120 savepoint first;
121 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
122 select * from t;
123 i f
124 10 2.2
125 20 3.3
126 30 4.4
127 40 5.5
128 rollback to first;
129 select * from t;
130 i f
131 insert into t values (10, 2.2), (20, 3.3), (30, 4.4), (40, 5.5);
132 begin;
133 delete from t where i = 10;
134 select * from t;
135 i f
136 20 3.3
137 30 4.4
138 40 5.5
139 savepoint first;
140 delete from t;
141 select * from t;
142 i f
143 rollback to first;
144 select * from t;
145 i f
146 20 3.3
147 30 4.4
148 40 5.5
149 commit;
150 select * from t;
151 i f
152 20 3.3
153 30 4.4
154 40 5.5
155 begin;
156 update t set i = 50 where i = 40;
157 savepoint first;
158 update t set i = 60 where i = 30;
159 select * from t;
160 i f
161 20 3.3
162 50 5.5
163 60 4.4
164 rollback to first;
165 select * from t;
166 i f
167 20 3.3
168 30 4.4
169 50 5.5
170 commit;
171 drop table if exists t;
172 use test;
173 create temporary table t1 (i int, f float,
174 primary key pk(i),
175 unique index fk(f)
176 ) engine = innodb;
177 insert into t1 values (1, 1.2), (2, 2.3), (3, 3.4);
178 select * from t1;
179 i f
180 1 1.2
181 2 2.3
182 3 3.4
183 insert into t1 values (4, 4.5), (1, 5.6);
184 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
185 select * from t1;
186 i f
187 1 1.2
188 2 2.3
189 3 3.4
190 insert into t1 values (4, 4.5), (5, 5.6);
191 select * from t1;
192 i f
193 1 1.2
194 2 2.3
195 3 3.4
196 4 4.5
197 5 5.6
198 update t1 set i = 1 where i = 4;
199 ERROR 23000: Duplicate entry '1' for key 't1.PRIMARY'
200 truncate table t1;
201 insert into t1 values (1, 1.2), (3, 2.3), (5, 3.4), (6, 7.4);
202 select * from t1;
203 i f
204 1 1.2
205 3 2.3
206 5 3.4
207 6 7.4
208 update t1 set i = i + 1;
209 ERROR 23000: Duplicate entry '6' for key 't1.PRIMARY'
210 select * from t1;
211 i f
212 1 1.2
213 3 2.3
214 5 3.4
215 6 7.4
216 drop table t1;