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# 11CREATE TABLE t1 ( 12c1 INT AUTO_INCREMENT PRIMARY KEY, 13c2 INT, 14c3 INT, 15c4 INT, 16INDEX k2(c2) 17) Engine=InnoDB; 18INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 19INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 20INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 21INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 22INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 23INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 24INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 25INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 26INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 27INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); 28CREATE TABLE t2 LIKE t1; 29INSERT INTO t2 (SELECT * FROM t1); 30affected rows: 10 31info: Records: 10 Duplicates: 0 Warnings: 0 32CREATE TABLE t3 ( 33c1 INT AUTO_INCREMENT PRIMARY KEY, 34c2 INT, 35c3 INT, 36c4 INT, 37INDEX k2(c2) 38) Engine=InnoDB PARTITION BY HASH(c1) PARTITIONS 4; 39INSERT INTO t3 (SELECT * FROM t1); 40affected rows: 10 41info: Records: 10 Duplicates: 0 Warnings: 0 42########## innodb_isolation_selects.inc ########## 43SELECT * FROM t1; 44c1 c2 c3 c4 451 1 1 1 462 1 1 1 473 1 1 1 484 1 1 1 495 1 1 1 506 1 1 1 517 1 1 1 528 1 1 1 539 1 1 1 5410 1 1 1 55SELECT COUNT(*) FROM t1; 56COUNT(*) 5710 58SELECT COUNT(*) FROM t3; 59COUNT(*) 6010 61SELECT COUNT(c1) FROM t1; 62COUNT(c1) 6310 64SELECT COUNT(c2) FROM t1; 65COUNT(c2) 6610 67SELECT COUNT(c3) FROM t1; 68COUNT(c3) 6910 70SELECT SUM(c1) FROM t1; 71SUM(c1) 7255 73SELECT SUM(c2) FROM t1; 74SUM(c2) 7510 76SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 77AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 785.5000 10 1 1.0000 10 79SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 80AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 815.5000 10 1 1.0000 10 82SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 83c1 c2 846 1 857 1 868 1 879 1 8810 1 89SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 90c1 c2 916 1 927 1 938 1 949 1 9510 1 96SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 97COUNT(c2) 985 99SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 100COUNT(c2) 1015 102SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 103COUNT(*) 1045 105SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 106COUNT(*) 1075 108############################################### 109# 110# Do some DML in the default connection and leave the transaction pending. 111# 112SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 113BEGIN; 114UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1; 115affected rows: 1 116info: Rows matched: 1 Changed: 1 Warnings: 0 117UPDATE t3 SET c2 = c2 * 3 WHERE c1 = 1; 118affected rows: 1 119info: Rows matched: 1 Changed: 1 Warnings: 0 120DELETE FROM t1 WHERE c1 = 6; 121affected rows: 1 122DELETE FROM t3 WHERE c1 = 6; 123affected rows: 1 124########## innodb_isolation_selects.inc ########## 125SELECT * FROM t1; 126c1 c2 c3 c4 1271 3 1 1 1282 1 1 1 1293 1 1 1 1304 1 1 1 1315 1 1 1 1327 1 1 1 1338 1 1 1 1349 1 1 1 13510 1 1 1 136SELECT COUNT(*) FROM t1; 137COUNT(*) 1389 139SELECT COUNT(*) FROM t3; 140COUNT(*) 1419 142SELECT COUNT(c1) FROM t1; 143COUNT(c1) 1449 145SELECT COUNT(c2) FROM t1; 146COUNT(c2) 1479 148SELECT COUNT(c3) FROM t1; 149COUNT(c3) 1509 151SELECT SUM(c1) FROM t1; 152SUM(c1) 15349 154SELECT SUM(c2) FROM t1; 155SUM(c2) 15611 157SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 158AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 1595.4444 10 1 1.0000 9 160SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 161AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 1625.4444 10 1 1.0000 9 163SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 164c1 c2 1655 1 1667 1 1678 1 1689 1 16910 1 170SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 171c1 c2 1725 1 1737 1 1748 1 1759 1 17610 1 177SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 178COUNT(c2) 1795 180SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 181COUNT(c2) 1825 183SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 184COUNT(*) 1854 186SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 187COUNT(*) 1884 189############################################### 190# 191# Start transactions of Repeatable Read, Read Committed, and Read Uncommitted 192# 193# Connection 1 REPEATABLE READ 194# 195SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; 196BEGIN; 197########## innodb_isolation_selects.inc ########## 198SELECT * FROM t1; 199c1 c2 c3 c4 2001 1 1 1 2012 1 1 1 2023 1 1 1 2034 1 1 1 2045 1 1 1 2056 1 1 1 2067 1 1 1 2078 1 1 1 2089 1 1 1 20910 1 1 1 210SELECT COUNT(*) FROM t1; 211COUNT(*) 21210 213SELECT COUNT(*) FROM t3; 214COUNT(*) 21510 216SELECT COUNT(c1) FROM t1; 217COUNT(c1) 21810 219SELECT COUNT(c2) FROM t1; 220COUNT(c2) 22110 222SELECT COUNT(c3) FROM t1; 223COUNT(c3) 22410 225SELECT SUM(c1) FROM t1; 226SUM(c1) 22755 228SELECT SUM(c2) FROM t1; 229SUM(c2) 23010 231SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 232AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 2335.5000 10 1 1.0000 10 234SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 235AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 2365.5000 10 1 1.0000 10 237SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 238c1 c2 2396 1 2407 1 2418 1 2429 1 24310 1 244SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 245c1 c2 2466 1 2477 1 2488 1 2499 1 25010 1 251SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 252COUNT(c2) 2535 254SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 255COUNT(c2) 2565 257SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 258COUNT(*) 2595 260SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 261COUNT(*) 2625 263############################################### 264UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2; 265affected rows: 1 266info: Rows matched: 1 Changed: 1 Warnings: 0 267UPDATE t3 SET c2 = c2 * 5 WHERE c1 = 2; 268affected rows: 1 269info: Rows matched: 1 Changed: 1 Warnings: 0 270DELETE FROM t1 WHERE c1 = 7; 271affected rows: 1 272DELETE FROM t3 WHERE c1 = 7; 273affected rows: 1 274INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1); 275affected rows: 1 276INSERT INTO t3(c2,c3,c4) VALUES (100, 1, 1); 277affected rows: 1 278########## innodb_isolation_selects.inc ########## 279SELECT * FROM t1; 280c1 c2 c3 c4 2811 1 1 1 2822 5 1 1 2833 1 1 1 2844 1 1 1 2855 1 1 1 2866 1 1 1 2878 1 1 1 2889 1 1 1 28910 1 1 1 29011 100 1 1 291SELECT COUNT(*) FROM t1; 292COUNT(*) 29310 294SELECT COUNT(*) FROM t3; 295COUNT(*) 29610 297SELECT COUNT(c1) FROM t1; 298COUNT(c1) 29910 300SELECT COUNT(c2) FROM t1; 301COUNT(c2) 30210 303SELECT COUNT(c3) FROM t1; 304COUNT(c3) 30510 306SELECT SUM(c1) FROM t1; 307SUM(c1) 30859 309SELECT SUM(c2) FROM t1; 310SUM(c2) 311113 312SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 313AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 3145.9000 11 1 1.0000 10 315SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 316AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 3175.9000 11 1 1.0000 10 318SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 319c1 c2 3206 1 3218 1 3229 1 32310 1 32411 100 325SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 326c1 c2 3276 1 3288 1 3299 1 33010 1 33111 100 332SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 333COUNT(c2) 3345 335SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 336COUNT(c2) 3375 338SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 339COUNT(*) 3405 341SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 342COUNT(*) 3435 344############################################### 345# 346# Test a lock wait timeout during COUNT(*) 347# 348SET innodb_lock_wait_timeout = 1; 349SELECT COUNT(*) FROM t1 FOR UPDATE; 350ERROR HY000: Lock wait timeout exceeded; try restarting transaction 351# 352# Connection 2 READ COMMITTED 353# 354SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 355BEGIN; 356########## innodb_isolation_selects.inc ########## 357SELECT * FROM t1; 358c1 c2 c3 c4 3591 1 1 1 3602 1 1 1 3613 1 1 1 3624 1 1 1 3635 1 1 1 3646 1 1 1 3657 1 1 1 3668 1 1 1 3679 1 1 1 36810 1 1 1 369SELECT COUNT(*) FROM t1; 370COUNT(*) 37110 372SELECT COUNT(*) FROM t3; 373COUNT(*) 37410 375SELECT COUNT(c1) FROM t1; 376COUNT(c1) 37710 378SELECT COUNT(c2) FROM t1; 379COUNT(c2) 38010 381SELECT COUNT(c3) FROM t1; 382COUNT(c3) 38310 384SELECT SUM(c1) FROM t1; 385SUM(c1) 38655 387SELECT SUM(c2) FROM t1; 388SUM(c2) 38910 390SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 391AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 3925.5000 10 1 1.0000 10 393SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 394AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 3955.5000 10 1 1.0000 10 396SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 397c1 c2 3986 1 3997 1 4008 1 4019 1 40210 1 403SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 404c1 c2 4056 1 4067 1 4078 1 4089 1 40910 1 410SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 411COUNT(c2) 4125 413SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 414COUNT(c2) 4155 416SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 417COUNT(*) 4185 419SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 420COUNT(*) 4215 422############################################### 423UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3; 424affected rows: 1 425info: Rows matched: 1 Changed: 1 Warnings: 0 426DELETE FROM t1 WHERE c1 = 8; 427affected rows: 1 428DELETE FROM t3 WHERE c1 = 8; 429affected rows: 1 430INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1); 431affected rows: 1 432INSERT INTO t3(c2,c3,c4) VALUES (1000, 1, 1); 433affected rows: 1 434########## innodb_isolation_selects.inc ########## 435SELECT * FROM t1; 436c1 c2 c3 c4 4371 1 1 1 4382 1 1 1 4393 7 1 1 4404 1 1 1 4415 1 1 1 4426 1 1 1 4437 1 1 1 4449 1 1 1 44510 1 1 1 44612 1000 1 1 447SELECT COUNT(*) FROM t1; 448COUNT(*) 44910 450SELECT COUNT(*) FROM t3; 451COUNT(*) 45210 453SELECT COUNT(c1) FROM t1; 454COUNT(c1) 45510 456SELECT COUNT(c2) FROM t1; 457COUNT(c2) 45810 459SELECT COUNT(c3) FROM t1; 460COUNT(c3) 46110 462SELECT SUM(c1) FROM t1; 463SUM(c1) 46459 465SELECT SUM(c2) FROM t1; 466SUM(c2) 4671015 468SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 469AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 4705.9000 12 1 1.0000 10 471SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 472AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 4735.9000 12 1 1.0000 10 474SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 475c1 c2 4766 1 4777 1 4789 1 47910 1 48012 1000 481SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 482c1 c2 4836 1 4847 1 4859 1 48610 1 48712 1000 488SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 489COUNT(c2) 4905 491SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 492COUNT(c2) 4935 494SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 495COUNT(*) 4965 497SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 498COUNT(*) 4995 500############################################### 501# 502# Connection 3 READ UNCOMMITTED 503# 504SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 505BEGIN; 506########## innodb_isolation_selects.inc ########## 507SELECT * FROM t1; 508c1 c2 c3 c4 5091 3 1 1 5102 5 1 1 5113 7 1 1 5124 1 1 1 5135 1 1 1 5149 1 1 1 51510 1 1 1 51611 100 1 1 51712 1000 1 1 518SELECT COUNT(*) FROM t1; 519COUNT(*) 5209 521SELECT COUNT(*) FROM t3; 522COUNT(*) 5239 524SELECT COUNT(c1) FROM t1; 525COUNT(c1) 5269 527SELECT COUNT(c2) FROM t1; 528COUNT(c2) 5299 530SELECT COUNT(c3) FROM t1; 531COUNT(c3) 5329 533SELECT SUM(c1) FROM t1; 534SUM(c1) 53557 536SELECT SUM(c2) FROM t1; 537SUM(c2) 5381119 539SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 540AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5416.3333 12 1 1.0000 9 542SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 543AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 5446.3333 12 1 1.0000 9 545SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 546c1 c2 5475 1 5489 1 54910 1 55011 100 55112 1000 552SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 553c1 c2 5545 1 5559 1 55610 1 55711 100 55812 1000 559SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 560COUNT(c2) 5615 562SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 563COUNT(c2) 5645 565SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 566COUNT(*) 5674 568SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 569COUNT(*) 5704 571############################################### 572UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4; 573affected rows: 1 574info: Rows matched: 1 Changed: 1 Warnings: 0 575UPDATE t3 SET c2 = c2 * 11 WHERE c1 = 4; 576affected rows: 1 577info: Rows matched: 1 Changed: 1 Warnings: 0 578INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1); 579affected rows: 1 580INSERT INTO t3(c2,c3,c4) VALUES (10000, 1, 1); 581affected rows: 1 582DELETE FROM t1 WHERE c1 in(9); 583affected rows: 1 584DELETE FROM t3 WHERE c1 in(9); 585affected rows: 1 586########## innodb_isolation_selects.inc ########## 587SELECT * FROM t1; 588c1 c2 c3 c4 5891 3 1 1 5902 5 1 1 5913 7 1 1 5924 11 1 1 5935 1 1 1 59410 1 1 1 59511 100 1 1 59612 1000 1 1 59713 10000 1 1 598SELECT COUNT(*) FROM t1; 599COUNT(*) 6009 601SELECT COUNT(*) FROM t3; 602COUNT(*) 6039 604SELECT COUNT(c1) FROM t1; 605COUNT(c1) 6069 607SELECT COUNT(c2) FROM t1; 608COUNT(c2) 6099 610SELECT COUNT(c3) FROM t1; 611COUNT(c3) 6129 613SELECT SUM(c1) FROM t1; 614SUM(c1) 61561 616SELECT SUM(c2) FROM t1; 617SUM(c2) 61811128 619SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 620AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6216.7778 13 1 1.0000 9 622SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 623AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6246.7778 13 1 1.0000 9 625SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 626c1 c2 6275 1 62810 1 62911 100 63012 1000 63113 10000 632SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 633c1 c2 6345 1 63510 1 63611 100 63712 1000 63813 10000 639SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 640COUNT(c2) 6415 642SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 643COUNT(c2) 6445 645SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 646COUNT(*) 6474 648SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 649COUNT(*) 6504 651############################################### 652# 653# Connection default REPEATABLE READ 654# 655########## innodb_isolation_selects.inc ########## 656SELECT * FROM t1; 657c1 c2 c3 c4 6581 3 1 1 6592 1 1 1 6603 1 1 1 6614 1 1 1 6625 1 1 1 6637 1 1 1 6648 1 1 1 6659 1 1 1 66610 1 1 1 667SELECT COUNT(*) FROM t1; 668COUNT(*) 6699 670SELECT COUNT(*) FROM t3; 671COUNT(*) 6729 673SELECT COUNT(c1) FROM t1; 674COUNT(c1) 6759 676SELECT COUNT(c2) FROM t1; 677COUNT(c2) 6789 679SELECT COUNT(c3) FROM t1; 680COUNT(c3) 6819 682SELECT SUM(c1) FROM t1; 683SUM(c1) 68449 685SELECT SUM(c2) FROM t1; 686SUM(c2) 68711 688SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 689AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6905.4444 10 1 1.0000 9 691SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 692AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 6935.4444 10 1 1.0000 9 694SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 695c1 c2 6965 1 6977 1 6988 1 6999 1 70010 1 701SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 702c1 c2 7035 1 7047 1 7058 1 7069 1 70710 1 708SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 709COUNT(c2) 7105 711SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 712COUNT(c2) 7135 714SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 715COUNT(*) 7164 717SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 718COUNT(*) 7194 720############################################### 721# 722# Commit the 3 extra connections 723# 724# Connection 1 REPEATABLE READ 725# 726########## innodb_isolation_selects.inc ########## 727SELECT * FROM t1; 728c1 c2 c3 c4 7291 1 1 1 7302 5 1 1 7313 1 1 1 7324 1 1 1 7335 1 1 1 7346 1 1 1 7358 1 1 1 7369 1 1 1 73710 1 1 1 73811 100 1 1 739SELECT COUNT(*) FROM t1; 740COUNT(*) 74110 742SELECT COUNT(*) FROM t3; 743COUNT(*) 74410 745SELECT COUNT(c1) FROM t1; 746COUNT(c1) 74710 748SELECT COUNT(c2) FROM t1; 749COUNT(c2) 75010 751SELECT COUNT(c3) FROM t1; 752COUNT(c3) 75310 754SELECT SUM(c1) FROM t1; 755SUM(c1) 75659 757SELECT SUM(c2) FROM t1; 758SUM(c2) 759113 760SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 761AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 7625.9000 11 1 1.0000 10 763SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 764AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 7655.9000 11 1 1.0000 10 766SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 767c1 c2 7686 1 7698 1 7709 1 77110 1 77211 100 773SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 774c1 c2 7756 1 7768 1 7779 1 77810 1 77911 100 780SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 781COUNT(c2) 7825 783SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 784COUNT(c2) 7855 786SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 787COUNT(*) 7885 789SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 790COUNT(*) 7915 792############################################### 793# 794# Connection 2 READ COMMITTED 795# 796########## innodb_isolation_selects.inc ########## 797SELECT * FROM t1; 798c1 c2 c3 c4 7991 1 1 1 8002 1 1 1 8013 7 1 1 8024 1 1 1 8035 1 1 1 8046 1 1 1 8057 1 1 1 8069 1 1 1 80710 1 1 1 80812 1000 1 1 809SELECT COUNT(*) FROM t1; 810COUNT(*) 81110 812SELECT COUNT(*) FROM t3; 813COUNT(*) 81410 815SELECT COUNT(c1) FROM t1; 816COUNT(c1) 81710 818SELECT COUNT(c2) FROM t1; 819COUNT(c2) 82010 821SELECT COUNT(c3) FROM t1; 822COUNT(c3) 82310 824SELECT SUM(c1) FROM t1; 825SUM(c1) 82659 827SELECT SUM(c2) FROM t1; 828SUM(c2) 8291015 830SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 831AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 8325.9000 12 1 1.0000 10 833SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 834AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 8355.9000 12 1 1.0000 10 836SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 837c1 c2 8386 1 8397 1 8409 1 84110 1 84212 1000 843SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 844c1 c2 8456 1 8467 1 8479 1 84810 1 84912 1000 850SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 851COUNT(c2) 8525 853SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 854COUNT(c2) 8555 856SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 857COUNT(*) 8585 859SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 860COUNT(*) 8615 862############################################### 863# 864# Connection 3 READ UNCOMMITTED 865# 866########## innodb_isolation_selects.inc ########## 867SELECT * FROM t1; 868c1 c2 c3 c4 8691 3 1 1 8702 5 1 1 8713 7 1 1 8724 11 1 1 8735 1 1 1 87410 1 1 1 87511 100 1 1 87612 1000 1 1 87713 10000 1 1 878SELECT COUNT(*) FROM t1; 879COUNT(*) 8809 881SELECT COUNT(*) FROM t3; 882COUNT(*) 8839 884SELECT COUNT(c1) FROM t1; 885COUNT(c1) 8869 887SELECT COUNT(c2) FROM t1; 888COUNT(c2) 8899 890SELECT COUNT(c3) FROM t1; 891COUNT(c3) 8929 893SELECT SUM(c1) FROM t1; 894SUM(c1) 89561 896SELECT SUM(c2) FROM t1; 897SUM(c2) 89811128 899SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 900AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 9016.7778 13 1 1.0000 9 902SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 903AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 9046.7778 13 1 1.0000 9 905SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 906c1 c2 9075 1 90810 1 90911 100 91012 1000 91113 10000 912SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 913c1 c2 9145 1 91510 1 91611 100 91712 1000 91813 10000 919SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 920COUNT(c2) 9215 922SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 923COUNT(c2) 9245 925SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 926COUNT(*) 9274 928SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 929COUNT(*) 9304 931############################################### 932COMMIT; 933########## innodb_isolation_selects.inc ########## 934SELECT * FROM t1; 935c1 c2 c3 c4 9361 3 1 1 9372 5 1 1 9383 7 1 1 9394 11 1 1 9405 1 1 1 94110 1 1 1 94211 100 1 1 94312 1000 1 1 94413 10000 1 1 945SELECT COUNT(*) FROM t1; 946COUNT(*) 9479 948SELECT COUNT(*) FROM t3; 949COUNT(*) 9509 951SELECT COUNT(c1) FROM t1; 952COUNT(c1) 9539 954SELECT COUNT(c2) FROM t1; 955COUNT(c2) 9569 957SELECT COUNT(c3) FROM t1; 958COUNT(c3) 9599 960SELECT SUM(c1) FROM t1; 961SUM(c1) 96261 963SELECT SUM(c2) FROM t1; 964SUM(c2) 96511128 966SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 967AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 9686.7778 13 1 1.0000 9 969SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 970AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 9716.7778 13 1 1.0000 9 972SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 973c1 c2 9745 1 97510 1 97611 100 97712 1000 97813 10000 979SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 980c1 c2 9815 1 98210 1 98311 100 98412 1000 98513 10000 986SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 987COUNT(c2) 9885 989SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 990COUNT(c2) 9915 992SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 993COUNT(*) 9944 995SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 996COUNT(*) 9974 998############################################### 999# 1000# Connection 2 READ COMMITTED 1001# 1002########## innodb_isolation_selects.inc ########## 1003SELECT * FROM t1; 1004c1 c2 c3 c4 10051 1 1 1 10062 1 1 1 10073 7 1 1 10084 11 1 1 10095 1 1 1 10106 1 1 1 10117 1 1 1 101210 1 1 1 101312 1000 1 1 101413 10000 1 1 1015SELECT COUNT(*) FROM t1; 1016COUNT(*) 101710 1018SELECT COUNT(*) FROM t3; 1019COUNT(*) 102010 1021SELECT COUNT(c1) FROM t1; 1022COUNT(c1) 102310 1024SELECT COUNT(c2) FROM t1; 1025COUNT(c2) 102610 1027SELECT COUNT(c3) FROM t1; 1028COUNT(c3) 102910 1030SELECT SUM(c1) FROM t1; 1031SUM(c1) 103263 1033SELECT SUM(c2) FROM t1; 1034SUM(c2) 103511024 1036SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1037AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 10386.3000 13 1 1.0000 10 1039SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1040AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 10416.3000 13 1 1.0000 10 1042SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1043c1 c2 10446 1 10457 1 104610 1 104712 1000 104813 10000 1049SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1050c1 c2 10516 1 10527 1 105310 1 105412 1000 105513 10000 1056SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1057COUNT(c2) 10585 1059SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1060COUNT(c2) 10615 1062SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1063COUNT(*) 10644 1065SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1066COUNT(*) 10674 1068############################################### 1069COMMIT; 1070########## innodb_isolation_selects.inc ########## 1071SELECT * FROM t1; 1072c1 c2 c3 c4 10731 1 1 1 10742 1 1 1 10753 7 1 1 10764 11 1 1 10775 1 1 1 10786 1 1 1 10797 1 1 1 108010 1 1 1 108112 1000 1 1 108213 10000 1 1 1083SELECT COUNT(*) FROM t1; 1084COUNT(*) 108510 1086SELECT COUNT(*) FROM t3; 1087COUNT(*) 108810 1089SELECT COUNT(c1) FROM t1; 1090COUNT(c1) 109110 1092SELECT COUNT(c2) FROM t1; 1093COUNT(c2) 109410 1095SELECT COUNT(c3) FROM t1; 1096COUNT(c3) 109710 1098SELECT SUM(c1) FROM t1; 1099SUM(c1) 110063 1101SELECT SUM(c2) FROM t1; 1102SUM(c2) 110311024 1104SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1105AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 11066.3000 13 1 1.0000 10 1107SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1108AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 11096.3000 13 1 1.0000 10 1110SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1111c1 c2 11126 1 11137 1 111410 1 111512 1000 111613 10000 1117SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1118c1 c2 11196 1 11207 1 112110 1 112212 1000 112313 10000 1124SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1125COUNT(c2) 11265 1127SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1128COUNT(c2) 11295 1130SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1131COUNT(*) 11324 1133SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1134COUNT(*) 11354 1136############################################### 1137# 1138# Connection 1 REPEATABLE READ 1139# 1140########## innodb_isolation_selects.inc ########## 1141SELECT * FROM t1; 1142c1 c2 c3 c4 11431 1 1 1 11442 5 1 1 11453 1 1 1 11464 1 1 1 11475 1 1 1 11486 1 1 1 11498 1 1 1 11509 1 1 1 115110 1 1 1 115211 100 1 1 1153SELECT COUNT(*) FROM t1; 1154COUNT(*) 115510 1156SELECT COUNT(*) FROM t3; 1157COUNT(*) 115810 1159SELECT COUNT(c1) FROM t1; 1160COUNT(c1) 116110 1162SELECT COUNT(c2) FROM t1; 1163COUNT(c2) 116410 1165SELECT COUNT(c3) FROM t1; 1166COUNT(c3) 116710 1168SELECT SUM(c1) FROM t1; 1169SUM(c1) 117059 1171SELECT SUM(c2) FROM t1; 1172SUM(c2) 1173113 1174SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1175AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 11765.9000 11 1 1.0000 10 1177SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1178AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 11795.9000 11 1 1.0000 10 1180SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1181c1 c2 11826 1 11838 1 11849 1 118510 1 118611 100 1187SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1188c1 c2 11896 1 11908 1 11919 1 119210 1 119311 100 1194SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1195COUNT(c2) 11965 1197SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1198COUNT(c2) 11995 1200SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1201COUNT(*) 12025 1203SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1204COUNT(*) 12055 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# 1211SET innodb_lock_wait_timeout = 1; 1212SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE; 1213ERROR HY000: Lock wait timeout exceeded; try restarting transaction 1214SELECT COUNT(*) FROM t1 FOR UPDATE; 1215ERROR HY000: Lock wait timeout exceeded; try restarting transaction 1216COMMIT; 1217########## innodb_isolation_selects.inc ########## 1218SELECT * FROM t1; 1219c1 c2 c3 c4 12201 1 1 1 12212 5 1 1 12223 7 1 1 12234 11 1 1 12245 1 1 1 12256 1 1 1 122610 1 1 1 122711 100 1 1 122812 1000 1 1 122913 10000 1 1 1230SELECT COUNT(*) FROM t1; 1231COUNT(*) 123210 1233SELECT COUNT(*) FROM t3; 1234COUNT(*) 123510 1236SELECT COUNT(c1) FROM t1; 1237COUNT(c1) 123810 1239SELECT COUNT(c2) FROM t1; 1240COUNT(c2) 124110 1242SELECT COUNT(c3) FROM t1; 1243COUNT(c3) 124410 1245SELECT SUM(c1) FROM t1; 1246SUM(c1) 124767 1248SELECT SUM(c2) FROM t1; 1249SUM(c2) 125011127 1251SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1252AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 12536.7000 13 1 1.0000 10 1254SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1255AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 12566.7000 13 1 1.0000 10 1257SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1258c1 c2 12596 1 126010 1 126111 100 126212 1000 126313 10000 1264SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1265c1 c2 12666 1 126710 1 126811 100 126912 1000 127013 10000 1271SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1272COUNT(c2) 12735 1274SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1275COUNT(c2) 12765 1277SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1278COUNT(*) 12794 1280SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1281COUNT(*) 12824 1283############################################### 1284# 1285# Show The EXPLAIN output for these queries; 1286# 1287ANALYZE TABLE t1; 1288Table Op Msg_type Msg_text 1289test.t1 analyze status OK 1290ANALYZE TABLE t2; 1291Table Op Msg_type Msg_text 1292test.t2 analyze status OK 1293ANALYZE TABLE t3; 1294Table Op Msg_type Msg_text 1295test.t3 analyze status OK 1296EXPLAIN SELECT * FROM t1; 1297id select_type table partitions type possible_keys key key_len ref rows filtered Extra 12981 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL 1299Warnings: 1300Note 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` 1301EXPLAIN SELECT COUNT(*) FROM t1; 1302id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13031 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index 1304Warnings: 1305Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` 1306EXPLAIN SELECT COUNT(*) FROM t3; 1307id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13081 SIMPLE t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index 1309Warnings: 1310Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t3` 1311EXPLAIN SELECT COUNT(c1) FROM t1; 1312id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13131 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index 1314Warnings: 1315Note 1003 /* select#1 */ select count(`test`.`t1`.`c1`) AS `COUNT(c1)` from `test`.`t1` 1316EXPLAIN SELECT COUNT(c2) FROM t1; 1317id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13181 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index 1319Warnings: 1320Note 1003 /* select#1 */ select count(`test`.`t1`.`c2`) AS `COUNT(c2)` from `test`.`t1` 1321EXPLAIN SELECT COUNT(c3) FROM t1; 1322id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13231 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL 1324Warnings: 1325Note 1003 /* select#1 */ select count(`test`.`t1`.`c3`) AS `COUNT(c3)` from `test`.`t1` 1326EXPLAIN SELECT SUM(c1) FROM t1; 1327id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13281 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index 1329Warnings: 1330Note 1003 /* select#1 */ select sum(`test`.`t1`.`c1`) AS `SUM(c1)` from `test`.`t1` 1331EXPLAIN SELECT SUM(c2) FROM t1; 1332id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13331 SIMPLE t1 NULL index NULL k2 5 NULL # 100.00 Using index 1334Warnings: 1335Note 1003 /* select#1 */ select sum(`test`.`t1`.`c2`) AS `SUM(c2)` from `test`.`t1` 1336EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1337id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13381 SIMPLE t1 NULL ALL NULL NULL NULL NULL # 100.00 NULL 1339Warnings: 1340Note 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` 1341EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1342id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13431 SIMPLE t3 p0,p1,p2,p3 ALL NULL NULL NULL NULL # 100.00 NULL 1344Warnings: 1345Note 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` 1346EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1347id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13481 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 13492 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index 1350Warnings: 1351Note 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))) 1352EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1353id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13541 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 13552 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index 1356Warnings: 1357Note 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))) 1358EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1359id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13601 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 13612 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index 1362Warnings: 1363Note 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))) 1364EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1365id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13661 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where 13672 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index 1368Warnings: 1369Note 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))) 1370EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1371id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13721 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where; Using index 13732 SUBQUERY t1 NULL index NULL k2 5 NULL # 100.00 Using index 1374Warnings: 1375Note 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`)) 1376EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1377id select_type table partitions type possible_keys key key_len ref rows filtered Extra 13781 PRIMARY t1 NULL range PRIMARY PRIMARY 4 NULL # 100.00 Using where; Using index 13792 SUBQUERY t3 p0,p1,p2,p3 index NULL k2 5 NULL # 100.00 Using index 1380Warnings: 1381Note 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`)) 1382# 1383# Make all indexes in t2 obsolete to the active repeatable read transaction 1384# in the default connection. 1385# 1386ALTER TABLE t2 row_format=redundant; 1387# 1388# Connection default REPEATABLE READ 1389# Do more DML in the default REPEATABLE READ transaction in order to use recently committed records. 1390# 1391########## innodb_isolation_selects.inc ########## 1392SELECT * FROM t1; 1393c1 c2 c3 c4 13941 3 1 1 13952 1 1 1 13963 1 1 1 13974 1 1 1 13985 1 1 1 13997 1 1 1 14008 1 1 1 14019 1 1 1 140210 1 1 1 1403SELECT COUNT(*) FROM t1; 1404COUNT(*) 14059 1406SELECT COUNT(*) FROM t3; 1407COUNT(*) 14089 1409SELECT COUNT(c1) FROM t1; 1410COUNT(c1) 14119 1412SELECT COUNT(c2) FROM t1; 1413COUNT(c2) 14149 1415SELECT COUNT(c3) FROM t1; 1416COUNT(c3) 14179 1418SELECT SUM(c1) FROM t1; 1419SUM(c1) 142049 1421SELECT SUM(c2) FROM t1; 1422SUM(c2) 142311 1424SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1425AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 14265.4444 10 1 1.0000 9 1427SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1428AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 14295.4444 10 1 1.0000 9 1430SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1431c1 c2 14325 1 14337 1 14348 1 14359 1 143610 1 1437SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1438c1 c2 14395 1 14407 1 14418 1 14429 1 144310 1 1444SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1445COUNT(c2) 14465 1447SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1448COUNT(c2) 14495 1450SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1451COUNT(*) 14524 1453SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1454COUNT(*) 14554 1456############################################### 1457UPDATE t1 SET c4 = c2 * 10; 1458affected rows: 9 1459info: Rows matched: 9 Changed: 9 Warnings: 0 1460UPDATE t3 SET c4 = c2 * 10; 1461affected rows: 9 1462info: Rows matched: 9 Changed: 9 Warnings: 0 1463########## innodb_isolation_selects.inc ########## 1464SELECT * FROM t1; 1465c1 c2 c3 c4 14661 3 1 30 14672 5 1 50 14683 7 1 70 14694 11 1 110 14705 1 1 10 14717 1 1 1 14728 1 1 1 14739 1 1 1 147410 1 1 10 147511 100 1 1000 147612 1000 1 10000 147713 10000 1 100000 1478SELECT COUNT(*) FROM t1; 1479COUNT(*) 148012 1481SELECT COUNT(*) FROM t3; 1482COUNT(*) 148312 1484SELECT COUNT(c1) FROM t1; 1485COUNT(c1) 148612 1487SELECT COUNT(c2) FROM t1; 1488COUNT(c2) 148912 1490SELECT COUNT(c3) FROM t1; 1491COUNT(c3) 149212 1493SELECT SUM(c1) FROM t1; 1494SUM(c1) 149585 1496SELECT SUM(c2) FROM t1; 1497SUM(c2) 149811131 1499SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1500AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 15017.0833 13 1 1.0000 111283 1502SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1503AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 15047.0833 13 1 1.0000 111223 1505SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1506c1 c2 15077 1 15088 1 15099 1 151010 1 151111 100 151212 1000 151313 10000 1514SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1515c1 c2 15167 1 15178 1 15189 1 151910 1 152011 100 152112 1000 152213 10000 1523SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1524COUNT(c2) 15257 1526SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1527COUNT(c2) 15287 1529SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1530COUNT(*) 15316 1532SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1533COUNT(*) 15346 1535############################################### 1536# 1537# Table t2 has been altered to a new row format. 1538# The index should not be useable. 1539# 1540SELECT COUNT(*) FROM t2; 1541ERROR HY000: Table definition has changed, please retry transaction 1542SELECT * FROM t2; 1543ERROR HY000: Table definition has changed, please retry transaction 1544COMMIT; 1545SELECT COUNT(*) FROM t2; 1546COUNT(*) 154710 1548########## innodb_isolation_selects.inc ########## 1549SELECT * FROM t1; 1550c1 c2 c3 c4 15511 3 1 30 15522 5 1 50 15533 7 1 70 15544 11 1 110 15555 1 1 10 155610 1 1 10 155711 100 1 1000 155812 1000 1 10000 155913 10000 1 100000 1560SELECT COUNT(*) FROM t1; 1561COUNT(*) 15629 1563SELECT COUNT(*) FROM t3; 1564COUNT(*) 15659 1566SELECT COUNT(c1) FROM t1; 1567COUNT(c1) 15689 1569SELECT COUNT(c2) FROM t1; 1570COUNT(c2) 15719 1572SELECT COUNT(c3) FROM t1; 1573COUNT(c3) 15749 1575SELECT SUM(c1) FROM t1; 1576SUM(c1) 157761 1578SELECT SUM(c2) FROM t1; 1579SUM(c2) 158011128 1581SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1582AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 15836.7778 13 1 1.0000 111280 1584SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1585AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 15866.7778 13 1 1.0000 111220 1587SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1588c1 c2 15895 1 159010 1 159111 100 159212 1000 159313 10000 1594SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1595c1 c2 15965 1 159710 1 159811 100 159912 1000 160013 10000 1601SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1602COUNT(c2) 16035 1604SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1605COUNT(c2) 16065 1607SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1608COUNT(*) 16094 1610SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1611COUNT(*) 16124 1613############################################### 1614# 1615# Connection 2 1616# 1617########## innodb_isolation_selects.inc ########## 1618SELECT * FROM t1; 1619c1 c2 c3 c4 16201 3 1 30 16212 5 1 50 16223 7 1 70 16234 11 1 110 16245 1 1 10 162510 1 1 10 162611 100 1 1000 162712 1000 1 10000 162813 10000 1 100000 1629SELECT COUNT(*) FROM t1; 1630COUNT(*) 16319 1632SELECT COUNT(*) FROM t3; 1633COUNT(*) 16349 1635SELECT COUNT(c1) FROM t1; 1636COUNT(c1) 16379 1638SELECT COUNT(c2) FROM t1; 1639COUNT(c2) 16409 1641SELECT COUNT(c3) FROM t1; 1642COUNT(c3) 16439 1644SELECT SUM(c1) FROM t1; 1645SUM(c1) 164661 1647SELECT SUM(c2) FROM t1; 1648SUM(c2) 164911128 1650SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; 1651AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 16526.7778 13 1 1.0000 111280 1653SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t3; 1654AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) 16556.7778 13 1 1.0000 111220 1656SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1657c1 c2 16585 1 165910 1 166011 100 166112 1000 166213 10000 1663SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1664c1 c2 16655 1 166610 1 166711 100 166812 1000 166913 10000 1670SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); 1671COUNT(c2) 16725 1673SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t3) / 2); 1674COUNT(c2) 16755 1676SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); 1677COUNT(*) 16784 1679SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t3); 1680COUNT(*) 16814 1682############################################### 1683# 1684# Try COUNT(*) on a DISCARDED table. 1685# 1686CREATE TABLE t4 LIKE t1; 1687INSERT INTO t4 (SELECT * FROM t1); 1688SELECT COUNT(*) FROM t4; 1689COUNT(*) 16909 1691ALTER TABLE t4 DISCARD TABLESPACE; 1692SELECT COUNT(*) FROM t4; 1693ERROR HY000: Tablespace has been discarded for table 't4' 1694# 1695# Test the interaction of a repeatable read transaction 1696# to changes that happen outside its view. 1697# 1698CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; 1699INSERT INTO t5(b) VALUES ("inserted by client 1"); 1700INSERT INTO t5(b) VALUES ("inserted by client 1"); 1701INSERT INTO t5(b) VALUES ("inserted by client 1"); 1702INSERT INTO t5(b) VALUES ("inserted by client 1"); 1703UPDATE t5 SET aa=a, bb=b; 1704CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; 1705INSERT INTO t6(b) VALUES ("inserted by client 1"); 1706INSERT INTO t6(b) VALUES ("inserted by client 1"); 1707INSERT INTO t6(b) VALUES ("inserted by client 1"); 1708INSERT INTO t6(b) VALUES ("inserted by client 1"); 1709UPDATE t6 SET aa=a, bb=b; 1710CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; 1711INSERT INTO t7(b) VALUES ("inserted by client 1"); 1712INSERT INTO t7(b) VALUES ("inserted by client 1"); 1713INSERT INTO t7(b) VALUES ("inserted by client 1"); 1714INSERT INTO t7(b) VALUES ("inserted by client 1"); 1715UPDATE t7 SET aa=a, bb=b; 1716BEGIN; 1717SELECT * FROM t5; 1718a b aa bb 17191 inserted by client 1 1 inserted by client 1 17202 inserted by client 1 2 inserted by client 1 17213 inserted by client 1 3 inserted by client 1 17224 inserted by client 1 4 inserted by client 1 1723SELECT COUNT(*) FROM t5; 1724COUNT(*) 17254 1726SELECT * FROM t6; 1727a b aa bb 17281 inserted by client 1 1 inserted by client 1 17292 inserted by client 1 2 inserted by client 1 17303 inserted by client 1 3 inserted by client 1 17314 inserted by client 1 4 inserted by client 1 1732SELECT COUNT(*) FROM t6; 1733COUNT(*) 17344 1735SELECT * FROM t7; 1736a b aa bb 17371 inserted by client 1 1 inserted by client 1 17382 inserted by client 1 2 inserted by client 1 17393 inserted by client 1 3 inserted by client 1 17404 inserted by client 1 4 inserted by client 1 1741SELECT COUNT(*) FROM t7; 1742COUNT(*) 17434 1744# 1745# Connection 1 1746# 1747INSERT INTO t5(b) VALUES ("inserted by client 2"); 1748UPDATE t5 SET a = 10 where a = 1; 1749UPDATE t5 SET b = "updated by client 2" where a = 2; 1750DELETE FROM t5 WHERE a = 3; 1751SELECT * FROM t5; 1752a b aa bb 17532 updated by client 2 2 inserted by client 1 17544 inserted by client 1 4 inserted by client 1 17555 inserted by client 2 NULL NULL 175610 inserted by client 1 1 inserted by client 1 1757SELECT COUNT(*) FROM t5; 1758COUNT(*) 17594 1760INSERT INTO t6(b) VALUES ("inserted by client 2"); 1761UPDATE t6 SET a = 10 where a = 1; 1762UPDATE t6 SET b = "updated by client 2" where a = 2; 1763DELETE FROM t6 WHERE a = 3; 1764SELECT * FROM t6; 1765a b aa bb 17662 updated by client 2 2 inserted by client 1 17674 inserted by client 1 4 inserted by client 1 17685 inserted by client 2 NULL NULL 176910 inserted by client 1 1 inserted by client 1 1770SELECT COUNT(*) FROM t6; 1771COUNT(*) 17724 1773INSERT INTO t7(b) VALUES ("inserted by client 2"); 1774UPDATE t7 SET a = 10 where a = 1; 1775UPDATE t7 SET b = "updated by client 2" where a = 2; 1776DELETE FROM t7 WHERE a = 3; 1777SELECT * FROM t7; 1778a b aa bb 17792 updated by client 2 2 inserted by client 1 17804 inserted by client 1 4 inserted by client 1 17815 inserted by client 2 NULL NULL 178210 inserted by client 1 1 inserted by client 1 1783SELECT COUNT(*) FROM t7; 1784COUNT(*) 17854 1786# 1787# Connection default 1788# 1789SELECT * FROM t5; 1790a b aa bb 17911 inserted by client 1 1 inserted by client 1 17922 inserted by client 1 2 inserted by client 1 17933 inserted by client 1 3 inserted by client 1 17944 inserted by client 1 4 inserted by client 1 1795INSERT INTO t5(b) VALUES ("inserted by client 1"); 1796SELECT * FROM t5; 1797a b aa bb 17981 inserted by client 1 1 inserted by client 1 17992 inserted by client 1 2 inserted by client 1 18003 inserted by client 1 3 inserted by client 1 18014 inserted by client 1 4 inserted by client 1 18026 inserted by client 1 NULL NULL 1803UPDATE t5 SET a = a + 100; 1804SELECT * FROM t5; 1805a b aa bb 18061 inserted by client 1 1 inserted by client 1 18073 inserted by client 1 3 inserted by client 1 1808102 updated by client 2 2 inserted by client 1 1809104 inserted by client 1 4 inserted by client 1 1810105 inserted by client 2 NULL NULL 1811106 inserted by client 1 NULL NULL 1812110 inserted by client 1 1 inserted by client 1 1813SELECT COUNT(*) FROM t5; 1814COUNT(*) 18157 1816UPDATE t6 SET b = "updated by client 2"; 1817SELECT * FROM t6; 1818a b aa bb 18191 inserted by client 1 1 inserted by client 1 18202 updated by client 2 2 inserted by client 1 18213 inserted by client 1 3 inserted by client 1 18224 updated by client 2 4 inserted by client 1 18235 updated by client 2 NULL NULL 182410 updated by client 2 1 inserted by client 1 1825SELECT COUNT(*) FROM t6; 1826COUNT(*) 18276 1828DELETE FROM t7; 1829SELECT * FROM t7; 1830a b aa bb 18311 inserted by client 1 1 inserted by client 1 18323 inserted by client 1 3 inserted by client 1 1833SELECT COUNT(*) FROM t7; 1834COUNT(*) 18352 1836COMMIT; 1837SELECT * FROM t5; 1838a b aa bb 1839102 updated by client 2 2 inserted by client 1 1840104 inserted by client 1 4 inserted by client 1 1841105 inserted by client 2 NULL NULL 1842106 inserted by client 1 NULL NULL 1843110 inserted by client 1 1 inserted by client 1 1844SELECT COUNT(*) FROM t5; 1845COUNT(*) 18465 1847SELECT * FROM t6; 1848a b aa bb 18492 updated by client 2 2 inserted by client 1 18504 updated by client 2 4 inserted by client 1 18515 updated by client 2 NULL NULL 185210 updated by client 2 1 inserted by client 1 1853SELECT COUNT(*) FROM t6; 1854COUNT(*) 18554 1856SELECT * FROM t7; 1857a b aa bb 1858SELECT COUNT(*) FROM t7; 1859COUNT(*) 18600 1861# 1862# Cleanup 1863# 1864DROP TABLE t1,t2,t3,t4,t5,t6,t7; 1865# 1866# Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*) 1867# 1868CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, 1869c2 INT NOT NULL DEFAULT 1, 1870c3 char(20) DEFAULT '', 1871KEY c2_idx (c2)); 1872INSERT INTO t1(c1) VALUES (1), (2), (3); 1873INSERT INTO t1(c1) SELECT c1 + 10 FROM t1; 1874INSERT INTO t1(c1) SELECT c1 + 100 FROM t1; 1875CREATE TABLE t2 SELECT * FROM t1; 1876ANALYZE TABLE t1; 1877Table Op Msg_type Msg_text 1878test.t1 analyze status OK 1879ANALYZE TABLE t2; 1880Table Op Msg_type Msg_text 1881test.t2 analyze status OK 1882EXPLAIN SELECT COUNT(*) FROM t1; 1883id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18841 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 1885Warnings: 1886Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` 1887EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx); 1888id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18891 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 1890Warnings: 1891Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`) 1892EXPLAIN SELECT COUNT(*) FROM t1, t2; 1893id select_type table partitions type possible_keys key key_len ref rows filtered Extra 18941 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 18951 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 Using join buffer (Block Nested Loop) 1896Warnings: 1897Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` join `test`.`t2` 1898EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2; 1899id select_type table partitions type possible_keys key key_len ref rows filtered Extra 19001 SIMPLE t1 NULL index NULL c2_idx 4 NULL # 100.00 Using index 19011 SIMPLE t2 NULL ALL NULL NULL NULL NULL # 100.00 Using join buffer (Block Nested Loop) 1902Warnings: 1903Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t1` FORCE INDEX (`c2_idx`) join `test`.`t2` 1904DROP TABLE t1, t2; 1905