4 files changed, 320 insertions, 2 deletions
diff --git a/mysql-test/r/commit.result b/mysql-test/r/commit.result
new file mode 100644
index 00000000000..8620d2077c6
--- /dev/null
+++ b/mysql-test/r/commit.result
@@ -0,0 +1,272 @@
+#
+# Bug#20837 Apparent change of isolation level 
+#           during transaction
+#
+# Bug#53343 completion_type=1, COMMIT/ROLLBACK 
+#           AND CHAIN don't preserve the isolation 
+#           level
+connection default;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+START TRANSACTION;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+ERROR 25001: Transaction isolation level can't be changed while a transaction is in progress
+COMMIT;
+SET @@autocommit=0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+SELECT * FROM t1;
+s1
+1
+2
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+COMMIT;
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connection con1
+START TRANSACTION;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+connection default
+We should not be able to read the '1000'
+SELECT * FROM t1;
+s1
+1
+2
+-1
+COMMIT;
+Now, the '1000' should appear.
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1000
+COMMIT;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+connection default
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+connection con1
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+COUNT(*)
+1
+Should be 1
+COMMIT AND CHAIN;
+connection con1
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+COUNT(*)
+1
+Should be 1
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1000
+1001
+1002
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+connection default
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+connection con1
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+COUNT(*)
+1
+Should be 1
+ROLLBACK AND CHAIN;
+connection con1
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+COUNT(*)
+1
+Should be 1
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+SET @@completion_type=1;
+connection default
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+connection con1
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+Should see 1001
+COMMIT AND NO CHAIN;
+default transaction is now in REPEATABLE READ
+connection con1
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002
+connection con1
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002, but NOT 1003
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+1003
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+connection default
+SET @@completion_type=1;
+COMMIT AND NO CHAIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+connection con1
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+Should see 1001
+ROLLBACK AND NO CHAIN;
+default transaction is now in REPEATABLE READ
+connection con1
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002
+connection con1
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+connection default
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002, but NOT 1003
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+1003
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+connection default
+SET TRANSACTION ISOLATION LEVEL	READ COMMITTED;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+connection con1
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+connection default
+SELECT * FROM t1;
+s1
+1
+2
+-1
+Should get same result as above (i.e should not read '1000')
+COMMIT;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+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;
+s1
+1000
+Should read '1000'
+connection con1
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default
+SELECT * FROM t1;
+s1
+1000
+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;
+SET @autocommit=1;
+COMMIT;
+DROP TABLE t1;
+#
+# End of test cases for Bug#20837
+#
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 467bb7c5cb8..0cfde6dd2a0 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -618,3 +618,48 @@ DROP TABLE t1, t2, t3;
 #
 # End of 5.1 tests
 #
+# Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
+#
+COMMIT AND CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT AND NO CHAIN RELEASE;
+COMMIT RELEASE;
+COMMIT CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+COMMIT NO CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+COMMIT AND NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT AND RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT NO RELEASE;
+COMMIT CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+COMMIT NO CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+COMMIT AND RELEASE CHAIN;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
+COMMIT AND NO CHAIN NO RELEASE;
+ROLLBACK AND CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK AND NO CHAIN RELEASE;
+ROLLBACK RELEASE;
+ROLLBACK CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+ROLLBACK NO CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+ROLLBACK AND NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK AND RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK NO RELEASE;
+ROLLBACK CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+ROLLBACK NO CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+ROLLBACK AND RELEASE CHAIN;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
+ROLLBACK AND NO CHAIN NO RELEASE;
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/r/partition_innodb_semi_consistent.result b/mysql-test/r/partition_innodb_semi_consistent.result
index ef9337fa65c..1ff7ffba6db 100644
--- a/mysql-test/r/partition_innodb_semi_consistent.result
+++ b/mysql-test/r/partition_innodb_semi_consistent.result
@@ -18,6 +18,7 @@ set autocommit=0;
 update t1 set a=10 where a=5;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 commit;
+commit;
 set session transaction isolation level read committed;
 update t1 set a=10 where a=5;
 select * from t1 where a=2 for update;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 16f6657ceeb..0e75ebd57ec 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -695,11 +695,11 @@ REPEATABLE-READ
 set transaction isolation level read committed;
 execute stmt;
 @@tx_isolation
-READ-COMMITTED
+REPEATABLE-READ
 set transaction isolation level serializable;
 execute stmt;
 @@tx_isolation
-SERIALIZABLE
+REPEATABLE-READ
 set @@tx_isolation=default;
 execute stmt;
 @@tx_isolation