Merge remote-tracking branch 'origin/bb-10.2-ext' into 10.310.3-MDEV-10953

1 files changed, 139 insertions, 0 deletions

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;