Debian Codegraph @

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

raw
1 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
2 create procedure populate_tables()
3 begin
4 declare n int default 20;
5 declare inner_loop int default 100;
6 create temporary table t1(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 row_format=redundant charset latin1;
19 create temporary table t2(c1 int not null,
20 c2 int not null,
21 c3 char(255) not null,
22 c4 text(6000) not null,
23 c5 blob(6000) not null,
24 c6 varchar(2000) not null,
25 c7 varchar(2000) not null,
26 c8 datetime,
27 c9 decimal(6,3),
28 primary key (c1),
29 index (c3,c4(50),c5(50)),
30 index (c2))
31 engine=innodb row_format=compact charset latin1;
32 create temporary table t4(c1 int not null,
33 c2 int not null,
34 c3 char(255) not null,
35 c4 text(6000) not null,
36 c5 blob(6000) not null,
37 c6 varchar(2000) not null,
38 c7 varchar(2000) not null,
39 c8 datetime,
40 c9 decimal(6,3),
41 primary key (c1),
42 index (c3,c4(50),c5(50)),
43 index (c2))
44 engine=innodb row_format=dynamic charset latin1;
45 create temporary table t5(c1 int not null,
46 c2 int not null,
47 c3 char(255) not null,
48 c4 text(6000) not null,
49 c5 blob(6000) not null,
50 c6 varchar(2000) not null,
51 c7 varchar(2000) not null,
52 c8 datetime,
53 c9 decimal(6,3),
54 primary key (c1),
55 index (c3,c4(50),c5(50)),
56 index (c2))
57 engine=innodb charset latin1;
58 create temporary table t6 ( a int ) engine = innodb;
59 insert into t6 values (50),(100),(150),(190);
60 while (n > 0) do
61 start transaction;
62 insert into t1 values(n,n,repeat(concat(' tc3_',n),30),
63 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
64 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
65 now(),(100.55+n));
66 insert into t2 values(n,n,repeat(concat(' tc3_',n),30),
67 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
68 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
69 now(),(100.55+n));
70 insert into t4 values(n,n,repeat(concat(' tc3_',n),30),
71 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
72 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
73 now(),(100.55+n));
74 insert into t5 values(n,n,repeat(concat(' tc3_',n),30),
75 repeat(concat(' tc4_',n),800),repeat(concat(' tc_',n),800),
76 repeat(concat(' tc6_',n),800),repeat(concat(' tc7_',n),800),
77 now(),(100.55+n));
78 if (n > 10) then
79 commit;
80 else
81 delete from t1 where c1 > 10 ;
82 delete from t2 where c1 > 10 ;
83 delete from t4 where c1 > 10 ;
84 delete from t5 where c1 > 10 ;
85 rollback;
86 start transaction;
87 update t1 set c1 = c1 + 1000 where c1 > 10;
88 update t2 set c1 = c1 + 1000 where c1 > 10;
89 update t4 set c1 = c1 + 1000 where c1 > 10;
90 update t5 set c1 = c1 + 1000 where c1 > 10;
91 rollback;
92 end if;
93 start transaction;
94 insert into t1 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
95 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
96 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
97 now(),(100.55+n+inner_loop));
98 insert into t2 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
99 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
100 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
101 now(),(100.55+n+inner_loop));
102 insert into t4 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
103 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
104 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
105 now(),(100.55+n+inner_loop));
106 insert into t5 values(n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
107 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
108 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
109 now(),(100.55+n+inner_loop));
110 delete from t1 where c1 between 100 and 110;
111 delete from t2 where c1 between 100 and 110;
112 delete from t4 where c1 between 100 and 110;
113 delete from t5 where c1 between 100 and 110;
114 update t1 set c1 = c1+1 where c1>110;
115 update t2 set c1 = c1+1 where c1>110;
116 update t4 set c1 = c1+1 where c1>110;
117 update t5 set c1 = c1+1 where c1>110;
118 savepoint a;
119 insert into t1 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
120 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
121 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
122 now(),(100.55+n+inner_loop));
123 insert into t2 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
124 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
125 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
126 now(),(100.55+n+inner_loop));
127 insert into t4 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
128 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
129 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
130 now(),(100.55+n+inner_loop));
131 insert into t5 values(300+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
132 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
133 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
134 now(),(100.55+n+inner_loop));
135 savepoint b;
136 insert into t1 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
137 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
138 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
139 now(),(100.55+n+inner_loop));
140 insert into t2 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
141 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
142 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
143 now(),(100.55+n+inner_loop));
144 insert into t4 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
145 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
146 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
147 now(),(100.55+n+inner_loop));
148 insert into t5 values(400+n+inner_loop,n+inner_loop,repeat(concat(' tc3_',n+inner_loop),30),
149 repeat(concat(' tc4_',n+inner_loop),800),repeat(concat(' tc_',n+inner_loop),800),
150 repeat(concat(' tc6_',n+inner_loop),245),repeat(concat(' tc7_',n+inner_loop),245),
151 now(),(100.55+n+inner_loop));
152 savepoint c;
153 rollback to b;
154 rollback to a;
155 commit;
156 commit;
157 rollback;
158 set n = n - 1;
159 end while;
160 end|
161 #---client 1 : dml operation ---"
162 #---client 2 : dml operation ---"
163 # In connection 1
164 select count(*) from t1;
165 count(*)
166 20
167 select count(*) from t2;
168 count(*)
169 20
170 select count(*) from t4;
171 count(*)
172 20
173 select count(*) from t5;
174 count(*)
175 20
176 select c1 from t1;
177 c1
178 11
179 12
180 13
181 14
182 15
183 16
184 17
185 18
186 19
187 20
188 122
189 124
190 126
191 128
192 130
193 132
194 134
195 136
196 138
197 140
198 select c1 from t2;
199 c1
200 11
201 12
202 13
203 14
204 15
205 16
206 17
207 18
208 19
209 20
210 122
211 124
212 126
213 128
214 130
215 132
216 134
217 136
218 138
219 140
220 select c1 from t4;
221 c1
222 11
223 12
224 13
225 14
226 15
227 16
228 17
229 18
230 19
231 20
232 122
233 124
234 126
235 128
236 130
237 132
238 134
239 136
240 138
241 140
242 select c1 from t5;
243 c1
244 11
245 12
246 13
247 14
248 15
249 16
250 17
251 18
252 19
253 20
254 122
255 124
256 126
257 128
258 130
259 132
260 134
261 136
262 138
263 140
264 # In connection 2
265 select count(*) from t1;
266 count(*)
267 20
268 select count(*) from t2;
269 count(*)
270 20
271 select count(*) from t4;
272 count(*)
273 20
274 select count(*) from t5;
275 count(*)
276 20
277 select c1 from t1;
278 c1
279 11
280 12
281 13
282 14
283 15
284 16
285 17
286 18
287 19
288 20
289 122
290 124
291 126
292 128
293 130
294 132
295 134
296 136
297 138
298 140
299 select c1 from t2;
300 c1
301 11
302 12
303 13
304 14
305 15
306 16
307 17
308 18
309 19
310 20
311 122
312 124
313 126
314 128
315 130
316 132
317 134
318 136
319 138
320 140
321 select c1 from t4;
322 c1
323 11
324 12
325 13
326 14
327 15
328 16
329 17
330 18
331 19
332 20
333 122
334 124
335 126
336 128
337 130
338 132
339 134
340 136
341 138
342 140
343 select c1 from t5;
344 c1
345 11
346 12
347 13
348 14
349 15
350 16
351 17
352 18
353 19
354 20
355 122
356 124
357 126
358 128
359 130
360 132
361 134
362 136
363 138
364 140
365 # In connection 1
366 set autocommit = 0;
367 insert into t1 values (20,1,'a','a','a','a','a',now(),100.55);
368 ERROR 23000: Duplicate entry '20' for key 't1.PRIMARY'
369 insert ignore into t1 values (20,1,'a','a','a','a','a',now(),100.55);
370 Warnings:
371 Warning 1062 Duplicate entry '20' for key 't1.PRIMARY'
372 insert into t2 values (20,1,'a','a','a','a','a',now(),100.55);
373 ERROR 23000: Duplicate entry '20' for key 't2.PRIMARY'
374 insert ignore into t2 values (20,1,'a','a','a','a','a',now(),100.55);
375 Warnings:
376 Warning 1062 Duplicate entry '20' for key 't2.PRIMARY'
377 insert into t4 values (20,1,'a','a','a','a','a',now(),100.55);
378 ERROR 23000: Duplicate entry '20' for key 't4.PRIMARY'
379 insert ignore into t4 values (20,1,'a','a','a','a','a',now(),100.55);
380 Warnings:
381 Warning 1062 Duplicate entry '20' for key 't4.PRIMARY'
382 insert into t5 values (20,1,'a','a','a','a','a',now(),100.55);
383 ERROR 23000: Duplicate entry '20' for key 't5.PRIMARY'
384 insert ignore into t5 values (20,1,'a','a','a','a','a',now(),100.55);
385 Warnings:
386 Warning 1062 Duplicate entry '20' for key 't5.PRIMARY'
387 insert into t1 values (1,1,'a','a','a','a','a',now(),100.55),
388 (20,1,'a','a','a','a','a',now(),100.55);
389 ERROR 23000: Duplicate entry '20' for key 't1.PRIMARY'
390 insert into t2 values (1,1,'a','a','a','a','a',now(),100.55),
391 (20,1,'a','a','a','a','a',now(),100.55);
392 ERROR 23000: Duplicate entry '20' for key 't2.PRIMARY'
393 insert into t4 values (1,1,'a','a','a','a','a',now(),100.55),
394 (20,1,'a','a','a','a','a',now(),100.55);
395 ERROR 23000: Duplicate entry '20' for key 't4.PRIMARY'
396 insert into t5 values (1,1,'a','a','a','a','a',now(),100.55),
397 (20,1,'a','a','a','a','a',now(),100.55);
398 ERROR 23000: Duplicate entry '20' for key 't5.PRIMARY'
399 set autocommit = 1;
400 select c1,c2 from t1 where c1 in (20,1);
401 c1 c2
402 20 20
403 select c1,c2 from t2 where c1 in (20,1);
404 c1 c2
405 20 20
406 select c1,c2 from t4 where c1 in (20,1);
407 c1 c2
408 20 20
409 select c1,c2 from t5 where c1 in (20,1);
410 c1 c2
411 20 20
412 replace into t1 values (20,1,'a','a','a','a','a',now(),100.55);
413 replace into t2 values (20,1,'a','a','a','a','a',now(),100.55);
414 replace into t4 values (20,1,'a','a','a','a','a',now(),100.55);
415 replace into t5 values (20,1,'a','a','a','a','a',now(),100.55);
416 select c1,c2,c3,c4,c5,c6,c7,c9 from t1 where c1 = 20;
417 c1 c2 c3 c4 c5 c6 c7 c9
418 20 1 a a a a a 100.550
419 select c1,c2,c3,c4,c5,c6,c7,c9 from t2 where c1 = 20;
420 c1 c2 c3 c4 c5 c6 c7 c9
421 20 1 a a a a a 100.550
422 select c1,c2,c3,c4,c5,c6,c7,c9 from t4 where c1 = 20;
423 c1 c2 c3 c4 c5 c6 c7 c9
424 20 1 a a a a a 100.550
425 select c1,c2,c3,c4,c5,c6,c7,c9 from t5 where c1 = 20;
426 c1 c2 c3 c4 c5 c6 c7 c9
427 20 1 a a a a a 100.550
428 update ignore t1 set c1 = 20 where c1 = 140 ;
429 Warnings:
430 Warning 1062 Duplicate entry '20' for key 't1.PRIMARY'
431 update ignore t2 set c1 = 20 where c1 = 140 ;
432 Warnings:
433 Warning 1062 Duplicate entry '20' for key 't2.PRIMARY'
434 update ignore t4 set c1 = 20 where c1 = 140 ;
435 Warnings:
436 Warning 1062 Duplicate entry '20' for key 't4.PRIMARY'
437 update ignore t5 set c1 = 20 where c1 = 140 ;
438 Warnings:
439 Warning 1062 Duplicate entry '20' for key 't5.PRIMARY'
440 select count(*) from t1 where c1 = 140;
441 count(*)
442 1
443 select count(*) from t2 where c1 = 140;
444 count(*)
445 1
446 select count(*) from t4 where c1 = 140;
447 count(*)
448 1
449 select count(*) from t5 where c1 = 140;
450 count(*)
451 1
452 "running select * into outfile <file> from t1 ;
453 create temporary table temp_1 engine = innodb as select * from t1 where 1=2;
454 select count(*) from temp_1;
455 count(*)
456 0
457 "running load data infile <file> into temp_1 ;
458 select count(*) from temp_1;
459 count(*)
460 20
461 alter table temp_1 add column c10 int default 99 ,
462 add column c11 varchar(100) default 'test';
463 alter table temp_1 add primary key (c1);
464 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55);
465 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
466 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
467 -1 -1 a a a a a 100.550 99 test
468 select count(*) from temp_1 where c10 = 99 and c11 like 'test';
469 count(*)
470 21
471 insert into temp_1 (c1,c2,c3,c4,c5,c6,c7,c8,c9) values (-1,-1,'a','a','a','a','a',now(),100.55)
472 on duplicate key update c1=-2,c2=-2;
473 select c1,c2,c3,c4,c5,c6,c7,c9,c10,c11 from temp_1 where c1 < 0;
474 c1 c2 c3 c4 c5 c6 c7 c9 c10 c11
475 -2 -2 a a a a a 100.550 99 test
476 drop table t1,t2,t4,t5,t6,temp_1;
477 drop table t1 ,t2,t4,t5,t6;
478 drop procedure populate_tables;
479 create temporary table prep_1(c1 int not null,
480 c2 int not null,
481 c3 char(255) not null,
482 c4 text(6000) not null,
483 c5 blob(6000) not null,
484 c6 varchar(2000) not null,
485 c7 varchar(2000) not null,
486 c8 datetime,
487 c9 decimal(6,3),
488 index (c3,c4(50),c5(50)),
489 index (c2))
490 engine=innodb;
491 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+?))";
492 set @var = 5;
493 set @var_static = 5;
494 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
495 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
496 set @var = @var - 1;
497 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
498 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
499 set @var = @var - 1;
500 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
501 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
502 set @var = @var - 1;
503 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
504 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
505 set @var = @var - 1;
506 EXECUTE stm USING @var,@var,@var,@var,@var,@var,@var,@var;
507 EXECUTE stm USING @var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static,@var_static;
508 set @var = @var - 1;
509 select c1,left(c3,15) from prep_1 order by c1 ;
510 c1 left(c3,15)
511 1 tc3_1 tc3_1 tc
512 2 tc3_2 tc3_2 tc
513 3 tc3_3 tc3_3 tc
514 4 tc3_4 tc3_4 tc
515 5 tc3_5 tc3_5 tc
516 5 tc3_5 tc3_5 tc
517 5 tc3_5 tc3_5 tc
518 5 tc3_5 tc3_5 tc
519 5 tc3_5 tc3_5 tc
520 5 tc3_5 tc3_5 tc
521 select count(*) from prep_1;
522 count(*)
523 10
524 PREPARE stm_1 FROM "UPDATE prep_1 SET c1 = c1 + 1";
525 EXECUTE stm_1;
526 EXECUTE stm_1;
527 select c1,left(c3,15) from prep_1 order by c1 ;
528 c1 left(c3,15)
529 3 tc3_1 tc3_1 tc
530 4 tc3_2 tc3_2 tc
531 5 tc3_3 tc3_3 tc
532 6 tc3_4 tc3_4 tc
533 7 tc3_5 tc3_5 tc
534 7 tc3_5 tc3_5 tc
535 7 tc3_5 tc3_5 tc
536 7 tc3_5 tc3_5 tc
537 7 tc3_5 tc3_5 tc
538 7 tc3_5 tc3_5 tc
539 select count(*) from prep_1;
540 count(*)
541 10
542 PREPARE stm_2 FROM "DELETE FROM prep_1 ORDER BY c1 LIMIT 1";
543 EXECUTE stm_2;
544 EXECUTE stm_2;
545 select c1,left(c3,15) from prep_1 order by c1 ;
546 c1 left(c3,15)
547 5 tc3_3 tc3_3 tc
548 6 tc3_4 tc3_4 tc
549 7 tc3_5 tc3_5 tc
550 7 tc3_5 tc3_5 tc
551 7 tc3_5 tc3_5 tc
552 7 tc3_5 tc3_5 tc
553 7 tc3_5 tc3_5 tc
554 7 tc3_5 tc3_5 tc
555 select count(*) from prep_1;
556 count(*)
557 8
558 drop prepare stm;
559 drop prepare stm_1;
560 drop prepare stm_2;
561 drop table prep_1;