MDEV-29540 Incorrect sequence values in INSERT SELECT

The population of default values in INSERT SELECT was being
performed twice. With sequences, this resulted in every
second sequence value being used.

With SELECT INSERT we remove the second invokation of
table->update_default_fields(). This was already performed
in store_values() invoking fill_record_n_invoke_before_triggers()
which invoked update_default_fields() previously.

We do need to return an error on duplicate values, so the
::store_values is extended to take the ignore option.

2 files changed, 182 insertions, 0 deletions

diff --git a/mysql-test/suite/sql_sequence/default.result b/mysql-test/suite/sql_sequence/default.result
index d767b5fb030..abed796cb75 100644
--- a/mysql-test/suite/sql_sequence/default.result
+++ b/mysql-test/suite/sql_sequence/default.result
@@ -195,3 +195,105 @@ INSERT INTO t1 () values ();
 EXECUTE stmt;
 DROP TABLE t1;
 DROP SEQUENCE s;
+#
+# MDEV-29540 Incorrect sequence values in INSERT SELECT
+#
+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;
+a	b
+1	a
+2	b
+3	c
+4	d
+5	e
+6	f
+7	g
+ALTER SEQUENCE s1 RESTART;
+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;
+ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
+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;
+Warnings:
+Warning	1062	Duplicate entry '1' for key 'PRIMARY'
+Warning	1062	Duplicate entry '2' for key 'PRIMARY'
+Warning	1062	Duplicate entry '3' for key 'PRIMARY'
+Warning	1062	Duplicate entry '4' for key 'PRIMARY'
+Warning	1062	Duplicate entry '5' for key 'PRIMARY'
+Warning	1062	Duplicate entry '6' for key 'PRIMARY'
+Warning	1062	Duplicate entry '7' for key 'PRIMARY'
+SELECT a, b FROM t1;
+a	b
+1	a
+2	b
+3	c
+4	d
+5	e
+6	f
+7	g
+INSERT IGNORE INTO t1 (b) SELECT c FROM (
+SELECT 'h' as c
+UNION
+SELECT 'i'
+  UNION
+SELECT 'j'
+) der;
+SELECT a, b FROM t1;
+a	b
+1	a
+2	b
+3	c
+4	d
+5	e
+6	f
+7	g
+8	h
+9	i
+10	j
+DROP TABLE t1;
+DROP SEQUENCE s1;
+#
+# End of 10.3 tests
+#
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 #