diff options
Diffstat (limited to 'mysql-test/suite')
18 files changed, 2599 insertions, 15 deletions
diff --git a/mysql-test/suite/funcs_1/r/is_columns_is.result b/mysql-test/suite/funcs_1/r/is_columns_is.result index c88a3a9ac8d..ba3bd6b4f90 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is.result @@ -339,6 +339,18 @@ def information_schema SCHEMA_PRIVILEGES IS_GRANTABLE 5 NULL NO varchar 3 9 NULL def information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL def information_schema SCHEMA_PRIVILEGES TABLE_CATALOG 2 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) select NEVER NULL def information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL +def information_schema SEQUENCES CYCLE_OPTION 12 NULL NO varchar 3 9 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(3) select NEVER NULL +def information_schema SEQUENCES DATA_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL +def information_schema SEQUENCES INCREMENT 11 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL +def information_schema SEQUENCES MAXIMUM_VALUE 10 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL +def information_schema SEQUENCES MINIMUM_VALUE 9 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL +def information_schema SEQUENCES NUMERIC_PRECISION 5 NULL NO int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL +def information_schema SEQUENCES NUMERIC_PRECISION_RADIX 6 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL +def information_schema SEQUENCES NUMERIC_SCALE 7 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL +def information_schema SEQUENCES SEQUENCE_CATALOG 1 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) select NEVER NULL +def information_schema SEQUENCES SEQUENCE_NAME 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL +def information_schema SEQUENCES SEQUENCE_SCHEMA 2 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL +def information_schema SEQUENCES START_VALUE 8 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) select NEVER NULL def information_schema SESSION_STATUS VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL def information_schema SESSION_STATUS VARIABLE_VALUE 2 NULL NO varchar 2048 6144 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(2048) select NEVER NULL def information_schema SESSION_VARIABLES VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) select NEVER NULL @@ -890,6 +902,18 @@ NULL information_schema ROUTINES LAST_ALTERED datetime NULL NULL NULL NULL datet 3.0000 information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SCHEMA_PRIVILEGES IS_GRANTABLE varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) +3.0000 information_schema SEQUENCES SEQUENCE_CATALOG varchar 512 1536 utf8mb3 utf8mb3_general_ci varchar(512) +3.0000 information_schema SEQUENCES SEQUENCE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema SEQUENCES SEQUENCE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema SEQUENCES DATA_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +NULL information_schema SEQUENCES NUMERIC_PRECISION int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES NUMERIC_PRECISION_RADIX int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES NUMERIC_SCALE int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES START_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES MINIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES MAXIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES INCREMENT bigint NULL NULL NULL NULL bigint(21) +3.0000 information_schema SEQUENCES CYCLE_OPTION varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) 3.0000 information_schema SESSION_STATUS VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SESSION_STATUS VARIABLE_VALUE varchar 2048 6144 utf8mb3 utf8mb3_general_ci varchar(2048) 3.0000 information_schema SESSION_VARIABLES VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) diff --git a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result index bb12a0c38df..65f601168b7 100644 --- a/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_columns_is_embedded.result @@ -339,6 +339,18 @@ def information_schema SCHEMA_PRIVILEGES IS_GRANTABLE 5 NULL NO varchar 3 9 NULL def information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL def information_schema SCHEMA_PRIVILEGES TABLE_CATALOG 2 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) NEVER NULL def information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL +def information_schema SEQUENCES CYCLE_OPTION 12 NULL NO varchar 3 9 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(3) NEVER NULL +def information_schema SEQUENCES DATA_TYPE 4 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL +def information_schema SEQUENCES INCREMENT 11 NULL NO bigint NULL NULL 19 0 NULL NULL NULL bigint(21) NEVER NULL +def information_schema SEQUENCES MAXIMUM_VALUE 10 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL +def information_schema SEQUENCES MINIMUM_VALUE 9 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL +def information_schema SEQUENCES NUMERIC_PRECISION 5 NULL NO int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL +def information_schema SEQUENCES NUMERIC_PRECISION_RADIX 6 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL +def information_schema SEQUENCES NUMERIC_SCALE 7 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL +def information_schema SEQUENCES SEQUENCE_CATALOG 1 NULL NO varchar 512 1536 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(512) NEVER NULL +def information_schema SEQUENCES SEQUENCE_NAME 3 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL +def information_schema SEQUENCES SEQUENCE_SCHEMA 2 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL +def information_schema SEQUENCES START_VALUE 8 NULL NO decimal NULL NULL 21 0 NULL NULL NULL decimal(21,0) NEVER NULL def information_schema SESSION_STATUS VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL def information_schema SESSION_STATUS VARIABLE_VALUE 2 NULL NO varchar 2048 6144 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(2048) NEVER NULL def information_schema SESSION_VARIABLES VARIABLE_NAME 1 NULL NO varchar 64 192 NULL NULL NULL utf8mb3 utf8mb3_general_ci varchar(64) NEVER NULL @@ -890,6 +902,18 @@ NULL information_schema ROUTINES LAST_ALTERED datetime NULL NULL NULL NULL datet 3.0000 information_schema SCHEMA_PRIVILEGES TABLE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SCHEMA_PRIVILEGES PRIVILEGE_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SCHEMA_PRIVILEGES IS_GRANTABLE varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) +3.0000 information_schema SEQUENCES SEQUENCE_CATALOG varchar 512 1536 utf8mb3 utf8mb3_general_ci varchar(512) +3.0000 information_schema SEQUENCES SEQUENCE_SCHEMA varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema SEQUENCES SEQUENCE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +3.0000 information_schema SEQUENCES DATA_TYPE varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) +NULL information_schema SEQUENCES NUMERIC_PRECISION int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES NUMERIC_PRECISION_RADIX int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES NUMERIC_SCALE int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES START_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES MINIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES MAXIMUM_VALUE decimal NULL NULL NULL NULL decimal(21,0) +NULL information_schema SEQUENCES INCREMENT bigint NULL NULL NULL NULL bigint(21) +3.0000 information_schema SEQUENCES CYCLE_OPTION varchar 3 9 utf8mb3 utf8mb3_general_ci varchar(3) 3.0000 information_schema SESSION_STATUS VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) 3.0000 information_schema SESSION_STATUS VARIABLE_VALUE varchar 2048 6144 utf8mb3 utf8mb3_general_ci varchar(2048) 3.0000 information_schema SESSION_VARIABLES VARIABLE_NAME varchar 64 192 utf8mb3 utf8mb3_general_ci varchar(64) diff --git a/mysql-test/suite/funcs_1/r/is_tables_is.result b/mysql-test/suite/funcs_1/r/is_tables_is.result index c18f733c86f..8c81a79b605 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is.result @@ -739,6 +739,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME SEQUENCES +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME SESSION_STATUS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1855,6 +1880,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME SEQUENCES +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME SESSION_STATUS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result index c18f733c86f..8c81a79b605 100644 --- a/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result +++ b/mysql-test/suite/funcs_1/r/is_tables_is_embedded.result @@ -739,6 +739,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME SEQUENCES +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME SESSION_STATUS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY @@ -1855,6 +1880,31 @@ user_comment Separator ----------------------------------------------------- TABLE_CATALOG def TABLE_SCHEMA information_schema +TABLE_NAME SEQUENCES +TABLE_TYPE SYSTEM VIEW +ENGINE MEMORY +VERSION 11 +ROW_FORMAT Fixed +TABLE_ROWS #TBLR# +AVG_ROW_LENGTH #ARL# +DATA_LENGTH #DL# +MAX_DATA_LENGTH #MDL# +INDEX_LENGTH #IL# +DATA_FREE #DF# +AUTO_INCREMENT NULL +CREATE_TIME #CRT# +UPDATE_TIME #UT# +CHECK_TIME #CT# +TABLE_COLLATION utf8mb3_general_ci +CHECKSUM NULL +CREATE_OPTIONS #CO# +TABLE_COMMENT #TC# +MAX_INDEX_LENGTH #MIL# +TEMPORARY Y +user_comment +Separator ----------------------------------------------------- +TABLE_CATALOG def +TABLE_SCHEMA information_schema TABLE_NAME SESSION_STATUS TABLE_TYPE SYSTEM VIEW ENGINE MEMORY diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result index 90de2ebfcc0..2b1a9d3fd40 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -248,3 +248,423 @@ SELECT NEXTVAL(s); NEXTVAL(s) 1 DROP SEQUENCE s; +# +# MDEV-28152 Features for sequence +# +create sequence s maxvalue 12345; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 123456789012345678901234; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +drop sequence s; +create sequence s as tinyint; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +alter sequence s as int; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +alter sequence s maxvalue 12345; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 12345 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +drop sequence s; +create sequence s; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +alter sequence s maxvalue 123; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +alter sequence s as tinyint; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 123 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +drop sequence s; +create sequence s as int; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +alter sequence s as tinyint; +ERROR 22003: Out of range value for column 'maximum_value' at row 1 +alter sequence s maxvalue 126; +alter sequence s as tinyint; +drop sequence s; +create sequence s as tinyint; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `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 +alter sequence s as int maxvalue 123; +ERROR 42000: This version of MariaDB doesn't yet support 'ALTER SEQUENCE with both AS <type> and something else.' +drop sequence s; +create sequence s as smallint; +select next value for s; +next value for s +1 +alter sequence s as int; +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as tinyint; +select next value for s; +next value for s +1 +alter sequence s cache 100; +select next value for s; +ERROR HY000: Sequence 'test.s' has run out +drop sequence s; +create sequence s as int; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +alter sequence s as int unsigned; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table s; +Table Create Table +s CREATE TABLE `s` ( + `next_not_cached_value` int(12) unsigned NOT NULL, + `minimum_value` int(12) unsigned NOT NULL, + `maximum_value` int(12) unsigned NOT NULL, + `start_value` int(12) 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 +drop sequence s; +create sequence s as tinyint; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 126; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 63; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint start with 1 minvalue 1 maxvalue 63 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +ERROR HY000: Sequence 'test.s' has run out +drop sequence s; +create sequence s as tinyint unsigned; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 254; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 120; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as tinyint unsigned start with 1 minvalue 1 maxvalue 120 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +ERROR HY000: Sequence 'test.s' has run out +drop sequence s; +create sequence s as smallint; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 32766; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 16030; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint start with 1 minvalue 1 maxvalue 16030 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as smallint unsigned; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 65534; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 32000; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as smallint unsigned start with 1 minvalue 1 maxvalue 32000 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as mediumint; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 8388606; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 4223212; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint start with 1 minvalue 1 maxvalue 4223212 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as mediumint unsigned; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 16777214; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 8389231; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as mediumint unsigned start with 1 minvalue 1 maxvalue 8389231 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as int; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 2147483646; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 1234567890; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int start with 1 minvalue 1 maxvalue 1234567890 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as int unsigned; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 4294967294; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 2123834923; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as int unsigned start with 1 minvalue 1 maxvalue 2123834923 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as bigint; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 9223372036854775806; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 4683883928492758294; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 4683883928492758294 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; +create sequence s as bigint unsigned; +alter sequence s maxvalue 123456789012345678901; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +alter sequence s maxvalue 18446744073709551614; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1 +alter sequence s maxvalue 9432738420582397432; +show create sequence s; +Table Create Table +s CREATE SEQUENCE `s` as bigint unsigned start with 1 minvalue 1 maxvalue 9432738420582397432 increment by 1 cache 1000 nocycle ENGINE=MyISAM +select next value for s; +next value for s +1001 +drop sequence s; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test index a5e6245d609..b47629b8902 100644 --- a/mysql-test/suite/sql_sequence/alter.test +++ b/mysql-test/suite/sql_sequence/alter.test @@ -161,3 +161,198 @@ CREATE SEQUENCE s; ALTER TABLE s ORDER BY cache_size; SELECT NEXTVAL(s); DROP SEQUENCE s; + + +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +# truncation in alter sequence +create sequence s maxvalue 12345; +show create sequence s; +alter sequence s maxvalue 123456789012345678901234; +show create sequence s; +drop sequence s; + +# alter first from a narrower type to a wider type, then maxvalue +create sequence s as tinyint; +show create sequence s; +show create table s; +alter sequence s as int; +show create sequence s; +show create table s; +alter sequence s maxvalue 12345; +show create sequence s; +show create table s; +drop sequence s; + +# alter first maxvalue then from a wider type to a narrower type +create sequence s; +show create sequence s; +show create table s; +alter sequence s maxvalue 123; +show create sequence s; +show create table s; +alter sequence s as tinyint; +show create sequence s; +show create table s; +drop sequence s; + +# from a wider type to a narrower type with out of range values +create sequence s as int; +show create sequence s; +show create table s; +--error ER_WARN_DATA_OUT_OF_RANGE +alter sequence s as tinyint; +alter sequence s maxvalue 126; +alter sequence s as tinyint; +drop sequence s; + +# cannot alter both value type and something else yet. +create sequence s as tinyint; +show create sequence s; +show create table s; +--error ER_NOT_SUPPORTED_YET +alter sequence s as int maxvalue 123; +drop sequence s; + +# alter sequence causes discarding of cache values +## alter type then next +create sequence s as smallint; +select next value for s; # 1 +alter sequence s as int; +select next value for s; # 1001 +drop sequence s; + +## alter a tinyint sequence +create sequence s as tinyint; +select next value for s; # 1 +alter sequence s cache 100; +--error ER_SEQUENCE_RUN_OUT +select next value for s; +drop sequence s; + +# from signed to unsigned +create sequence s as int; +show create sequence s; +show create table s; +alter sequence s as int unsigned; +show create sequence s; +show create table s; +drop sequence s; + +# for each type +create sequence s as tinyint; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 126; +show create sequence s; +select next value for s; +alter sequence s maxvalue 63; +show create sequence s; +#fixme: should not run out +--error ER_SEQUENCE_RUN_OUT +select next value for s; +drop sequence s; + +create sequence s as tinyint unsigned; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 254; +show create sequence s; +select next value for s; +alter sequence s maxvalue 120; +show create sequence s; +#fixme: should not run out +--error ER_SEQUENCE_RUN_OUT +select next value for s; +drop sequence s; + +create sequence s as smallint; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 32766; +show create sequence s; +select next value for s; +alter sequence s maxvalue 16030; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as smallint unsigned; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 65534; +show create sequence s; +select next value for s; +alter sequence s maxvalue 32000; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as mediumint; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 8388606; +show create sequence s; +select next value for s; +alter sequence s maxvalue 4223212; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as mediumint unsigned; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 16777214; +show create sequence s; +select next value for s; +alter sequence s maxvalue 8389231; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as int; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 2147483646; +show create sequence s; +select next value for s; +alter sequence s maxvalue 1234567890; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as int unsigned; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 4294967294; +show create sequence s; +select next value for s; +alter sequence s maxvalue 2123834923; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as bigint; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 9223372036854775806; +show create sequence s; +select next value for s; +alter sequence s maxvalue 4683883928492758294; +show create sequence s; +select next value for s; +drop sequence s; + +create sequence s as bigint unsigned; +alter sequence s maxvalue 123456789012345678901; +show create sequence s; +alter sequence s maxvalue 18446744073709551614; +show create sequence s; +select next value for s; +alter sequence s maxvalue 9432738420582397432; +show create sequence s; +select next value for s; +drop sequence s; + diff --git a/mysql-test/suite/sql_sequence/auto_increment.result b/mysql-test/suite/sql_sequence/auto_increment.result index bf0a04bbaf1..7fcf26d6cf6 100644 --- a/mysql-test/suite/sql_sequence/auto_increment.result +++ b/mysql-test/suite/sql_sequence/auto_increment.result @@ -28,3 +28,17 @@ nextval(s) 1997 drop sequence s; set global auto_increment_increment= default, auto_increment_offset= default; +# +# MDEV-28152 Features for sequence +# +set global auto_increment_increment= 100; +set global auto_increment_offset= 42; +create sequence s as bigint unsigned start with 9223372036854775800 increment 0; +select next value for s; +next value for s +9223372036854775842 +select next value for s; +next value for s +9223372036854775942 +drop sequence s; +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/auto_increment.test b/mysql-test/suite/sql_sequence/auto_increment.test index 335bef7ea37..e9d5a253757 100644 --- a/mysql-test/suite/sql_sequence/auto_increment.test +++ b/mysql-test/suite/sql_sequence/auto_increment.test @@ -28,3 +28,15 @@ drop sequence s; # Clean up set global auto_increment_increment= default, auto_increment_offset= default; + +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +set global auto_increment_increment= 100; +set global auto_increment_offset= 42; +create sequence s as bigint unsigned start with 9223372036854775800 increment 0; +select next value for s; +select next value for s; +drop sequence s; +set global auto_increment_increment= default, auto_increment_offset= default; diff --git a/mysql-test/suite/sql_sequence/concurrent_create.result b/mysql-test/suite/sql_sequence/concurrent_create.result index 2473abef37d..a03f512a4c3 100644 --- a/mysql-test/suite/sql_sequence/concurrent_create.result +++ b/mysql-test/suite/sql_sequence/concurrent_create.result @@ -22,6 +22,23 @@ select * from s2; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1 1 9223372036854775806 1 1 1000 0 0 DROP SEQUENCE s1, s2; +# +# MDEV-28152 Features for sequence +# +CREATE SEQUENCE s1 as mediumint unsigned ENGINE=InnoDB; +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; +execute stmt; +drop table s2; +execute stmt; +show create sequence s2; +Table Create Table +s2 CREATE SEQUENCE `s2` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=InnoDB +drop table s2; +execute stmt; +select * from s2; +next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count +1 1 16777214 1 1 1000 0 0 +DROP SEQUENCE s1, s2; CREATE SEQUENCE s1 ENGINE=InnoDB; connect con1,localhost,root,,test; CREATE TABLE s2 LIKE s1;; diff --git a/mysql-test/suite/sql_sequence/concurrent_create.test b/mysql-test/suite/sql_sequence/concurrent_create.test index b27a6d3bdb9..1a88b1a6d3e 100644 --- a/mysql-test/suite/sql_sequence/concurrent_create.test +++ b/mysql-test/suite/sql_sequence/concurrent_create.test @@ -38,6 +38,21 @@ execute stmt; select * from s2; DROP SEQUENCE s1, s2; +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +CREATE SEQUENCE s1 as mediumint unsigned ENGINE=InnoDB; +PREPARE stmt FROM "CREATE TABLE s2 LIKE s1"; +execute stmt; +drop table s2; +execute stmt; +show create sequence s2; +drop table s2; +execute stmt; +select * from s2; +DROP SEQUENCE s1, s2; + # # MDEV-15117 Server crashes in in open_and_process_table or ASAN # heap-use-after-free in is_temporary_table upon creating/flushing sequences diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 9326d7016c9..5b888993368 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -182,15 +182,13 @@ create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; 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 'NO MINVALUE' at line 1 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; 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 'NO MINVALUE' at line 1 -create sequence t1 start with 10 maxvalue=9223372036854775807; -ERROR HY000: Sequence 'test.t1' has out of range value for options -create sequence t1 start with 10 minvalue=-9223372036854775808; -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 '9223372036854775808' at line 1 create sequence t1 RESTART WITH 10; 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 'RESTART' at line 1 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; -drop sequence t1; -create sequence t1; +ERROR HY000: Option 'MINVALUE' used twice in statement +create sequence s increment by 3234567890123456789; +ERROR HY000: Sequence 'test.s' has out of range value for options +create or replace sequence t1; show fields from t1; Field Type Null Key Default Extra next_not_cached_value bigint(21) NO NULL @@ -317,7 +315,7 @@ CREATE OR REPLACE TABLE t1 ( `cycle_option` tinyint(1) unsigned NOT NULL, `cycle_count` bigint(21) NOT NULL ) sequence=1; -ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value) +ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value) CREATE OR REPLACE TABLE t1 ( `next_not_cached_value` bigint(21) NOT NULL, `minimum_value` bigint(21) NOT NULL, @@ -716,3 +714,622 @@ CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; ERROR HY000: Trigger's 'seq1' is a view, temporary table or sequence DROP SEQUENCE seq1; # End of 10.4 test +###### +# MDEV-28152 Features for sequence +###### +# ----- +# Truncating out-of-bound numbers for minvalue and maxvalue +# ----- +create or replace sequence t1 minvalue -999999999999999999999; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue -9223372036854775808; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue -9223372036854775807; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue 9223372036854775805; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 9223372036854775805 minvalue 9223372036854775805 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue 9223372036854775806; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9223372036854775807; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9223372036854775808; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9999999999999999999999; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775806 minvalue -9223372036854775807 maxvalue -9223372036854775806 increment by -1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775806; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775807; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775808; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9999999999999999999999; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +# ----- +# Create with value types +# ----- +create or replace sequence t1 as tinyint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint start with 1 minvalue 1 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 +create or replace sequence t1 as smallint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 as mediumint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) NOT NULL, + `minimum_value` mediumint(10) NOT NULL, + `maximum_value` mediumint(10) NOT NULL, + `start_value` mediumint(10) 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 +create or replace sequence t1 as int; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +create or replace sequence t1 as bigint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +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 +create or replace sequence t1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 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 +create or replace sequence t1 as smallint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 as mediumint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) 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 +create or replace sequence t1 as int unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) unsigned NOT NULL, + `minimum_value` int(12) unsigned NOT NULL, + `maximum_value` int(12) unsigned NOT NULL, + `start_value` int(12) 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 +create or replace sequence t1 as bigint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) 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 +create or replace sequence t1 increment -1 as tinyint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint start with -1 minvalue -127 maxvalue -1 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 +create or replace sequence t1 increment -1 as smallint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with -1 minvalue -32767 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 increment -1 as mediumint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint start with -1 minvalue -8388607 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) NOT NULL, + `minimum_value` mediumint(10) NOT NULL, + `maximum_value` mediumint(10) NOT NULL, + `start_value` mediumint(10) 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 +create or replace sequence t1 increment -1 as int; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int start with -1 minvalue -2147483647 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) 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 +create or replace sequence t1 increment -1 as bigint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +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 +create or replace sequence t1 increment -1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 254 minvalue 1 maxvalue 254 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 +create or replace sequence t1 increment -1 as smallint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 65534 minvalue 1 maxvalue 65534 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 increment -1 as mediumint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 16777214 minvalue 1 maxvalue 16777214 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) 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 +create or replace sequence t1 increment -1 as int unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int unsigned start with 4294967294 minvalue 1 maxvalue 4294967294 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) unsigned NOT NULL, + `minimum_value` int(12) unsigned NOT NULL, + `maximum_value` int(12) unsigned NOT NULL, + `start_value` int(12) 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 +create or replace sequence t1 increment -1 as bigint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 18446744073709551614 minvalue 1 maxvalue 18446744073709551614 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) 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 +create or replace sequence t1 as tinyint zerofill; +ERROR HY000: Incorrect value 'ZEROFILL' for option 'AS' +create or replace sequence t1 as bigint unsigned start with 12345678901234567890; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 12345678901234567890 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) 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 +create or replace sequence t1 as bigint unsigned increment 12345678901234567; +ERROR HY000: Sequence 'test.t1' has out of range value for options +# ----- +# value types + truncating +# ----- +create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 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 +create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with -32767 minvalue -32767 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) 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 +create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) 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 +create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 12345678901234 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) 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 +# ----- +# indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser. +# ----- +create or replace sequence t1 as bigint maxvalue -1 increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +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 +create or replace sequence t1 as bigint maxvalue 18446744073709551615; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +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 +create or replace sequence t1 as bigint unsigned maxvalue -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) 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 +# ----- +# value types + out of range start +# ----- +create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned; +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 '123456789012345678901 as tinyint unsigned' at line 1 +create or replace sequence t1 start with -1 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 0 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 start with 1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 start with 254 as tinyint unsigned; +create or replace sequence t1 start with 255 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 256 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned; +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 '123456789012345678901 as tinyint unsigned' at line 1 +drop sequence t1; +# ----- +# information_schema.sequences +# ----- +create sequence s1 as tinyint unsigned increment by 23; +create sequence s2 start with 42 minvalue -9223372036854775807; +create sequence s3 as bigint unsigned start with 12345678901234567890 cycle; +create sequence s4 as tinyint; +create sequence s5 as smallint; +create sequence s6 as mediumint; +create sequence s7 as int; +create sequence s8 as bigint; +create sequence s9 as tinyint unsigned; +create sequence s10 as smallint unsigned; +create sequence s11 as mediumint unsigned; +create sequence s12 as int unsigned; +create sequence s13 as bigint unsigned; +select * from information_schema.sequences order by sequence_name; +SEQUENCE_CATALOG SEQUENCE_SCHEMA SEQUENCE_NAME DATA_TYPE NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE START_VALUE MINIMUM_VALUE MAXIMUM_VALUE INCREMENT CYCLE_OPTION +def test s1 tinyint unsigned 8 2 0 1 1 254 23 0 +def test s10 smallint unsigned 16 2 0 1 1 65534 1 0 +def test s11 mediumint unsigned 24 2 0 1 1 16777214 1 0 +def test s12 int unsigned 32 2 0 1 1 4294967294 1 0 +def test s13 bigint unsigned 64 2 0 1 1 18446744073709551614 1 0 +def test s2 bigint 64 2 0 42 -9223372036854775807 9223372036854775806 1 0 +def test s3 bigint unsigned 64 2 0 12345678901234567890 1 18446744073709551614 1 1 +def test s4 tinyint 8 2 0 1 1 126 1 0 +def test s5 smallint 16 2 0 1 1 32766 1 0 +def test s6 mediumint 24 2 0 1 1 8388606 1 0 +def test s7 int 32 2 0 1 1 2147483646 1 0 +def test s8 bigint 64 2 0 1 1 9223372036854775806 1 0 +def test s9 tinyint unsigned 8 2 0 1 1 254 1 0 +drop sequence s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13; diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test index 49b39fbb40c..07bf9b29090 100644 --- a/mysql-test/suite/sql_sequence/create.test +++ b/mysql-test/suite/sql_sequence/create.test @@ -114,21 +114,18 @@ create or replace sequence t1 maxvalue=13, increment= -1; create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; --error ER_PARSE_ERROR create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; ---error ER_SEQUENCE_INVALID_DATA -create sequence t1 start with 10 maxvalue=9223372036854775807; ---error ER_PARSE_ERROR -create sequence t1 start with 10 minvalue=-9223372036854775808; --error ER_PARSE_ERROR create sequence t1 RESTART WITH 10; - -# This should probably give an error +--error ER_DUP_ARGUMENT create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; -drop sequence t1; +# hidden constraints cache < (LONGLONG_MAX - max_increment) / max_increment +--error ER_SEQUENCE_INVALID_DATA +create sequence s increment by 3234567890123456789; # # Test with LIST COLUMNS as first command # -create sequence t1; +create or replace sequence t1; show fields from t1; flush tables; show fields from t1; @@ -553,3 +550,206 @@ CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; DROP SEQUENCE seq1; --echo # End of 10.4 test + +--echo ###### +--echo # MDEV-28152 Features for sequence +--echo ###### + +--echo # ----- +--echo # Truncating out-of-bound numbers for minvalue and maxvalue +--echo # ----- +--disable_ps_protocol +create or replace sequence t1 minvalue -999999999999999999999; +show create sequence t1; +create or replace sequence t1 minvalue -9223372036854775808; +show create sequence t1; +create or replace sequence t1 minvalue -9223372036854775807; +show create sequence t1; +create or replace sequence t1 minvalue 9223372036854775805; +show create sequence t1; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 minvalue 9223372036854775806; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 minvalue 9223372036854775807; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 minvalue 9223372036854775808; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 minvalue 9999999999999999999999; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; +create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; +show create sequence t1; +create or replace sequence t1 maxvalue 9223372036854775806; +show create sequence t1; +create or replace sequence t1 maxvalue 9223372036854775807; +show create sequence t1; +create or replace sequence t1 maxvalue 9223372036854775808; +show create sequence t1; +create or replace sequence t1 maxvalue 9999999999999999999999; +show create sequence t1; +--enable_ps_protocol + +--echo # ----- +--echo # Create with value types +--echo # ----- +create or replace sequence t1 as tinyint; +show create sequence t1; +show create table t1; +create or replace sequence t1 as smallint; +show create sequence t1; +show create table t1; +create or replace sequence t1 as mediumint; +show create sequence t1; +show create table t1; +create or replace sequence t1 as int; +show create sequence t1; +show create table t1; +create or replace sequence t1 as bigint; +show create sequence t1; +show create table t1; +create or replace sequence t1 as tinyint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 as smallint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 as mediumint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 as int unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 as bigint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as tinyint; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as smallint; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as mediumint; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as int; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as bigint; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as tinyint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as smallint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as mediumint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as int unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 increment -1 as bigint unsigned; +show create sequence t1; +show create table t1; + +#zerofill is not supported +--error ER_BAD_OPTION_VALUE +create or replace sequence t1 as tinyint zerofill; + +#start with a number between longlong_max and ulonglong_max +create or replace sequence t1 as bigint unsigned start with 12345678901234567890; +show create sequence t1; +show create table t1; + +# hidden constraints cache < (LONGLONG_MAX - max_increment) / max_increment +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 as bigint unsigned increment 12345678901234567; + +--echo # ----- +--echo # value types + truncating +--echo # ----- +--disable_ps_protocol +create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint; +show create sequence t1; +show create table t1; +create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned; +show create sequence t1; +show create table t1; +create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234; +show create sequence t1; +show create table t1; +create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234; +show create sequence t1; +show create table t1; +--enable_ps_protocol + +--echo # ----- +--echo # indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser. +--echo # ----- +#signed, -1: no truncation. Note that we need a negative increment because this is a nagative sequence +create or replace sequence t1 as bigint maxvalue -1 increment by -1; +show create sequence t1; +show create table t1; +--disable_ps_protocol +#signed, ulonglong_max: turncating to longlong_max-1 +create or replace sequence t1 as bigint maxvalue 18446744073709551615; +show create sequence t1; +show create table t1; +#unsigned, -1: truncation and invalid data (max_value truncated to 1 which is equal to min_value) +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 as bigint unsigned maxvalue -1; +#unsigned, ulonglong_max: truncating to ulonglong_max-1 +create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615; +show create sequence t1; +show create table t1; +--enable_ps_protocol + +--echo # ----- +--echo # value types + out of range start +--echo # ----- +--error ER_PARSE_ERROR +create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 start with -1 as tinyint unsigned; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 start with 0 as tinyint unsigned; +show create sequence t1; +create or replace sequence t1 start with 1 as tinyint unsigned; +show create sequence t1; +create or replace sequence t1 start with 254 as tinyint unsigned; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 start with 255 as tinyint unsigned; +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 start with 256 as tinyint unsigned; +--error ER_PARSE_ERROR +create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned; + +drop sequence t1; + +--echo # ----- +--echo # information_schema.sequences +--echo # ----- +create sequence s1 as tinyint unsigned increment by 23; +create sequence s2 start with 42 minvalue -9223372036854775807; +create sequence s3 as bigint unsigned start with 12345678901234567890 cycle; +#test support of all types in information schema +create sequence s4 as tinyint; +create sequence s5 as smallint; +create sequence s6 as mediumint; +create sequence s7 as int; +create sequence s8 as bigint; +create sequence s9 as tinyint unsigned; +create sequence s10 as smallint unsigned; +create sequence s11 as mediumint unsigned; +create sequence s12 as int unsigned; +create sequence s13 as bigint unsigned; +select * from information_schema.sequences order by sequence_name; +drop sequence s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13; diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result index 0199bb7162f..b6936a2794e 100644 --- a/mysql-test/suite/sql_sequence/mysqldump.result +++ b/mysql-test/suite/sql_sequence/mysqldump.result @@ -73,3 +73,145 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si 1 1 9223372036854775806 1 1 1000 0 0 unlock tables; drop table t1; +# +# MDEV-28152 Features for sequence +# +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE s1 as tinyint; +CREATE SEQUENCE s2 as smallint; +CREATE SEQUENCE s3 as mediumint; +CREATE SEQUENCE s4 as int; +CREATE SEQUENCE s5 as bigint; +CREATE SEQUENCE s6 as tinyint unsigned; +CREATE SEQUENCE s7 as smallint unsigned; +CREATE SEQUENCE s8 as mediumint unsigned; +CREATE SEQUENCE s9 as int unsigned; +CREATE SEQUENCE s10 as bigint unsigned; +# Dump database 1 +# Restore from database 1 to database 2 +SETVAL(`s1`, 1, 0) +1 +SETVAL(`s10`, 1, 0) +1 +SETVAL(`s2`, 1, 0) +1 +SETVAL(`s3`, 1, 0) +1 +SETVAL(`s4`, 1, 0) +1 +SETVAL(`s5`, 1, 0) +1 +SETVAL(`s6`, 1, 0) +1 +SETVAL(`s7`, 1, 0) +1 +SETVAL(`s8`, 1, 0) +1 +SETVAL(`s9`, 1, 0) +1 +USE test2; +SHOW CREATE SEQUENCE s1; +Table Create Table +s1 CREATE SEQUENCE `s1` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s2; +Table Create Table +s2 CREATE SEQUENCE `s2` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s3; +Table Create Table +s3 CREATE SEQUENCE `s3` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s4; +Table Create Table +s4 CREATE SEQUENCE `s4` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s5; +Table Create Table +s5 CREATE SEQUENCE `s5` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s6; +Table Create Table +s6 CREATE SEQUENCE `s6` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s7; +Table Create Table +s7 CREATE SEQUENCE `s7` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s8; +Table Create Table +s8 CREATE SEQUENCE `s8` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s9; +Table Create Table +s9 CREATE SEQUENCE `s9` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=InnoDB +SHOW CREATE SEQUENCE s10; +Table Create Table +s10 CREATE SEQUENCE `s10` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=InnoDB +DROP DATABASE test1; +DROP DATABASE test2; +## test ORACLE mode +set sql_mode=ORACLE; +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE s1 as tinyint; +CREATE SEQUENCE s2 as smallint; +CREATE SEQUENCE s3 as mediumint; +CREATE SEQUENCE s4 as int; +CREATE SEQUENCE s5 as bigint; +CREATE SEQUENCE s6 as tinyint unsigned; +CREATE SEQUENCE s7 as smallint unsigned; +CREATE SEQUENCE s8 as mediumint unsigned; +CREATE SEQUENCE s9 as int unsigned; +CREATE SEQUENCE s10 as bigint unsigned; +# Dump database 1 +# Restore from database 1 to database 2 +SETVAL(`s1`, 1, 0) +1 +SETVAL(`s10`, 1, 0) +1 +SETVAL(`s2`, 1, 0) +1 +SETVAL(`s3`, 1, 0) +1 +SETVAL(`s4`, 1, 0) +1 +SETVAL(`s5`, 1, 0) +1 +SETVAL(`s6`, 1, 0) +1 +SETVAL(`s7`, 1, 0) +1 +SETVAL(`s8`, 1, 0) +1 +SETVAL(`s9`, 1, 0) +1 +USE test2; +SHOW CREATE SEQUENCE s1; +Table Create Table +s1 CREATE SEQUENCE "s1" start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s2; +Table Create Table +s2 CREATE SEQUENCE "s2" start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s3; +Table Create Table +s3 CREATE SEQUENCE "s3" start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s4; +Table Create Table +s4 CREATE SEQUENCE "s4" start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s5; +Table Create Table +s5 CREATE SEQUENCE "s5" start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s6; +Table Create Table +s6 CREATE SEQUENCE "s6" start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s7; +Table Create Table +s7 CREATE SEQUENCE "s7" start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s8; +Table Create Table +s8 CREATE SEQUENCE "s8" start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s9; +Table Create Table +s9 CREATE SEQUENCE "s9" start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle +SHOW CREATE SEQUENCE s10; +Table Create Table +s10 CREATE SEQUENCE "s10" start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle +DROP DATABASE test1; +DROP DATABASE test2; +set sql_mode=default; diff --git a/mysql-test/suite/sql_sequence/mysqldump.test b/mysql-test/suite/sql_sequence/mysqldump.test index d2afb2fd675..dab61f1bfda 100644 --- a/mysql-test/suite/sql_sequence/mysqldump.test +++ b/mysql-test/suite/sql_sequence/mysqldump.test @@ -35,3 +35,83 @@ LOCK TABLES t1 READ; SELECT * FROM t1; unlock tables; drop table t1; + + +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE s1 as tinyint; +CREATE SEQUENCE s2 as smallint; +CREATE SEQUENCE s3 as mediumint; +CREATE SEQUENCE s4 as int; +CREATE SEQUENCE s5 as bigint; +CREATE SEQUENCE s6 as tinyint unsigned; +CREATE SEQUENCE s7 as smallint unsigned; +CREATE SEQUENCE s8 as mediumint unsigned; +CREATE SEQUENCE s9 as int unsigned; +CREATE SEQUENCE s10 as bigint unsigned; + +--echo # Dump database 1 +--exec $MYSQL_DUMP test1 > $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # Restore from database 1 to database 2 + +--exec $MYSQL test2 < $MYSQLTEST_VARDIR/tmp/dumptest1.sql +USE test2; +SHOW CREATE SEQUENCE s1; +SHOW CREATE SEQUENCE s2; +SHOW CREATE SEQUENCE s3; +SHOW CREATE SEQUENCE s4; +SHOW CREATE SEQUENCE s5; +SHOW CREATE SEQUENCE s6; +SHOW CREATE SEQUENCE s7; +SHOW CREATE SEQUENCE s8; +SHOW CREATE SEQUENCE s9; +SHOW CREATE SEQUENCE s10; + +--remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql +DROP DATABASE test1; +DROP DATABASE test2; + +--echo ## test ORACLE mode +set sql_mode=ORACLE; + +CREATE DATABASE test1; +CREATE DATABASE test2; +USE test1; +CREATE SEQUENCE s1 as tinyint; +CREATE SEQUENCE s2 as smallint; +CREATE SEQUENCE s3 as mediumint; +CREATE SEQUENCE s4 as int; +CREATE SEQUENCE s5 as bigint; +CREATE SEQUENCE s6 as tinyint unsigned; +CREATE SEQUENCE s7 as smallint unsigned; +CREATE SEQUENCE s8 as mediumint unsigned; +CREATE SEQUENCE s9 as int unsigned; +CREATE SEQUENCE s10 as bigint unsigned; + +--echo # Dump database 1 +--exec $MYSQL_DUMP test1 > $MYSQLTEST_VARDIR/tmp/dumptest1.sql +--echo # Restore from database 1 to database 2 + +--exec $MYSQL test2 < $MYSQLTEST_VARDIR/tmp/dumptest1.sql +USE test2; +SHOW CREATE SEQUENCE s1; +SHOW CREATE SEQUENCE s2; +SHOW CREATE SEQUENCE s3; +SHOW CREATE SEQUENCE s4; +SHOW CREATE SEQUENCE s5; +SHOW CREATE SEQUENCE s6; +SHOW CREATE SEQUENCE s7; +SHOW CREATE SEQUENCE s8; +SHOW CREATE SEQUENCE s9; +SHOW CREATE SEQUENCE s10; + +--remove_file $MYSQLTEST_VARDIR/tmp/dumptest1.sql +DROP DATABASE test1; +DROP DATABASE test2; + +set sql_mode=default; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index 9d55921006b..b2ec860b74f 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -548,3 +548,302 @@ 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; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index a80f9fad561..62caad8e215 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -297,3 +297,182 @@ SELECT SETVAL (v,0); UNLOCK TABLES; DROP VIEW v; + +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2; +show create sequence t1; +show create table t1; +select * from t1; +select next value for t1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; + +create or replace sequence t1 as tinyint unsigned minvalue 1 maxvalue 2 cycle; +show create sequence t1; +show create table t1; +select * from t1; +select next value for t1; +select next value for t1; +select next value for t1; + +--disable_ps_protocol +create or replace sequence t1 minvalue -23 maxvalue 99999 as tinyint; +--enable_ps_protocol +show create sequence t1; +show create table t1; +select * from t1; +select next value for t1; +select next value for t1; +select next value for t1; + +#test previous value for bigint unsigned +create or replace sequence t1 as bigint unsigned start with 18446744073709551614; +select previous value for t1; +select next value for t1; +select previous value for t1; + +create or replace sequence t1 as tinyint start with 126; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as tinyint unsigned start with 254; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as smallint start with 32766; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as smallint unsigned start with 65534; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as mediumint start with 8388606; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as mediumint unsigned start with 16777214; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as int start with 2147483646; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as int unsigned start with 4294967294; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as bigint start with 9223372036854775806; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as bigint unsigned start with 18446744073709551614; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as tinyint start with -127 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as tinyint unsigned start with 1 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as smallint start with -32767 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as smallint unsigned start with 1 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as mediumint start with -8388607 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as mediumint unsigned start with 1 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as int start with -2147483647 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as int unsigned start with 1 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as bigint start with -9223372036854775807 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +create or replace sequence t1 as bigint unsigned start with 1 increment -1; +select next value for t1; +--error ER_SEQUENCE_RUN_OUT +select next value for t1; +alter sequence t1 cycle; +select next value for t1; + +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/setval.result b/mysql-test/suite/sql_sequence/setval.result index 504d460fea5..598c7395423 100644 --- a/mysql-test/suite/sql_sequence/setval.result +++ b/mysql-test/suite/sql_sequence/setval.result @@ -269,3 +269,152 @@ 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; diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test index 1993bdbe077..b45533f63d1 100644 --- a/mysql-test/suite/sql_sequence/setval.test +++ b/mysql-test/suite/sql_sequence/setval.test @@ -154,3 +154,100 @@ DROP SEQUENCE s; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-28152 Features for sequence +--echo # + +# out of bounds for the type: returns NULL +create sequence s; +select setval(s, 12345678901234567890); +select nextval(s); +drop sequence s; + +create sequence s increment -1; +select setval(s, 12345678901234567890); +select nextval(s); +drop sequence s; + +create sequence s as smallint; +select setval(s, 55555); +select setval(s, -55555); +select nextval(s); +drop sequence s; + +create sequence s as smallint increment -1; +select setval(s, 55555); +select setval(s, -55555); +select nextval(s); +drop sequence s; + +create sequence s as bigint unsigned; +select setval(s, -123); +select nextval(s); +drop sequence s; + +create sequence s as bigint unsigned increment -1; +select setval(s, -123); +select nextval(s); +drop sequence s; + +# large setval values for bigint unsigned +create sequence s as bigint unsigned; +select setval(s, -123); +select setval(s, 0); +select nextval(s); +select setval(s, 12345678901234567890); +select nextval(s); +select setval(s, 12345678901234567890); +select nextval(s); +select setval(s, 18446744073709551614, 0); +select nextval(s); +--error ER_SEQUENCE_RUN_OUT +select nextval(s); +--error ER_SEQUENCE_RUN_OUT +select setval(s, 12345678901234567890, 0, 1); +alter sequence s cycle; +select setval(s, 12345678901234567890, 0, 1); +select nextval(s); +drop sequence s; + +# extreme setval values for bigint unsigned +create sequence s as bigint unsigned; +--error ER_PARSE_ERROR +select setval(s, 18446744073709551616); +select setval(s, 18446744073709551615); +--error ER_SEQUENCE_RUN_OUT +select nextval(s); +drop sequence s; + +create sequence s as bigint unsigned; +select setval(s, 18446744073709551615, 0); +--error ER_SEQUENCE_RUN_OUT +select nextval(s); +drop sequence s; + +create sequence s as bigint unsigned cycle; +select setval(s, 18446744073709551615); +select nextval(s); +drop sequence s; + +create sequence s as bigint unsigned cycle; +select setval(s, 18446744073709551615, 0); +select nextval(s); +drop sequence s; + +# a small range for bigint unsigned +create sequence s as bigint unsigned minvalue 123 maxvalue 456; +select setval(s, 100); +select nextval(s); +select setval(s, 500); +--error ER_SEQUENCE_RUN_OUT +select nextval(s); +drop sequence s; + +create sequence s as smallint; +select setval(s, 32767); +--error ER_SEQUENCE_RUN_OUT +select nextval(s); +drop sequence s; |