Merge branch '10.9' into 10.10

1 files changed, 80 insertions, 0 deletions

diff --git a/mysql-test/suite/sql_sequence/default.test b/mysql-test/suite/sql_sequence/default.test
index e7c13211013..28eb71e39cc 100644
--- a/mysql-test/suite/sql_sequence/default.test
+++ b/mysql-test/suite/sql_sequence/default.test
@@ -135,3 +135,83 @@ EXECUTE stmt;
 # Cleanup
 DROP TABLE t1;
 DROP SEQUENCE s;
+
+--echo #
+--echo # MDEV-29540 Incorrect sequence values in INSERT SELECT
+--echo #
+
+CREATE SEQUENCE s1;
+CREATE TABLE t1 (
+  a BIGINT UNSIGNED NOT NULL PRIMARY KEY
+      DEFAULT (NEXT VALUE FOR s1),
+  b CHAR(1) NOT NULL
+);
+
+INSERT INTO t1 (b) VALUES ('a');
+INSERT INTO t1 (b) VALUES ('b'), ('c');
+INSERT INTO t1 (b) VALUES ('d');
+INSERT INTO t1 (b) SELECT c FROM (
+  SELECT 'e' as c
+  UNION
+  SELECT 'f'
+  UNION
+  SELECT 'g'
+) der;
+
+SELECT a, b FROM t1;
+
+ALTER SEQUENCE s1 RESTART;
+
+--error ER_DUP_ENTRY
+INSERT INTO t1 (b) SELECT c FROM (
+  SELECT 'a' as c
+  UNION
+  SELECT 'b'
+  UNION
+  SELECT 'c'
+  UNION
+  SELECT 'd'
+  UNION
+  SELECT 'e'
+  UNION
+  SELECT 'f'
+  UNION
+  SELECT 'g'
+) der;
+
+ALTER SEQUENCE s1 RESTART;
+
+INSERT IGNORE INTO t1 (b) SELECT c FROM (
+  SELECT 'a' as c
+  UNION
+  SELECT 'b'
+  UNION
+  SELECT 'c'
+  UNION
+  SELECT 'd'
+  UNION
+  SELECT 'e'
+  UNION
+  SELECT 'f'
+  UNION
+  SELECT 'g'
+) der;
+
+SELECT a, b FROM t1;
+
+INSERT IGNORE INTO t1 (b) SELECT c FROM (
+  SELECT 'h' as c
+  UNION
+  SELECT 'i'
+  UNION
+  SELECT 'j'
+) der;
+
+SELECT a, b FROM t1;
+
+DROP TABLE t1;
+DROP SEQUENCE s1;
+
+--echo #
+--echo # End of 10.3 tests
+--echo #