Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 221 insertions, 0 deletions

diff --git a/mysql-test/suite/sql_sequence/default.test b/mysql-test/suite/sql_sequence/default.test
new file mode 100644
index 00000000..f965089d
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/default.test
@@ -0,0 +1,221 @@
+#
+# Testing sequence in DEFAULT clause
+#
+--source include/have_sequence.inc
+
+drop table if exists t1,s1,s2;
+drop view if exists v1;
+
+--echo #
+--echo # Test DEFAULT
+--echo #
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+show create table t1;
+insert into t1 SET b=1;
+insert into t1 SET b=2;
+insert into t1 (b) values (3),(4);
+select * from t1;
+update t1 set b=5 where a=1;
+delete from t1 where b=1;
+select * from t1;
+
+--echo #
+--echo # Executing DEFAULT function
+--echo #
+
+INSERT into t1 values(default(a),10);
+INSERT into t1 values(default(a),default(a));
+update t1 set a=default(a), b=12 where b=2;
+select * from t1;
+--disable_ps2_protocol
+select default(a), a, b from t1;
+select * from s1;
+select * from t1 where default(a) > 0;
+select * from s1;
+--enable_ps2_protocol
+
+--echo #
+--echo # View
+--echo #
+
+create view v1 as select * from t1;
+insert into v1 set b=20;
+select * from v1;
+drop view v1;
+
+--echo #
+--echo # Alter table
+--echo #
+
+CREATE SEQUENCE s2 nocache engine=myisam;
+alter table t1 add column c int default next value for s2, add column d int default previous value for s2;
+show create table t1;
+select * from t1;
+drop sequence s2;
+show create table t1;
+drop table t1;
+drop sequence s1;
+
+--echo #
+--echo # LOCK TABLES
+--echo #
+
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+insert into t1 (b) values (3),(4);
+LOCK TABLE t1 WRITE;
+--error ER_TABLE_NOT_LOCKED
+insert into t1 (b) values (5),(6);
+UNLOCK TABLES;
+
+LOCK TABLE t1 WRITE, s1 WRITE;
+insert into t1 (b) values (5),(6);
+--disable_ps2_protocol
+select default(a) from t1;
+--enable_ps2_protocol
+UNLOCK TABLES;
+
+LOCK TABLE t1 READ;
+--error ER_TABLE_NOT_LOCKED
+insert into t1 (b) values (5),(6);
+--error ER_TABLE_NOT_LOCKED
+select default(a) from t1;
+UNLOCK TABLES;
+
+LOCK TABLE t1 READ, s1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+insert into t1 (b) values (5),(6);
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+select default(a) from t1;
+UNLOCK TABLES;
+
+drop table t1;
+drop sequence s1;
+
+--echo #
+--echo # Testing prepared statements
+--echo #
+
+CREATE or replace SEQUENCE s1 nocache engine=myisam;
+CREATE or replace table t1 (a int default next value for s1, b int);
+PREPARE stmt FROM "insert into t1 (b) values(?)";
+execute stmt using 1;
+execute stmt using 2;
+execute stmt using 3;
+select * from t1;
+drop table t1,s1;
+deallocate prepare stmt;
+
+--echo #
+--echo # Wrong usage of default
+--echo #
+
+--error ER_NO_SUCH_TABLE
+CREATE table t1 (a int default next value for s1, b int);
+CREATE SEQUENCE s1 nocache engine=myisam;
+CREATE table t1 (a int default next value for s1, b int);
+DROP SEQUENCE s1;
+--error ER_NO_SUCH_TABLE
+insert into t1 (b) values (5),(6);
+--error ER_NO_SUCH_TABLE
+ALTER TABLE t1 add column c int;
+CREATE SEQUENCE s1 nocache engine=myisam;
+ALTER TABLE t1 add column c int;
+--error ER_NO_SUCH_TABLE
+ALTER TABLE t1 add column d int default next value for s_not_exits;
+drop table t1;
+drop sequence s1;
+
+--echo #
+--echo # MDEV 22785 Crash with prepared statements and NEXTVAL()
+--echo #
+CREATE SEQUENCE s;
+CREATE TABLE t1 (id int NOT NULL DEFAULT NEXTVAL(s), PRIMARY KEY (id));
+PREPARE stmt FROM " INSERT INTO t1 () values ()";
+INSERT INTO t1 () values ();
+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 #