diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/setval.test')
| -rw-r--r-- | mysql-test/suite/sql_sequence/setval.test | 126 |
1 files changed, 126 insertions, 0 deletions
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; |