11 files changed, 837 insertions, 1 deletions

diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result
new file mode 100644
index 00000000000..43afe2377c3
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/alter.result
@@ -0,0 +1,238 @@
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+#
+# Test alter sequence
+#
+CREATE SEQUENCE t1 nocache engine=myisam;
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	0	0	0
+select next value for t1;
+next value for t1
+1
+alter sequence t1 start=50;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+2	1	9223372036854775806	50	1	0	0	0
+select next value for t1;
+next value for t1
+2
+alter sequence t1 minvalue=-100;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3	-100	9223372036854775806	50	1	0	0	0
+alter sequence t1 minvalue=100 start=100;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3	100	9223372036854775806	100	1	0	0	0
+alter sequence t1 maxvalue=500;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3	100	500	100	1	0	0	0
+drop sequence t1;
+CREATE SEQUENCE t1 engine=myisam;
+alter sequence t1 nocache;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+alter sequence t1 cache=100;
+flush tables;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM
+alter sequence t1 nocache;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+flush tables;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	0	0	0
+select next value for t1;
+next value for t1
+1
+select next value for t1;
+next value for t1
+2
+select next value for t1;
+next value for t1
+3
+select next_value, round from t1;
+next_value	round
+4	0
+drop sequence t1;
+CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
+alter sequence t1 no maxvalue;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	1000	0	0
+alter sequence t1 cycle;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM
+alter sequence t1 nocycle;
+alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+15	10	20	15	1	1000	1	0
+select NEXT VALUE for t1 from seq_1_to_10;
+NEXT VALUE for t1
+15
+16
+17
+18
+19
+20
+10
+11
+12
+13
+alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
+select NEXT VALUE for t1 from seq_1_to_10;
+NEXT VALUE for t1
+17
+18
+19
+20
+10
+11
+12
+13
+14
+15
+drop sequence t1;
+CREATE SEQUENCE t1 maxvalue=100;
+alter sequence t1 increment=-2 start with 50 minvalue=-100;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	-100	100	50	-2	1000	0	0
+select NEXT VALUE for t1 from seq_1_to_10;
+NEXT VALUE for t1
+1
+-1
+-3
+-5
+-7
+-9
+-11
+-13
+-15
+-17
+drop sequence t1;
+#
+# InnoDB (some things work different with InnoDB)
+
+CREATE SEQUENCE t1 cache 10 engine=innodb;
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+1	1	9223372036854775806	1	1	10	0	0
+select next value for t1;
+next value for t1
+1
+alter sequence t1 start=100;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+11	1	9223372036854775806	100	1	10	0	0
+select next value for t1;
+next value for t1
+11
+drop sequence t1;
+#
+# ALTER TABLE
+#
+CREATE SEQUENCE t1 engine=innodb;
+select next value for t1;
+next value for t1
+1
+alter table t1 rename t2;
+select next value for t2;
+next value for t2
+1001
+rename table t2 to t1;
+select next value for t1;
+next value for t1
+2001
+alter table t1 comment="foo";
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo'
+alter table t1 engine=myisam;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo'
+alter table t1 engine=innodb;
+show create sequence t1;
+Table	Create Table
+t1	CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo'
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+3001	1	9223372036854775806	1	1	1000	0	0
+drop sequence t1;
+CREATE SEQUENCE t1 engine=myisam;
+alter sequence t1 minvalue=100;
+ERROR HY000: Sequence 'test.t1' values are conflicting
+drop sequence t1;
+CREATE SEQUENCE t1 engine=myisam;
+alter sequence t1 minvalue=25 maxvalue=20;
+ERROR HY000: Sequence 'test.t1' values are conflicting
+drop sequence t1;
+create table t1 (a int);
+alter sequence t1 minvalue=100;
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+drop table t1;
+alter sequence if exists t1 minvalue=100;
+Warnings:
+Note	4067	Unknown SEQUENCE: 'test.t1'
+alter sequence t1 minvalue=100;
+ERROR 42S02: Table 'test.t1' doesn't exist
+create sequence t1;
+alter sequence t1;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+drop sequence t1;
+CREATE SEQUENCE t1 maxvalue=100;
+alter sequence t1 increment=-2 start with 50;
+select next value for t1;
+next value for t1
+1
+select next value for t1;
+ERROR HY000: Sequence 'test.t1' has run out
+select * from t1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+0	1	100	50	-2	1000	0	0
+alter sequence t1 restart;
+select next value for t1;
+next value for t1
+50
+alter sequence t1 restart with 90;
+select next value for t1;
+next value for t1
+90
+drop sequence t1;
diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test
new file mode 100644
index 00000000000..7454f5900f7
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/alter.test
@@ -0,0 +1,139 @@
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+
+drop table if exists t1;
+
+--echo #
+--echo # Test alter sequence
+--echo #
+
+CREATE SEQUENCE t1 nocache engine=myisam;
+select * from t1;
+select next value for t1;
+alter sequence t1 start=50;
+show create sequence t1;
+select * from t1;
+select next value for t1;
+
+alter sequence t1 minvalue=-100;
+show create sequence t1;
+select * from t1;
+alter sequence t1 minvalue=100 start=100;
+show create sequence t1;
+select * from t1;
+
+alter sequence t1 maxvalue=500;
+show create sequence t1;
+select * from t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 engine=myisam;
+alter sequence t1 nocache;
+show create sequence t1;
+alter sequence t1 cache=100;
+flush tables;
+show create sequence t1;
+alter sequence t1 nocache;
+show create sequence t1;
+flush tables;
+show create sequence t1;
+select * from t1;
+select next value for t1;
+select next value for t1;
+select next value for t1;
+select next_value, round from t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 maxvalue=100 engine=myisam;
+alter sequence t1 no maxvalue;
+show create sequence t1;
+select * from t1;
+alter sequence t1 cycle;
+show create sequence t1;
+alter sequence t1 nocycle;
+alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle;
+show create sequence t1;
+select * from t1;
+select NEXT VALUE for t1 from seq_1_to_10;
+alter sequence t1 restart with 17 minvalue=10 maxvalue=20 cycle;
+select NEXT VALUE for t1 from seq_1_to_10;
+drop sequence t1;
+
+CREATE SEQUENCE t1 maxvalue=100;
+alter sequence t1 increment=-2 start with 50 minvalue=-100;
+show create sequence t1;
+select * from t1;
+select NEXT VALUE for t1 from seq_1_to_10;
+drop sequence t1;
+
+--echo #
+--echo # InnoDB (some things work different with InnoDB)
+--echo
+
+CREATE SEQUENCE t1 cache 10 engine=innodb;
+select * from t1;
+select next value for t1;
+alter sequence t1 start=100;
+show create sequence t1;
+select * from t1;
+select next value for t1;
+drop sequence t1;
+
+--echo #
+--echo # ALTER TABLE
+--echo #
+
+CREATE SEQUENCE t1 engine=innodb;
+select next value for t1;
+alter table t1 rename t2;
+select next value for t2;
+rename table t2 to t1;
+select next value for t1;
+alter table t1 comment="foo";
+show create sequence t1;
+alter table t1 engine=myisam;
+show create sequence t1;
+alter table t1 engine=innodb;
+show create sequence t1;
+select * from t1;
+drop sequence t1;
+
+#
+# Some error testing
+#
+
+CREATE SEQUENCE t1 engine=myisam;
+--error ER_SEQUENCE_INVALID_DATA
+alter sequence t1 minvalue=100;
+drop sequence t1;
+
+CREATE SEQUENCE t1 engine=myisam;
+--error ER_SEQUENCE_INVALID_DATA
+alter sequence t1 minvalue=25 maxvalue=20;
+drop sequence t1;
+
+create table t1 (a int);
+--error ER_NOT_SEQUENCE
+alter sequence t1 minvalue=100;
+drop table t1;
+
+alter sequence if exists t1 minvalue=100;
+--error ER_NO_SUCH_TABLE
+alter sequence t1 minvalue=100;
+
+create sequence t1;
+--error ER_PARSE_ERROR
+alter sequence t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 maxvalue=100;
+alter sequence t1 increment=-2 start with 50;
+select next value for t1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for t1;
+select * from t1;
+alter sequence t1 restart;
+select next value for t1;
+alter sequence t1 restart with 90;
+select next value for t1;
+drop sequence t1;
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result
index 59dfe62acee..4962752c7d9 100644
--- a/mysql-test/suite/sql_sequence/create.result
+++ b/mysql-test/suite/sql_sequence/create.result
@@ -186,6 +186,8 @@ create sequence t1 start with 10 maxvalue=9223372036854775807;
 ERROR HY000: Sequence 'test.t1' values are conflicting
 create sequence t1 start with 10 minvalue=-9223372036854775808;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '9223372036854775808' at line 1
