Debian Codegraph @

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

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 create procedure populate_tables()
3 begin
4 declare n int default 20;
5 declare inner_loop int default 100;
6 create temporary table t5(c1 int not null,
7 c2 int not null,
8 c3 char(255) not null,
9 c4 text(6000) not null,
10 c5 blob(6000) not null,
11 c6 varchar(2000) not null,
12 c7 varchar(2000) not null,
13 c8 datetime,
14 c9 decimal(6,3),
15 primary key (c1),
16 index (c3,c4(50),c5(50)),
17 index (c2))
18 engine=innodb;
19 create temporary table t6 ( a int ) engine = innodb;
20 insert into t6 values (50),(100),(150),(190);
21 while (n > 0) do
22 start transaction;
23 insert ignore into t5 values(n,n,repeat(concat(' tc3_',n),30),
24 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
25 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
26 now(),(100.55+n));
27 if (n > 10) then
28 commit;
29 else
30 delete from t5 where c1 > 10 ;
31 rollback;
32 start transaction;
33 update t5 set c1 = c1 + 1000 where c1 > 10;
34 rollback;
35 end if;
36 start transaction;
37 insert ignore into t5 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
38 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
39 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
40 now(),(100.55+n+inner_loop));
41 delete from t5 where c1 between 100 and 110;
42 update t5 set c1 = c1+1 where c1>110;
43 savepoint a;
44 insert ignore into t5 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
45 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
46 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
47 now(),(100.55+n+inner_loop));
48 savepoint b;
49 insert into t5 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
50 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
51 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
52 now(),(100.55+n+inner_loop));
53 savepoint c;
54 rollback to b;
55 rollback to a;
56 commit;
57 commit;
58 rollback;
59 set n = n - 1;
60 end while;
61 end|
62 connect con1,localhost,root,,;
63 call populate_tables();;
64 connect con2,localhost,root,,;
65 call populate_tables();;
66 connection con1;
67 select c1 from t5;
68 c1
69 11
70 12
71 13
72 14
73 15
74 16
75 17
76 18
77 19
78 20
79 122
80 124
81 126
82 128
83 130
84 132
85 134
86 136
87 138
88 140
89 connection con2;
90 select c1 from t5;
91 c1
92 11
93 12
94 13
95 14
96 15
97 16
98 17
99 18
100 19
101 20
102 122
103 124
104 126
105 128
106 130
107 132
108 134
109 136
110 138
111 140
112 connection con1;
113 set autocommit = 0;
114 insert into t5 values (20,1,'a','a','a','a','a',now(),100.55);
115 ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
116 insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55);
117 Warnings:
118 Warning 1062 Duplicate entry '20' for key 'PRIMARY'
119 insert into t5 values (1,1,'a','a','a','a','a',now(),100.55),
120 (20,1,'a','a','a','a','a',now(),100.55);
121 ERROR 23000: Duplicate entry '20' for key 'PRIMARY'
122 set autocommit = 1;
123 select c1,c2 from t5 where c1 in (20,1);
124 c1 c2
125 20 20
126 replace into t5 values (20,1,'a','a','a','a','a',now(),100.55);
127 select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20;
128 c1 c2 c3 c4 c5 c6 c7 c9
129 20 1 a a a a a 100.550
130 update ignore t5 set c1 = 20 where c1 = 140 ;
131 select count(*) from t5 where c1 = 140;
132 count(*)
133 1
134 select * into outfile "VARDIR/tmp/t5.outfile" from t5;
135 create temporary table temp_1 engine = innodb as select * from t5 where 1=2;
136 select count(*) from temp_1;
137 count(*)
138 0
139 load data infile 'VARDIR/tmp/t5.outfile' into table temp_1;
140 select count(*) from temp_1;
141 count(*)
142 20
143 alter table temp_1 add column c10 int default 99 ,
144 add column c11 varchar(100) default 'test';
145 alter table temp_1 add primary key (c1);
146 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55);
147 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
148 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
149 -1 -1 a a a a a 100.550 99 test
150 select count(*) from temp_1 where c10 = 99 and c11 like 'test';
151 count(*)
152 21
153 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55)
154 on duplicate key update c1=-2,c2=-2;
155 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
156 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
157 -2 -2 a a a a a 100.550 99 test
158 disconnect con1;
159 disconnect con2;
160 connection default;
161 drop procedure populate_tables;
162 create temporary table prep_1(c1 int not null,
163 c2 int not null,
164 c3 char(255) not null,
165 c4 text(6000) not null,
166 c5 blob(6000) not null,
167 c6 varchar(2000) not null,
168 c7 varchar(2000) not null,
169 c8 datetime,
170 c9 decimal(6,3),
171 index (c3,c4(50),c5(50)),
172 index (c2))
173 engine=innodb;
174 PREPARE stm FROM "insert into prep_1 values(?,?,repeat(concat(' tc3_',?),30),repeat(concat(' tc4_',?),800),repeat(concat(' tc_',?),800),repeat(concat(' tc6_',?),245),repeat(concat(' tc7_',?),245),now(),(100.55+?))";
175 set @var = 5;
176 set @var_static = 5;
177 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
178 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
179 set @var = @var - 1;
180 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
181 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
182 set @var = @var - 1;
183 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
184 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
185 set @var = @var - 1;
186 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
187 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
188 set @var = @var - 1;
189 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
190 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
191 set @var = @var - 1;
192 select c1,left(c3,15) from prep_1 order by c1 ;
193 c1 left(c3,15)
194 1 tc3_1 tc3_1 tc
195 2 tc3_2 tc3_2 tc
196 3 tc3_3 tc3_3 tc
197 4 tc3_4 tc3_4 tc
198 5 tc3_5 tc3_5 tc
199 5 tc3_5 tc3_5 tc
200 5 tc3_5 tc3_5 tc
201 5 tc3_5 tc3_5 tc
202 5 tc3_5 tc3_5 tc
203 5 tc3_5 tc3_5 tc
204 select count(*) from prep_1;
205 count(*)
206 10
207 PREPARE stm_1 FROM "UPDATE prep_1 SET c1 = c1 + 1";
208 EXECUTE stm_1;
209 EXECUTE stm_1;
210 select c1,left(c3,15) from prep_1 order by c1 ;
211 c1 left(c3,15)
212 3 tc3_1 tc3_1 tc
213 4 tc3_2 tc3_2 tc
214 5 tc3_3 tc3_3 tc
215 6 tc3_4 tc3_4 tc
216 7 tc3_5 tc3_5 tc
217 7 tc3_5 tc3_5 tc
218 7 tc3_5 tc3_5 tc
219 7 tc3_5 tc3_5 tc
220 7 tc3_5 tc3_5 tc
221 7 tc3_5 tc3_5 tc
222 select count(*) from prep_1;
223 count(*)
224 10
225 PREPARE stm_2 FROM "DELETE FROM prep_1 ORDER BY c1 LIMIT 1";
226 EXECUTE stm_2;
227 EXECUTE stm_2;
228 select c1,left(c3,15) from prep_1 order by c1 ;
229 c1 left(c3,15)
230 5 tc3_3 tc3_3 tc
231 6 tc3_4 tc3_4 tc
232 7 tc3_5 tc3_5 tc
233 7 tc3_5 tc3_5 tc
234 7 tc3_5 tc3_5 tc
235 7 tc3_5 tc3_5 tc
236 7 tc3_5 tc3_5 tc
237 7 tc3_5 tc3_5 tc
238 select count(*) from prep_1;
239 count(*)
240 8
241 drop prepare stm;
242 drop prepare stm_1;
243 drop prepare stm_2;
244 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;