Adapt a test from MySQL

3 files changed, 1780 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/include/innodb_isolation_selects.inc b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc
new file mode 100644
index 00000000000..922f5c1c42c
--- /dev/null
+++ b/mysql-test/suite/innodb/include/innodb_isolation_selects.inc
@@ -0,0 +1,15 @@
+# These same selects are used many times in innodb_isolation.test
+
+--echo ########## innodb_isolation_selects.inc ##########
+SELECT * FROM t1;
+SELECT COUNT(*) FROM t1;
+SELECT COUNT(c1) FROM t1;
+SELECT COUNT(c2) FROM t1;        # Uses secondary index k2
+SELECT COUNT(c3) FROM t1;        # Uses clustered index
+SELECT SUM(c1) FROM t1;          # Uses secondary index
+SELECT SUM(c2) FROM t1;          # Uses secondary index
+SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1; # Uses clustered index
+SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
+SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
+SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
+--echo ###############################################
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 00000000000..2248d25b39a
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb-isolation.result
@@ -0,0 +1,1411 @@
+#
+# 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	12	#
+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	12	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	12	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	12	#
+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	12	#
+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	12	#
+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	12	#
+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	12	#
+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	8	#
+2	SUBQUERY	t1	index	NULL	k2	5	NULL	12	#
+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	8	#
+2	SUBQUERY	t1	index	NULL	k2	5	NULL	12	#
+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	7	#
+2	SUBQUERY	t1	index	NULL	k2	5	NULL	12	#
+#
+# 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	updated by client 2	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 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;
+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 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;
diff --git a/mysql-test/suite/innodb/t/innodb-isolation.test b/mysql-test/suite/innodb/t/innodb-isolation.test
new file mode 100644
index 00000000000..56cd668b305
--- /dev/null
+++ b/mysql-test/suite/innodb/t/innodb-isolation.test
@@ -0,0 +1,354 @@
+
+#Note:- WL6742 has been removed from 5.7 (Bug 23046302),
+#       but we are keeping this test since it tests
+#       count(*) correctness for various isolation
+#       levels.
+
+--echo #
+--echo # WL#6742 - Test the interaction of multiple transactions using
+--echo # different isolation levels to make sure that the value returned
+--echo # by count(*) always reflects the correct view of the table according
+--echo # to the transaction's selected isolation level.
+--echo #
+
+--source include/have_innodb.inc
+
+--disable_query_log
+let $MYSQLD_DATADIR= `select @@datadir`;
+let $initial_timeout=`select @@innodb_lock_wait_timeout`;
+--enable_query_log
+
+--echo #
+--echo # Traverse various indexes to get the right counts.
+--echo # This especially tests count(*) which is pushed down to InnoDB in WL#6742.
+--echo #
+CREATE TABLE t1 (
+	c1 INT AUTO_INCREMENT PRIMARY KEY,
+	c2 INT,
+	c3 INT,
+	c4 INT,
+	INDEX k2(c2)
+) Engine=InnoDB;
+let $1=10;
+while ($1 > 0) {
+  INSERT INTO t1(c2,c3,c4) VALUES (1, 1, 1);
+  dec $1;
+}
+
+CREATE TABLE t2 LIKE t1;
+--enable_info
+INSERT INTO t2 (SELECT * FROM t1);
+--disable_info
+
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Do some DML in the default connection and leave the transaction pending.
+--echo #
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+--enable_info
+UPDATE t1 SET c2 = c2 * 3 WHERE c1 = 1;
+DELETE FROM t1 WHERE c1 = 6;
+--disable_info
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Start transactions of Repeatable Read, Read Committed, and Read Uncommitted
+--echo #
+--echo # Connection 1 REPEATABLE READ
+--echo #
+connect (con1,localhost,root,,);
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
+--source suite/innodb/include/innodb_isolation_selects.inc
+--enable_info
+UPDATE t1 SET c2 = c2 * 5 WHERE c1 = 2;
+DELETE FROM t1 WHERE c1 = 7;
+INSERT INTO t1(c2,c3,c4) VALUES (100, 1, 1);
+--disable_info
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Test a lock wait timeout during COUNT(*)
+--echo #
+SET innodb_lock_wait_timeout = 1;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT COUNT(*) FROM t1 FOR UPDATE;
+
+
+--echo #
+--echo # Connection 2 READ COMMITTED
+--echo #
+connect (con2,localhost,root,,);
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
+--source suite/innodb/include/innodb_isolation_selects.inc
+--enable_info
+UPDATE t1 SET c2 = c2 * 7 WHERE c1 = 3;
+DELETE FROM t1 WHERE c1 = 8;
+INSERT INTO t1(c2,c3,c4) VALUES (1000, 1, 1);
+--disable_info
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 3  READ UNCOMMITTED
+--echo #
+connect (con3,localhost,root,,);
+SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
+--source suite/innodb/include/innodb_isolation_selects.inc
+--enable_info
+UPDATE t1 SET c2 = c2 * 11 WHERE c1 = 4;
+INSERT INTO t1(c2,c3,c4) VALUES (10000, 1, 1);
+DELETE FROM t1 WHERE c1 in(9);
+--disable_info
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection default REPEATABLE READ
+--echo #
+connection default;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Commit the 3 extra connections
+--echo #
+--echo # Connection 1 REPEATABLE READ
+--echo #
+connection con1;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 2 READ COMMITTED
+--echo #
+connection con2;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 3  READ UNCOMMITTED
+--echo #
+connection con3;
+--source suite/innodb/include/innodb_isolation_selects.inc
+COMMIT;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 2 READ COMMITTED
+--echo #
+connection con2;
+--source suite/innodb/include/innodb_isolation_selects.inc
+COMMIT;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 1 REPEATABLE READ
+--echo #
+connection con1;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Select the first 5 records FOR UPDATE using count(*) in a subquery.
+--echo # The second record is still pending so we get a lock timeout.
+--echo #
+SET innodb_lock_wait_timeout = 1;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT c1, c2 FROM t1 WHERE c1 < ((SELECT COUNT(*) FROM t1) / 2) FOR UPDATE;
+--error ER_LOCK_WAIT_TIMEOUT
+SELECT COUNT(*) FROM t1 FOR UPDATE;
+COMMIT;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Show The EXPLAIN output for these queries;
+--echo #
+# column 10 is the row count provided by handler::info().  In InnoDB, this is
+# a statistical estimate.  After the multi-transactional changes above,
+# Solaris reports 10 rows which is correct, but other OSes report 9.
+--replace_column 10 #
+EXPLAIN SELECT * FROM t1;
+EXPLAIN SELECT COUNT(*) FROM t1;
+EXPLAIN SELECT COUNT(c1) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT COUNT(c2) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT COUNT(c3) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT SUM(c1) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT SUM(c2) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT AVG(c1), MAX(c1), MIN(c2), AVG(c3), SUM(c4) FROM t1;
+--replace_column 10 #
+EXPLAIN SELECT c1, c2 FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
+--replace_column 10 #
+EXPLAIN SELECT COUNT(c2) FROM t1 WHERE c1 > ((SELECT COUNT(*) FROM t1) / 2);
+--replace_column 10 #
+EXPLAIN SELECT COUNT(*) FROM t1 WHERE c1 > (SELECT AVG(c1) FROM t1);
+
+--echo #
+--echo # Make all indexes in t2 obsolete to the active repeatable read transaction
+--echo # in the default connection.
+--echo #
+ALTER TABLE t2 row_format=redundant;
+
+--echo #
+--echo # Connection default REPEATABLE READ
+--echo # Do more DML in the default REPEATABLE READ transaction in order to use recently committed records.
+--echo #
+connection default;
+--source suite/innodb/include/innodb_isolation_selects.inc
+--enable_info
+UPDATE t1 SET c4 = c2 * 10;
+--disable_info
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Table t2 has been altered to a new row format.
+--echo # The index should not be useable.
+--echo #
+--error ER_TABLE_DEF_CHANGED
+SELECT COUNT(*) FROM t2;
+--error ER_TABLE_DEF_CHANGED
+SELECT * FROM t2;
+
+COMMIT;
+SELECT COUNT(*) FROM t2;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Connection 2
+--echo #
+connection con2;
+--source suite/innodb/include/innodb_isolation_selects.inc
+
+--echo #
+--echo # Try COUNT(*) on a DISCARDED table.
+--echo #
+connection default;
+CREATE TABLE t4 LIKE t1;
+INSERT INTO t4 (SELECT * FROM t1);
+SELECT COUNT(*) FROM t4;
+ALTER TABLE t4 DISCARD TABLESPACE;
+--error ER_TABLESPACE_DISCARDED
+SELECT COUNT(*) FROM t4;
+
+
+--echo #
+--echo # Test the interaction of a repeatable read transaction
+--echo # to changes that happen outside its view.
+--echo #
+
+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;
+SELECT COUNT(*) FROM t5;
+SELECT * FROM t6;
+SELECT COUNT(*) FROM t6;
+SELECT * FROM t7;
+SELECT COUNT(*) FROM t7;
+
+--echo #
+--echo # Connection 1
+--echo #
+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;
+SELECT COUNT(*) FROM t5;
+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;
+SELECT COUNT(*) FROM t6;
+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;
+SELECT COUNT(*) FROM t7;
+
+--echo #
+--echo # Connection default
+--echo #
+connection default;
+SELECT * FROM t5;
+INSERT INTO t5(b) VALUES ("inserted by client 1");
+SELECT * FROM t5;
+UPDATE t5 SET a = a + 100;
+SELECT * FROM t5;
+SELECT COUNT(*) FROM t5;
+
+UPDATE t6 SET b = "updated by client 2";
+SELECT * FROM t6;
+SELECT COUNT(*) FROM t6;
+
+DELETE FROM t7;
+SELECT * FROM t7;
+SELECT COUNT(*) FROM t7;
+
+COMMIT;
+SELECT * FROM t5;
+SELECT COUNT(*) FROM t5;
+SELECT * FROM t6;
+SELECT COUNT(*) FROM t6;
+SELECT * FROM t7;
+SELECT COUNT(*) FROM t7;
+
+--echo #
+--echo # Cleanup
+--echo #
+DROP TABLE t1,t2,t4,t5,t6,t7;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+--disable_query_log
+eval set global innodb_lock_wait_timeout=$initial_timeout;
+--enable_query_log
+
+--echo #
+--echo # Bug #23596760: FORCE INDEX IS SKIPPED WHILE EXECUTING SELECT COUNT(*)
+--echo #
+
+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;
+
+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 SELECT * FROM t1;
+
+let query1= SELECT COUNT(*) FROM t1;
+let query2= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx);
+let query3= SELECT COUNT(*) FROM t1, t2;
+let query4= SELECT COUNT(*) FROM t1 FORCE INDEX(c2_idx), t2;
+
+eval EXPLAIN $query1;
+eval EXPLAIN $query2;
+eval EXPLAIN $query3;
+eval EXPLAIN $query4;
+
+DROP TABLE t1, t2;