+create sequence t1 RESTART WITH 10;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1
 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
 drop sequence t1;
 create sequence t1;
diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test
index 6cb6dedd91b..cf094c2cedd 100644
--- a/mysql-test/suite/sql_sequence/create.test
+++ b/mysql-test/suite/sql_sequence/create.test
@@ -118,6 +118,8 @@ create or replace sequence t1 start with 10 min_value=1 NO MINVALUE;
 create sequence t1 start with 10 maxvalue=9223372036854775807;
 --error ER_PARSE_ERROR
 create sequence t1 start with 10 minvalue=-9223372036854775808;
+--error ER_PARSE_ERROR
+create sequence t1 RESTART WITH 10;
 
 # This should probably give an error
 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1;
diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result
index fc28152a2b7..84f91bc0bdd 100644
--- a/mysql-test/suite/sql_sequence/next.result
+++ b/mysql-test/suite/sql_sequence/next.result
@@ -390,6 +390,16 @@ next_value	min_value	max_value	start	increment	cache	cycle	round
 select next value for s1;
 next value for s1
 3984356
+explain extended select next value for s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select nextval(`test`.`s1`) AS `next value for s1`
+explain extended select previous value for s1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select lastval(`test`.`s1`) AS `previous value for s1`
 drop sequence s1;
 create table t1 (a int);
 select next value for t1;
diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test
index 426ee5709a1..472feafb2c6 100644
--- a/mysql-test/suite/sql_sequence/next.test
+++ b/mysql-test/suite/sql_sequence/next.test
@@ -182,9 +182,10 @@ drop table t1,s1;
 CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
 select * from s1;
 select next value for s1;
+explain extended select next value for s1;
+explain extended select previous value for s1;
 drop sequence s1;
 
-
 #
 # Some error testing
 #
diff --git a/mysql-test/suite/sql_sequence/other.result b/mysql-test/suite/sql_sequence/other.result
new file mode 100644
index 00000000000..b9510d46de7
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/other.result
@@ -0,0 +1,42 @@
+#
+# Create and check
+#
+create sequence s1 engine=innodb;
+check table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	check	note	The storage engine for the table doesn't support check
+select next value for s1;
+next value for s1
+1
+flush tables;
+check table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	check	note	The storage engine for the table doesn't support check
+select next value for s1;
+next value for s1
+1001
+flush tables;
+repair table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	repair	note	The storage engine for the table doesn't support repair
+select next value for s1;
+next value for s1
+2001
+drop sequence s1;
+create or replace sequence s1 engine=innodb;
+select next value for s1;
+next value for s1
+1
+repair table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	repair	note	The storage engine for the table doesn't support repair
+check table s1;
+Table	Op	Msg_type	Msg_text
+test.s1	check	note	The storage engine for the table doesn't support check
+select next value for s1;
+next value for s1
+1001
+select * from s1;
+next_value	min_value	max_value	start	increment	cache	cycle	round
+2001	1	9223372036854775806	1	1	1000	0	0
+drop sequence s1;
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test
new file mode 100644
index 00000000000..c9ed326004a
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/other.test
@@ -0,0 +1,29 @@
+--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;
diff --git a/mysql-test/suite/sql_sequence/read_only.test b/mysql-test/suite/sql_sequence/read_only.test
index d8743617ad2..04dab2bb525 100644
--- a/mysql-test/suite/sql_sequence/read_only.test
+++ b/mysql-test/suite/sql_sequence/read_only.test
@@ -1,4 +1,5 @@
 --source include/have_innodb.inc
