Bug#40178: Test main.completion_type_func does not clean up / needs to be rewritten

Revised the test to include a test of completion_type = 1 as well as making the test more readable / worthwhile
Removed the master.opt file as it was redundant / unnecessary.

mysql-test/suite/sys_vars/t/completion_type_func-master.opt:
  Removed as redundant.  Test uses include/have_innodb.inc.

1 files changed, 133 insertions, 22 deletions

diff --git a/mysql-test/suite/sys_vars/r/completion_type_func.result b/mysql-test/suite/sys_vars/r/completion_type_func.result
index daee738c10d..f3ddcd287b6 100644
--- a/mysql-test/suite/sys_vars/r/completion_type_func.result
+++ b/mysql-test/suite/sys_vars/r/completion_type_func.result
@@ -2,76 +2,187 @@ DROP TABLE IF EXISTS t1;
 ## Creating new table ##
 CREATE TABLE t1
 (
-id INT NOT NULL AUTO_INCREMENT,
+id INT NOT NULL,
 PRIMARY KEY (id),
 name VARCHAR(30)
 ) ENGINE = INNODB;
-'#--------------------FN_DYNVARS_017_01-------------------------#'
-## Creating new connection ##
-INSERT INTO t1(name) VALUES('Record_1');
-SET @@autocommit = 0;
+## Creating new connections test_con1, test_con2 ##
+#########################################################
+#    Setting initial value of completion_type to zero   #
+#########################################################
+INSERT INTO t1 VALUES(1,'Record_1');
 SELECT * FROM t1;
 id	name
 1	Record_1
 ## Setting value of variable to 0 ##
 SET @@session.completion_type = 0;
 ## Here commit & rollback should work normally ##
+## test commit ##
 START TRANSACTION;
+INSERT INTO t1 VALUES(2,'Record_2');
+INSERT INTO t1 VALUES(3,'Record_3');
 SELECT * FROM t1;
 id	name
 1	Record_1
-INSERT INTO t1(name) VALUES('Record_2');
-INSERT INTO t1(name) VALUES('Record_3');
+2	Record_2
+3	Record_3
+Switching to connection test_con1
+## Don't expect to see id's 2 and 3 in the table w/o COMMIT ##
+SELECT * FROM t1;
+id	name
+1	Record_1
+Switching to default connection
+COMMIT;
+## test rollback ##
+START TRANSACTION;
+INSERT INTO t1 VALUES(4,'Record_4');
+INSERT INTO t1 VALUES(5,'Record_5');
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+4	Record_4
+5	Record_5
+Switching to connection test_con1
+## Don't expect to see id's 4 and 5 here ##
+## Expect to see 3, Record_3 ##
 SELECT * FROM t1;
 id	name
 1	Record_1
 2	Record_2
 3	Record_3
-DELETE FROM t1 WHERE id = 2;
+Switching to connection default;
+ROLLBACK;
+## Don't expect to see id's 4 and 5 now ##
 SELECT * FROM t1;
 id	name
 1	Record_1
+2	Record_2
 3	Record_3
+
+#########################################################
+#    Setting initial value of completion_type to one    #
+#########################################################
+Switching to connection test_con1;
+SET @@session.completion_type = 1;
 START TRANSACTION;
 SELECT * FROM t1;
 id	name
 1	Record_1
+2	Record_2
 3	Record_3
-INSERT INTO t1(name) VALUES('Record_4');
-INSERT INTO t1(name) VALUES('Record_5');
+INSERT INTO t1 VALUES(6,'Record_6');
+INSERT INTO t1 VALUES(7,'Record_7');
 COMMIT;
-'#--------------------FN_DYNVARS_017_02-------------------------#'
+## Expect to immediately have a new transaction ##
+INSERT INTO t1 VALUES(8,'Record_8');
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+8	Record_8
+switching to test_con2
+## Do not expect to see 8, Record_8 as no COMMIT has occurred ##
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+switch to connection test_con1
+## Testing ROLLBACK behavior
+START TRANSACTION;
+INSERT INTO t1 VALUES(9, 'Record_9');
+INSERT INTO t1 VALUES(10, 'Record_10');
+## Expect to see id's 8, 9, 10 here ##
+## 8, Record_8 COMMITted with the start of this transaction ##
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+8	Record_8
+9	Record_9
+10	Record_10
+ROLLBACK;
+## id's 9 and 10 are gone now due to ROLLBACK ##
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+8	Record_8
+## Expect a new transaction ##
+INSERT INTO t1 VALUES(9, 'Record_9');
+Switching to connection test_con2
+## Don't expect to see 9, Record_9 due to no COMMIT yet ##
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+8	Record_8
+Switching to connection test_con1
+ROLLBACK;
+## Don't expect to see 9, Record_9
+SELECT * FROM t1;
+id	name
+1	Record_1
+2	Record_2
+3	Record_3
+6	Record_6
+7	Record_7
+8	Record_8
+#########################################################
+#    Setting initial value of completion_type to 2      #
+#########################################################
 SET @@session.completion_type = 2;
 ## Here commit should work as COMMIT RELEASE ##
 START TRANSACTION;
 SELECT * FROM t1;
 id	name
 1	Record_1
+2	Record_2
 3	Record_3
-4	Record_4
-5	Record_5
-INSERT INTO t1(name) VALUES('Record_6');
-INSERT INTO t1(name) VALUES('Record_7');
+6	Record_6
+7	Record_7
+8	Record_8
+INSERT INTO t1 VALUES(9,'Record_9');
+INSERT INTO t1 VALUES(10,'Record_10');
 COMMIT;
 ## Inserting rows should give error here because connection should ##
 ## disconnect after using COMMIT ##
-INSERT INTO t1(name) VALUES('Record_4');
+INSERT INTO t1 VALUES(4,'Record_4');
 Got one of the listed errors
-## Creating new connection test_con2 ##
+switch to connection test_con2
 SET @@session.completion_type = 2;
 ## Inserting rows and using Rollback which should Rollback & release ##
 START TRANSACTION;
 SELECT * FROM t1;
 id	name
 1	Record_1
+2	Record_2
 3	Record_3
-4	Record_4
-5	Record_5
 6	Record_6
 7	Record_7
-INSERT INTO t1(name) VALUES('Record_8');
-INSERT INTO t1(name) VALUES('Record_9');
+8	Record_8
+9	Record_9
+10	Record_10
+INSERT INTO t1 VALUES(11,'Record_11');
+INSERT INTO t1 VALUES(12,'Record_12');
 ROLLBACK;
-INSERT INTO t1(name) VALUES('Record_4');
+## Expect a failure due to COMMIT/ROLLBACK AND RELEASE behavior ##
+INSERT INTO t1 VALUES(4,'Record_4');
 Got one of the listed errors
 DROP TABLE t1;