diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/alter.test')
| -rw-r--r-- | mysql-test/suite/sql_sequence/alter.test | 178 |
1 files changed, 178 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 00000000..dab0e665 --- /dev/null +++ b/mysql-test/suite/sql_sequence/alter.test @@ -0,0 +1,178 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +drop table if exists t1; + +--echo # +--echo # Test alter sequence +--echo # + +--disable_ps2_protocol +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; +--error ER_SEQUENCE_INVALID_DATA +alter sequence t1 minvalue=100 start=100; +alter sequence t1 minvalue=100 start=100 restart=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_not_cached_value, cycle_count 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; + +--error ER_UNKNOWN_SEQUENCES +alter sequence if exists t1 minvalue=100; +--error ER_UNKNOWN_SEQUENCES +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; + +# +# MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X' +# failed in lock_rec_lock +# + +CREATE SEQUENCE t1 engine=innodb; +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value); +DROP SEQUENCE t1; + +# +# MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't +# find record) after ALTER .. ORDER BY +# + +CREATE SEQUENCE s; +--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE +ALTER TABLE s ORDER BY cache_size; +SELECT NEXTVAL(s); +DROP SEQUENCE s; + +--echo # +--echo # MDEV-31607 ER_DUP_KEY in mysql.table_stats upon REANME on sequence +--echo # + +CREATE SEQUENCE s1 ENGINE=InnoDB; +CREATE SEQUENCE s2 ENGINE=InnoDB; +SHOW CREATE SEQUENCE s1; +SHOW CREATE SEQUENCE s2; +DROP SEQUENCE s2; +RENAME TABLE s1 TO s2; +DROP SEQUENCE s2; + +--enable_ps2_protocol |