+--source include/not_embedded.inc
 
 #
 # Test innodb read only
diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result
new file mode 100644
index 00000000000..2fe46ff90c2
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/setval.result
@@ -0,0 +1,246 @@
+drop table if exists t1;
+Warnings:
+Note	1051	Unknown table 'test.t1'
+#
+# Test setval function
+#
+CREATE SEQUENCE t1 cache 10 engine=myisam;
+select next_value,round from t1;
+next_value	round
+1	0
+do setval(t1,10);
+select next_value,round from t1;
+next_value	round
+11	0
+select next value for t1;
+next value for t1
+11
+do setval(t1,12,1);
+select next_value,round from t1;
+next_value	round
+21	0
+select next value for t1;
+next value for t1
+13
+do setval(t1,15,0);
+select next_value,round from t1;
+next_value	round
+21	0
+select next value for t1;
+next value for t1
+15
+select setval(t1,16,0);
+setval(t1,16,0)
+16
+select next value for t1;
+next value for t1
+16
+do setval(t1,1000,0);
+select next value for t1;
+next value for t1
+1000
+select next_value,round from t1;
+next_value	round
+1010	0
+do setval(t1,2000,0);
+select next value for t1;
+next value for t1
+2000
+select next_value,round from t1;
+next_value	round
+2010	0
+select setval(t1,1000,0);
+setval(t1,1000,0)
+NULL
+select next value for t1;
+next value for t1
+2001
+select setval(t1,1000,TRUE);
+setval(t1,1000,TRUE)
+NULL
+select next value for t1;
+next value for t1
+2002
+select next_value,round from t1;
+next_value	round
+2010	0
+select setval(t1,2002,0);
+setval(t1,2002,0)
+NULL
+select next value for t1;
+next value for t1
+2003
+select setval(t1,2010,0);
+setval(t1,2010,0)
+2010
+select next value for t1;
+next value for t1
+2010
+select next_value,round from t1;
+next_value	round
+2020	0
+drop sequence t1;
+#
+# Testing with cycle
+#
+CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
+select next_value,round from t1;
+next_value	round
+1	0
+select setval(t1,100,0);
+setval(t1,100,0)
+100
+select next_value,round from t1;
+next_value	round
+100	0
+select next value for t1;
+next value for t1
+100
+select next_value,round from t1;
+next_value	round
+101	0
+select setval(t1,100,0);
+setval(t1,100,0)
+NULL
+select next_value,round from t1;
+next_value	round
+101	0
+select next value for t1;
+next value for t1
+1
+select next_value,round from t1;
+next_value	round
+11	1
+select next value for t1;
+next value for t1
+2
+select setval(t1,100,0,1);
+setval(t1,100,0,1)
+100
+select next_value,round from t1;
+next_value	round
+100	1
+select next value for t1;
+next value for t1
+100
+select setval(t1,100,1,2);
+setval(t1,100,1,2)
+100
+select next_value,round from t1;
+next_value	round
+101	2
+select next value for t1;
+next value for t1
+1
+select setval(t1,100,0,3);
+setval(t1,100,0,3)
+100
+select next_value,round from t1;
+next_value	round
+100	3
+select next value for t1;
+next value for t1
+100
+drop sequence t1;
+#
+# Testing extreme values
+#
+CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb;
+select next_value,round from t1;
+next_value	round
+1	0
+select setval(t1,200);
+setval(t1,200)
+200
+select next_value,round from t1;
+next_value	round
+101	0
+select next value for t1;
+ERROR HY000: Sequence 'test.t1' has run out
+drop sequence t1;
+CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
+select next_value,round from t1;
+next_value	round
+1	0
+select setval(t1,200);
+setval(t1,200)
+200
+select next_value,round from t1;
+next_value	round
+101	0
+select next value for t1;
+next value for t1
+1
+drop sequence t1;
+CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
+select setval(t1,-10);
+setval(t1,-10)
+-10
+select next_value,round from t1;
+next_value	round
+-20	0
+select next value for t1;
+next value for t1
+-20
+select setval(t1,-15);
+setval(t1,-15)
+NULL
+select next_value,round from t1;
+next_value	round
+-120	0
+select next value for t1;
+next value for t1
+-30
+select setval(t1,-500,FALSE);
+setval(t1,-500,FALSE)
+-500
+select next value for t1;
+next value for t1
+-500
+select next value for t1;
+next value for t1
+-510
+select setval(t1,-525,0);
+setval(t1,-525,0)
+-525
+select next value for t1;
+next value for t1
+-525
+select next value for t1;
+next value for t1
+-535
+drop sequence t1;
+CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
+select setval(t1,-10,0);
+setval(t1,-10,0)
+-10
+select next_value,round from t1;
+next_value	round
+-10	0
+select next value for t1;
+next value for t1
+-10
+drop sequence t1;
+#
+# Other testing
+#
+CREATE SEQUENCE t1;
+select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1);
+setval(t1,10,0)	setval(t1,15,1)	setval(t1,5,1)
+10	15	NULL
+select next value for t1;
+next value for t1
+16
+select next_value,round from t1;
+next_value	round
+1016	0
+explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)`
+drop sequence t1;
+create table t1 (a int);
+select setval(t1,10);
+ERROR 42S02: 'test.t1' is not a SEQUENCE
+drop table t1;
diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test
new file mode 100644
index 00000000000..fe0c0669494
--- /dev/null
+++ b/mysql-test/suite/sql_sequence/setval.test
@@ -0,0 +1,126 @@
+--source include/have_sequence.inc
+--source include/have_innodb.inc
+
+drop table if exists t1;
+
+--echo #
+--echo # Test setval function
+--echo #
+
+CREATE SEQUENCE t1 cache 10 engine=myisam;
+select next_value,round from t1;
+do setval(t1,10);
+select next_value,round from t1;
+select next value for t1;
+do setval(t1,12,1);
+select next_value,round from t1;
+select next value for t1;
+do setval(t1,15,0);
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,16,0);
+select next value for t1;
+do setval(t1,1000,0);
+select next value for t1;
+select next_value,round from t1;
+do setval(t1,2000,0);
+select next value for t1;
+select next_value,round from t1;
+# Set smaller value
+select setval(t1,1000,0);
+select next value for t1;
+select setval(t1,1000,TRUE);
+select next value for t1;
+select next_value,round from t1;
+select setval(t1,2002,0);
+select next value for t1;
+select setval(t1,2010,0);
+select next value for t1;
+select next_value,round from t1;
+drop sequence t1;
+
+--echo #
+--echo # Testing with cycle
+--echo #
+
+CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
+select next_value,round from t1;
+select setval(t1,100,0);
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select setval(t1,100,0);
+select next_value,round from t1;
+select next value for t1;
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,100,0,1);
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,100,1,2);
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,100,0,3);
+select next_value,round from t1;
+select next value for t1;
+drop sequence t1;
+
+--echo #
+--echo # Testing extreme values
+--echo #
+
+CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb;
+select next_value,round from t1;
+select setval(t1,200);
+select next_value,round from t1;
+--error ER_SEQUENCE_RUN_OUT
+select next value for t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
+select next_value,round from t1;
+select setval(t1,200);
+select next_value,round from t1;
+select next value for t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
+select setval(t1,-10);
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,-15);
+select next_value,round from t1;
+select next value for t1;
+select setval(t1,-500,FALSE);
+select next value for t1;
+select next value for t1;
+select setval(t1,-525,0);
+select next value for t1;
+select next value for t1;
+drop sequence t1;
+
+CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
+select setval(t1,-10,0);
+select next_value,round from t1;
+select next value for t1;
+drop sequence t1;
+
+--echo #
+--echo # Other testing
+--echo #
+
+CREATE SEQUENCE t1;
+select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1);
+select next value for t1;
+select next_value,round from t1;
+explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50);
+drop sequence t1;
+
+#
+# Some error testing
+#
+
+create table t1 (a int);
+--error ER_NOT_SEQUENCE
+select setval(t1,10);
+drop table t1;