Debian Codegraph @

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

raw
1 create temporary table t1
2 (keyc int, c1 char(100), c2 char(100),
3 primary key(keyc), index sec_index(c1)
4 ) engine = innodb;
5 create temporary table t2
6 (keyc int, c1 char(100), c2 char(100),
7 primary key(keyc), index sec_index(c1)
8 ) engine = innodb;
9 create procedure populate_t1()
10 begin
11 declare i int default 1;
12 while (i <= 200) do
13 insert into t1 values (i, 'a', 'b');
14 set i = i + 1;
15 end while;
16 end|
17 create procedure populate_t1_small()
18 begin
19 declare i int default 1;
20 while (i <= 20) do
21 insert into t1 values (i, 'c', 'd');
22 set i = i + 1;
23 end while;
24 end|
25 create procedure populate_t1_small2()
26 begin
27 declare i int default 30;
28 while (i <= 50) do
29 insert into t1 values (i, 'e', 'f');
30 set i = i + 1;
31 end while;
32 end|
33 begin;
34 select count(*) from t1;
35 count(*)
36 0
37 call populate_t1();
38 select count(*) from t1;
39 count(*)
40 200
41 select * from t1 limit 10;
42 keyc c1 c2
43 1 a b
44 2 a b
45 3 a b
46 4 a b
47 5 a b
48 6 a b
49 7 a b
50 8 a b
51 9 a b
52 10 a b
53 rollback;
54 select count(*) from t1;
55 count(*)
56 0
57 begin;
58 call populate_t1();
59 select count(*) from t1;
60 count(*)
61 200
62 commit;
63 select count(*) from t1;
64 count(*)
65 200
66 truncate table t1;
67 select count(*) from t1;
68 count(*)
69 0
70 call populate_t1_small();
71 select count(*) from t1;
72 count(*)
73 20
74 rollback;
75 select count(*) from t1;
76 count(*)
77 20
78 truncate table t1;
79 call populate_t1();
80 select count(*) from t1;
81 count(*)
82 200
83 delete from t1 where keyc <= 60;
84 select count(*) from t1;
85 count(*)
86 140
87 call populate_t1_small();
88 select count(*) from t1;
89 count(*)
90 160
91 select * from t1 limit 10;
92 keyc c1 c2
93 1 c d
94 2 c d
95 3 c d
96 4 c d
97 5 c d
98 6 c d
99 7 c d
100 8 c d
101 9 c d
102 10 c d
103 begin;
104 call populate_t1_small2();
105 select count(*) from t1;
106 count(*)
107 181
108 select * from t1 where keyc > 30 limit 10;
109 keyc c1 c2
110 31 e f
111 32 e f
112 33 e f
113 34 e f
114 35 e f
115 36 e f
116 37 e f
117 38 e f
118 39 e f
119 40 e f
120 rollback;
121 select count(*) from t1;
122 count(*)
123 160
124 select * from t1 where keyc > 30 limit 10;
125 keyc c1 c2
126 61 a b
127 62 a b
128 63 a b
129 64 a b
130 65 a b
131 66 a b
132 67 a b
133 68 a b
134 69 a b
135 70 a b
136 update t1 set keyc = keyc + 2000;
137 select * from t1 limit 10;
138 keyc c1 c2
139 2001 c d
140 2002 c d
141 2003 c d
142 2004 c d
143 2005 c d
144 2006 c d
145 2007 c d
146 2008 c d
147 2009 c d
148 2010 c d
149 rollback;
150 begin;
151 update t1 set keyc = keyc + 2000;
152 select * from t1 limit 10;
153 keyc c1 c2
154 4001 c d
155 4002 c d
156 4003 c d
157 4004 c d
158 4005 c d
159 4006 c d
160 4007 c d
161 4008 c d
162 4009 c d
163 4010 c d
164 rollback;
165 select * from t1 limit 10;
166 keyc c1 c2
167 2001 c d
168 2002 c d
169 2003 c d
170 2004 c d
171 2005 c d
172 2006 c d
173 2007 c d
174 2008 c d
175 2009 c d
176 2010 c d
177 commit;
178 select * from t1 limit 10;
179 keyc c1 c2
180 2001 c d
181 2002 c d
182 2003 c d
183 2004 c d
184 2005 c d
185 2006 c d
186 2007 c d
187 2008 c d
188 2009 c d
189 2010 c d
190 insert into t2 select * from t1 where keyc < 2101;
191 select count(*) from t2;
192 count(*)
193 60
194 drop procedure populate_t1;
195 drop procedure populate_t1_small;
196 drop procedure populate_t1_small2;
197 drop temporary table t1,t2;
198 create temporary table t1
199 (keyc int, c1 char(100), c2 char(100),
200 primary key(keyc), index sec_index(c1)
201 ) engine = innodb key_block_size = 4;
202 ERROR HY000: CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
203 create temporary table t1
204 (keyc int, c1 char(100), c2 char(100),
205 primary key(keyc), index sec_index(c1)
206 ) engine = innodb;
207 set innodb_strict_mode=off;
208 create temporary table t2
209 (keyc int, c1 char(100), c2 char(100),
210 primary key(keyc), index sec_index(c1)
211 ) engine = innodb key_block_size = 8;
212 set innodb_strict_mode=on;
213 create procedure populate_t1()
214 begin
215 declare i int default 1;
216 while (i <= 200) do
217 insert into t1 values (i, 'a', 'b');
218 set i = i + 1;
219 end while;
220 end|
221 create procedure populate_t1_small()
222 begin
223 declare i int default 1;
224 while (i <= 20) do
225 insert into t1 values (i, 'c', 'd');
226 set i = i + 1;
227 end while;
228 end|
229 create procedure populate_t1_small2()
230 begin
231 declare i int default 30;
232 while (i <= 50) do
233 insert into t1 values (i, 'e', 'f');
234 set i = i + 1;
235 end while;
236 end|
237 begin;
238 select count(*) from t1;
239 count(*)
240 0
241 call populate_t1();
242 select count(*) from t1;
243 count(*)
244 200
245 select * from t1 limit 10;
246 keyc c1 c2
247 1 a b
248 2 a b
249 3 a b
250 4 a b
251 5 a b
252 6 a b
253 7 a b
254 8 a b
255 9 a b
256 10 a b
257 rollback;
258 select count(*) from t1;
259 count(*)
260 0
261 begin;
262 call populate_t1();
263 select count(*) from t1;
264 count(*)
265 200
266 commit;
267 select count(*) from t1;
268 count(*)
269 200
270 truncate table t1;
271 select count(*) from t1;
272 count(*)
273 0
274 call populate_t1_small();
275 select count(*) from t1;
276 count(*)
277 20
278 rollback;
279 select count(*) from t1;
280 count(*)
281 20
282 truncate table t1;
283 call populate_t1();
284 select count(*) from t1;
285 count(*)
286 200
287 delete from t1 where keyc <= 60;
288 select count(*) from t1;
289 count(*)
290 140
291 call populate_t1_small();
292 select count(*) from t1;
293 count(*)
294 160
295 select * from t1 limit 10;
296 keyc c1 c2
297 1 c d
298 2 c d
299 3 c d
300 4 c d
301 5 c d
302 6 c d
303 7 c d
304 8 c d
305 9 c d
306 10 c d
307 begin;
308 call populate_t1_small2();
309 select count(*) from t1;
310 count(*)
311 181
312 select * from t1 where keyc > 30 limit 10;
313 keyc c1 c2
314 31 e f
315 32 e f
316 33 e f
317 34 e f
318 35 e f
319 36 e f
320 37 e f
321 38 e f
322 39 e f
323 40 e f
324 rollback;
325 select count(*) from t1;
326 count(*)
327 160
328 select * from t1 where keyc > 30 limit 10;
329 keyc c1 c2
330 61 a b
331 62 a b
332 63 a b
333 64 a b
334 65 a b
335 66 a b
336 67 a b
337 68 a b
338 69 a b
339 70 a b
340 update t1 set keyc = keyc + 2000;
341 select * from t1 limit 10;
342 keyc c1 c2
343 2001 c d
344 2002 c d
345 2003 c d
346 2004 c d
347 2005 c d
348 2006 c d
349 2007 c d
350 2008 c d
351 2009 c d
352 2010 c d
353 rollback;
354 begin;
355 update t1 set keyc = keyc + 2000;
356 select * from t1 limit 10;
357 keyc c1 c2
358 4001 c d
359 4002 c d
360 4003 c d
361 4004 c d
362 4005 c d
363 4006 c d
364 4007 c d
365 4008 c d
366 4009 c d
367 4010 c d
368 rollback;
369 select * from t1 limit 10;
370 keyc c1 c2
371 2001 c d
372 2002 c d
373 2003 c d
374 2004 c d
375 2005 c d
376 2006 c d
377 2007 c d
378 2008 c d
379 2009 c d
380 2010 c d
381 commit;
382 select * from t1 limit 10;
383 keyc c1 c2
384 2001 c d
385 2002 c d
386 2003 c d
387 2004 c d
388 2005 c d
389 2006 c d
390 2007 c d
391 2008 c d
392 2009 c d
393 2010 c d
394 insert into t2 select * from t1 where keyc < 2101;
395 select count(*) from t2;
396 count(*)
397 60
398 drop procedure populate_t1;
399 drop procedure populate_t1_small;
400 drop procedure populate_t1_small2;
401 drop temporary table t1, t2;
402 create temporary table t1
403 (keyc int, c1 char(100), c2 char(100),
404 primary key(keyc), index sec_index(c1)
405 ) engine = innodb;
406 create temporary table t2
407 (keyc int, c1 char(100), c2 char(100),
408 primary key(keyc), index sec_index(c1)
409 ) engine = innodb;
410 create procedure populate_t1()
411 begin
412 declare i int default 1;
413 while (i <= 200) do
414 insert into t1 values (i, 'a', 'b');
415 set i = i + 1;
416 end while;
417 end|
418 create procedure populate_t1_small()
419 begin
420 declare i int default 1;
421 while (i <= 20) do
422 insert into t1 values (i, 'c', 'd');
423 set i = i + 1;
424 end while;
425 end|
426 create procedure populate_t1_small2()
427 begin
428 declare i int default 30;
429 while (i <= 50) do
430 insert into t1 values (i, 'e', 'f');
431 set i = i + 1;
432 end while;
433 end|
434 begin;
435 select count(*) from t1;
436 count(*)
437 0
438 call populate_t1();
439 select count(*) from t1;
440 count(*)
441 200
442 select * from t1 limit 10;
443 keyc c1 c2
444 1 a b
445 2 a b
446 3 a b
447 4 a b
448 5 a b
449 6 a b
450 7 a b
451 8 a b
452 9 a b
453 10 a b
454 rollback;
455 select count(*) from t1;
456 count(*)
457 0
458 begin;
459 call populate_t1();
460 select count(*) from t1;
461 count(*)
462 200
463 commit;
464 select count(*) from t1;
465 count(*)
466 200
467 truncate table t1;
468 select count(*) from t1;
469 count(*)
470 0
471 call populate_t1_small();
472 select count(*) from t1;
473 count(*)
474 20
475 rollback;
476 select count(*) from t1;
477 count(*)
478 20
479 truncate table t1;
480 call populate_t1();
481 select count(*) from t1;
482 count(*)
483 200
484 delete from t1 where keyc <= 60;
485 select count(*) from t1;
486 count(*)
487 140
488 call populate_t1_small();
489 select count(*) from t1;
490 count(*)
491 160
492 select * from t1 limit 10;
493 keyc c1 c2
494 1 c d
495 2 c d
496 3 c d
497 4 c d
498 5 c d
499 6 c d
500 7 c d
501 8 c d
502 9 c d
503 10 c d
504 begin;
505 call populate_t1_small2();
506 select count(*) from t1;
507 count(*)
508 181
509 select * from t1 where keyc > 30 limit 10;
510 keyc c1 c2
511 31 e f
512 32 e f
513 33 e f
514 34 e f
515 35 e f
516 36 e f
517 37 e f
518 38 e f
519 39 e f
520 40 e f
521 rollback;
522 select count(*) from t1;
523 count(*)
524 160
525 select * from t1 where keyc > 30 limit 10;
526 keyc c1 c2
527 61 a b
528 62 a b
529 63 a b
530 64 a b
531 65 a b
532 66 a b
533 67 a b
534 68 a b
535 69 a b
536 70 a b
537 update t1 set keyc = keyc + 2000;
538 select * from t1 limit 10;
539 keyc c1 c2
540 2001 c d
541 2002 c d
542 2003 c d
543 2004 c d
544 2005 c d
545 2006 c d
546 2007 c d
547 2008 c d
548 2009 c d
549 2010 c d
550 rollback;
551 begin;
552 update t1 set keyc = keyc + 2000;
553 select * from t1 limit 10;
554 keyc c1 c2
555 4001 c d
556 4002 c d
557 4003 c d
558 4004 c d
559 4005 c d
560 4006 c d
561 4007 c d
562 4008 c d
563 4009 c d
564 4010 c d
565 rollback;
566 select * from t1 limit 10;
567 keyc c1 c2
568 2001 c d
569 2002 c d
570 2003 c d
571 2004 c d
572 2005 c d
573 2006 c d
574 2007 c d
575 2008 c d
576 2009 c d
577 2010 c d
578 commit;
579 select * from t1 limit 10;
580 keyc c1 c2
581 2001 c d
582 2002 c d
583 2003 c d
584 2004 c d
585 2005 c d
586 2006 c d
587 2007 c d
588 2008 c d
589 2009 c d
590 2010 c d
591 insert into t2 select * from t1 where keyc < 2101;
592 select count(*) from t2;
593 count(*)
594 60
595 drop procedure populate_t1;
596 drop procedure populate_t1_small;
597 drop procedure populate_t1_small2;