diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/other.test')
-rw-r--r-- | mysql-test/suite/sql_sequence/other.test | 423 |
1 files changed, 423 insertions, 0 deletions
diff --git a/mysql-test/suite/sql_sequence/other.test b/mysql-test/suite/sql_sequence/other.test new file mode 100644 index 00000000..9761e426 --- /dev/null +++ b/mysql-test/suite/sql_sequence/other.test @@ -0,0 +1,423 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc + +# +# Test various combinations of operations on sequence +# + +--echo # +--echo # Create and check +--echo # + +--disable_ps2_protocol +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; +--enable_ps2_protocol + +--echo # +--echo # INSERT +--echo # + +create sequence s1; +create sequence s2; +--error ER_NO_DEFAULT_FOR_FIELD +insert into s1 (next_not_cached_value, minimum_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_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; +--disable_ps2_protocol +select next value for s1; +--enable_ps2_protocol +select * from s1; +--error ER_SEQUENCE_INVALID_DATA +insert into s2 values(0, 1, 10, 1, 2, 1, 1, 0); + +select * from s1; +insert into s1 values (next value for s2, 1,9223372036854775806,1,1,1000,0,0); +select * from s1; +select * from s2; +insert into s1 select * from s2; +select * from s1; +drop sequence s1,s2; + +--echo # +--echo # UPDATE and DELETE +--echo # + +create sequence s1; +--error ER_ILLEGAL_HA +update s1 set next_not_cached_value=100; +--error ER_ILLEGAL_HA +delete from s1 where next_not_cached_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" ORDER BY TABLE_NAME; +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; + +--echo # +--echo # Many sequence calls with innodb +--echo # + +--disable_ps2_protocol +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +commit; +select * from s1; +drop sequence s1; + +create sequence s1 cache=1000 engine=innodb; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; + +connect (addconroot, localhost, root,,); +connection addconroot; +start transaction; +select count(nextval(s1)) from seq_1_to_2000; +select * from s1; +commit; +disconnect addconroot; +connection default; +select * from s1; +commit; +drop sequence s1; +--enable_ps2_protocol + +--echo # +--echo # Flush tables with read lock +--echo # + +--disable_ps2_protocol +create sequence s1; +select next value for s1; +flush tables with read lock; +--error 1223 +create sequence s2; +--error 1223 +select next value for s1; +unlock tables; +drop sequence s1; +--enable_ps2_protocol + +--echo # +--echo # MDEV-14761 +--echo # Assertion `!mysql_parse_status || thd->is_error() || +--echo # thd->get_internal_handler()' failed in parse_sql +--echo # + +CREATE SEQUENCE s1; +--error ER_DUP_ARGUMENT +ALTER SEQUENCE s1 MAXVALUE 100 NO MAXVALUE; +DROP SEQUENCE s1; + +--echo # +--echo # Don't allow SEQUENCE to be used with CHECK or virtual fields +--echo # + +CREATE SEQUENCE s1 nocache engine=myisam; +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (next value for s1 > 0)); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (previous value for s1 > 0)); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE table t1 (a int check (setval(s1,10))); +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 (a int, b int as (next value for s1 > 0)); +drop sequence s1; + + +--echo # +--echo # MDEV-13024: Server crashes in my_store_ptr upon DELETE from +--echo # sequence in multi-table format +--echo # +CREATE SEQUENCE s; +CREATE table t1 (a int); +insert into t1 values (1),(2); +--error ER_ILLEGAL_HA +DELETE s FROM s; +--error ER_ILLEGAL_HA +delete t1,s from s,t1; +--error ER_ILLEGAL_HA +delete s,t1 from t1,s; +DROP SEQUENCE s; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20074: Lost connection on update trigger +--echo # + +--echo # INSERT & table +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO t2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop sequence s1; +drop table t1,t2; + + +--echo # INSERT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # INSERT SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +INSERT INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # REPLACE & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) VALUES(old.p_name, old.p_first_name); +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + + +--echo # REPLACE SELECT & view +create sequence s1 increment by 1 start with 1; +create table t1 (p_id integer, p_name varchar(128), p_first_name varchar(128)); +create table t2 (a_id integer default nextval(s1), a_p_name varchar(128), a_p_first_name varchar(128), t timestamp default current_timestamp); +create view v2 as select * from t2; + +insert into t1 values +(1, 'Luo','Frank'),(2, 'Xe','Emma'),(3, 'Li','Anna'),(4, 'Lun','Serg'),(5, 'Xu','Nils'),(6, 'Ja','Ute'),(7, 'Jin','Mike'),(8, 'Lio','Carl'),(9, 'Lang','Kevin'),(10, 'Ling','Lisa'),(11, 'Fang','Frank'),(12, 'Feng','Emma'),(13, 'Tuo','Anna'),(14, 'Tua','Serg'),(15, 'Moa','Nils'),(16, 'Hua','Ute'),(17, 'Xufa','Mike'),(18, 'Lulu','Carl'),(19, 'Hoho','Kevin'),(20, 'Tata','Lisa'); + +DELIMITER $$; + +CREATE TRIGGER tr_upd +BEFORE UPDATE on t1 +FOR EACH ROW +BEGIN +REPLACE INTO v2(a_p_name, a_p_first_name) SELECT old.p_name, old.p_first_name; +END; +$$ +DELIMITER ;$$ + +update t1 set p_first_name='Yunxi' where p_id=1; + +drop view v2; +drop table t1,t2; +drop sequence s1; + +--echo # +--echo # MDEV-19273:Server crash in MDL_ticket::has_stronger_or_equal_type or +--echo # Assertion `thd->mdl_context.is_lock_owner(MDL_key::TABLE, +--echo # table->db.str, table->table_name.str, MDL_SHARED)' failed +--echo # in mysql_rm_table_no_locks +--echo # + +CREATE TABLE t1 (a INT); +CREATE TEMPORARY TABLE tmp (b INT); +LOCK TABLE t1 READ; +--error ER_UNKNOWN_SEQUENCES +DROP SEQUENCE tmp; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE tmp; +--error ER_TABLE_NOT_LOCKED_FOR_WRITE +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; +UNLOCK TABLES; +--error ER_NOT_SEQUENCE2 +DROP SEQUENCE t1; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE t1; + +# Cleanup +DROP TABLE t1; + + +CREATE TABLE t (a INT); +CREATE SEQUENCE s; +LOCK TABLE t WRITE; +--error ER_TABLE_NOT_LOCKED +DROP SEQUENCE s; +--error ER_UNKNOWN_SEQUENCES +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +CREATE TEMPORARY SEQUENCE s; +LOCK TABLE t WRITE; +DROP TEMPORARY SEQUENCE s; +UNLOCK TABLES; +DROP SEQUENCE s; + +create table s(a INT); +CREATE TEMPORARY TABLE s (f INT); +LOCK TABLE t WRITE; +DROP TEMPORARY TABLE s; +CREATE TEMPORARY TABLE s (f INT); +DROP TABLE s; +--error ER_TABLE_NOT_LOCKED +DROP TABLE s; +UNLOCK TABLES; +DROP TABLE s; + +CREATE VIEW v1 as SELECT * FROM t; +CREATE SEQUENCE s; + +DROP SEQUENCE IF EXISTS v1; +DROP VIEW IF EXISTS s; + +DROP VIEW v1; +DROP SEQUENCE s; +DROP TABLE t; +--echo # +--echo # End of 10.3 tests +--echo # + +--echo # +--echo # MDEV-32541 Assertion `!(thd->server_status & (1U | 8192U))' failed in MDL_context::release_transactional_locks +--echo # +create sequence s1; +create sequence s2; +--connect (con1,localhost,root,,) +set session transaction read only; +start transaction; + +--connection default +start transaction; +insert into s2 values (1, 1, 10000, 100, 1, 1000, 0, 0); + +--connection con1 +select lastval(s1); +--send select lastval(s2); + +--connection default +set lock_wait_timeout= 1; +insert into s1 values (1, 1, 10000, 100, 1, 1000, 0, 0); + +--connection con1 +--error ER_LOCK_DEADLOCK +--reap +--disconnect con1 +--connection default +drop sequence s1; +drop sequence s2; + +--echo # +--echo # End of 10.4 tests +--echo # |