Debian Codegraph @

mysql-test/suite/innodb/r/select_count_perf.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 CREATE TABLE t3 (
33 c1 INT AUTO_INCREMENT PRIMARY KEY,
34 c2 INT,
35 c3 INT,
36 c4 INT,
37 INDEX k2(c2)
38 ) Engine=InnoDB PARTITION BY HASH(c1) PARTITIONS 4;
39 INSERT INTO t3 (SELECT * FROM t1);
40 affected rows: 10
41 info: Records: 10 Duplicates: 0 Warnings: 0
42 ########## innodb_wl6742_selects.inc ##########
43 SELECT * FROM t1;
44 c1 c2 c3 c4
45 1 1 1 1
46 2 1 1 1
47 3 1 1 1
48 4 1 1 1
49 5 1 1 1
50 6 1 1 1
51 7 1 1 1
52 8 1 1 1
53 9 1 1 1
54 10 1 1 1
55 SELECT COUNT(*) FROM t1;
56 COUNT(*)
57 10
58 SELECT COUNT(*) FROM t3;
59 COUNT(*)
60 10
61 SELECT COUNT(c1) FROM t1;
62 COUNT(c1)
63 10
64 SELECT COUNT(c2) FROM t1;
65 COUNT(c2)
66 10
67 SELECT COUNT(c3) FROM t1;
68 COUNT(c3)
69 10
70 SELECT SUM(c1) FROM t1;
71 SUM(c1)
72 55
73 SELECT SUM(c2) FROM t1;
74 SUM(c2)
75 10
76 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
77 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
78 5.5000 10 1 1.0000 10
79 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
80 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
81 5.5000 10 1 1.0000 10
82 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
83 c1 c2
84 6 1
85 7 1
86 8 1
87 9 1
88 10 1
89 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
90 c1 c2
91 6 1
92 7 1
93 8 1
94 9 1
95 10 1
96 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
97 COUNT(c2)
98 5
99 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
100 COUNT(c2)
101 5
102 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
103 COUNT(*)
104 5
105 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
106 COUNT(*)
107 5
108 ###############################################
109 #
110 # Do some DML in the default connection and leave the transaction pending.
111 #
112 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
113 BEGIN;
114 UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1;
115 affected rows: 1
116 info: Rows matched: 1 Changed: 1 Warnings: 0
117 UPDATE t3 SET c2 = c2 * 3 WHERE c1 = 1;
118 affected rows: 1
119 info: Rows matched: 1 Changed: 1 Warnings: 0
120 DELETE FROM t1 WHERE c1 = 6;
121 affected rows: 1
122 DELETE FROM t3 WHERE c1 = 6;
123 affected rows: 1
124 ########## innodb_wl6742_selects.inc ##########
125 SELECT * FROM t1;
126 c1 c2 c3 c4
127 1 3 1 1
128 2 1 1 1
129 3 1 1 1
130 4 1 1 1
131 5 1 1 1
132 7 1 1 1
133 8 1 1 1
134 9 1 1 1
135 10 1 1 1
136 SELECT COUNT(*) FROM t1;
137 COUNT(*)
138 9
139 SELECT COUNT(*) FROM t3;
140 COUNT(*)
141 9
142 SELECT COUNT(c1) FROM t1;
143 COUNT(c1)
144 9
145 SELECT COUNT(c2) FROM t1;
146 COUNT(c2)
147 9
148 SELECT COUNT(c3) FROM t1;
149 COUNT(c3)
150 9
151 SELECT SUM(c1) FROM t1;
152 SUM(c1)
153 49
154 SELECT SUM(c2) FROM t1;
155 SUM(c2)
156 11
157 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
158 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
159 5.4444 10 1 1.0000 9
160 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
161 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
162 5.4444 10 1 1.0000 9
163 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
164 c1 c2
165 5 1
166 7 1
167 8 1
168 9 1
169 10 1
170 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
171 c1 c2
172 5 1
173 7 1
174 8 1
175 9 1
176 10 1
177 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
178 COUNT(c2)
179 5
180 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
181 COUNT(c2)
182 5
183 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
184 COUNT(*)
185 4
186 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
187 COUNT(*)
188 4
189 ###############################################
190 #
191 # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted
192 #
193 # Connection 1 REPEATABLE READ
194 #
195 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
196 BEGIN;
197 ########## innodb_wl6742_selects.inc ##########
198 SELECT * FROM t1;
199 c1 c2 c3 c4
200 1 1 1 1
201 2 1 1 1
202 3 1 1 1
203 4 1 1 1
204 5 1 1 1
205 6 1 1 1
206 7 1 1 1
207 8 1 1 1
208 9 1 1 1
209 10 1 1 1
210 SELECT COUNT(*) FROM t1;
211 COUNT(*)
212 10
213 SELECT COUNT(*) FROM t3;
214 COUNT(*)
215 10
216 SELECT COUNT(c1) FROM t1;
217 COUNT(c1)
218 10
219 SELECT COUNT(c2) FROM t1;
220 COUNT(c2)
221 10
222 SELECT COUNT(c3) FROM t1;
223 COUNT(c3)
224 10
225 SELECT SUM(c1) FROM t1;
226 SUM(c1)
227 55
228 SELECT SUM(c2) FROM t1;
229 SUM(c2)
230 10
231 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
232 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
233 5.5000 10 1 1.0000 10
234 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
235 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
236 5.5000 10 1 1.0000 10
237 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
238 c1 c2
239 6 1
240 7 1
241 8 1
242 9 1
243 10 1
244 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
245 c1 c2
246 6 1
247 7 1
248 8 1
249 9 1
250 10 1
251 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
252 COUNT(c2)
253 5
254 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
255 COUNT(c2)
256 5
257 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
258 COUNT(*)
259 5
260 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
261 COUNT(*)
262 5
263 ###############################################
264 UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2;
265 affected rows: 1
266 info: Rows matched: 1 Changed: 1 Warnings: 0
267 UPDATE t3 SET c2 = c2 * 5 WHERE c1 = 2;
268 affected rows: 1
269 info: Rows matched: 1 Changed: 1 Warnings: 0
270 DELETE FROM t1 WHERE c1 = 7;
271 affected rows: 1
272 DELETE FROM t3 WHERE c1 = 7;
273 affected rows: 1
274 INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1);
275 affected rows: 1
276 INSERT INTO t3(c2,c3,c4) VALUES (100, 1, 1);
277 affected rows: 1
278 ########## innodb_wl6742_selects.inc ##########
279 SELECT * FROM t1;
280 c1 c2 c3 c4
281 1 1 1 1
282 2 5 1 1
283 3 1 1 1
284 4 1 1 1
285 5 1 1 1
286 6 1 1 1
287 8 1 1 1
288 9 1 1 1
289 10 1 1 1
290 11 100 1 1
291 SELECT COUNT(*) FROM t1;
292 COUNT(*)
293 10
294 SELECT COUNT(*) FROM t3;
295 COUNT(*)
296 10
297 SELECT COUNT(c1) FROM t1;
298 COUNT(c1)
299 10
300 SELECT COUNT(c2) FROM t1;
301 COUNT(c2)
302 10
303 SELECT COUNT(c3) FROM t1;
304 COUNT(c3)
305 10
306 SELECT SUM(c1) FROM t1;
307 SUM(c1)
308 59
309 SELECT SUM(c2) FROM t1;
310 SUM(c2)
311 113
312 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
313 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
314 5.9000 11 1 1.0000 10
315 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
316 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
317 5.9000 11 1 1.0000 10
318 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
319 c1 c2
320 6 1
321 8 1
322 9 1
323 10 1
324 11 100
325 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
326 c1 c2
327 6 1
328 8 1
329 9 1
330 10 1
331 11 100
332 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
333 COUNT(c2)
334 5
335 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
336 COUNT(c2)
337 5
338 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
339 COUNT(*)
340 5
341 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
342 COUNT(*)
343 5
344 ###############################################
345 #
346 # Test a lock wait timeout during COUNT(*)
347 #
348 SET innodb_lock_wait_timeout = 1;
349 SELECT COUNT(*) FROM t1 FOR UPDATE;
350 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
351 #
352 # Connection 2 READ COMMITTED
353 #
354 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
355 BEGIN;
356 ########## innodb_wl6742_selects.inc ##########
357 SELECT * FROM t1;
358 c1 c2 c3 c4
359 1 1 1 1
360 2 1 1 1
361 3 1 1 1
362 4 1 1 1
363 5 1 1 1
364 6 1 1 1
365 7 1 1 1
366 8 1 1 1
367 9 1 1 1
368 10 1 1 1
369 SELECT COUNT(*) FROM t1;
370 COUNT(*)
371 10
372 SELECT COUNT(*) FROM t3;
373 COUNT(*)
374 10
375 SELECT COUNT(c1) FROM t1;
376 COUNT(c1)
377 10
378 SELECT COUNT(c2) FROM t1;
379 COUNT(c2)
380 10
381 SELECT COUNT(c3) FROM t1;
382 COUNT(c3)
383 10
384 SELECT SUM(c1) FROM t1;
385 SUM(c1)
386 55
387 SELECT SUM(c2) FROM t1;
388 SUM(c2)
389 10
390 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
391 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
392 5.5000 10 1 1.0000 10
393 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
394 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
395 5.5000 10 1 1.0000 10
396 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
397 c1 c2
398 6 1
399 7 1
400 8 1
401 9 1
402 10 1
403 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
404 c1 c2
405 6 1
406 7 1
407 8 1
408 9 1
409 10 1
410 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
411 COUNT(c2)
412 5
413 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
414 COUNT(c2)
415 5
416 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
417 COUNT(*)
418 5
419 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
420 COUNT(*)
421 5
422 ###############################################
423 UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3;
424 affected rows: 1
425 info: Rows matched: 1 Changed: 1 Warnings: 0
426 DELETE FROM t1 WHERE c1 = 8;
427 affected rows: 1
428 DELETE FROM t3 WHERE c1 = 8;
429 affected rows: 1
430 INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1);
431 affected rows: 1
432 INSERT INTO t3(c2,c3,c4) VALUES (1000, 1, 1);
433 affected rows: 1
434 ########## innodb_wl6742_selects.inc ##########
435 SELECT * FROM t1;
436 c1 c2 c3 c4
437 1 1 1 1
438 2 1 1 1
439 3 7 1 1
440 4 1 1 1
441 5 1 1 1
442 6 1 1 1
443 7 1 1 1
444 9 1 1 1
445 10 1 1 1
446 12 1000 1 1
447 SELECT COUNT(*) FROM t1;
448 COUNT(*)
449 10
450 SELECT COUNT(*) FROM t3;
451 COUNT(*)
452 10
453 SELECT COUNT(c1) FROM t1;
454 COUNT(c1)
455 10
456 SELECT COUNT(c2) FROM t1;
457 COUNT(c2)
458 10
459 SELECT COUNT(c3) FROM t1;
460 COUNT(c3)
461 10
462 SELECT SUM(c1) FROM t1;
463 SUM(c1)
464 59
465 SELECT SUM(c2) FROM t1;
466 SUM(c2)
467 1015
468 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
469 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
470 5.9000 12 1 1.0000 10
471 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
472 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
473 5.9000 12 1 1.0000 10
474 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
475 c1 c2
476 6 1
477 7 1
478 9 1
479 10 1
480 12 1000
481 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
482 c1 c2
483 6 1
484 7 1
485 9 1
486 10 1
487 12 1000
488 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
489 COUNT(c2)
490 5
491 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
492 COUNT(c2)
493 5
494 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
495 COUNT(*)
496 5
497 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
498 COUNT(*)
499 5
500 ###############################################
501 #
502 # Connection 3 READ UNCOMMITTED
503 #
504 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
505 BEGIN;
506 ########## innodb_wl6742_selects.inc ##########
507 SELECT * FROM t1;
508 c1 c2 c3 c4
509 1 3 1 1
510 2 5 1 1
511 3 7 1 1
512 4 1 1 1
513 5 1 1 1
514 9 1 1 1
515 10 1 1 1
516 11 100 1 1
517 12 1000 1 1
518 SELECT COUNT(*) FROM t1;
519 COUNT(*)
520 9
521 SELECT COUNT(*) FROM t3;
522 COUNT(*)
523 9
524 SELECT COUNT(c1) FROM t1;
525 COUNT(c1)
526 9
527 SELECT COUNT(c2) FROM t1;
528 COUNT(c2)
529 9
530 SELECT COUNT(c3) FROM t1;
531 COUNT(c3)
532 9
533 SELECT SUM(c1) FROM t1;
534 SUM(c1)
535 57
536 SELECT SUM(c2) FROM t1;
537 SUM(c2)
538 1119
539 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
540 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
541 6.3333 12 1 1.0000 9
542 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
543 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
544 6.3333 12 1 1.0000 9
545 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
546 c1 c2
547 5 1
548 9 1
549 10 1
550 11 100
551 12 1000
552 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
553 c1 c2
554 5 1
555 9 1
556 10 1
557 11 100
558 12 1000
559 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
560 COUNT(c2)
561 5
562 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
563 COUNT(c2)
564 5
565 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
566 COUNT(*)
567 4
568 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
569 COUNT(*)
570 4
571 ###############################################
572 UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4;
573 affected rows: 1
574 info: Rows matched: 1 Changed: 1 Warnings: 0
575 UPDATE t3 SET c2 = c2 * 11 WHERE c1 = 4;
576 affected rows: 1
577 info: Rows matched: 1 Changed: 1 Warnings: 0
578 INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1);
579 affected rows: 1
580 INSERT INTO t3(c2,c3,c4) VALUES (10000, 1, 1);
581 affected rows: 1
582 DELETE FROM t1 WHERE c1 in(9);
583 affected rows: 1
584 DELETE FROM t3 WHERE c1 in(9);
585 affected rows: 1
586 ########## innodb_wl6742_selects.inc ##########
587 SELECT * FROM t1;
588 c1 c2 c3 c4
589 1 3 1 1
590 2 5 1 1
591 3 7 1 1
592 4 11 1 1
593 5 1 1 1
594 10 1 1 1
595 11 100 1 1
596 12 1000 1 1
597 13 10000 1 1
598 SELECT COUNT(*) FROM t1;
599 COUNT(*)
600 9
601 SELECT COUNT(*) FROM t3;
602 COUNT(*)
603 9
604 SELECT COUNT(c1) FROM t1;
605 COUNT(c1)
606 9
607 SELECT COUNT(c2) FROM t1;
608 COUNT(c2)
609 9
610 SELECT COUNT(c3) FROM t1;
611 COUNT(c3)
612 9
613 SELECT SUM(c1) FROM t1;
614 SUM(c1)
615 61
616 SELECT SUM(c2) FROM t1;
617 SUM(c2)
618 11128
619 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
620 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
621 6.7778 13 1 1.0000 9
622 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
623 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
624 6.7778 13 1 1.0000 9
625 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
626 c1 c2
627 5 1
628 10 1
629 11 100
630 12 1000
631 13 10000
632 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
633 c1 c2
634 5 1
635 10 1
636 11 100
637 12 1000
638 13 10000
639 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
640 COUNT(c2)
641 5
642 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
643 COUNT(c2)
644 5
645 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
646 COUNT(*)
647 4
648 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
649 COUNT(*)
650 4
651 ###############################################
652 #
653 # Connection default REPEATABLE READ
654 #
655 ########## innodb_wl6742_selects.inc ##########
656 SELECT * FROM t1;
657 c1 c2 c3 c4
658 1 3 1 1
659 2 1 1 1
660 3 1 1 1
661 4 1 1 1
662 5 1 1 1
663 7 1 1 1
664 8 1 1 1
665 9 1 1 1
666 10 1 1 1
667 SELECT COUNT(*) FROM t1;
668 COUNT(*)
669 9
670 SELECT COUNT(*) FROM t3;
671 COUNT(*)
672 9
673 SELECT COUNT(c1) FROM t1;
674 COUNT(c1)
675 9
676 SELECT COUNT(c2) FROM t1;
677 COUNT(c2)
678 9
679 SELECT COUNT(c3) FROM t1;
680 COUNT(c3)
681 9
682 SELECT SUM(c1) FROM t1;
683 SUM(c1)
684 49
685 SELECT SUM(c2) FROM t1;
686 SUM(c2)
687 11
688 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
689 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
690 5.4444 10 1 1.0000 9
691 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
692 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
693 5.4444 10 1 1.0000 9
694 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
695 c1 c2
696 5 1
697 7 1
698 8 1
699 9 1
700 10 1
701 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
702 c1 c2
703 5 1
704 7 1
705 8 1
706 9 1
707 10 1
708 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
709 COUNT(c2)
710 5
711 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
712 COUNT(c2)
713 5
714 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
715 COUNT(*)
716 4
717 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
718 COUNT(*)
719 4
720 ###############################################
721 #
722 # Commit the 3 extra connections
723 #
724 # Connection 1 REPEATABLE READ
725 #
726 ########## innodb_wl6742_selects.inc ##########
727 SELECT * FROM t1;
728 c1 c2 c3 c4
729 1 1 1 1
730 2 5 1 1
731 3 1 1 1
732 4 1 1 1
733 5 1 1 1
734 6 1 1 1
735 8 1 1 1
736 9 1 1 1
737 10 1 1 1
738 11 100 1 1
739 SELECT COUNT(*) FROM t1;
740 COUNT(*)
741 10
742 SELECT COUNT(*) FROM t3;
743 COUNT(*)
744 10
745 SELECT COUNT(c1) FROM t1;
746 COUNT(c1)
747 10
748 SELECT COUNT(c2) FROM t1;
749 COUNT(c2)
750 10
751 SELECT COUNT(c3) FROM t1;
752 COUNT(c3)
753 10
754 SELECT SUM(c1) FROM t1;
755 SUM(c1)
756 59
757 SELECT SUM(c2) FROM t1;
758 SUM(c2)
759 113
760 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
761 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
762 5.9000 11 1 1.0000 10
763 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
764 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
765 5.9000 11 1 1.0000 10
766 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
767 c1 c2
768 6 1
769 8 1
770 9 1
771 10 1
772 11 100
773 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
774 c1 c2
775 6 1
776 8 1
777 9 1
778 10 1
779 11 100
780 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
781 COUNT(c2)
782 5
783 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
784 COUNT(c2)
785 5
786 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
787 COUNT(*)
788 5
789 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
790 COUNT(*)
791 5
792 ###############################################
793 #
794 # Connection 2 READ COMMITTED
795 #
796 ########## innodb_wl6742_selects.inc ##########
797 SELECT * FROM t1;
798 c1 c2 c3 c4
799 1 1 1 1
800 2 1 1 1
801 3 7 1 1
802 4 1 1 1
803 5 1 1 1
804 6 1 1 1
805 7 1 1 1
806 9 1 1 1
807 10 1 1 1
808 12 1000 1 1
809 SELECT COUNT(*) FROM t1;
810 COUNT(*)
811 10
812 SELECT COUNT(*) FROM t3;
813 COUNT(*)
814 10
815 SELECT COUNT(c1) FROM t1;
816 COUNT(c1)
817 10
818 SELECT COUNT(c2) FROM t1;
819 COUNT(c2)
820 10
821 SELECT COUNT(c3) FROM t1;
822 COUNT(c3)
823 10
824 SELECT SUM(c1) FROM t1;
825 SUM(c1)
826 59
827 SELECT SUM(c2) FROM t1;
828 SUM(c2)
829 1015
830 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
831 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
832 5.9000 12 1 1.0000 10
833 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
834 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
835 5.9000 12 1 1.0000 10
836 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
837 c1 c2
838 6 1
839 7 1
840 9 1
841 10 1
842 12 1000
843 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
844 c1 c2
845 6 1
846 7 1
847 9 1
848 10 1
849 12 1000
850 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
851 COUNT(c2)
852 5
853 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
854 COUNT(c2)
855 5
856 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
857 COUNT(*)
858 5
859 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
860 COUNT(*)
861 5
862 ###############################################
863 #
864 # Connection 3 READ UNCOMMITTED
865 #
866 ########## innodb_wl6742_selects.inc ##########
867 SELECT * FROM t1;
868 c1 c2 c3 c4
869 1 3 1 1
870 2 5 1 1
871 3 7 1 1
872 4 11 1 1
873 5 1 1 1
874 10 1 1 1
875 11 100 1 1
876 12 1000 1 1
877 13 10000 1 1
878 SELECT COUNT(*) FROM t1;
879 COUNT(*)
880 9
881 SELECT COUNT(*) FROM t3;
882 COUNT(*)
883 9
884 SELECT COUNT(c1) FROM t1;
885 COUNT(c1)
886 9
887 SELECT COUNT(c2) FROM t1;
888 COUNT(c2)
889 9
890 SELECT COUNT(c3) FROM t1;
891 COUNT(c3)
892 9
893 SELECT SUM(c1) FROM t1;
894 SUM(c1)
895 61
896 SELECT SUM(c2) FROM t1;
897 SUM(c2)
898 11128
899 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
900 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
901 6.7778 13 1 1.0000 9
902 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
903 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
904 6.7778 13 1 1.0000 9
905 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
906 c1 c2
907 5 1
908 10 1
909 11 100
910 12 1000
911 13 10000
912 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
913 c1 c2
914 5 1
915 10 1
916 11 100
917 12 1000
918 13 10000
919 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
920 COUNT(c2)
921 5
922 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
923 COUNT(c2)
924 5
925 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
926 COUNT(*)
927 4
928 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
929 COUNT(*)
930 4
931 ###############################################
932 COMMIT;
933 ########## innodb_wl6742_selects.inc ##########
934 SELECT * FROM t1;
935 c1 c2 c3 c4
936 1 3 1 1
937 2 5 1 1
938 3 7 1 1
939 4 11 1 1
940 5 1 1 1
941 10 1 1 1
942 11 100 1 1
943 12 1000 1 1
944 13 10000 1 1
945 SELECT COUNT(*) FROM t1;
946 COUNT(*)
947 9
948 SELECT COUNT(*) FROM t3;
949 COUNT(*)
950 9
951 SELECT COUNT(c1) FROM t1;
952 COUNT(c1)
953 9
954 SELECT COUNT(c2) FROM t1;
955 COUNT(c2)
956 9
957 SELECT COUNT(c3) FROM t1;
958 COUNT(c3)
959 9
960 SELECT SUM(c1) FROM t1;
961 SUM(c1)
962 61
963 SELECT SUM(c2) FROM t1;
964 SUM(c2)
965 11128
966 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
967 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
968 6.7778 13 1 1.0000 9
969 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
970 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
971 6.7778 13 1 1.0000 9
972 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
973 c1 c2
974 5 1
975 10 1
976 11 100
977 12 1000
978 13 10000
979 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
980 c1 c2
981 5 1
982 10 1
983 11 100
984 12 1000
985 13 10000
986 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
987 COUNT(c2)
988 5
989 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
990 COUNT(c2)
991 5
992 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
993 COUNT(*)
994 4
995 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
996 COUNT(*)
997 4
998 ###############################################
999 #
1000 # Connection 2 READ COMMITTED
1001 #
1002 ########## innodb_wl6742_selects.inc ##########
1003 SELECT * FROM t1;
1004 c1 c2 c3 c4
1005 1 1 1 1
1006 2 1 1 1
1007 3 7 1 1
1008 4 11 1 1
1009 5 1 1 1
1010 6 1 1 1
1011 7 1 1 1
1012 10 1 1 1
1013 12 1000 1 1
1014 13 10000 1 1
1015 SELECT COUNT(*) FROM t1;
1016 COUNT(*)
1017 10
1018 SELECT COUNT(*) FROM t3;
1019 COUNT(*)
1020 10
1021 SELECT COUNT(c1) FROM t1;
1022 COUNT(c1)
1023 10
1024 SELECT COUNT(c2) FROM t1;
1025 COUNT(c2)
1026 10
1027 SELECT COUNT(c3) FROM t1;
1028 COUNT(c3)
1029 10
1030 SELECT SUM(c1) FROM t1;
1031 SUM(c1)
1032 63
1033 SELECT SUM(c2) FROM t1;
1034 SUM(c2)
1035 11024
1036 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1037 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1038 6.3000 13 1 1.0000 10
1039 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1040 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1041 6.3000 13 1 1.0000 10
1042 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1043 c1 c2
1044 6 1
1045 7 1
1046 10 1
1047 12 1000
1048 13 10000
1049 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1050 c1 c2
1051 6 1
1052 7 1
1053 10 1
1054 12 1000
1055 13 10000
1056 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1057 COUNT(c2)
1058 5
1059 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1060 COUNT(c2)
1061 5
1062 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1063 COUNT(*)
1064 4
1065 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1066 COUNT(*)
1067 4
1068 ###############################################
1069 COMMIT;
1070 ########## innodb_wl6742_selects.inc ##########
1071 SELECT * FROM t1;
1072 c1 c2 c3 c4
1073 1 1 1 1
1074 2 1 1 1
1075 3 7 1 1
1076 4 11 1 1
1077 5 1 1 1
1078 6 1 1 1
1079 7 1 1 1
1080 10 1 1 1
1081 12 1000 1 1
1082 13 10000 1 1
1083 SELECT COUNT(*) FROM t1;
1084 COUNT(*)
1085 10
1086 SELECT COUNT(*) FROM t3;
1087 COUNT(*)
1088 10
1089 SELECT COUNT(c1) FROM t1;
1090 COUNT(c1)
1091 10
1092 SELECT COUNT(c2) FROM t1;
1093 COUNT(c2)
1094 10
1095 SELECT COUNT(c3) FROM t1;
1096 COUNT(c3)
1097 10
1098 SELECT SUM(c1) FROM t1;
1099 SUM(c1)
1100 63
1101 SELECT SUM(c2) FROM t1;
1102 SUM(c2)
1103 11024
1104 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1105 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1106 6.3000 13 1 1.0000 10
1107 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1108 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1109 6.3000 13 1 1.0000 10
1110 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1111 c1 c2
1112 6 1
1113 7 1
1114 10 1
1115 12 1000
1116 13 10000
1117 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1118 c1 c2
1119 6 1
1120 7 1
1121 10 1
1122 12 1000
1123 13 10000
1124 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1125 COUNT(c2)
1126 5
1127 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1128 COUNT(c2)
1129 5
1130 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1131 COUNT(*)
1132 4
1133 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1134 COUNT(*)
1135 4
1136 ###############################################
1137 #
1138 # Connection 1 REPEATABLE READ
1139 #
1140 ########## innodb_wl6742_selects.inc ##########
1141 SELECT * FROM t1;
1142 c1 c2 c3 c4
1143 1 1 1 1
1144 2 5 1 1
1145 3 1 1 1
1146 4 1 1 1
1147 5 1 1 1
1148 6 1 1 1
1149 8 1 1 1
1150 9 1 1 1
1151 10 1 1 1
1152 11 100 1 1
1153 SELECT COUNT(*) FROM t1;
1154 COUNT(*)
1155 10
1156 SELECT COUNT(*) FROM t3;
1157 COUNT(*)
1158 10
1159 SELECT COUNT(c1) FROM t1;
1160 COUNT(c1)
1161 10
1162 SELECT COUNT(c2) FROM t1;
1163 COUNT(c2)
1164 10
1165 SELECT COUNT(c3) FROM t1;
1166 COUNT(c3)
1167 10
1168 SELECT SUM(c1) FROM t1;
1169 SUM(c1)
1170 59
1171 SELECT SUM(c2) FROM t1;
1172 SUM(c2)
1173 113
1174 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1175 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1176 5.9000 11 1 1.0000 10
1177 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1178 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1179 5.9000 11 1 1.0000 10
1180 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1181 c1 c2
1182 6 1
1183 8 1
1184 9 1
1185 10 1
1186 11 100
1187 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1188 c1 c2
1189 6 1
1190 8 1
1191 9 1
1192 10 1
1193 11 100
1194 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1195 COUNT(c2)
1196 5
1197 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1198 COUNT(c2)
1199 5
1200 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1201 COUNT(*)
1202 5
1203 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1204 COUNT(*)
1205 5
1206 ###############################################
1207 #
1208 # Select the first 5 records FOR UPDATE using count(*) in a subquery.
1209 # The second record is still pending so we get a lock timeout.
1210 #
1211 SET innodb_lock_wait_timeout = 1;
1212 SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE;
1213 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1214 SELECT COUNT(*) FROM t1 FOR UPDATE;
1215 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
1216 COMMIT;
1217 ########## innodb_wl6742_selects.inc ##########
1218 SELECT * FROM t1;
1219 c1 c2 c3 c4
1220 1 1 1 1
1221 2 5 1 1
1222 3 7 1 1
1223 4 11 1 1
1224 5 1 1 1
1225 6 1 1 1
1226 10 1 1 1
1227 11 100 1 1
1228 12 1000 1 1
1229 13 10000 1 1
1230 SELECT COUNT(*) FROM t1;
1231 COUNT(*)
1232 10
1233 SELECT COUNT(*) FROM t3;
1234 COUNT(*)
1235 10
1236 SELECT COUNT(c1) FROM t1;
1237 COUNT(c1)
1238 10
1239 SELECT COUNT(c2) FROM t1;
1240 COUNT(c2)
1241 10
1242 SELECT COUNT(c3) FROM t1;
1243 COUNT(c3)
1244 10
1245 SELECT SUM(c1) FROM t1;
1246 SUM(c1)
1247 67
1248 SELECT SUM(c2) FROM t1;
1249 SUM(c2)
1250 11127
1251 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1252 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1253 6.7000 13 1 1.0000 10
1254 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1255 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1256 6.7000 13 1 1.0000 10
1257 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1258 c1 c2
1259 6 1
1260 10 1
1261 11 100
1262 12 1000
1263 13 10000
1264 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1265 c1 c2
1266 6 1
1267 10 1
1268 11 100
1269 12 1000
1270 13 10000
1271 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1272 COUNT(c2)
1273 5
1274 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1275 COUNT(c2)
1276 5
1277 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1278 COUNT(*)
1279 4
1280 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1281 COUNT(*)
1282 4
1283 ###############################################
1284 #
1285 # Show The EXPLAIN output for these queries;
1286 #
1287 EXPLAIN SELECT * FROM t1;
1288 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1289 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1290 Warnings:
1291 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2`,`test`.`t1`.`c3` AS `c3`,`test`.`t1`.`c4` AS `c4` from `test`.`t1`
1292 EXPLAIN SELECT COUNT(*) FROM t1;
1293 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1294 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index
1295 Warnings:
1296 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1`
1297 EXPLAIN SELECT COUNT(*) FROM t3;
1298 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1299 1 SIMPLE t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index
1300 Warnings:
1301 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t3`
1302 EXPLAIN SELECT COUNT(c1) FROM t1;
1303 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1304 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index
1305 Warnings:
1306 Note 1003 /* select#1 */ select count(`test`.`t1`.`c1`) AS `COUNT(c1)` from `test`.`t1`
1307 EXPLAIN SELECT COUNT(c2) FROM t1;
1308 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1309 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index
1310 Warnings:
1311 Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1`
1312 EXPLAIN SELECT COUNT(c3) FROM t1;
1313 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1314 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1315 Warnings:
1316 Note 1003 /* select#1 */ select count(`test`.`t1`.`c3`) AS `COUNT(c3)` from `test`.`t1`
1317 EXPLAIN SELECT SUM(c1) FROM t1;
1318 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1319 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index
1320 Warnings:
1321 Note 1003 /* select#1 */ select sum(`test`.`t1`.`c1`) AS `SUM(c1)` from `test`.`t1`
1322 EXPLAIN SELECT SUM(c2) FROM t1;
1323 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1324 1 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index
1325 Warnings:
1326 Note 1003 /* select#1 */ select sum(`test`.`t1`.`c2`) AS `SUM(c2)` from `test`.`t1`
1327 EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1328 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1329 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1330 Warnings:
1331 Note 1003 /* select#1 */ select avg(`test`.`t1`.`c1`) AS `AVG(c1)`,max(`test`.`t1`.`c1`) AS `MAX(c1)`,min(`test`.`t1`.`c2`) AS `MIN(c2)`,avg(`test`.`t1`.`c3`) AS `AVG(c3)`,sum(`test`.`t1`.`c4`) AS `SUM(c4)` from `test`.`t1`
1332 EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1333 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1334 1 SIMPLE t3 p0,p1,p2,p3 ALL NULL NULL NULL NULL # 100.00 NULL
1335 Warnings:
1336 Note 1003 /* select#1 */ select avg(`test`.`t3`.`c1`) AS `AVG(c1)`,max(`test`.`t3`.`c1`) AS `MAX(c1)`,min(`test`.`t3`.`c2`) AS `MIN(c2)`,avg(`test`.`t3`.`c3`) AS `AVG(c3)`,sum(`test`.`t3`.`c4`) AS `SUM(c4)` from `test`.`t3`
1337 EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1338 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1339 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where
1340 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index
1341 Warnings:
1342 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t1`) / 2)))
1343 EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1344 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1345 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where
1346 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index
1347 Warnings:
1348 Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1`,`test`.`t1`.`c2` AS `c2` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t3`) / 2)))
1349 EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1350 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1351 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where
1352 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index
1353 Warnings:
1354 Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t1`) / 2)))
1355 EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1356 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1357 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where
1358 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index
1359 Warnings:
1360 Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` where (`test`.`t1`.`c1` > <cache>(((/* select#2 */ select count(0) from `test`.`t3`) / 2)))
1361 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1362 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1363 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where; Using index
1364 2 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index
1365 Warnings:
1366 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`c1` > (/* select#2 */ select avg(`test`.`t1`.`c1`) from `test`.`t1`))
1367 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1368 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1369 1 PRIMARY t1 NULL range PRIMARY,k2 PRIMARY 4 NULL # 100.00 Using where; Using index
1370 2 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index
1371 Warnings:
1372 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` where (`test`.`t1`.`c1` > (/* select#2 */ select avg(`test`.`t3`.`c1`) from `test`.`t3`))
1373 #
1374 # Make all indexes in t2 obsolete to the active repeatable read transaction
1375 # in the default connection.
1376 #
1377 ALTER TABLE t2 row_format=redundant;
1378 #
1379 # Connection default REPEATABLE READ
1380 # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records.
1381 #
1382 ########## innodb_wl6742_selects.inc ##########
1383 SELECT * FROM t1;
1384 c1 c2 c3 c4
1385 1 3 1 1
1386 2 1 1 1
1387 3 1 1 1
1388 4 1 1 1
1389 5 1 1 1
1390 7 1 1 1
1391 8 1 1 1
1392 9 1 1 1
1393 10 1 1 1
1394 SELECT COUNT(*) FROM t1;
1395 COUNT(*)
1396 9
1397 SELECT COUNT(*) FROM t3;
1398 COUNT(*)
1399 9
1400 SELECT COUNT(c1) FROM t1;
1401 COUNT(c1)
1402 9
1403 SELECT COUNT(c2) FROM t1;
1404 COUNT(c2)
1405 9
1406 SELECT COUNT(c3) FROM t1;
1407 COUNT(c3)
1408 9
1409 SELECT SUM(c1) FROM t1;
1410 SUM(c1)
1411 49
1412 SELECT SUM(c2) FROM t1;
1413 SUM(c2)
1414 11
1415 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1416 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1417 5.4444 10 1 1.0000 9
1418 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1419 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1420 5.4444 10 1 1.0000 9
1421 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1422 c1 c2
1423 5 1
1424 7 1
1425 8 1
1426 9 1
1427 10 1
1428 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1429 c1 c2
1430 5 1
1431 7 1
1432 8 1
1433 9 1
1434 10 1
1435 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1436 COUNT(c2)
1437 5
1438 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1439 COUNT(c2)
1440 5
1441 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1442 COUNT(*)
1443 4
1444 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1445 COUNT(*)
1446 4
1447 ###############################################
1448 UPDATE t1 SET c4 = c2 * 10;
1449 affected rows: 9
1450 info: Rows matched: 9 Changed: 9 Warnings: 0
1451 UPDATE t3 SET c4 = c2 * 10;
1452 affected rows: 9
1453 info: Rows matched: 9 Changed: 9 Warnings: 0
1454 ########## innodb_wl6742_selects.inc ##########
1455 SELECT * FROM t1;
1456 c1 c2 c3 c4
1457 1 3 1 30
1458 2 5 1 50
1459 3 7 1 70
1460 4 11 1 110
1461 5 1 1 10
1462 7 1 1 1
1463 8 1 1 1
1464 9 1 1 1
1465 10 1 1 10
1466 11 100 1 1000
1467 12 1000 1 10000
1468 13 10000 1 100000
1469 SELECT COUNT(*) FROM t1;
1470 COUNT(*)
1471 12
1472 SELECT COUNT(*) FROM t3;
1473 COUNT(*)
1474 12
1475 SELECT COUNT(c1) FROM t1;
1476 COUNT(c1)
1477 12
1478 SELECT COUNT(c2) FROM t1;
1479 COUNT(c2)
1480 12
1481 SELECT COUNT(c3) FROM t1;
1482 COUNT(c3)
1483 12
1484 SELECT SUM(c1) FROM t1;
1485 SUM(c1)
1486 85
1487 SELECT SUM(c2) FROM t1;
1488 SUM(c2)
1489 11131
1490 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1491 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1492 7.0833 13 1 1.0000 111283
1493 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1494 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1495 7.0833 13 1 1.0000 111223
1496 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1497 c1 c2
1498 7 1
1499 8 1
1500 9 1
1501 10 1
1502 11 100
1503 12 1000
1504 13 10000
1505 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1506 c1 c2
1507 7 1
1508 8 1
1509 9 1
1510 10 1
1511 11 100
1512 12 1000
1513 13 10000
1514 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1515 COUNT(c2)
1516 7
1517 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1518 COUNT(c2)
1519 7
1520 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1521 COUNT(*)
1522 6
1523 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1524 COUNT(*)
1525 6
1526 ###############################################
1527 #
1528 # Table t2 has been altered to a new row format.
1529 # The index should not be useable.
1530 #
1531 SELECT COUNT(*) FROM t2;
1532 ERROR HY000: Table definition has changed, please retry transaction
1533 SELECT * FROM t2;
1534 ERROR HY000: Table definition has changed, please retry transaction
1535 COMMIT;
1536 SELECT COUNT(*) FROM t2;
1537 COUNT(*)
1538 10
1539 ########## innodb_wl6742_selects.inc ##########
1540 SELECT * FROM t1;
1541 c1 c2 c3 c4
1542 1 3 1 30
1543 2 5 1 50
1544 3 7 1 70
1545 4 11 1 110
1546 5 1 1 10
1547 10 1 1 10
1548 11 100 1 1000
1549 12 1000 1 10000
1550 13 10000 1 100000
1551 SELECT COUNT(*) FROM t1;
1552 COUNT(*)
1553 9
1554 SELECT COUNT(*) FROM t3;
1555 COUNT(*)
1556 9
1557 SELECT COUNT(c1) FROM t1;
1558 COUNT(c1)
1559 9
1560 SELECT COUNT(c2) FROM t1;
1561 COUNT(c2)
1562 9
1563 SELECT COUNT(c3) FROM t1;
1564 COUNT(c3)
1565 9
1566 SELECT SUM(c1) FROM t1;
1567 SUM(c1)
1568 61
1569 SELECT SUM(c2) FROM t1;
1570 SUM(c2)
1571 11128
1572 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1573 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1574 6.7778 13 1 1.0000 111280
1575 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1576 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1577 6.7778 13 1 1.0000 111220
1578 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1579 c1 c2
1580 5 1
1581 10 1
1582 11 100
1583 12 1000
1584 13 10000
1585 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1586 c1 c2
1587 5 1
1588 10 1
1589 11 100
1590 12 1000
1591 13 10000
1592 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1593 COUNT(c2)
1594 5
1595 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1596 COUNT(c2)
1597 5
1598 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1599 COUNT(*)
1600 4
1601 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1602 COUNT(*)
1603 4
1604 ###############################################
1605 #
1606 # Connection 2
1607 #
1608 ########## innodb_wl6742_selects.inc ##########
1609 SELECT * FROM t1;
1610 c1 c2 c3 c4
1611 1 3 1 30
1612 2 5 1 50
1613 3 7 1 70
1614 4 11 1 110
1615 5 1 1 10
1616 10 1 1 10
1617 11 100 1 1000
1618 12 1000 1 10000
1619 13 10000 1 100000
1620 SELECT COUNT(*) FROM t1;
1621 COUNT(*)
1622 9
1623 SELECT COUNT(*) FROM t3;
1624 COUNT(*)
1625 9
1626 SELECT COUNT(c1) FROM t1;
1627 COUNT(c1)
1628 9
1629 SELECT COUNT(c2) FROM t1;
1630 COUNT(c2)
1631 9
1632 SELECT COUNT(c3) FROM t1;
1633 COUNT(c3)
1634 9
1635 SELECT SUM(c1) FROM t1;
1636 SUM(c1)
1637 61
1638 SELECT SUM(c2) FROM t1;
1639 SUM(c2)
1640 11128
1641 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
1642 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1643 6.7778 13 1 1.0000 111280
1644 SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3;
1645 AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4)
1646 6.7778 13 1 1.0000 111220
1647 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1648 c1 c2
1649 5 1
1650 10 1
1651 11 100
1652 12 1000
1653 13 10000
1654 SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1655 c1 c2
1656 5 1
1657 10 1
1658 11 100
1659 12 1000
1660 13 10000
1661 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
1662 COUNT(c2)
1663 5
1664 SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2);
1665 COUNT(c2)
1666 5
1667 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
1668 COUNT(*)
1669 4
1670 SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3);
1671 COUNT(*)
1672 4
1673 ###############################################
1674 #
1675 # Try COUNT(*) on a DISCARDED table.
1676 #
1677 CREATE TABLE t4 LIKE t1;
1678 INSERT INTO t4 (SELECT * FROM t1);
1679 SELECT COUNT(*) FROM t4;
1680 COUNT(*)
1681 9
1682 ALTER TABLE t4 DISCARD TABLESPACE;
1683 SELECT COUNT(*) FROM t4;
1684 ERROR HY000: Tablespace has been discarded for table 't4'
1685 #
1686 # Test the interaction of a repeatable read transaction
1687 # to changes that happen outside its view.
1688 #
1689 CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1690 INSERT INTO t5(b) VALUES ("inserted by client 1");
1691 INSERT INTO t5(b) VALUES ("inserted by client 1");
1692 INSERT INTO t5(b) VALUES ("inserted by client 1");
1693 INSERT INTO t5(b) VALUES ("inserted by client 1");
1694 UPDATE t5 SET aa=a, bb=b;
1695 CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1696 INSERT INTO t6(b) VALUES ("inserted by client 1");
1697 INSERT INTO t6(b) VALUES ("inserted by client 1");
1698 INSERT INTO t6(b) VALUES ("inserted by client 1");
1699 INSERT INTO t6(b) VALUES ("inserted by client 1");
1700 UPDATE t6 SET aa=a, bb=b;
1701 CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB;
1702 INSERT INTO t7(b) VALUES ("inserted by client 1");
1703 INSERT INTO t7(b) VALUES ("inserted by client 1");
1704 INSERT INTO t7(b) VALUES ("inserted by client 1");
1705 INSERT INTO t7(b) VALUES ("inserted by client 1");
1706 UPDATE t7 SET aa=a, bb=b;
1707 BEGIN;
1708 SELECT * FROM t5;
1709 a b aa bb
1710 1 inserted by client 1 1 inserted by client 1
1711 2 inserted by client 1 2 inserted by client 1
1712 3 inserted by client 1 3 inserted by client 1
1713 4 inserted by client 1 4 inserted by client 1
1714 SELECT COUNT(*) FROM t5;
1715 COUNT(*)
1716 4
1717 SELECT * FROM t6;
1718 a b aa bb
1719 1 inserted by client 1 1 inserted by client 1
1720 2 inserted by client 1 2 inserted by client 1
1721 3 inserted by client 1 3 inserted by client 1
1722 4 inserted by client 1 4 inserted by client 1
1723 SELECT COUNT(*) FROM t6;
1724 COUNT(*)
1725 4
1726 SELECT * FROM t7;
1727 a b aa bb
1728 1 inserted by client 1 1 inserted by client 1
1729 2 inserted by client 1 2 inserted by client 1
1730 3 inserted by client 1 3 inserted by client 1
1731 4 inserted by client 1 4 inserted by client 1
1732 SELECT COUNT(*) FROM t7;
1733 COUNT(*)
1734 4
1735 #
1736 # Connection 1
1737 #
1738 INSERT INTO t5(b) VALUES ("inserted by client 2");
1739 UPDATE t5 SET a = 10 where a = 1;
1740 UPDATE t5 SET b = "updated by client 2" where a = 2;
1741 DELETE FROM t5 WHERE a = 3;
1742 SELECT * FROM t5;
1743 a b aa bb
1744 2 updated by client 2 2 inserted by client 1
1745 4 inserted by client 1 4 inserted by client 1
1746 5 inserted by client 2 NULL NULL
1747 10 inserted by client 1 1 inserted by client 1
1748 SELECT COUNT(*) FROM t5;
1749 COUNT(*)
1750 4
1751 INSERT INTO t6(b) VALUES ("inserted by client 2");
1752 UPDATE t6 SET a = 10 where a = 1;
1753 UPDATE t6 SET b = "updated by client 2" where a = 2;
1754 DELETE FROM t6 WHERE a = 3;
1755 SELECT * FROM t6;
1756 a b aa bb
1757 2 updated by client 2 2 inserted by client 1
1758 4 inserted by client 1 4 inserted by client 1
1759 5 inserted by client 2 NULL NULL
1760 10 inserted by client 1 1 inserted by client 1
1761 SELECT COUNT(*) FROM t6;
1762 COUNT(*)
1763 4
1764 INSERT INTO t7(b) VALUES ("inserted by client 2");
1765 UPDATE t7 SET a = 10 where a = 1;
1766 UPDATE t7 SET b = "updated by client 2" where a = 2;
1767 DELETE FROM t7 WHERE a = 3;
1768 SELECT * FROM t7;
1769 a b aa bb
1770 2 updated by client 2 2 inserted by client 1
1771 4 inserted by client 1 4 inserted by client 1
1772 5 inserted by client 2 NULL NULL
1773 10 inserted by client 1 1 inserted by client 1
1774 SELECT COUNT(*) FROM t7;
1775 COUNT(*)
1776 4
1777 #
1778 # Connection default
1779 #
1780 SELECT * FROM t5;
1781 a b aa bb
1782 1 inserted by client 1 1 inserted by client 1
1783 2 inserted by client 1 2 inserted by client 1
1784 3 inserted by client 1 3 inserted by client 1
1785 4 inserted by client 1 4 inserted by client 1
1786 INSERT INTO t5(b) VALUES ("inserted by client 1");
1787 SELECT * FROM t5;
1788 a b aa bb
1789 1 inserted by client 1 1 inserted by client 1
1790 2 inserted by client 1 2 inserted by client 1
1791 3 inserted by client 1 3 inserted by client 1
1792 4 inserted by client 1 4 inserted by client 1
1793 11 inserted by client 1 NULL NULL
1794 UPDATE t5 SET a = a + 100;
1795 SELECT * FROM t5;
1796 a b aa bb
1797 1 inserted by client 1 1 inserted by client 1
1798 3 inserted by client 1 3 inserted by client 1
1799 102 updated by client 2 2 inserted by client 1
1800 104 inserted by client 1 4 inserted by client 1
1801 105 inserted by client 2 NULL NULL
1802 110 inserted by client 1 1 inserted by client 1
1803 111 inserted by client 1 NULL NULL
1804 SELECT COUNT(*) FROM t5;
1805 COUNT(*)
1806 7
1807 UPDATE t6 SET b = "updated by client 2";
1808 SELECT * FROM t6;
1809 a b aa bb
1810 1 inserted by client 1 1 inserted by client 1
1811 2 updated by client 2 2 inserted by client 1
1812 3 inserted by client 1 3 inserted by client 1
1813 4 updated by client 2 4 inserted by client 1
1814 5 updated by client 2 NULL NULL
1815 10 updated by client 2 1 inserted by client 1
1816 SELECT COUNT(*) FROM t6;
1817 COUNT(*)
1818 6
1819 DELETE FROM t7;
1820 SELECT * FROM t7;
1821 a b aa bb
1822 1 inserted by client 1 1 inserted by client 1
1823 3 inserted by client 1 3 inserted by client 1
1824 SELECT COUNT(*) FROM t7;
1825 COUNT(*)
1826 2
1827 COMMIT;
1828 SELECT * FROM t5;
1829 a b aa bb
1830 102 updated by client 2 2 inserted by client 1
1831 104 inserted by client 1 4 inserted by client 1
1832 105 inserted by client 2 NULL NULL
1833 110 inserted by client 1 1 inserted by client 1
1834 111 inserted by client 1 NULL NULL
1835 SELECT COUNT(*) FROM t5;
1836 COUNT(*)
1837 5
1838 SELECT * FROM t6;
1839 a b aa bb
1840 2 updated by client 2 2 inserted by client 1
1841 4 updated by client 2 4 inserted by client 1
1842 5 updated by client 2 NULL NULL
1843 10 updated by client 2 1 inserted by client 1
1844 SELECT COUNT(*) FROM t6;
1845 COUNT(*)
1846 4
1847 SELECT * FROM t7;
1848 a b aa bb
1849 SELECT COUNT(*) FROM t7;
1850 COUNT(*)
1851 0
1852 #
1853 # Cleanup
1854 #
1855 DROP TABLE t1,t2,t3,t4,t5,t6,t7;
1856 #
1857 # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*)
1858 #
1859 CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY,
1860 c2 INT NOT NULL DEFAULT 1,
1861 c3 char(20) DEFAULT '',
1862 KEY c2_idx (c2));
1863 INSERT INTO t1(c1) VALUES (1), (2), (3);
1864 INSERT INTO t1(c1) SELECT c1 + 10 FROM t1;
1865 INSERT INTO t1(c1) SELECT c1 + 100 FROM t1;
1866 CREATE TABLE t2 SELECT * FROM t1;
1867 ANALYZE TABLE t1, t2;
1868 Table Op Msg_type Msg_text
1869 test.t1 analyze status OK
1870 test.t2 analyze status OK
1871 EXPLAIN SELECT COUNT(*) FROM t1;
1872 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1873 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index
1874 Warnings:
1875 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1`
1876 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx);
1877 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1878 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index
1879 Warnings:
1880 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`)
1881 EXPLAIN SELECT COUNT(*) FROM t1, t2;
1882 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1883 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index
1884 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1885 Warnings:
1886 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2`
1887 EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2;
1888 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1889 1 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index
1890 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 NULL
1891 Warnings:
1892 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`) join `test`.`t2`
1893 DROP TABLE t1, t2;