mirror of
https://github.com/MariaDB/server.git
synced 2025-02-21 21:03:09 +01:00
8b8c8fcb86
When the sequence is unsigned bigint, it needs to be cast to unsigned for correct computation of the modulus.
432 lines
10 KiB
Text
432 lines
10 KiB
Text
drop table if exists t1;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t1'
|
|
#
|
|
# Test setval function
|
|
#
|
|
CREATE SEQUENCE t1 cache 10 engine=myisam;
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1 0
|
|
do setval(t1,10);
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
11 0
|
|
select next value for t1;
|
|
next value for t1
|
|
11
|
|
do setval(t1,12,1);
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
21 0
|
|
select next value for t1;
|
|
next value for t1
|
|
13
|
|
do setval(t1,15,0);
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
21 0
|
|
select next value for t1;
|
|
next value for t1
|
|
15
|
|
select setval(t1,16,0);
|
|
setval(t1,16,0)
|
|
16
|
|
select next value for t1;
|
|
next value for t1
|
|
16
|
|
do setval(t1,1000,0);
|
|
select next value for t1;
|
|
next value for t1
|
|
1000
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1010 0
|
|
do setval(t1,2000,0);
|
|
select next value for t1;
|
|
next value for t1
|
|
2000
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
2010 0
|
|
select setval(t1,1000,0);
|
|
setval(t1,1000,0)
|
|
NULL
|
|
select next value for t1;
|
|
next value for t1
|
|
2001
|
|
select setval(t1,1000,TRUE);
|
|
setval(t1,1000,TRUE)
|
|
NULL
|
|
select next value for t1;
|
|
next value for t1
|
|
2002
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
2010 0
|
|
select setval(t1,2002,0);
|
|
setval(t1,2002,0)
|
|
NULL
|
|
select next value for t1;
|
|
next value for t1
|
|
2003
|
|
select setval(t1,2010,0);
|
|
setval(t1,2010,0)
|
|
2010
|
|
select next value for t1;
|
|
next value for t1
|
|
2010
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
2020 0
|
|
drop sequence t1;
|
|
#
|
|
# Testing with cycle
|
|
#
|
|
CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1 0
|
|
select setval(t1,100,0);
|
|
setval(t1,100,0)
|
|
100
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
100 0
|
|
select next value for t1;
|
|
next value for t1
|
|
100
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
101 0
|
|
select setval(t1,100,0);
|
|
setval(t1,100,0)
|
|
NULL
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
101 0
|
|
select next value for t1;
|
|
next value for t1
|
|
1
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
11 1
|
|
select next value for t1;
|
|
next value for t1
|
|
2
|
|
select setval(t1,100,0,1);
|
|
setval(t1,100,0,1)
|
|
100
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
100 1
|
|
select next value for t1;
|
|
next value for t1
|
|
100
|
|
select setval(t1,100,1,2);
|
|
setval(t1,100,1,2)
|
|
100
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
101 2
|
|
select next value for t1;
|
|
next value for t1
|
|
1
|
|
select setval(t1,100,0,3);
|
|
setval(t1,100,0,3)
|
|
100
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
100 3
|
|
select next value for t1;
|
|
next value for t1
|
|
100
|
|
drop sequence t1;
|
|
#
|
|
# Testing extreme values
|
|
#
|
|
CREATE SEQUENCE t1 cache=10 maxvalue=100 engine=innodb;
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1 0
|
|
select setval(t1,200);
|
|
setval(t1,200)
|
|
200
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
101 0
|
|
select next value for t1;
|
|
ERROR HY000: Sequence 'test.t1' has run out
|
|
drop sequence t1;
|
|
CREATE SEQUENCE t1 cache=10 maxvalue=100 cycle engine=innodb;
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1 0
|
|
select setval(t1,200);
|
|
setval(t1,200)
|
|
200
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
101 0
|
|
select next value for t1;
|
|
next value for t1
|
|
1
|
|
drop sequence t1;
|
|
CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
|
|
select setval(t1,-10);
|
|
setval(t1,-10)
|
|
-10
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
-20 0
|
|
select next value for t1;
|
|
next value for t1
|
|
-20
|
|
select setval(t1,-15);
|
|
setval(t1,-15)
|
|
NULL
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
-120 0
|
|
select next value for t1;
|
|
next value for t1
|
|
-30
|
|
select setval(t1,-500,FALSE);
|
|
setval(t1,-500,FALSE)
|
|
-500
|
|
select next value for t1;
|
|
next value for t1
|
|
-500
|
|
select next value for t1;
|
|
next value for t1
|
|
-510
|
|
select setval(t1,-525,0);
|
|
setval(t1,-525,0)
|
|
-525
|
|
select next value for t1;
|
|
next value for t1
|
|
-525
|
|
select next value for t1;
|
|
next value for t1
|
|
-535
|
|
drop sequence t1;
|
|
CREATE SEQUENCE t1 cache=10 maxvalue=0 increment=-10;
|
|
select setval(t1,-10,0);
|
|
setval(t1,-10,0)
|
|
-10
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
-10 0
|
|
select next value for t1;
|
|
next value for t1
|
|
-10
|
|
drop sequence t1;
|
|
#
|
|
# Other testing
|
|
#
|
|
CREATE SEQUENCE t1;
|
|
select setval(t1,10,0),setval(t1,15,1),setval(t1,5,1);
|
|
setval(t1,10,0) setval(t1,15,1) setval(t1,5,1)
|
|
10 15 NULL
|
|
select next value for t1;
|
|
next value for t1
|
|
16
|
|
select next_not_cached_value,cycle_count from t1;
|
|
next_not_cached_value cycle_count
|
|
1016 0
|
|
explain extended select setval(t1,100),setval(t1,100,TRUE),setval(t1,100,FALSE,50);
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 select setval(`test`.`t1`,100,1,0) AS `setval(t1,100)`,setval(`test`.`t1`,100,1,0) AS `setval(t1,100,TRUE)`,setval(`test`.`t1`,100,0,50) AS `setval(t1,100,FALSE,50)`
|
|
drop sequence t1;
|
|
create table t1 (a int);
|
|
select setval(t1,10);
|
|
ERROR 42S02: 'test.t1' is not a SEQUENCE
|
|
drop table t1;
|
|
#
|
|
# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
|
|
#
|
|
CREATE SEQUENCE s1;
|
|
SELECT SETVAL(s1,10);
|
|
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
|
|
def SETVAL(s1,10) 8 20 2 Y 32896 0 63
|
|
SETVAL(s1,10)
|
|
10
|
|
DROP SEQUENCE s1;
|
|
#
|
|
# MDEV-15732: Assertion `next_free_value % real_increment == offset &&
|
|
# next_free_value >= reserved_until' failed in
|
|
# sequence_definition::adjust_values upon SETVAL for sequence with
|
|
# INCREMENT 0
|
|
#
|
|
CREATE SEQUENCE s INCREMENT 0;
|
|
SELECT NEXTVAL(s);
|
|
NEXTVAL(s)
|
|
1
|
|
SELECT SETVAL(s, 10);
|
|
SETVAL(s, 10)
|
|
10
|
|
DROP SEQUENCE s;
|
|
# End of 10.3 tests
|
|
#
|
|
# MDEV-28152 Features for sequence
|
|
#
|
|
create sequence s;
|
|
select setval(s, 12345678901234567890);
|
|
setval(s, 12345678901234567890)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
drop sequence s;
|
|
create sequence s increment -1;
|
|
select setval(s, 12345678901234567890);
|
|
setval(s, 12345678901234567890)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
-1
|
|
drop sequence s;
|
|
create sequence s as smallint;
|
|
select setval(s, 55555);
|
|
setval(s, 55555)
|
|
NULL
|
|
select setval(s, -55555);
|
|
setval(s, -55555)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
drop sequence s;
|
|
create sequence s as smallint increment -1;
|
|
select setval(s, 55555);
|
|
setval(s, 55555)
|
|
NULL
|
|
select setval(s, -55555);
|
|
setval(s, -55555)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
-1
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned;
|
|
select setval(s, -123);
|
|
setval(s, -123)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned increment -1;
|
|
select setval(s, -123);
|
|
setval(s, -123)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
18446744073709551614
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned;
|
|
select setval(s, -123);
|
|
setval(s, -123)
|
|
NULL
|
|
select setval(s, 0);
|
|
setval(s, 0)
|
|
0
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
select setval(s, 12345678901234567890);
|
|
setval(s, 12345678901234567890)
|
|
12345678901234567890
|
|
select nextval(s);
|
|
nextval(s)
|
|
12345678901234567891
|
|
select setval(s, 12345678901234567890);
|
|
setval(s, 12345678901234567890)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
12345678901234567892
|
|
select setval(s, 18446744073709551614, 0);
|
|
setval(s, 18446744073709551614, 0)
|
|
18446744073709551614
|
|
select nextval(s);
|
|
nextval(s)
|
|
18446744073709551614
|
|
select nextval(s);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
select setval(s, 12345678901234567890, 0, 1);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
alter sequence s cycle;
|
|
select setval(s, 12345678901234567890, 0, 1);
|
|
setval(s, 12345678901234567890, 0, 1)
|
|
12345678901234567890
|
|
select nextval(s);
|
|
nextval(s)
|
|
12345678901234567890
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned;
|
|
select setval(s, 18446744073709551616);
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '18446744073709551616)' at line 1
|
|
select setval(s, 18446744073709551615);
|
|
setval(s, 18446744073709551615)
|
|
18446744073709551615
|
|
select nextval(s);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned;
|
|
select setval(s, 18446744073709551615, 0);
|
|
setval(s, 18446744073709551615, 0)
|
|
18446744073709551615
|
|
select nextval(s);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned cycle;
|
|
select setval(s, 18446744073709551615);
|
|
setval(s, 18446744073709551615)
|
|
18446744073709551615
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned cycle;
|
|
select setval(s, 18446744073709551615, 0);
|
|
setval(s, 18446744073709551615, 0)
|
|
18446744073709551615
|
|
select nextval(s);
|
|
nextval(s)
|
|
1
|
|
drop sequence s;
|
|
create sequence s as bigint unsigned minvalue 123 maxvalue 456;
|
|
select setval(s, 100);
|
|
setval(s, 100)
|
|
NULL
|
|
select nextval(s);
|
|
nextval(s)
|
|
123
|
|
select setval(s, 500);
|
|
setval(s, 500)
|
|
500
|
|
select nextval(s);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
drop sequence s;
|
|
create sequence s as smallint;
|
|
select setval(s, 32767);
|
|
setval(s, 32767)
|
|
32767
|
|
select nextval(s);
|
|
ERROR HY000: Sequence 'test.s' has run out
|
|
drop sequence s;
|
|
#
|
|
# MDEV-33836 Assertion `(ulonglong) next_free_value % real_increment == (ulonglong) offset' failed in void sequence_definition::adjust_values(longlong)
|
|
#
|
|
CREATE SEQUENCE s AS BIGINT UNSIGNED START WITH 9223372036854775800 INCREMENT 0;
|
|
set @old_AUTO_INCREMENT_INCREMENT=@@global.AUTO_INCREMENT_INCREMENT;
|
|
set global AUTO_INCREMENT_INCREMENT=100;
|
|
SELECT SETVAL (s,12345678901234567890);
|
|
SETVAL (s,12345678901234567890)
|
|
12345678901234567890
|
|
drop sequence s;
|
|
set global AUTO_INCREMENT_INCREMENT=@old_AUTO_INCREMENT_INCREMENT;
|
|
# End of 11.5 tests
|