mariadb/mysql-test/suite/sql_sequence/default.test

#
# 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 #