mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-02-05 21:32:18 +01:00
Code Issues Projects Releases Packages Wiki Activity Actions
mariadb/mysql-test/suite/sql_sequence/next.result
Oleksandr Byelkin 9e1fb104a3 MariaDB 11.4.4 release 
-----BEGIN PGP SIGNATURE-----
 
 iQIzBAABCgAdFiEEF39AEP5WyjM2MAMF8WVvJMdM0dgFAmck77AACgkQ8WVvJMdM
 0dgccQ/+Lls8fWt4D+gMPP7x+drJSO/IE/gZFt3ugbWF+/p3B2xXAs5AAE83wxEh
 QSbp4DCkb/9PnuakhLmzg0lFbxMUlh4rsJ1YyiuLB2J+YgKbAc36eQQf+rtYSipd
 DT5uRk36c9wOcOXo/mMv4APEvpPXBIBdIL4VvpKFbIOE7xT24Sp767zWXdXqrB1f
 JgOQdM2ct+bvSPC55oZ5p1kqyxwvd6K6+3RB3CIpwW9zrVSLg7enT3maLjj/761s
 jvlRae+Cv+r+Hit9XpmEH6n2FYVgIJ3o3WhdAHwN0kxKabXYTg7OCB7QxDZiUHI9
 C/5goKmKaPB1PCQyuTQyLSyyK9a8nPfgn6tqw/p/ZKDQhKT9sWJv/5bSWecrVndx
 LLYifSTrFC/eXLzgPvCnNv/U8SjsZaAdMIKS681+qDJ0P5abghUIlGnMYTjYXuX1
 1B6Vrr0bdrQ3V1CLB3tpkRjpUvicrsabtuAUAP65QnEG2G9UJXklOer+DE291Gsl
 f1I0o6C1zVGAOkUUD3QEYaHD8w7hlvyfKme5oXKUm3DOjaAar5UUKLdr6prxRZL4
 ebhmGEy42Mf8fBYoeohIxmxgvv6h2Xd9xCukgPp8hFpqJGw8abg7JNZTTKH4h2IY
 J51RpD10h4eoi6WRn3opEcjexTGvZ+xNR7yYO5WxWw6VIre9IUA=
 =s+WW
 -----END PGP SIGNATURE-----

Merge tag '11.4' into 11.6

MariaDB 11.4.4 release
2024-11-08 07:17:00 +01:00

852 lines
23 KiB
Text
Raw Permalink Blame History

CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 2 cycle;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`next_not_cached_value` bigint(21) NOT NULL,
`minimum_value` bigint(21) NOT NULL,
`maximum_value` bigint(21) NOT NULL,
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
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
3 0
select next value for t1;
next value for t1
2
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
3 0
select next value for t1;
next value for t1
3
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
5 0
select next value for t1;
next value for t1
4
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
5 0
select next value for t1;
next value for t1
5
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
7 0
select next value for t1;
next value for t1
6
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
7 0
select next value for t1;
next value for t1
7
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
9 0
select next value for t1;
next value for t1
8
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
9 0
select next value for t1;
next value for t1
9
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
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
1
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
3 1
select NEXT VALUE for t1,seq from seq_1_to_20;
NEXT VALUE for t1 seq
2 1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
1 10
2 11
3 12
4 13
5 14
6 15
7 16
8 17
9 18
10 19
1 20
drop sequence t1;
CREATE SEQUENCE t1 minvalue 1 maxvalue 10 increment by -1 cache 2 cycle engine=aria;
select next value for t1;
next value for t1
10
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 0
select next value for t1;
next value for t1
9
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 0
select next value for t1;
next value for t1
8
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
6 0
select next value for t1;
next value for t1
7
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
6 0
select next value for t1;
next value for t1
6
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
4 0
select next value for t1;
next value for t1
5
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
4 0
select next value for t1;
next value for t1
4
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
2 0
select next value for t1;
next value for t1
3
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
2 0
select next value for t1;
next value for t1
2
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
0 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
0 0
select next value for t1;
next value for t1
10
select next_not_cached_value,cycle_count from t1;
next_not_cached_value cycle_count
8 1
select NEXT VALUE for t1,seq from seq_1_to_20;
NEXT VALUE for t1 seq
9 1
8 2
7 3
6 4
5 5
4 6
3 7
2 8
1 9
10 10
9 11
8 12
7 13
6 14
5 15
4 16
3 17
2 18
1 19
10 20
drop sequence t1;
CREATE SEQUENCE t1 start with 8 minvalue 1 maxvalue 10 increment by 1 cache 2 nocycle;
select next value for t1;
next value for t1
8
select next value for t1;
next value for t1
9
select next value for t1;
next value for t1
10
select previous value for t1;
previous value for t1
10
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
select previous value for t1;
previous value for t1
NULL
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
drop sequence t1;
create sequence s1 start with 1 cache 2 maxvalue 5;
select next value for s1;
next value for s1
1
select next value for s1;
next value for s1
2
select next value for s1;
next value for s1
3
select next value for s1;
next value for s1
4
select next value for s1;
next value for s1
5
select next value for s1;
ERROR HY000: Sequence 'test.s1' has run out
drop sequence s1;
CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 100 increment by 1 cache 10;
select next value for t1;
next value for t1
1
select * from t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
11 1 100 1 1 10 0 0
flush tables;
select next value for t1;
next value for t1
11
select nextval(t1);
nextval(t1)
12
drop sequence t1;
CREATE SEQUENCE t9 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select previous value for t9;
previous value for t9
NULL
select next value for t9;
next value for t9
1
select previous value for t9, lastval(t9);
previous value for t9 lastval(t9)
1 1
select next value for t9;
next value for t9
2
select previous value for t9, lastval(t9);
previous value for t9 lastval(t9)
2 2
select seq, previous value for t9, NEXT VALUE for t9, previous value for t9 from seq_1_to_20;
seq previous value for t9 NEXT VALUE for t9 previous value for t9
1 2 3 3
2 3 4 4
3 4 5 5
4 5 6 6
5 6 7 7
6 7 8 8
7 8 9 9
8 9 10 10
9 10 1 1
10 1 2 2
11 2 3 3
12 3 4 4
13 4 5 5
14 5 6 6
15 6 7 7
16 7 8 8
17 8 9 9
18 9 10 10
19 10 1 1
20 1 2 2
select * from t9;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
6 1 10 1 1 5 1 2
drop sequence t9;
CREATE SEQUENCE s1 cache=0;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 9223372036854775806 1 1 0 0 0
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
2
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1 cache=1;
select next_not_cached_value from s1;
next_not_cached_value
1
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
2
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1 cache=2;
select next_not_cached_value from s1;
next_not_cached_value
1
select next value for s1;
next value for s1
1
select next_not_cached_value from s1;
next_not_cached_value
3
select next value for s1;
next value for s1
2
select next_not_cached_value from s1;
next_not_cached_value
3
DROP SEQUENCE s1;
CREATE SEQUENCE s1;
select next value for s1;
next value for s1
1
select next value for s1;
next value for s1
2
select next value for s1;
next value for s1
3
select next value for s1;
next value for s1
4
alter sequence s1 increment -2;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1001 1 9223372036854775806 1 -2 1000 0 0
select next value for s1;
next value for s1
1001
select next value for s1;
next value for s1
999
alter sequence s1 restart 6;
select next value for s1;
next value for s1
6
select next value for s1;
next value for s1
4
select next value for s1;
next value for s1
2
select next value for s1;
ERROR HY000: Sequence 'test.s1' has run out
DROP SEQUENCE s1;
CREATE SEQUENCE t1 start with 1 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select next value for t1;
next value for t1
1
select previous value for t1;
previous value for t1
1
flush tables;
select previous value for t1;
previous value for t1
1
drop sequence t1;
select previous value for t1;
ERROR 42S02: Unknown SEQUENCE: 't1'
CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
select previous value for t1;
previous value for t1
NULL
select next value for t1;
next value for t1
5
select previous value for t1;
previous value for t1
5
drop sequence t1;
CREATE or replace SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999
INCREMENT BY 1 START WITH 3984356 CACHE 20 CYCLE engine=innodb;
show create table s1;
Table Create Table
s1 CREATE TABLE `s1` (
`next_not_cached_value` bigint(21) NOT NULL,
`minimum_value` bigint(21) NOT NULL,
`maximum_value` bigint(21) NOT NULL,
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984356 1 9999999999 3984356 1 20 1 0
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984356
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984357
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984358
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984376 1 9999999999 3984356 1 20 1 0
FLUSH TABLES;
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984376 1 9999999999 3984356 1 20 1 0
select NEXT VALUE FOR s1;
NEXT VALUE FOR s1
3984376
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984396 1 9999999999 3984356 1 20 1 0
drop sequence s1;
CREATE SEQUENCE t1 start with 5 minvalue 1 maxvalue 10 increment by 1 cache 5 cycle;
explain select next value for t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
explain select next value for t1, minimum_value from t1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1
drop table t1;
CREATE SEQUENCE s1;
CREATE TABLE t1 (a int);
insert into t1 values (next value for s1);
insert into t1 values (next value for s1);
select * from t1;
a
1
2
drop table t1,s1;
CREATE SEQUENCE s1;
CREATE TABLE t1 (a int primary key auto_increment, b int default 0) engine=myisam;
insert into t1 values (),(),(),(),(),(),();
update t1 set b= next value for s1 where a <= 3;
select * from t1;
a b
1 1
2 2
3 3
4 0
5 0
6 0
7 0
drop table t1,s1;
CREATE OR REPLACE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999 INCREMENT BY 1 START WITH 3984356 nocache CYCLE engine='innodb';
select * from s1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
3984356 1 9999999999 3984356 1 0 1 0
select next value for s1;
next value for s1
3984356
explain extended select next value for s1;
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 nextval(`test`.`s1`) AS `next value for s1`
explain extended select previous value for s1;
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 lastval(`test`.`s1`) AS `previous value for s1`
drop sequence s1;
create table t1 (a int);
select next value for t1;
ERROR 42S02: 'test.t1' is not a SEQUENCE
drop table t1;
create sequence t1;
select next value for t1;
next value for t1
1
select next value for t1, minimum_value;
ERROR 42S22: Unknown column 'minimum_value' in 'SELECT'
drop sequence t1;
#
# MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
#
CREATE SEQUENCE s1;
SELECT
NEXT VALUE FOR s1,
PREVIOUS VALUE FOR s1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def NEXT VALUE FOR s1 8 20 1 Y 32896 0 63
def PREVIOUS VALUE FOR s1 8 20 1 Y 32896 0 63
NEXT VALUE FOR s1 PREVIOUS VALUE FOR s1
1 1
DROP SEQUENCE s1;
#
# MDEV-13720 ER_NOT_SEQUENCE for temporary table
#
create temporary table tmp (i int);
select next value for tmp;
ERROR 42S02: 'test.tmp' is not a SEQUENCE
drop table tmp;
#
# Test negative numbers
#
create sequence s start with 1 minvalue=-1000 maxvalue=1000 increment -1;
select next value for s;
next value for s
1
select next value for s;
next value for s
0
flush tables;
select next value for s;
next value for s
-999
drop sequence s;
#
# MDEV-23823 NEXT VALUE crash on locked view
#
CREATE VIEW v AS SELECT 1;
LOCK TABLE v READ;
SELECT NEXT VALUE FOR v;
ERROR 42S02: 'test.v' is not a SEQUENCE
#
# MDEV-24018: SIGSEGV in Item_func_nextval::update_table on SELECT SETVAL
#
SELECT SETVAL (v,0);
ERROR 42S02: 'test.v' is not a SEQUENCE
UNLOCK TABLES;
DROP VIEW v;
#
# MDEV-28152 Features for sequence
#
create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2;
show create sequence t1;
Table Create Table
t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 2 increment by 1 cache 1000 nocycle ENGINE=MyISAM
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`next_not_cached_value` tinyint(5) unsigned NOT NULL,
`minimum_value` tinyint(5) unsigned NOT NULL,
`maximum_value` tinyint(5) unsigned NOT NULL,
`start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
select * from t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 2 1 1 1000 0 0
select next value for t1;
next value for t1
1
select next value for t1;
next value for t1
2
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2 cycle;
show create sequence t1;
Table Create Table
t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 2 increment by 1 cache 1000 cycle ENGINE=MyISAM
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`next_not_cached_value` tinyint(5) unsigned NOT NULL,
`minimum_value` tinyint(5) unsigned NOT NULL,
`maximum_value` tinyint(5) unsigned NOT NULL,
`start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
select * from t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
1 1 2 1 1 1000 1 0
select next value for t1;
next value for t1
1
select next value for t1;
next value for t1
2
select next value for t1;
next value for t1
1
create or replace sequence t1 minvalue -23 maxvalue 99999 as tinyint;
Warnings:
Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE'
show create sequence t1;
Table Create Table
t1 CREATE SEQUENCE `t1` as tinyint start with -23 minvalue -23 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`next_not_cached_value` tinyint(5) NOT NULL,
`minimum_value` tinyint(5) NOT NULL,
`maximum_value` tinyint(5) NOT NULL,
`start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=MyISAM SEQUENCE=1
select * from t1;
next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
-23 -23 126 -23 1 1000 0 0
select next value for t1;
next value for t1
-23
select next value for t1;
next value for t1
-22
select next value for t1;
next value for t1
-21
create or replace sequence t1 as bigint unsigned start with 18446744073709551614;
select previous value for t1;
previous value for t1
NULL
select next value for t1;
next value for t1
18446744073709551614
select previous value for t1;
previous value for t1
18446744073709551614
create or replace sequence t1 as tinyint start with 126;
select next value for t1;
next value for t1
126
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as tinyint unsigned start with 254;
select next value for t1;
next value for t1
254
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as smallint start with 32766;
select next value for t1;
next value for t1
32766
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as smallint unsigned start with 65534;
select next value for t1;
next value for t1
65534
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as mediumint start with 8388606;
select next value for t1;
next value for t1
8388606
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as mediumint unsigned start with 16777214;
select next value for t1;
next value for t1
16777214
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as int start with 2147483646;
select next value for t1;
next value for t1
2147483646
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as int unsigned start with 4294967294;
select next value for t1;
next value for t1
4294967294
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as bigint start with 9223372036854775806;
select next value for t1;
next value for t1
9223372036854775806
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as bigint unsigned start with 18446744073709551614;
select next value for t1;
next value for t1
18446744073709551614
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
1
create or replace sequence t1 as tinyint start with -127 increment -1;
select next value for t1;
next value for t1
-127
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
-1
create or replace sequence t1 as tinyint unsigned start with 1 increment -1;
select next value for t1;
next value for t1
1
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
254
create or replace sequence t1 as smallint start with -32767 increment -1;
select next value for t1;
next value for t1
-32767
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
-1
create or replace sequence t1 as smallint unsigned start with 1 increment -1;
select next value for t1;
next value for t1
1
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
65534
create or replace sequence t1 as mediumint start with -8388607 increment -1;
select next value for t1;
next value for t1
-8388607
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
-1
create or replace sequence t1 as mediumint unsigned start with 1 increment -1;
select next value for t1;
next value for t1
1
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
16777214
create or replace sequence t1 as int start with -2147483647 increment -1;
select next value for t1;
next value for t1
-2147483647
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
-1
create or replace sequence t1 as int unsigned start with 1 increment -1;
select next value for t1;
next value for t1
1
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
4294967294
create or replace sequence t1 as bigint start with -9223372036854775807 increment -1;
select next value for t1;
next value for t1
-9223372036854775807
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
-1
create or replace sequence t1 as bigint unsigned start with 1 increment -1;
select next value for t1;
next value for t1
1
select next value for t1;
ERROR HY000: Sequence 'test.t1' has run out
alter sequence t1 cycle;
select next value for t1;
next value for t1
18446744073709551614
drop sequence t1;
#
# End of 11.5 tests
#
Reference in a new issue View git blame Copy permalink