--source include/have_sequence.inc
--source include/have_innodb.inc

#
# Test various combinations of operations on sequence
#

--echo #
--echo # Create and check
--echo #

create sequence s1 engine=innodb;
check table s1;
select next value for s1;
flush tables;
check table s1;
select next value for s1;
flush tables;
repair table s1;
select next value for s1;
drop sequence s1;

create or replace sequence s1 engine=innodb;
select next value for s1;
repair table s1;
check table s1;
select next value for s1;
select * from s1;
drop sequence s1;

--echo #
--echo # ÌNSERT
--echo #

create sequence s1;
create sequence s2;
--error ER_NO_DEFAULT_FOR_FIELD
insert into s1 (next_value, min_value) values (100,1000);
--error ER_UPDATE_TABLE_USED
insert into s1 values (next value for s1, 1,9223372036854775806,1,1,1000,0,0);
--error ER_WRONG_INSERT_INTO_SEQUENCE
insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0);
--error ER_WRONG_INSERT_INTO_SEQUENCE
insert into s1 select * from s2;
--error ER_SEQUENCE_INVALID_DATA
insert into s1 values(1000,9223372036854775806,1,1,1,1000,0,0);
--error ER_SEQUENCE_INVALID_DATA
insert into s1 values(0,9223372036854775806,1,1,1,1000,0,0);
select * from s1;
insert into s1 values(1000,1,9223372036854775806,1,1,1000,0,0);
select * from s1;
select next value for s1;
select * from s1;
--error ER_SEQUENCE_INVALID_DATA
insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0);
drop sequence s1,s2;

--echo #
--echo # UPDATE and DELETE
--echo #

create sequence s1;
--error ER_ILLEGAL_HA
update s1 set next_value=100;
--error ER_ILLEGAL_HA
delete from s1 where next_value > 0;
drop sequence s1;

--echo #
--echo # SHOW TABLES
--echo #

create sequence s1;
create table t1 (a int);
create view v1 as select * from s1;
show full tables;
SELECT TABLE_TYPE,ENGINE FROM INFORMATION_SCHEMA.TABLES where table_schema="test";
drop table t1,s1;
drop view v1;

--echo #
--echo # LOCK TABLES (as in mysqldump)
--echo #

create sequence s1 engine=innodb;
LOCK TABLES s1 READ;
SELECT * from s1;
UNLOCK TABLES;
LOCK TABLES s1 WRITE;
insert into s1 values (1,1,9223372036854775806, 1, 1, 1000, 0, 0);
UNLOCK TABLES;
drop table s1;