diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/default.test')
| -rw-r--r-- | mysql-test/suite/sql_sequence/default.test | 221 |
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 # |