From 71fa413c165e644f8f1433356f95fed12579fe3e Mon Sep 17 00:00:00 2001
From: Monty <monty@mariadb.org>
Date: Mon, 8 May 2017 02:44:55 +0300
Subject: MDEV-10139 Support for SEQUENCE objects

- SETVAL(sequence_name, next_value, is_used, round)
- ALTER SEQUENCE, including RESTART WITH

Other things:
- Added handler::extra() option HA_EXTRA_PREPARE_FOR_ALTER_TABLE to signal
  ha_sequence() that it should allow write_row statments.
- ALTER ONLINE TABLE now works with SEQUENCE:s
---
 mysql-test/suite/sql_sequence/alter.test | 139 +++++++++++++++++++++++++++++++
 1 file changed, 139 insertions(+)
 create mode 100644 mysql-test/suite/sql_sequence/alter.test

(limited to 'mysql-test/suite/sql_sequence/alter.test')

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;
-- 
cgit v1.2.1