Debian Codegraph @

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

raw
1 #
2 # WL#6742 - Test the interaction of multiple transactions using
3 # different isolation levels to make sure that the value returned
4 # by count(*) always reflects the correct view of the table according
5 # to the transaction's selected isolation level.
6 #
7 #
8 # Traverse various indexes to get the right counts.
9 # This especially tests count(*) which is pushed down to InnoDB in WL#6742.
10 #
11 CREATE TABLE t1 (
12 c1 INT AUTO_INCREMENT PRIMARY KEY,
13 c2 INT,
14 c3 INT,
15 c4 INT,
16 INDEX k2(c2)
17 ) Engine=InnoDB;
18 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
19 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
20 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
21 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
22 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
23 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
24 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
25 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
26 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
27 INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
28 CREATE TABLE t2 LIKE t1;
29 INSERT INTO t2 (SELECT * FROM t1);
30 affected rows: 10
31 info: Records: 10 Duplicates: 0 Warnings: 0
32 ########## innodb_isolation_selects.inc ##########
33 SELECT * FROM t1;
34 c1 c2 c3 c4
35 1 1 1 1
36 2 1 1 1
37 3 1 1 1
38 4 1 1 1
39 5 1 1 1
40 6 1 1 1
41 7 1 1 1
42 8 1 1 1
43 9 1 1 1
44 10 1 1 1
45 SELECT COUNT(*) FROM t1;
46 COUNT(*)
47 10
48 SELECT COUNT(c1) FROM t1;
49 COUNT(c1)
50 10
51 SELECT COUNT(c2) FROM t1;
52 COUNT(c2)
53 10
54 SELECT COUNT(c3) FROM t1;
55 COUNT(c3)
56 10
57 SELECT SUM(c1) FROM t1;
58 SUM(c1)
59 55
60 SELECT SUM(c2) FROM t1;
61 SUM(c2)
62 10
63 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
64 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
65 5.5000 10 1 1.0000 10
66 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
67 c1 c2
68 6 1
69 7 1
70 8 1
71 9 1
72 10 1
73 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
74 COUNT(c2)
75 5
76 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
77 COUNT(*)
78 5
79 ###############################################
80 #
81 # Do some DML in the default connection and leave the transaction pending.
82 #
83 SET innodb_snapshot_isolation=OFF;
84 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
85 BEGIN;
86 UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1;
87 affected rows: 1
88 info: Rows matched: 1 Changed: 1 Warnings: 0
89 DELETE FROM t1 WHERE c1 = 6;
90 affected rows: 1
91 ########## innodb_isolation_selects.inc ##########
92 SELECT * FROM t1;
93 c1 c2 c3 c4
94 1 3 1 1
95 2 1 1 1
96 3 1 1 1
97 4 1 1 1
98 5 1 1 1
99 7 1 1 1
100 8 1 1 1
101 9 1 1 1
102 10 1 1 1
103 SELECT COUNT(*) FROM t1;
104 COUNT(*)
105 9
106 SELECT COUNT(c1) FROM t1;
107 COUNT(c1)
108 9
109 SELECT COUNT(c2) FROM t1;
110 COUNT(c2)
111 9
112 SELECT COUNT(c3) FROM t1;
113 COUNT(c3)
114 9
115 SELECT SUM(c1) FROM t1;
116 SUM(c1)
117 49
118 SELECT SUM(c2) FROM t1;
119 SUM(c2)
120 11
121 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
122 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
123 5.4444 10 1 1.0000 9
124 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
125 c1 c2
126 5 1
127 7 1
128 8 1
129 9 1
130 10 1
131 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
132 COUNT(c2)
133 5
134 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
135 COUNT(*)
136 4
137 ###############################################
138 #
139 # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted
140 #
141 # Connection 1 REPEATABLE READ
142 #
143 connect con1,localhost,root,,;
144 SET innodb_snapshot_isolation=OFF;
145 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
146 BEGIN;
147 ########## innodb_isolation_selects.inc ##########
148 SELECT * FROM t1;
149 c1 c2 c3 c4
150 1 1 1 1
151 2 1 1 1
152 3 1 1 1
153 4 1 1 1
154 5 1 1 1
155 6 1 1 1
156 7 1 1 1
157 8 1 1 1
158 9 1 1 1
159 10 1 1 1
160 SELECT COUNT(*) FROM t1;
161 COUNT(*)
162 10
163 SELECT COUNT(c1) FROM t1;
164 COUNT(c1)
165 10
166 SELECT COUNT(c2) FROM t1;
167 COUNT(c2)
168 10
169 SELECT COUNT(c3) FROM t1;
170 COUNT(c3)
171 10
172 SELECT SUM(c1) FROM t1;
173 SUM(c1)
174 55
175 SELECT SUM(c2) FROM t1;
176 SUM(c2)
177 10
178 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
179 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
180 5.5000 10 1 1.0000 10
181 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
182 c1 c2
183 6 1
184 7 1
185 8 1
186 9 1
187 10 1
188 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
189 COUNT(c2)
190 5
191 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
192 COUNT(*)
193 5
194 ###############################################
195 UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2;
196 affected rows: 1
197 info: Rows matched: 1 Changed: 1 Warnings: 0
198 DELETE FROM t1 WHERE c1 = 7;
199 affected rows: 1
200 INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1);
201 affected rows: 1
202 ########## innodb_isolation_selects.inc ##########
203 SELECT * FROM t1;
204 c1 c2 c3 c4
205 1 1 1 1
206 2 5 1 1
207 3 1 1 1
208 4 1 1 1
209 5 1 1 1
210 6 1 1 1
211 8 1 1 1
212 9 1 1 1
213 10 1 1 1
214 11 100 1 1
215 SELECT COUNT(*) FROM t1;
216 COUNT(*)
217 10
218 SELECT COUNT(c1) FROM t1;
219 COUNT(c1)
220 10
221 SELECT COUNT(c2) FROM t1;
222 COUNT(c2)
223 10
224 SELECT COUNT(c3) FROM t1;
225 COUNT(c3)
226 10
227 SELECT SUM(c1) FROM t1;
228 SUM(c1)
229 59
230 SELECT SUM(c2) FROM t1;
231 SUM(c2)
232 113
233 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
234 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
235 5.9000 11 1 1.0000 10
236 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
237 c1 c2
238 6 1
239 8 1
240 9 1
241 10 1
242 11 100
243 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
244 COUNT(c2)
245 5
246 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
247 COUNT(*)
248 5
249 ###############################################
250 #
251 # Test a lock wait timeout during COUNT(*)
252 #
253 SET innodb_lock_wait_timeout = 1;
254 SELECT COUNT(*) FROM t1 FOR UPDATE;
255 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
256 #
257 # Connection 2 READ COMMITTED
258 #
259 connect con2,localhost,root,,;
260 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
261 BEGIN;
262 ########## innodb_isolation_selects.inc ##########
263 SELECT * FROM t1;
264 c1 c2 c3 c4
265 1 1 1 1
266 2 1 1 1
267 3 1 1 1
268 4 1 1 1
269 5 1 1 1
270 6 1 1 1
271 7 1 1 1
272 8 1 1 1
273 9 1 1 1
274 10 1 1 1
275 SELECT COUNT(*) FROM t1;
276 COUNT(*)
277 10
278 SELECT COUNT(c1) FROM t1;
279 COUNT(c1)
280 10
281 SELECT COUNT(c2) FROM t1;
282 COUNT(c2)
283 10
284 SELECT COUNT(c3) FROM t1;
285 COUNT(c3)
286 10
287 SELECT SUM(c1) FROM t1;
288 SUM(c1)
289 55
290 SELECT SUM(c2) FROM t1;
291 SUM(c2)
292 10
293 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
294 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
295 5.5000 10 1 1.0000 10
296 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
297 c1 c2
298 6 1
299 7 1
300 8 1
301 9 1
302 10 1
303 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
304 COUNT(c2)
305 5
306 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
307 COUNT(*)
308 5
309 ###############################################
310 UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3;
311 affected rows: 1
312 info: Rows matched: 1 Changed: 1 Warnings: 0
313 DELETE FROM t1 WHERE c1 = 8;
314 affected rows: 1
315 INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1);
316 affected rows: 1
317 ########## innodb_isolation_selects.inc ##########
318 SELECT * FROM t1;
319 c1 c2 c3 c4
320 1 1 1 1
321 2 1 1 1
322 3 7 1 1
323 4 1 1 1
324 5 1 1 1
325 6 1 1 1
326 7 1 1 1
327 9 1 1 1
328 10 1 1 1
329 12 1000 1 1
330 SELECT COUNT(*) FROM t1;
331 COUNT(*)
332 10
333 SELECT COUNT(c1) FROM t1;
334 COUNT(c1)
335 10
336 SELECT COUNT(c2) FROM t1;
337 COUNT(c2)
338 10
339 SELECT COUNT(c3) FROM t1;
340 COUNT(c3)
341 10
342 SELECT SUM(c1) FROM t1;
343 SUM(c1)
344 59
345 SELECT SUM(c2) FROM t1;
346 SUM(c2)
347 1015
348 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
349 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
350 5.9000 12 1 1.0000 10
351 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
352 c1 c2
353 6 1
354 7 1
355 9 1
356 10 1
357 12 1000
358 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
359 COUNT(c2)
360 5
361 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
362 COUNT(*)
363 5
364 ###############################################
365 #
366 # Connection 3 READ UNCOMMITTED
367 #
368 connect con3,localhost,root,,;
369 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
370 BEGIN;
371 ########## innodb_isolation_selects.inc ##########
372 SELECT * FROM t1;
373 c1 c2 c3 c4
374 1 3 1 1
375 2 5 1 1
376 3 7 1 1
377 4 1 1 1
378 5 1 1 1
379 9 1 1 1
380 10 1 1 1
381 11 100 1 1
382 12 1000 1 1
383 SELECT COUNT(*) FROM t1;
384 COUNT(*)
385 9
386 SELECT COUNT(c1) FROM t1;
387 COUNT(c1)
388 9
389 SELECT COUNT(c2) FROM t1;
390 COUNT(c2)
391 9
392 SELECT COUNT(c3) FROM t1;
393 COUNT(c3)
394 9
395 SELECT SUM(c1) FROM t1;
396 SUM(c1)
397 57
398 SELECT SUM(c2) FROM t1;
399 SUM(c2)
400 1119
401 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
402 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
403 6.3333 12 1 1.0000 9
404 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
405 c1 c2
406 5 1
407 9 1
408 10 1
409 11 100
410 12 1000
411 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
412 COUNT(c2)
413 5
414 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
415 COUNT(*)
416 4
417 ###############################################
418 UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4;
419 affected rows: 1
420 info: Rows matched: 1 Changed: 1 Warnings: 0
421 INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1);
422 affected rows: 1
423 DELETE FROM t1 WHERE c1 in(9);
424 affected rows: 1
425 ########## innodb_isolation_selects.inc ##########
426 SELECT * FROM t1;
427 c1 c2 c3 c4
428 1 3 1 1
429 2 5 1 1
430 3 7 1 1
431 4 11 1 1
432 5 1 1 1
433 10 1 1 1
434 11 100 1 1
435 12 1000 1 1
436 13 10000 1 1
437 SELECT COUNT(*) FROM t1;
438 COUNT(*)
439 9
440 SELECT COUNT(c1) FROM t1;
441 COUNT(c1)
442 9
443 SELECT COUNT(c2) FROM t1;
444 COUNT(c2)
445 9
446 SELECT COUNT(c3) FROM t1;
447 COUNT(c3)
448 9
449 SELECT SUM(c1) FROM t1;
450 SUM(c1)
451 61
452 SELECT SUM(c2) FROM t1;
453 SUM(c2)
454 11128
455 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
456 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
457 6.7778 13 1 1.0000 9
458 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
459 c1 c2
460 5 1
461 10 1
462 11 100
463 12 1000
464 13 10000
465 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
466 COUNT(c2)
467 5
468 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
469 COUNT(*)
470 4
471 ###############################################
472 #
473 # Connection default REPEATABLE READ
474 #
475 connection default;
476 ########## innodb_isolation_selects.inc ##########
477 SELECT * FROM t1;
478 c1 c2 c3 c4
479 1 3 1 1
480 2 1 1 1
481 3 1 1 1
482 4 1 1 1
483 5 1 1 1
484 7 1 1 1
485 8 1 1 1
486 9 1 1 1
487 10 1 1 1
488 SELECT COUNT(*) FROM t1;
489 COUNT(*)
490 9
491 SELECT COUNT(c1) FROM t1;
492 COUNT(c1)
493 9
494 SELECT COUNT(c2) FROM t1;
495 COUNT(c2)
496 9
497 SELECT COUNT(c3) FROM t1;
498 COUNT(c3)
499 9
500 SELECT SUM(c1) FROM t1;
501 SUM(c1)
502 49
503 SELECT SUM(c2) FROM t1;
504 SUM(c2)
505 11
506 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
507 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
508 5.4444 10 1 1.0000 9
509 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
510 c1 c2
511 5 1
512 7 1
513 8 1
514 9 1
515 10 1
516 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
517 COUNT(c2)
518 5
519 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
520 COUNT(*)
521 4
522 ###############################################
523 #
524 # Commit the 3 extra connections
525 #
526 # Connection 1 REPEATABLE READ
527 #
528 connection con1;
529 ########## innodb_isolation_selects.inc ##########
530 SELECT * FROM t1;
531 c1 c2 c3 c4
532 1 1 1 1
533 2 5 1 1
534 3 1 1 1
535 4 1 1 1
536 5 1 1 1
537 6 1 1 1
538 8 1 1 1
539 9 1 1 1
540 10 1 1 1
541 11 100 1 1
542 SELECT COUNT(*) FROM t1;
543 COUNT(*)
544 10
545 SELECT COUNT(c1) FROM t1;
546 COUNT(c1)
547 10
548 SELECT COUNT(c2) FROM t1;
549 COUNT(c2)
550 10
551 SELECT COUNT(c3) FROM t1;
552 COUNT(c3)
553 10
554 SELECT SUM(c1) FROM t1;
555 SUM(c1)
556 59
557 SELECT SUM(c2) FROM t1;
558 SUM(c2)
559 113
560 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
561 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
562 5.9000 11 1 1.0000 10
563 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
564 c1 c2
565 6 1
566 8 1
567 9 1
568 10 1
569 11 100
570 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
571 COUNT(c2)
572 5
573 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
574 COUNT(*)
575 5
576 ###############################################
577 #
578 # Connection 2 READ COMMITTED
579 #
580 connection con2;
581 ########## innodb_isolation_selects.inc ##########
582 SELECT * FROM t1;
583 c1 c2 c3 c4
584 1 1 1 1
585 2 1 1 1
586 3 7 1 1
587 4 1 1 1
588 5 1 1 1
589 6 1 1 1
590 7 1 1 1
591 9 1 1 1
592 10 1 1 1
593 12 1000 1 1
594 SELECT COUNT(*) FROM t1;
595 COUNT(*)
596 10
597 SELECT COUNT(c1) FROM t1;
598 COUNT(c1)
599 10
600 SELECT COUNT(c2) FROM t1;
601 COUNT(c2)
602 10
603 SELECT COUNT(c3) FROM t1;
604 COUNT(c3)
605 10
606 SELECT SUM(c1) FROM t1;
607 SUM(c1)
608 59
609 SELECT SUM(c2) FROM t1;
610 SUM(c2)
611 1015
612 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
613 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
614 5.9000 12 1 1.0000 10
615 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
616 c1 c2
617 6 1
618 7 1
619 9 1
620 10 1
621 12 1000
622 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
623 COUNT(c2)
624 5
625 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
626 COUNT(*)
627 5
628 ###############################################
629 #
630 # Connection 3 READ UNCOMMITTED
631 #
632 connection con3;
633 ########## innodb_isolation_selects.inc ##########
634 SELECT * FROM t1;
635 c1 c2 c3 c4
636 1 3 1 1
637 2 5 1 1
638 3 7 1 1
639 4 11 1 1
640 5 1 1 1
641 10 1 1 1
642 11 100 1 1
643 12 1000 1 1
644 13 10000 1 1
645 SELECT COUNT(*) FROM t1;
646 COUNT(*)
647 9
648 SELECT COUNT(c1) FROM t1;
649 COUNT(c1)
650 9
651 SELECT COUNT(c2) FROM t1;
652 COUNT(c2)
653 9
654 SELECT COUNT(c3) FROM t1;
655 COUNT(c3)
656 9
657 SELECT SUM(c1) FROM t1;
658 SUM(c1)
659 61
660 SELECT SUM(c2) FROM t1;
661 SUM(c2)
662 11128
663 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
664 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
665 6.7778 13 1 1.0000 9
666 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
667 c1 c2
668 5 1
669 10 1
670 11 100
671 12 1000
672 13 10000
673 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
674 COUNT(c2)
675 5
676 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
677 COUNT(*)
678 4
679 ###############################################
680 COMMIT;
681 ########## innodb_isolation_selects.inc ##########
682 SELECT * FROM t1;
683 c1 c2 c3 c4
684 1 3 1 1
685 2 5 1 1
686 3 7 1 1
687 4 11 1 1
688 5 1 1 1
689 10 1 1 1
690 11 100 1 1
691 12 1000 1 1
692 13 10000 1 1
693 SELECT COUNT(*) FROM t1;
694 COUNT(*)
695 9
696 SELECT COUNT(c1) FROM t1;
697 COUNT(c1)
698 9
699 SELECT COUNT(c2) FROM t1;
700 COUNT(c2)
701 9
702 SELECT COUNT(c3) FROM t1;
703 COUNT(c3)
704 9
705 SELECT SUM(c1) FROM t1;
706 SUM(c1)
707 61
708 SELECT SUM(c2) FROM t1;
709 SUM(c2)
710 11128
711 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
712 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
713 6.7778 13 1 1.0000 9
714 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
715 c1 c2
716 5 1
717 10 1
718 11 100
719 12 1000
720 13 10000
721 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
722 COUNT(c2)
723 5
724 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
725 COUNT(*)
726 4
727 ###############################################
728 #
729 # Connection 2 READ COMMITTED
730 #
731 connection con2;
732 ########## innodb_isolation_selects.inc ##########
733 SELECT * FROM t1;
734 c1 c2 c3 c4
735 1 1 1 1
736 2 1 1 1
737 3 7 1 1
738 4 11 1 1
739 5 1 1 1
740 6 1 1 1
741 7 1 1 1
742 10 1 1 1
743 12 1000 1 1
744 13 10000 1 1
745 SELECT COUNT(*) FROM t1;
746 COUNT(*)
747 10
748 SELECT COUNT(c1) FROM t1;
749 COUNT(c1)
750 10
751 SELECT COUNT(c2) FROM t1;
752 COUNT(c2)
753 10
754 SELECT COUNT(c3) FROM t1;
755 COUNT(c3)
756 10
757 SELECT SUM(c1) FROM t1;
758 SUM(c1)
759 63
760 SELECT SUM(c2) FROM t1;
761 SUM(c2)
762 11024
763 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
764 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
765 6.3000 13 1 1.0000 10
766 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
767 c1 c2
768 6 1
769 7 1
770 10 1
771 12 1000
772 13 10000
773 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
774 COUNT(c2)
775 5
776 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
777 COUNT(*)
778 4
779 ###############################################
780 COMMIT;
781 ########## innodb_isolation_selects.inc ##########
782 SELECT * FROM t1;
783 c1 c2 c3 c4
784 1 1 1 1
785 2 1 1 1
786 3 7 1 1
787 4 11 1 1
788 5 1 1 1
789 6 1 1 1
790 7 1 1 1
791 10 1 1 1
792 12 1000 1 1
793 13 10000 1 1
794 SELECT COUNT(*) FROM t1;
795 COUNT(*)
796 10
797 SELECT COUNT(c1) FROM t1;
798 COUNT(c1)
799 10
800 SELECT COUNT(c2) FROM t1;
801 COUNT(c2)
802 10
803 SELECT COUNT(c3) FROM t1;
804 COUNT(c3)
805 10
806 SELECT SUM(c1) FROM t1;
807 SUM(c1)
808 63
809 SELECT SUM(c2) FROM t1;
810 SUM(c2)
811 11024
812 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
813 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
814 6.3000 13 1 1.0000 10
815 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
816 c1 c2
817 6 1
818 7 1
819 10 1
820 12 1000
821 13 10000
822 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
823 COUNT(c2)
824 5
825 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
826 COUNT(*)
827 4
828 ###############################################
829 #
830 # Connection 1 REPEATABLE READ
831 #
832 connection con1;
833 ########## innodb_isolation_selects.inc ##########
834 SELECT * FROM t1;
835 c1 c2 c3 c4
836 1 1 1 1
837 2 5 1 1
838 3 1 1 1
839 4 1 1 1
840 5 1 1 1
841 6 1 1 1
842 8 1 1 1
843 9 1 1 1
844 10 1 1 1
845 11 100 1 1
846 SELECT COUNT(*) FROM t1;
847 COUNT(*)
848 10
849 SELECT COUNT(c1) FROM t1;
850 COUNT(c1)
851 10
852 SELECT COUNT(c2) FROM t1;
853 COUNT(c2)
854 10
855 SELECT COUNT(c3) FROM t1;
856 COUNT(c3)
857 10
858 SELECT SUM(c1) FROM t1;
859 SUM(c1)
860 59
861 SELECT SUM(c2) FROM t1;
862 SUM(c2)
863 113
864 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
865 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
866 5.9000 11 1 1.0000 10
867 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
868 c1 c2
869 6 1
870 8 1
871 9 1
872 10 1
873 11 100
874 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
875 COUNT(c2)
876 5
877 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
878 COUNT(*)
879 5
880 ###############################################
881 #
882 # Select the first 5 records FOR UPDATE using count(*) in a subquery.
883 # The second record is still pending so we get a lock timeout.
884 #
885 SET innodb_lock_wait_timeout = 1;
886 SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE;
887 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
888 SELECT COUNT(*) FROM t1 FOR UPDATE;
889 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
890 COMMIT;
891 ########## innodb_isolation_selects.inc ##########
892 SELECT * FROM t1;
893 c1 c2 c3 c4
894 1 1 1 1
895 2 5 1 1
896 3 7 1 1
897 4 11 1 1
898 5 1 1 1
899 6 1 1 1
900 10 1 1 1
901 11 100 1 1
902 12 1000 1 1
903 13 10000 1 1
904 SELECT COUNT(*) FROM t1;
905 COUNT(*)
906 10
907 SELECT COUNT(c1) FROM t1;
908 COUNT(c1)
909 10
910 SELECT COUNT(c2) FROM t1;
911 COUNT(c2)
912 10
913 SELECT COUNT(c3) FROM t1;
914 COUNT(c3)
915 10
916 SELECT SUM(c1) FROM t1;
917 SUM(c1)
918 67
919 SELECT SUM(c2) FROM t1;
920 SUM(c2)
921 11127
922 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
923 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
924 6.7000 13 1 1.0000 10
925 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
926 c1 c2
927 6 1
928 10 1
929 11 100
930 12 1000
931 13 10000
932 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
933 COUNT(c2)
934 5
935 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
936 COUNT(*)
937 4
938 ###############################################
939 #
940 # Show The EXPLAIN output for these queries;
941 #
942 EXPLAIN SELECT * FROM t1;
943 id select_type table type possible_keys key key_len ref rows Extra
944 1 SIMPLE t1 ALL NULL NULL NULL NULL #
945 EXPLAIN SELECT COUNT(*) FROM t1;
946 id select_type table type possible_keys key key_len ref rows Extra
947 1 SIMPLE t1 index NULL k2 5 NULL # Using index
948 EXPLAIN SELECT COUNT(c1) FROM t1;
949 id select_type table type possible_keys key key_len ref rows Extra
950 1 SIMPLE t1 index NULL k2 5 NULL # Using index
951 EXPLAIN SELECT COUNT(c2) FROM t1;
952 id select_type table type possible_keys key key_len ref rows Extra
953 1 SIMPLE t1 index NULL k2 5 NULL # Using index
954 EXPLAIN SELECT COUNT(c3) FROM t1;
955 id select_type table type possible_keys key key_len ref rows Extra
956 1 SIMPLE t1 ALL NULL NULL NULL NULL #
957 EXPLAIN SELECT SUM(c1) FROM t1;
958 id select_type table type possible_keys key key_len ref rows Extra
959 1 SIMPLE t1 index NULL k2 5 NULL # Using index
960 EXPLAIN SELECT SUM(c2) FROM t1;
961 id select_type table type possible_keys key key_len ref rows Extra
962 1 SIMPLE t1 index NULL k2 5 NULL # Using index
963 EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
964 id select_type table type possible_keys key key_len ref rows Extra
965 1 SIMPLE t1 ALL NULL NULL NULL NULL #
966 EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
967 id select_type table type possible_keys key key_len ref rows Extra
968 1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where
969 2 SUBQUERY t1 index NULL k2 5 NULL # Using index
970 EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
971 id select_type table type possible_keys key key_len ref rows Extra
972 1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where
973 2 SUBQUERY t1 index NULL k2 5 NULL # Using index
974 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
975 id select_type table type possible_keys key key_len ref rows Extra
976 1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where
977 2 SUBQUERY t1 index NULL k2 5 NULL # Using index
978 #
979 # Make all indexes in t2 obsolete to the active repeatable read transaction
980 # in the default connection.
981 #
982 ALTER TABLE t2 row_format=redundant;
983 #
984 # Connection default REPEATABLE READ
985 # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records.
986 #
987 connection default;
988 ########## innodb_isolation_selects.inc ##########
989 SELECT * FROM t1;
990 c1 c2 c3 c4
991 1 3 1 1
992 2 1 1 1
993 3 1 1 1
994 4 1 1 1
995 5 1 1 1
996 7 1 1 1
997 8 1 1 1
998 9 1 1 1
999 10 1 1 1
1000 SELECT COUNT(*) FROM t1;
1001 COUNT(*)
1002 9
1003 SELECT COUNT(c1) FROM t1;
1004 COUNT(c1)
1005 9
1006 SELECT COUNT(c2) FROM t1;
1007 COUNT(c2)
1008 9
1009 SELECT COUNT(c3) FROM t1;
1010 COUNT(c3)
1011 9
1012 SELECT SUM(c1) FROM t1;
1013 SUM(c1)
1014 49
1015 SELECT SUM(c2) FROM t1;
1016 SUM(c2)
1017 11
1018 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1019 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1020 5.4444 10 1 1.0000 9
1021 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1022 c1 c2
1023 5 1
1024 7 1
1025 8 1
1026 9 1
1027 10 1
1028 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1029 COUNT(c2)
1030 5
1031 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1032 COUNT(*)
1033 4
1034 ###############################################
1035 UPDATE t1 SET c4 = c2 * 10;
1036 affected rows: 9
1037 info: Rows matched: 9 Changed: 9 Warnings: 0
1038 ########## innodb_isolation_selects.inc ##########
1039 SELECT * FROM t1;
1040 c1 c2 c3 c4
1041 1 3 1 30
1042 2 5 1 50
1043 3 7 1 70
1044 4 11 1 110
1045 5 1 1 10
1046 7 1 1 1
1047 8 1 1 1
1048 9 1 1 1
1049 10 1 1 10
1050 11 100 1 1000
1051 12 1000 1 10000
1052 13 10000 1 100000
1053 SELECT COUNT(*) FROM t1;
1054 COUNT(*)
1055 12
1056 SELECT COUNT(c1) FROM t1;
1057 COUNT(c1)
1058 12
1059 SELECT COUNT(c2) FROM t1;
1060 COUNT(c2)
1061 12
1062 SELECT COUNT(c3) FROM t1;
1063 COUNT(c3)
1064 12
1065 SELECT SUM(c1) FROM t1;
1066 SUM(c1)
1067 85
1068 SELECT SUM(c2) FROM t1;
1069 SUM(c2)
1070 11131
1071 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1072 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1073 7.0833 13 1 1.0000 111283
1074 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1075 c1 c2
1076 7 1
1077 8 1
1078 9 1
1079 10 1
1080 11 100
1081 12 1000
1082 13 10000
1083 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1084 COUNT(c2)
1085 7
1086 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1087 COUNT(*)
1088 6
1089 ###############################################
1090 #
1091 # Table t2 has been altered to a new row format.
1092 # The index should not be useable.
1093 #
1094 SELECT COUNT(*) FROM t2;
1095 ERROR HY000: Table definition has changed, please retry transaction
1096 SELECT * FROM t2;
1097 ERROR HY000: Table definition has changed, please retry transaction
1098 COMMIT;
1099 SELECT COUNT(*) FROM t2;
1100 COUNT(*)
1101 10
1102 ########## innodb_isolation_selects.inc ##########
1103 SELECT * FROM t1;
1104 c1 c2 c3 c4
1105 1 3 1 30
1106 2 5 1 50
1107 3 7 1 70
1108 4 11 1 110
1109 5 1 1 10
1110 10 1 1 10
1111 11 100 1 1000
1112 12 1000 1 10000
1113 13 10000 1 100000
1114 SELECT COUNT(*) FROM t1;
1115 COUNT(*)
1116 9
1117 SELECT COUNT(c1) FROM t1;
1118 COUNT(c1)
1119 9
1120 SELECT COUNT(c2) FROM t1;
1121 COUNT(c2)
1122 9
1123 SELECT COUNT(c3) FROM t1;
1124 COUNT(c3)
1125 9
1126 SELECT SUM(c1) FROM t1;
1127 SUM(c1)
1128 61
1129 SELECT SUM(c2) FROM t1;
1130 SUM(c2)
1131 11128
1132 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1133 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1134 6.7778 13 1 1.0000 111280
1135 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1136 c1 c2
1137 5 1
1138 10 1
1139 11 100
1140 12 1000
1141 13 10000
1142 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1143 COUNT(c2)
1144 5
1145 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1146 COUNT(*)
1147 4
1148 ###############################################
1149 #
1150 # Connection 2
1151 #
1152 connection con2;
1153 ########## innodb_isolation_selects.inc ##########
1154 SELECT * FROM t1;
1155 c1 c2 c3 c4
1156 1 3 1 30
1157 2 5 1 50
1158 3 7 1 70
1159 4 11 1 110
1160 5 1 1 10
1161 10 1 1 10
1162 11 100 1 1000
1163 12 1000 1 10000
1164 13 10000 1 100000
1165 SELECT COUNT(*) FROM t1;
1166 COUNT(*)
1167 9
1168 SELECT COUNT(c1) FROM t1;
1169 COUNT(c1)
1170 9
1171 SELECT COUNT(c2) FROM t1;
1172 COUNT(c2)
1173 9
1174 SELECT COUNT(c3) FROM t1;
1175 COUNT(c3)
1176 9
1177 SELECT SUM(c1) FROM t1;
1178 SUM(c1)
1179 61
1180 SELECT SUM(c2) FROM t1;
1181 SUM(c2)
1182 11128
1183 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1184 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1185 6.7778 13 1 1.0000 111280
1186 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1187 c1 c2
1188 5 1
1189 10 1
1190 11 100
1191 12 1000
1192 13 10000
1193 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1194 COUNT(c2)
1195 5
1196 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1197 COUNT(*)
1198 4
1199 ###############################################
1200 #
1201 # Try COUNT(*) on a DISCARDED table.
1202 #
1203 connection default;
1204 CREATE TABLE t4 LIKE t1;
1205 INSERT INTO t4 (SELECT * FROM t1);
1206 SELECT COUNT(*) FROM t4;
1207 COUNT(*)
1208 9
1209 ALTER TABLE t4 DISCARD TABLESPACE;
1210 SELECT COUNT(*) FROM t4;
1211 ERROR HY000: Tablespace has been discarded for table `t4`
1212 #
1213 # Test the interaction of a repeatable read transaction
1214 # to changes that happen outside its view.
1215 #
1216 CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1217 INSERT INTO t5(b) VALUES ("inserted by client 1");
1218 INSERT INTO t5(b) VALUES ("inserted by client 1");
1219 INSERT INTO t5(b) VALUES ("inserted by client 1");
1220 INSERT INTO t5(b) VALUES ("inserted by client 1");
1221 UPDATE t5 SET aa=a, bb=b;
1222 CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1223 INSERT INTO t6(b) VALUES ("inserted by client 1");
1224 INSERT INTO t6(b) VALUES ("inserted by client 1");
1225 INSERT INTO t6(b) VALUES ("inserted by client 1");
1226 INSERT INTO t6(b) VALUES ("inserted by client 1");
1227 UPDATE t6 SET aa=a, bb=b;
1228 CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1229 INSERT INTO t7(b) VALUES ("inserted by client 1");
1230 INSERT INTO t7(b) VALUES ("inserted by client 1");
1231 INSERT INTO t7(b) VALUES ("inserted by client 1");
1232 INSERT INTO t7(b) VALUES ("inserted by client 1");
1233 UPDATE t7 SET aa=a, bb=b;
1234 BEGIN;
1235 SELECT * FROM t5;
1236 a b aa bb
1237 1 inserted by client 1 1 inserted by client 1
1238 2 inserted by client 1 2 inserted by client 1
1239 3 inserted by client 1 3 inserted by client 1
1240 4 inserted by client 1 4 inserted by client 1
1241 SELECT COUNT(*) FROM t5;
1242 COUNT(*)
1243 4
1244 SELECT * FROM t6;
1245 a b aa bb
1246 1 inserted by client 1 1 inserted by client 1
1247 2 inserted by client 1 2 inserted by client 1
1248 3 inserted by client 1 3 inserted by client 1
1249 4 inserted by client 1 4 inserted by client 1
1250 SELECT COUNT(*) FROM t6;
1251 COUNT(*)
1252 4
1253 SELECT * FROM t7;
1254 a b aa bb
1255 1 inserted by client 1 1 inserted by client 1
1256 2 inserted by client 1 2 inserted by client 1
1257 3 inserted by client 1 3 inserted by client 1
1258 4 inserted by client 1 4 inserted by client 1
1259 SELECT COUNT(*) FROM t7;
1260 COUNT(*)
1261 4
1262 #
1263 # Connection 1
1264 #
1265 connection con1;
1266 INSERT INTO t5(b) VALUES ("inserted by client 2");
1267 UPDATE t5 SET a = 10 where a = 1;
1268 UPDATE t5 SET b = "updated by client 2" where a = 2;
1269 DELETE FROM t5 WHERE a = 3;
1270 SELECT * FROM t5;
1271 a b aa bb
1272 2 updated by client 2 2 inserted by client 1
1273 4 inserted by client 1 4 inserted by client 1
1274 5 inserted by client 2 NULL NULL
1275 10 inserted by client 1 1 inserted by client 1
1276 SELECT COUNT(*) FROM t5;
1277 COUNT(*)
1278 4
1279 INSERT INTO t6(b) VALUES ("inserted by client 2");
1280 UPDATE t6 SET a = 10 where a = 1;
1281 UPDATE t6 SET b = "updated by client 2" where a = 2;
1282 DELETE FROM t6 WHERE a = 3;
1283 SELECT * FROM t6;
1284 a b aa bb
1285 2 updated by client 2 2 inserted by client 1
1286 4 inserted by client 1 4 inserted by client 1
1287 5 inserted by client 2 NULL NULL
1288 10 inserted by client 1 1 inserted by client 1
1289 SELECT COUNT(*) FROM t6;
1290 COUNT(*)
1291 4
1292 INSERT INTO t7(b) VALUES ("inserted by client 2");
1293 UPDATE t7 SET a = 10 where a = 1;
1294 UPDATE t7 SET b = "updated by client 2" where a = 2;
1295 DELETE FROM t7 WHERE a = 3;
1296 SELECT * FROM t7;
1297 a b aa bb
1298 2 updated by client 2 2 inserted by client 1
1299 4 inserted by client 1 4 inserted by client 1
1300 5 inserted by client 2 NULL NULL
1301 10 inserted by client 1 1 inserted by client 1
1302 SELECT COUNT(*) FROM t7;
1303 COUNT(*)
1304 4
1305 #
1306 # Connection default
1307 #
1308 connection default;
1309 SELECT * FROM t5;
1310 a b aa bb
1311 1 inserted by client 1 1 inserted by client 1
1312 2 inserted by client 1 2 inserted by client 1
1313 3 inserted by client 1 3 inserted by client 1
1314 4 inserted by client 1 4 inserted by client 1
1315 INSERT INTO t5(b) VALUES ("inserted by client 1");
1316 SELECT * FROM t5;
1317 a b aa bb
1318 1 inserted by client 1 1 inserted by client 1
1319 2 inserted by client 1 2 inserted by client 1
1320 3 inserted by client 1 3 inserted by client 1
1321 4 inserted by client 1 4 inserted by client 1
1322 11 inserted by client 1 NULL NULL
1323 UPDATE t5 SET a = a + 100;
1324 SELECT * FROM t5;
1325 a b aa bb
1326 1 inserted by client 1 1 inserted by client 1
1327 3 inserted by client 1 3 inserted by client 1
1328 102 updated by client 2 2 inserted by client 1
1329 104 inserted by client 1 4 inserted by client 1
1330 105 inserted by client 2 NULL NULL
1331 110 inserted by client 1 1 inserted by client 1
1332 111 inserted by client 1 NULL NULL
1333 SELECT COUNT(*) FROM t5;
1334 COUNT(*)
1335 7
1336 UPDATE t6 SET b = "updated by client 2";
1337 SELECT * FROM t6;
1338 a b aa bb
1339 1 inserted by client 1 1 inserted by client 1
1340 2 inserted by client 1 2 inserted by client 1
1341 3 inserted by client 1 3 inserted by client 1
1342 4 updated by client 2 4 inserted by client 1
1343 5 updated by client 2 NULL NULL
1344 10 updated by client 2 1 inserted by client 1
1345 SELECT * FROM t6 LOCK IN SHARE MODE;
1346 a b aa bb
1347 2 updated by client 2 2 inserted by client 1
1348 4 updated by client 2 4 inserted by client 1
1349 5 updated by client 2 NULL NULL
1350 10 updated by client 2 1 inserted by client 1
1351 SELECT COUNT(*) FROM t6;
1352 COUNT(*)
1353 6
1354 DELETE FROM t7;
1355 SELECT * FROM t7;
1356 a b aa bb
1357 1 inserted by client 1 1 inserted by client 1
1358 3 inserted by client 1 3 inserted by client 1
1359 SELECT COUNT(*) FROM t7;
1360 COUNT(*)
1361 2
1362 COMMIT;
1363 SELECT * FROM t5;
1364 a b aa bb
1365 102 updated by client 2 2 inserted by client 1
1366 104 inserted by client 1 4 inserted by client 1
1367 105 inserted by client 2 NULL NULL
1368 110 inserted by client 1 1 inserted by client 1
1369 111 inserted by client 1 NULL NULL
1370 SELECT COUNT(*) FROM t5;
1371 COUNT(*)
1372 5
1373 SELECT * FROM t6;
1374 a b aa bb
1375 2 updated by client 2 2 inserted by client 1
1376 4 updated by client 2 4 inserted by client 1
1377 5 updated by client 2 NULL NULL
1378 10 updated by client 2 1 inserted by client 1
1379 SELECT COUNT(*) FROM t6;
1380 COUNT(*)
1381 4
1382 SELECT * FROM t7;
1383 a b aa bb
1384 SELECT COUNT(*) FROM t7;
1385 COUNT(*)
1386 0
1387 #
1388 # Cleanup
1389 #
1390 DROP TABLE t1,t2,t4,t5,t6,t7;
1391 disconnect con1;
1392 disconnect con2;
1393 disconnect con3;
1394 #
1395 # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*)
1396 #
1397 CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY,
1398 c2 INT NOT NULL DEFAULT 1,
1399 c3 char(20) DEFAULT '',
1400 KEY c2_idx (c2)) ENGINE=InnoDB STATS_PERSISTENT=0;
1401 INSERT INTO t1(c1) VALUES (1), (2), (3);
1402 INSERT INTO t1(c1) SELECT c1 + 10 FROM t1;
1403 INSERT INTO t1(c1) SELECT c1 + 100 FROM t1;
1404 CREATE TABLE t2 STATS_PERSISTENT=0 SELECT * FROM t1;
1405 EXPLAIN SELECT COUNT(*) FROM t1;
1406 id select_type table type possible_keys key key_len ref rows Extra
1407 1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index
1408 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx);
1409 id select_type table type possible_keys key key_len ref rows Extra
1410 1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index
1411 EXPLAIN SELECT COUNT(*) FROM t1, t2;
1412 id select_type table type possible_keys key key_len ref rows Extra
1413 1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index
1414 1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join)
1415 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2;
1416 id select_type table type possible_keys key key_len ref rows Extra
1417 1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index
1418 1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join)
1419 DROP TABLE t1, t2;