Bug#20837 Apparent change of isolation level during transaction

Bug#46527 COMMIT AND CHAIN RELEASE does not make sense
Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
          preserve the isolation level
Bug#53346 completion_type has strange effect in a stored 
          procedure/prepared statement

Added test cases to verify the expected behaviour of :
 SET SESSION TRANSACTION ISOLATION LEVEL, 
 SET TRANSACTION ISOLATION LEVEL,
 @@completion_type,
 COMMIT AND CHAIN,
 ROLLBACK AND CHAIN
 ..and some combinations of the above

1 files changed, 350 insertions, 0 deletions

diff --git a/mysql-test/t/commit.test b/mysql-test/t/commit.test
new file mode 100644
index 00000000000..261867bd5cc
--- /dev/null
+++ b/mysql-test/t/commit.test
@@ -0,0 +1,350 @@
+--source include/have_innodb.inc
+
+connect (con1,localhost,root,,);
+
+--echo #
+--echo # Bug#20837 Apparent change of isolation level 
+--echo #           during transaction
+--echo #
+--echo # Bug#53343 completion_type=1, COMMIT/ROLLBACK 
+--echo #           AND CHAIN don't preserve the isolation 
+--echo #           level
+#
+# A set of test cases that verifies operation of
+# transaction isolation level and chaining is 
+# provided
+
+# init
+--echo connection default;
+connection default;
+
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+
+#
+# Verify that SET TRANS ISO LEVEL is not allowed 
+# inside a transaction
+#
+START TRANSACTION;
+--error ER_CANT_CHANGE_TX_ISOLATION
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+
+#
+# Verify consistent output from 
+# SELECT @@tx_isolation  (Bug#20837)
+#
+# The transaction will be in READ UNCOMMITTED mode,
+# but SELECT @@tx_isolation should report the session
+# value, which is REPEATABLE READ
+#
+SET @@autocommit=0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+SELECT * FROM t1;
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+COMMIT;
+
+#
+# Verify that a change in the session variable
+# does not affect the currently started
+# transaction
+#
+START TRANSACTION;
+SELECT * FROM t1;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+
+--echo connection con1
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+
+--echo connection default
+connection default;
+--echo We should not be able to read the '1000'
+SELECT * FROM t1;
+COMMIT;
+
+--echo Now, the '1000' should appear.
+START TRANSACTION;
+SELECT * FROM t1;
+COMMIT;
+
+# restore the session value
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
+#
+# A set of test cases for verification that
+# isolation level during chaining works. MySQL
+# has three variants of chaining, i.e
+# COMMIT AND CHAIN, ROLLBACK AND CHAIN, and
+# the use of @completion_type
+#
+
+#
+# Verify isolation level with COMMIT AND CHAIN
+#
+# COMMIT AND CHAIN causes a new transaction to
+# begin as soon as the current ends, and the new
+# transaction will have the same tran. iso. level
+# as the first.
+#
+--echo connection default
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+--echo connection con1
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+--echo Should be 1
+COMMIT AND CHAIN;
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+--echo Should be 1
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+#
+# Verify isolation level with ROLLBACK AND CHAIN
+#
+--echo connection default
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+--echo connection con1
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+--echo Should be 1
+ROLLBACK AND CHAIN;
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+--echo Should be 1
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+#
+# Verify isolation level with @completion_type=1.
+# (A @@completion_type value of 1 is equivalentl to 
+# explicitly adding "AND CHAIN" to COMMIT or ROLLBACK)
+#
+
+#
+# Verify that COMMIT AND NO CHAIN  overrides the value
+# of @@completion_type
+#
+SET @@completion_type=1;
+
+--echo connection default
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+--echo connection con1
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001
+COMMIT AND NO CHAIN;
+--echo default transaction is now in REPEATABLE READ
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002, but NOT 1003
+COMMIT;
+
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+
+#
+# Verify that ROLLBACK AND NO CHAIN  overrides the value
+# of @@completion_type
+#
+--echo connection default
+connection default;
+SET @@completion_type=1;
+COMMIT AND NO CHAIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+--echo connection con1
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001
+ROLLBACK AND NO CHAIN;
+--echo default transaction is now in REPEATABLE READ
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002, but NOT 1003
+
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+
+#
+# Verify that in the sequence:
+#  SET TRANSACTION ISOLATION LEVEL
+#  SET SESSION ISOLATION LEVEL
+#
+# SET SESSION ISOLATION LEVEL has precedence over 
+# SET TRANSACTION.  (Note that this is _not_ 
+# in accordance with ISO 9075.)
+#
+--echo connection default
+connection default;
+
+SET TRANSACTION ISOLATION LEVEL	READ COMMITTED;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+START TRANSACTION;
+SELECT * FROM t1;
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1;
+--echo Should get same result as above (i.e should not read '1000')
+COMMIT;
+
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+
+#
+# Verify that a transaction ended with an
+# implicit commit (i.e a DDL statement), the
+# @@completetion_type setting is ignored, and
+# the next transaction's isolation level is
+# the session level.
+#
+SET @@completion_type=1;
+COMMIT AND NO CHAIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (1000);
+SELECT * FROM t1;
+--echo Should read '1000'
+
+--echo connection con1
+connection con1;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+--echo connection default
+connection default;
+SELECT * FROM t1;
+--echo Should only read the '1000' as this transaction is now in REP READ
+COMMIT AND NO CHAIN;
+
+SET @@completion_type=0;
+COMMIT AND NO CHAIN;
+
+
+#
+# Cleanup
+#
+SET @autocommit=1;
+COMMIT;
+
+disconnect con1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of test cases for Bug#20837
+--echo #