MDEV-15106 Unexpected ER_WRONG_INSERT_INTO_SEQUENCE upon INSERT with multiple locks on sequences

Fixed by removing the check of single lock in sequence insert and let MDL
code handle deadlock detection

4 files changed, 39 insertions, 8 deletions

diff --git a/mysql-test/suite/sql_sequence/lock.result b/mysql-test/suite/sql_sequence/lock.result
index 05a06b218b3..92c75ff8188 100644
--- a/mysql-test/suite/sql_sequence/lock.result
+++ b/mysql-test/suite/sql_sequence/lock.result
@@ -27,3 +27,8 @@ DROP SEQUENCE s1;
 ERROR HY000: Table 's1' was locked with a READ lock and can't be updated
 unlock tables;
 DROP SEQUENCE s1;
+CREATE SEQUENCE seq1;
+CREATE SEQUENCE seq2;
+LOCK TABLE seq1 WRITE, seq2 WRITE;
+INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
+DROP SEQUENCE seq1, seq2;
diff --git a/mysql-test/suite/sql_sequence/lock.test b/mysql-test/suite/sql_sequence/lock.test
index 2208a1f1ffa..730404abf38 100644
--- a/mysql-test/suite/sql_sequence/lock.test
+++ b/mysql-test/suite/sql_sequence/lock.test
@@ -38,3 +38,16 @@ SELECT NEXTVAL(s);
 DROP SEQUENCE s1;
 unlock tables;
 DROP SEQUENCE s1;
+
+
+#
+# MDEV-15106 Unexpected ER_WRONG_INSERT_INTO_SEQUENCE upon INSERT with
+# multiple locks on sequences
+#
+
+CREATE SEQUENCE seq1;
+CREATE SEQUENCE seq2;
+LOCK TABLE seq1 WRITE, seq2 WRITE;
+INSERT INTO seq1 VALUES (1, 1, 100000, 1, 1, 100, 1, 1);
+DROP SEQUENCE seq1, seq2;
+
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
index e3ec94cf2c5..a56eb4d09a7 100644
--- a/mysql-test/suite/sql_sequence/other.result
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -49,10 +49,6 @@ insert into s1 (next_not_cached_value, minimum_value) values (100,1000);
 ERROR HY000: Field 'maximum_value' doesn't have a default value
 insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
 ERROR HY000: Table 's1' is specified twice, both as a target for 'INSERT' and as a separate source for data
-insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
-ERROR HY000: Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a sequence object (like with mysqldump).  If you want to change the SEQUENCE, use ALTER SEQUENCE instead.
-insert into s1 select * from s2;
-ERROR HY000: Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a sequence object (like with mysqldump).  If you want to change the SEQUENCE, use ALTER SEQUENCE instead.
 insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
 ERROR HY000: Sequence 'test.s1' values are conflicting
 insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0);
@@ -72,6 +68,20 @@ next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_si
 2000	1	9223372036854775806	1	1	1000	0	0
 insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
 ERROR HY000: Sequence 'test.s2' values are conflicting
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+2000	1	9223372036854775806	1	1	1000	0	0
+insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+1	1	9223372036854775806	1	1	1000	0	0
+select * from s2;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+1001	1	9223372036854775806	1	1	1000	0	0
+insert into s1 select * from s2;
+select * from s1;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+1001	1	9223372036854775806	1	1	1000	0	0
 drop sequence s1,s2;
 #
 # UPDATE and DELETE
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test
index ff0db9e158d..a493687faa3 100644
--- a/mysql-test/suite/sql_sequence/other.test
+++ b/mysql-test/suite/sql_sequence/other.test
@@ -38,10 +38,6 @@ create sequence s2;
 insert into s1 (next_not_cached_value, minimum_value) values (100,1000);
 --error ER_UPDATE_TABLE_USED
 insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
---error ER_WRONG_INSERT_INTO_SEQUENCE
-insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
---error ER_WRONG_INSERT_INTO_SEQUENCE
-insert into s1 select * from s2;
 --error ER_SEQUENCE_INVALID_DATA
 insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
 --error ER_SEQUENCE_INVALID_DATA
@@ -53,6 +49,13 @@ select next value for s1;
 select * from s1;
 --error ER_SEQUENCE_INVALID_DATA
 insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
+
+select * from s1;
+insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
+select * from s1;
+select * from s2;
+insert into s1 select * from s2;
+select * from s1;
 drop sequence s1,s2;
 
 --echo #