diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-isolation.result | 1417 |
1 files changed, 1417 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-isolation.result b/mysql-test/suite/innodb/r/innodb-isolation.result new file mode 100644 index 00000000..b6e512cc --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-isolation.result @@ -0,0 +1,1417 @@ +# +# WL#6742 - Test the interaction of multiple transactions using +# different isolation levels to make sure that the value returned +# by count(*) always reflects the correct view of the table according +# to the transaction's selected isolation level. +# +# +# Traverse various indexes to get the right counts. +# This especially tests count(*) which is pushed down to InnoDB in WL#6742. +# +CREATE TABLE t1 ( +c1 INT AUTO_INCREMENT PRIMARY KEY, +c2 INT, +c3 INT, +c4 INT, +INDEX k2(c2) +) Engine=InnoDB; +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 (SELECT * FROM t1); +affected rows: 10 +info: Records: 10 Duplicates: 0 Warnings: 0 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Do some DML in the default connection and leave the transaction pending. +# +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 6; +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Start transactions of Repeatable Read, Read Committed, and Read Uncommitted +# +# Connection 1 REPEATABLE READ +# +connect con1,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 7; +affected rows: 1 +INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Test a lock wait timeout during COUNT(*) +# +SET innodb_lock_wait_timeout = 1; +SELECT COUNT(*) FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +# +# Connection 2 READ COMMITTED +# +connect con2,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +55 +SELECT SUM(c2) FROM t1; +SUM(c2) +10 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.5000 10 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +DELETE FROM t1 WHERE c1 = 8; +affected rows: 1 +INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +9 1 1 1 +10 1 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +1015 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 12 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +9 1 +10 1 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 3 READ UNCOMMITTED +# +connect con3,localhost,root,,; +SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +BEGIN; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +57 +SELECT SUM(c2) FROM t1; +SUM(c2) +1119 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3333 12 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +9 1 +10 1 +11 100 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4; +affected rows: 1 +info: Rows matched: 1 Changed: 1 Warnings: 0 +INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1); +affected rows: 1 +DELETE FROM t1 WHERE c1 in(9); +affected rows: 1 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection default REPEATABLE READ +# +connection default; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Commit the 3 extra connections +# +# Connection 1 REPEATABLE READ +# +connection con1; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 2 READ COMMITTED +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +9 1 1 1 +10 1 1 1 +12 1000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +1015 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 12 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +9 1 +10 1 +12 1000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Connection 3 READ UNCOMMITTED +# +connection con3; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 2 READ COMMITTED +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +10 1 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +63 +SELECT SUM(c2) FROM t1; +SUM(c2) +11024 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +10 1 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 1 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +7 1 1 1 +10 1 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +63 +SELECT SUM(c2) FROM t1; +SUM(c2) +11024 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.3000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +7 1 +10 1 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 1 REPEATABLE READ +# +connection con1; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +6 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +11 100 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +59 +SELECT SUM(c2) FROM t1; +SUM(c2) +113 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.9000 11 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +8 1 +9 1 +10 1 +11 100 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +5 +############################################### +# +# Select the first 5 records FOR UPDATE using count(*) in a subquery. +# The second record is still pending so we get a lock timeout. +# +SET innodb_lock_wait_timeout = 1; +SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +SELECT COUNT(*) FROM t1 FOR UPDATE; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +COMMIT; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 1 1 1 +2 5 1 1 +3 7 1 1 +4 11 1 1 +5 1 1 1 +6 1 1 1 +10 1 1 1 +11 100 1 1 +12 1000 1 1 +13 10000 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +10 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +10 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +10 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +10 +SELECT SUM(c1) FROM t1; +SUM(c1) +67 +SELECT SUM(c2) FROM t1; +SUM(c2) +11127 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7000 13 1 1.0000 10 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +6 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Show The EXPLAIN output for these queries; +# +EXPLAIN SELECT * FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # +EXPLAIN SELECT COUNT(*) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT COUNT(c1) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT COUNT(c2) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT COUNT(c3) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # +EXPLAIN SELECT SUM(c1) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT SUM(c2) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL # +EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where +2 SUBQUERY t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where +2 SUBQUERY t1 index NULL k2 5 NULL # Using index +EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL # Using where; Using index +2 SUBQUERY t1 index NULL k2 5 NULL # Using index +# +# Make all indexes in t2 obsolete to the active repeatable read transaction +# in the default connection. +# +ALTER TABLE t2 row_format=redundant; +# +# Connection default REPEATABLE READ +# Do more DML in the default REPEATABLE READ transaction in order to use recently committed records. +# +connection default; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 1 +2 1 1 1 +3 1 1 1 +4 1 1 1 +5 1 1 1 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 1 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +49 +SELECT SUM(c2) FROM t1; +SUM(c2) +11 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +5.4444 10 1 1.0000 9 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +7 1 +8 1 +9 1 +10 1 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +UPDATE t1 SET c4 = c2 * 10; +affected rows: 9 +info: Rows matched: 9 Changed: 9 Warnings: 0 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +7 1 1 1 +8 1 1 1 +9 1 1 1 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +12 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +12 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +12 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +12 +SELECT SUM(c1) FROM t1; +SUM(c1) +85 +SELECT SUM(c2) FROM t1; +SUM(c2) +11131 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +7.0833 13 1 1.0000 111283 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +7 1 +8 1 +9 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +7 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +6 +############################################### +# +# Table t2 has been altered to a new row format. +# The index should not be useable. +# +SELECT COUNT(*) FROM t2; +ERROR HY000: Table definition has changed, please retry transaction +SELECT * FROM t2; +ERROR HY000: Table definition has changed, please retry transaction +COMMIT; +SELECT COUNT(*) FROM t2; +COUNT(*) +10 +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 111280 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Connection 2 +# +connection con2; +########## innodb_isolation_selects.inc ########## +SELECT * FROM t1; +c1 c2 c3 c4 +1 3 1 30 +2 5 1 50 +3 7 1 70 +4 11 1 110 +5 1 1 10 +10 1 1 10 +11 100 1 1000 +12 1000 1 10000 +13 10000 1 100000 +SELECT COUNT(*) FROM t1; +COUNT(*) +9 +SELECT COUNT(c1) FROM t1; +COUNT(c1) +9 +SELECT COUNT(c2) FROM t1; +COUNT(c2) +9 +SELECT COUNT(c3) FROM t1; +COUNT(c3) +9 +SELECT SUM(c1) FROM t1; +SUM(c1) +61 +SELECT SUM(c2) FROM t1; +SUM(c2) +11128 +SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; +AVG(c1) MAX(c1) MIN(c2) AVG(c3) SUM(c4) +6.7778 13 1 1.0000 111280 +SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +c1 c2 +5 1 +10 1 +11 100 +12 1000 +13 10000 +SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2); +COUNT(c2) +5 +SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1); +COUNT(*) +4 +############################################### +# +# Try COUNT(*) on a DISCARDED table. +# +connection default; +CREATE TABLE t4 LIKE t1; +INSERT INTO t4 (SELECT * FROM t1); +SELECT COUNT(*) FROM t4; +COUNT(*) +9 +ALTER TABLE t4 DISCARD TABLESPACE; +SELECT COUNT(*) FROM t4; +ERROR HY000: Tablespace has been discarded for table `t4` +# +# Test the interaction of a repeatable read transaction +# to changes that happen outside its view. +# +CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +INSERT INTO t5(b) VALUES ("inserted by client 1"); +UPDATE t5 SET aa=a, bb=b; +CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +INSERT INTO t6(b) VALUES ("inserted by client 1"); +UPDATE t6 SET aa=a, bb=b; +CREATE TABLE t7 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, aa INT, bb TEXT) Engine=InnoDB; +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +INSERT INTO t7(b) VALUES ("inserted by client 1"); +UPDATE t7 SET aa=a, bb=b; +BEGIN; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t5; +COUNT(*) +4 +SELECT * FROM t6; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +SELECT * FROM t7; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +4 +# +# Connection 1 +# +connection con1; +INSERT INTO t5(b) VALUES ("inserted by client 2"); +UPDATE t5 SET a = 10 where a = 1; +UPDATE t5 SET b = "updated by client 2" where a = 2; +DELETE FROM t5 WHERE a = 3; +SELECT * FROM t5; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t5; +COUNT(*) +4 +INSERT INTO t6(b) VALUES ("inserted by client 2"); +UPDATE t6 SET a = 10 where a = 1; +UPDATE t6 SET b = "updated by client 2" where a = 2; +DELETE FROM t6 WHERE a = 3; +SELECT * FROM t6; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +INSERT INTO t7(b) VALUES ("inserted by client 2"); +UPDATE t7 SET a = 10 where a = 1; +UPDATE t7 SET b = "updated by client 2" where a = 2; +DELETE FROM t7 WHERE a = 3; +SELECT * FROM t7; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +5 inserted by client 2 NULL NULL +10 inserted by client 1 1 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +4 +# +# Connection default +# +connection default; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +INSERT INTO t5(b) VALUES ("inserted by client 1"); +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 inserted by client 1 4 inserted by client 1 +11 inserted by client 1 NULL NULL +UPDATE t5 SET a = a + 100; +SELECT * FROM t5; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +102 updated by client 2 2 inserted by client 1 +104 inserted by client 1 4 inserted by client 1 +105 inserted by client 2 NULL NULL +110 inserted by client 1 1 inserted by client 1 +111 inserted by client 1 NULL NULL +SELECT COUNT(*) FROM t5; +COUNT(*) +7 +UPDATE t6 SET b = "updated by client 2"; +SELECT * FROM t6; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +2 inserted by client 1 2 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 +SELECT * FROM t6 LOCK IN SHARE MODE; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +6 +DELETE FROM t7; +SELECT * FROM t7; +a b aa bb +1 inserted by client 1 1 inserted by client 1 +3 inserted by client 1 3 inserted by client 1 +SELECT COUNT(*) FROM t7; +COUNT(*) +2 +COMMIT; +SELECT * FROM t5; +a b aa bb +102 updated by client 2 2 inserted by client 1 +104 inserted by client 1 4 inserted by client 1 +105 inserted by client 2 NULL NULL +110 inserted by client 1 1 inserted by client 1 +111 inserted by client 1 NULL NULL +SELECT COUNT(*) FROM t5; +COUNT(*) +5 +SELECT * FROM t6; +a b aa bb +2 updated by client 2 2 inserted by client 1 +4 updated by client 2 4 inserted by client 1 +5 updated by client 2 NULL NULL +10 updated by client 2 1 inserted by client 1 +SELECT COUNT(*) FROM t6; +COUNT(*) +4 +SELECT * FROM t7; +a b aa bb +SELECT COUNT(*) FROM t7; +COUNT(*) +0 +# +# Cleanup +# +DROP TABLE t1,t2,t4,t5,t6,t7; +disconnect con1; +disconnect con2; +disconnect con3; +# +# Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*) +# +CREATE TABLE t1(c1 INT NOT NULL PRIMARY KEY, +c2 INT NOT NULL DEFAULT 1, +c3 char(20) DEFAULT '', +KEY c2_idx (c2)) ENGINE=InnoDB STATS_PERSISTENT=0; +INSERT INTO t1(c1) VALUES (1), (2), (3); +INSERT INTO t1(c1) SELECT c1 + 10 FROM t1; +INSERT INTO t1(c1) SELECT c1 + 100 FROM t1; +CREATE TABLE t2 STATS_PERSISTENT=0 SELECT * FROM t1; +EXPLAIN SELECT COUNT(*) FROM t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +EXPLAIN SELECT COUNT(*) FROM t1, t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +EXPLAIN SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL c2_idx 4 NULL 12 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using join buffer (flat, BNL join) +DROP TABLE t1, t2; |