diff options
| author | Oleksandr Byelkin <sanja@mariadb.com> | 2022-10-29 19:22:04 +0200 |
|---|---|---|
| committer | Oleksandr Byelkin <sanja@mariadb.com> | 2022-10-29 19:22:04 +0200 |
| commit | 1ebfa2af62246b98e17cd255a9ccd858151b355a (patch) | |
| tree | f9cf4670ebe96b46f8e0107acc31f7235ec4c554 /mysql-test/suite/sql_sequence | |
| parent | dd9da61dcfd7f5e675ed876cf38886b29d0ddc57 (diff) | |
| parent | 64143741789a3e1c2bb8c6bf627eaec3751af0c6 (diff) | |
| download | mariadb-git-1ebfa2af62246b98e17cd255a9ccd858151b355a.tar.gz | |
Merge branch '10.6' into 10.7
Diffstat (limited to 'mysql-test/suite/sql_sequence')
| -rw-r--r-- | mysql-test/suite/sql_sequence/default.result | 102 | ||||
| -rw-r--r-- | mysql-test/suite/sql_sequence/default.test | 80 |
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 cc22fb42464..eecef1d3527 100644 --- a/mysql-test/suite/sql_sequence/default.result +++ b/mysql-test/suite/sql_sequence/default.result @@ -193,3 +193,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 # |