diff options
author | Yuchen Pei <yuchen.pei@mariadb.com> | 2023-02-08 12:29:52 +1100 |
---|---|---|
committer | Yuchen Pei <yuchen.pei@mariadb.com> | 2023-03-20 17:37:09 +1100 |
commit | 725c759a1ad22cf9c11b6a1415d44c5249467c8b (patch) | |
tree | d7f80d2363880f844ec4845631b3904ea17e9259 | |
parent | ce4a289f1c367987977f1a02bbb8d8b8e8e6bb53 (diff) | |
download | mariadb-git-bb-11.0-mdev-28152-squashed.tar.gz |
MDEV-28152 Features for sequencesbb-11.0-mdev-28152-squashed
- Add `as <int_type>` to sequence creation options
- int_type can be signed or unsigned integer types, including
tinyint, smallint, mediumint, int and bigint
- Limitation: when alter sequence as <new_int_type>, cannot have any
other alter options in the same statement
- Limitation: increment remains signed longlong, and the hidden
constraint (cache_size x abs(increment) < longlong_max) stays for
unsigned types. This means for bigint unsigned, neither
abs(increment) nor (cache_size x abs(increment)) can be between
longlong_max and ulonglong_max
- Truncating maxvalue and minvalue from user input to the nearest max
or min value of the type, plus or minus 1. When the truncation
happens, a warning is emitted
- Information schema table for sequences
33 files changed, 3400 insertions, 229 deletions
diff --git a/mysql-test/main/func_hybrid_type.result b/mysql-test/main/func_hybrid_type.result index 550f61d46e8..1a50d84aa3a 100644 --- a/mysql-test/main/func_hybrid_type.result +++ b/mysql-test/main/func_hybrid_type.result @@ -4148,7 +4148,7 @@ ERROR HY000: Illegal parameter data types bigint unsigned and row for operation # COALESCE CREATE TABLE t1 (a TINYINT UNSIGNED, b TINYINT); SELECT COALESCE(a,a)=ROW(1,1) FROM t1; -ERROR HY000: Illegal parameter data types tiny unsigned and row for operation '=' +ERROR HY000: Illegal parameter data types tinyint unsigned and row for operation '=' SELECT COALESCE(b,b)=ROW(1,1) FROM t1; ERROR HY000: Illegal parameter data types tinyint and row for operation '=' DROP TABLE t1; @@ -4278,7 +4278,7 @@ DROP TABLE t1; # SUM CREATE TABLE t1 (a TINYINT UNSIGNED, b TINYINT); SELECT MAX(a)=ROW(1,1) FROM t1; -ERROR HY000: Illegal parameter data types tiny unsigned and row for operation '=' +ERROR HY000: Illegal parameter data types tinyint unsigned and row for operation '=' SELECT MAX(b)=ROW(1,1) FROM t1; ERROR HY000: Illegal parameter data types tinyint and row for operation '=' DROP TABLE t1; diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index e46014e44b9..ce111c58796 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -81,6 +81,7 @@ REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SCHEMA_PRIVILEGES +SEQUENCES SESSION_STATUS SESSION_VARIABLES SPATIAL_REF_SYS diff --git a/mysql-test/main/information_schema_all_engines.result b/mysql-test/main/information_schema_all_engines.result index 23a853e363c..316871995c1 100644 --- a/mysql-test/main/information_schema_all_engines.result +++ b/mysql-test/main/information_schema_all_engines.result @@ -52,6 +52,7 @@ REFERENTIAL_CONSTRAINTS ROUTINES SCHEMATA SCHEMA_PRIVILEGES +SEQUENCES SESSION_STATUS SESSION_VARIABLES SPATIAL_REF_SYS @@ -133,6 +134,7 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA +SEQUENCES SEQUENCE_SCHEMA SESSION_STATUS VARIABLE_NAME SESSION_VARIABLES VARIABLE_NAME SPATIAL_REF_SYS SRID @@ -214,6 +216,7 @@ REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA ROUTINES ROUTINE_SCHEMA SCHEMATA SCHEMA_NAME SCHEMA_PRIVILEGES TABLE_SCHEMA +SEQUENCES SEQUENCE_SCHEMA SESSION_STATUS VARIABLE_NAME SESSION_VARIABLES VARIABLE_NAME SPATIAL_REF_SYS SRID @@ -299,6 +302,7 @@ REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS 1 ROUTINES information_schema.ROUTINES 1 SCHEMATA information_schema.SCHEMATA 1 SCHEMA_PRIVILEGES information_schema.SCHEMA_PRIVILEGES 1 +SEQUENCES information_schema.SEQUENCES 1 SESSION_STATUS information_schema.SESSION_STATUS 1 SESSION_VARIABLES information_schema.SESSION_VARIABLES 1 SPATIAL_REF_SYS information_schema.SPATIAL_REF_SYS 1 @@ -369,6 +373,7 @@ Database: information_schema | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | +| SEQUENCES | | SESSION_STATUS | | SESSION_VARIABLES | | SPATIAL_REF_SYS | @@ -440,6 +445,7 @@ Database: INFORMATION_SCHEMA | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | +| SEQUENCES | | SESSION_STATUS | | SESSION_VARIABLES | | SPATIAL_REF_SYS | @@ -463,5 +469,5 @@ Wildcard: inf_rmation_schema | information_schema | SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') GROUP BY TABLE_SCHEMA; table_schema count(*) -information_schema 66 +information_schema 67 mysql 31 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; diff --git a/sql/ha_sequence.cc b/sql/ha_sequence.cc index b348e6e7025..8f511ba3a7f 100644 --- a/sql/ha_sequence.cc +++ b/sql/ha_sequence.cc @@ -227,7 +227,7 @@ int ha_sequence::write_row(const uchar *buf) int error= 0; /* This is called from alter table */ tmp_seq.read_fields(table); - if (tmp_seq.check_and_adjust(0)) + if (tmp_seq.check_and_adjust(thd, 0)) DBUG_RETURN(HA_ERR_SEQUENCE_INVALID_DATA); sequence->copy(&tmp_seq); if (likely(!(error= file->write_row(buf)))) @@ -258,7 +258,7 @@ int ha_sequence::write_row(const uchar *buf) DBUG_RETURN(ER_LOCK_WAIT_TIMEOUT); tmp_seq.read_fields(table); - if (tmp_seq.check_and_adjust(0)) + if (tmp_seq.check_and_adjust(thd, 0)) DBUG_RETURN(HA_ERR_SEQUENCE_INVALID_DATA); /* diff --git a/sql/handler.h b/sql/handler.h index 6b05da2ca98..f573dc835e9 100644 --- a/sql/handler.h +++ b/sql/handler.h @@ -1055,6 +1055,7 @@ enum enum_schema_tables SCH_PROCEDURES, SCH_SCHEMATA, SCH_SCHEMA_PRIVILEGES, + SCH_SEQUENCES, SCH_SESSION_STATUS, SCH_SESSION_VARIABLES, SCH_STATISTICS, diff --git a/sql/item_func.cc b/sql/item_func.cc index a07595cbbd8..7d78a33c005 100644 --- a/sql/item_func.cc +++ b/sql/item_func.cc @@ -7070,7 +7070,9 @@ longlong Item_func_nextval::val_int() } entry->null_value= null_value= 0; value= table->s->sequence->next_value(table, 0, &error); + unsigned_flag= table->s->sequence->is_unsigned; entry->value= value; + entry->unsigned_flag= unsigned_flag; entry->set_version(table); if (unlikely(error)) // Warning already printed @@ -7162,6 +7164,7 @@ longlong Item_func_lastval::val_int() } null_value= entry->null_value; + unsigned_flag= entry->unsigned_flag; DBUG_RETURN(entry->value); } @@ -7192,8 +7195,20 @@ longlong Item_func_setval::val_int() DBUG_RETURN(0); } - value= nextval; - error= table->s->sequence->set_value(table, nextval, round, is_used); + /* + Truncate nextval according to the value type of the sequence if + out of bounds. If truncation happens i.e. nextval is out of + bounds for the value type, return null immediately. + */ + value= table->s->sequence->truncate_value(nextval); + if (value != nextval.value()) + { + null_value= 1; + value= 0; + DBUG_RETURN(0); + } + unsigned_flag= table->s->sequence->is_unsigned; + error= table->s->sequence->set_value(table, value, round, is_used); if (unlikely(error)) { null_value= 1; @@ -7241,7 +7256,10 @@ void Item_func_setval::print(String *str, enum_query_type query_type) } append_identifier(thd, str, &t_name); str->append(','); - str->append_longlong(nextval); + if (nextval.is_unsigned()) + str->append_ulonglong(nextval.value()); + else + str->append_longlong(nextval.value()); str->append(','); str->append_longlong(is_used); str->append(','); diff --git a/sql/item_func.h b/sql/item_func.h index 520dbdc90c7..22f0cbd10bd 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -4190,11 +4190,11 @@ public: class Item_func_setval :public Item_func_nextval { - longlong nextval; + Longlong_hybrid nextval; ulonglong round; bool is_used; public: - Item_func_setval(THD *thd, TABLE_LIST *table_list_arg, longlong nextval_arg, + Item_func_setval(THD *thd, TABLE_LIST *table_list_arg, Longlong_hybrid nextval_arg, ulonglong round_arg, bool is_used_arg) : Item_func_nextval(thd, table_list_arg), nextval(nextval_arg), round(round_arg), is_used(is_used_arg) diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt index 65a7e613b72..79966ac9a05 100644 --- a/sql/share/errmsg-utf8.txt +++ b/sql/share/errmsg-utf8.txt @@ -10076,3 +10076,11 @@ ER_CM_OPTION_MISSING_REQUIREMENT eng "CHANGE MASTER TO option '%s=%s' is missing requirement %s" ER_SLAVE_STATEMENT_TIMEOUT 70100 eng "Slave log event execution was interrupted (slave_max_statement_time exceeded)" +ER_SEQUENCE_TABLE_HAS_WRONG_NUMBER_OF_COLUMNS + eng "Wrong number of columns" +ER_SEQUENCE_TABLE_CANNOT_HAVE_ANY_KEYS + eng "Sequence tables cannot have any keys" +ER_SEQUENCE_TABLE_CANNOT_HAVE_ANY_CONSTRAINTS + eng "Sequence tables cannot have any constraints" +ER_SEQUENCE_TABLE_ORDER_BY + eng "ORDER BY" diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index e3f486486d7..6645f8a0c77 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -8230,7 +8230,7 @@ Item *LEX::create_item_func_lastval(THD *thd, Item *LEX::create_item_func_setval(THD *thd, Table_ident *table_ident, - longlong nextval, ulonglong round, + Longlong_hybrid nextval, ulonglong round, bool is_used) { TABLE_LIST *table; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 95ed308103d..0d8dd837542 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -4093,8 +4093,9 @@ public: /* Create an item for "SETVAL(sequence_name, value [, is_used [, round]]) */ - Item *create_item_func_setval(THD *thd, Table_ident *ident, longlong value, - ulonglong round, bool is_used); + Item *create_item_func_setval(THD *thd, Table_ident *ident, + Longlong_hybrid value, ulonglong round, + bool is_used); /* Create an item for a name in LIMIT clause: LIMIT var diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc index 60da595afd0..3a4f5cfa141 100644 --- a/sql/sql_sequence.cc +++ b/sql/sql_sequence.cc @@ -30,85 +30,211 @@ #include "wsrep_mysqld.h" #endif -struct Field_definition -{ - const char *field_name; - uint length; - const Type_handler *type_handler; - LEX_CSTRING comment; - ulong flags; -}; +#define MAX_AUTO_INCREMENT_VALUE 65535 -/* - Structure for all SEQUENCE tables +/** + Structure for SEQUENCE tables of a certain value type - Note that the first field is named "next_val" to all us to have - NEXTVAL a reserved word that will on access be changed to - NEXTVAL(sequence_table). For this to work, the table can't have - a column named NEXTVAL. + @param in handler The handler of a sequence value type + + @return The sequence table structure given the value type */ +Sequence_row_definition sequence_structure(const Type_handler* handler) +{ + /* + We don't really care about src because it is unused in + max_display_length_for_field(). + */ + const Conv_source src(handler, 0, system_charset_info); + const uint32 len= handler->max_display_length_for_field(src) + 1; + const LEX_CSTRING empty= {STRING_WITH_LEN("")}; + const uint flag_unsigned= handler->is_unsigned() ? UNSIGNED_FLAG : 0; +#define FNND (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG) +#define FNNDFU (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG | flag_unsigned) + return {{{"next_not_cached_value", len, handler, empty, FNNDFU}, + {"minimum_value", len, handler, empty, FNNDFU}, + {"maximum_value", len, handler, empty, FNNDFU}, + {"start_value", len, handler, + {STRING_WITH_LEN("start value when sequences is created or value " + "if RESTART is used")}, FNNDFU}, + {"increment", 21, &type_handler_slonglong, + {STRING_WITH_LEN("increment value")}, FNND}, + {"cache_size", 21, &type_handler_ulonglong, empty, + FNND | UNSIGNED_FLAG}, + {"cycle_option", 1, &type_handler_utiny, + {STRING_WITH_LEN("0 if no cycles are allowed, 1 if the sequence " + "should begin a new cycle when maximum_value is " + "passed")}, FNND | UNSIGNED_FLAG}, + {"cycle_count", 21, &type_handler_slonglong, + {STRING_WITH_LEN("How many cycles have been done")}, FNND}, + {NULL, 0, &type_handler_slonglong, {STRING_WITH_LEN("")}, 0}}}; +#undef FNNDFU +#undef FNND +} + +/** + Whether a type is allowed as a sequence value type. -#define FL (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG) + @param in type The type to check -static Field_definition sequence_structure[]= + @retval true allowed + false not allowed +*/ +bool sequence_definition::is_allowed_value_type(enum_field_types type) { - {"next_not_cached_value", 21, &type_handler_slonglong, - {STRING_WITH_LEN("")}, FL}, - {"minimum_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("")}, FL}, - {"maximum_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("")}, FL}, - {"start_value", 21, &type_handler_slonglong, {STRING_WITH_LEN("start value when sequences is created or value if RESTART is used")}, FL}, - {"increment", 21, &type_handler_slonglong, - {STRING_WITH_LEN("increment value")}, FL}, - {"cache_size", 21, &type_handler_ulonglong, {STRING_WITH_LEN("")}, - FL | UNSIGNED_FLAG}, - {"cycle_option", 1, &type_handler_utiny, {STRING_WITH_LEN("0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed")}, - FL | UNSIGNED_FLAG }, - {"cycle_count", 21, &type_handler_slonglong, - {STRING_WITH_LEN("How many cycles have been done")}, FL}, - {NULL, 0, &type_handler_slonglong, {STRING_WITH_LEN("")}, 0} -}; - -#undef FL + switch (type) + { + case MYSQL_TYPE_TINY: + case MYSQL_TYPE_SHORT: + case MYSQL_TYPE_LONG: + case MYSQL_TYPE_INT24: + case MYSQL_TYPE_LONGLONG: + return true; + default: + return false; + } +} +/* + Get the type handler for the value type of a sequence. +*/ +Type_handler const *sequence_definition::value_type_handler() +{ + const Type_handler *handler= + Type_handler::get_handler_by_field_type(value_type); + return is_unsigned ? handler->type_handler_unsigned() : handler; +} -#define MAX_AUTO_INCREMENT_VALUE 65535 +/* + Get the upper bound for a sequence value type. +*/ +longlong sequence_definition::value_type_max() +{ + /* + Use value_type != MYSQL_TYPE_LONGLONG to avoid undefined behaviour + https://stackoverflow.com/questions/9429156/by-left-shifting-can-a-number-be-set-to-zero + */ + return is_unsigned && value_type != MYSQL_TYPE_LONGLONG ? + ~(~0ULL << 8 * value_type_handler()->calc_pack_length(0)) : + ~value_type_min(); +} /* + Get the lower bound for a sequence value type. +*/ +longlong sequence_definition::value_type_min() { + return is_unsigned ? 0 : + ~0ULL << (8 * value_type_handler()->calc_pack_length(0) - 1); +} + +/** + Truncate a Longlong_hybrid. + + If `original` is greater than value_type_max(), truncate down to + value_type_max() + + If `original` is less than value_type_min(), truncate up to + value_type_min() + + Whenever a truncation happens, the resulting value is just out of + bounds for sequence values because value_type_max() is the maximum + possible sequence value + 1, and the same applies to + value_type_min(). + + @param in original The value to truncate + + @return The truncated value +*/ +longlong sequence_definition::truncate_value(const Longlong_hybrid& original) +{ + if (is_unsigned) + return original.to_ulonglong(value_type_max()); + else if (original.is_unsigned_outside_of_signed_range()) + return value_type_max(); + else + return original.value() > value_type_max() ? value_type_max() + : original.value() < value_type_min() ? value_type_min() + : original.value(); +} + +/** Check whether sequence values are valid. + Sets default values for fields that are not used, according to Oracle spec. - RETURN VALUES - false valid - true invalid + @param in thd The connection + @param in set_reserved_until Whether to set reserved_until to start + + @retval false valid + true invalid */ - -bool sequence_definition::check_and_adjust(bool set_reserved_until) +bool sequence_definition::check_and_adjust(THD *thd, bool set_reserved_until) { - longlong max_increment; - DBUG_ENTER("sequence_definition::check"); + DBUG_ENTER("sequence_definition::check_and_adjust"); + + /* Easy error to detect. */ + if (!is_allowed_value_type(value_type) || cache < 0) + DBUG_RETURN(TRUE); if (!(real_increment= increment)) real_increment= global_system_variables.auto_increment_increment; /* - If min_value is not set, set it to LONGLONG_MIN or 1, depending on - real_increment + If min_value is not set, in case of signed sequence, set it to + value_type_min()+1 or 1, depending on real_increment, and in case + of unsigned sequence, set it to value_type_min()+1 */ - if (!(used_fields & seq_field_used_min_value)) - min_value= real_increment < 0 ? LONGLONG_MIN+1 : 1; + if (!(used_fields & seq_field_specified_min_value)) + min_value= real_increment < 0 || is_unsigned ? value_type_min()+1 : 1; + else + { + min_value= truncate_value(min_value_from_parser); + if ((is_unsigned && + (ulonglong) min_value <= (ulonglong) value_type_min()) || + (!is_unsigned && min_value <= value_type_min())) + { + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, + ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MINVALUE"); + min_value= value_type_min() + 1; + } + } /* - If max_value is not set, set it to LONGLONG_MAX or -1, depending on - real_increment + If max_value is not set, in case of signed sequence set it to + value_type_max()-1 or -1, depending on real_increment, and in case + of unsigned sequence, set it to value_type_max()-1 */ - if (!(used_fields & seq_field_used_max_value)) - max_value= real_increment < 0 ? -1 : LONGLONG_MAX-1; + if (!(used_fields & seq_field_specified_max_value)) + max_value= real_increment > 0 || is_unsigned ? value_type_max()-1 : -1; + else + { + max_value= truncate_value(max_value_from_parser); + if ((is_unsigned && + (ulonglong) max_value >= (ulonglong) value_type_max()) || + (!is_unsigned && max_value >= value_type_max())) + { + push_warning_printf( + thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, + ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MAXVALUE"); + max_value= value_type_max() - 1; + } + } if (!(used_fields & seq_field_used_start)) { /* Use min_value or max_value for start depending on real_increment */ start= real_increment < 0 ? max_value : min_value; - } + } else + /* + If the supplied start value is out of range for the value type, + instead of immediately reporting error, we truncate it to + value_type_min or value_type_max depending on which side it is + one. Whenever such truncation happens, the condition that + max_value >= start >= min_value will be violated, and the error + will be reported then. + */ + start= truncate_value(start_from_parser); if (set_reserved_until) reserved_until= start; @@ -116,16 +242,31 @@ bool sequence_definition::check_and_adjust(bool set_reserved_until) adjust_values(reserved_until); /* To ensure that cache * real_increment will never overflow */ - max_increment= (real_increment ? - llabs(real_increment) : - MAX_AUTO_INCREMENT_VALUE); + const longlong max_increment= (real_increment ? + llabs(real_increment) : + MAX_AUTO_INCREMENT_VALUE); + + /* + To ensure that cache * real_increment will never overflow. See the + calculation of add_to below in SEQUENCE::next_value(). We need + this for unsigned too, because otherwise we will need to handle + add_to as an equivalent of Longlong_hybrid type in + SEQUENCE::increment_value(). + */ + if (cache >= (LONGLONG_MAX - max_increment) / max_increment) + DBUG_RETURN(TRUE); + + if (is_unsigned && (ulonglong) max_value >= (ulonglong) start && + (ulonglong) max_value > (ulonglong) min_value && + (ulonglong) start >= (ulonglong) min_value && + ((real_increment > 0 && + (ulonglong) reserved_until >= (ulonglong) min_value) || + (real_increment < 0 && + (ulonglong) reserved_until <= (ulonglong) max_value))) + DBUG_RETURN(FALSE); - if (max_value >= start && - max_value > min_value && + if (!is_unsigned && max_value >= start && max_value > min_value && start >= min_value && - max_value != LONGLONG_MAX && - min_value != LONGLONG_MIN && - cache >= 0 && cache < (LONGLONG_MAX - max_increment) / max_increment && ((real_increment > 0 && reserved_until >= min_value) || (real_increment < 0 && reserved_until <= max_value))) DBUG_RETURN(FALSE); @@ -149,6 +290,11 @@ void sequence_definition::read_fields(TABLE *table) cache= table->field[5]->val_int(); cycle= table->field[6]->val_int(); round= table->field[7]->val_int(); + value_type= table->field[0]->type(); + is_unsigned= table->field[0]->is_unsigned(); + min_value_from_parser= Longlong_hybrid(min_value, is_unsigned); + max_value_from_parser= Longlong_hybrid(max_value, is_unsigned); + start_from_parser= Longlong_hybrid(start, is_unsigned); dbug_tmp_restore_column_map(&table->read_set, old_map); used_fields= ~(uint) 0; print_dbug(); @@ -165,10 +311,10 @@ void sequence_definition::store_fields(TABLE *table) /* zero possible delete markers & null bits */ memcpy(table->record[0], table->s->default_values, table->s->null_bytes); - table->field[0]->store(reserved_until, 0); - table->field[1]->store(min_value, 0); - table->field[2]->store(max_value, 0); - table->field[3]->store(start, 0); + table->field[0]->store(reserved_until, is_unsigned); + table->field[1]->store(min_value, is_unsigned); + table->field[2]->store(max_value, is_unsigned); + table->field[3]->store(start, is_unsigned); table->field[4]->store(increment, 0); table->field[5]->store(cache, 0); table->field[6]->store((longlong) cycle != 0, 0); @@ -194,33 +340,40 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields) uint field_count; uint field_no; const char *reason; + Sequence_row_definition row_structure; DBUG_ENTER("check_sequence_fields"); field_count= fields->elements; - if (field_count != array_elements(sequence_structure)-1) + if (!field_count) { - reason= "Wrong number of columns"; + reason= my_get_err_msg(ER_SEQUENCE_TABLE_HAS_WRONG_NUMBER_OF_COLUMNS); + goto err; + } + row_structure= sequence_structure(fields->head()->type_handler()); + if (field_count != array_elements(row_structure.fields)-1) + { + reason= my_get_err_msg(ER_SEQUENCE_TABLE_HAS_WRONG_NUMBER_OF_COLUMNS); goto err; } if (lex->alter_info.key_list.elements > 0) { - reason= "Sequence tables cannot have any keys"; + reason= my_get_err_msg(ER_SEQUENCE_TABLE_CANNOT_HAVE_ANY_KEYS); goto err; } if (lex->alter_info.check_constraint_list.elements > 0) { - reason= "Sequence tables cannot have any constraints"; + reason= my_get_err_msg(ER_SEQUENCE_TABLE_CANNOT_HAVE_ANY_CONSTRAINTS); goto err; } if (lex->alter_info.flags & ALTER_ORDER) { - reason= "ORDER BY"; + reason= my_get_err_msg(ER_SEQUENCE_TABLE_ORDER_BY); goto err; } for (field_no= 0; (field= it++); field_no++) { - Field_definition *field_def= &sequence_structure[field_no]; + const Sequence_field_definition *field_def= &row_structure.fields[field_no]; if (my_strcasecmp(system_charset_info, field_def->field_name, field->field_name.str) || field->flags != field_def->flags || @@ -249,12 +402,15 @@ err: true Failure (out of memory) */ -bool prepare_sequence_fields(THD *thd, List<Create_field> *fields) +bool sequence_definition::prepare_sequence_fields(List<Create_field> *fields, + bool alter) { - Field_definition *field_info; DBUG_ENTER("prepare_sequence_fields"); + const Sequence_row_definition row_def= + sequence_structure(value_type_handler()); - for (field_info= sequence_structure; field_info->field_name ; field_info++) + for (const Sequence_field_definition *field_info= row_def.fields; + field_info->field_name; field_info++) { Create_field *new_field; LEX_CSTRING field_name= {field_info->field_name, @@ -269,6 +425,8 @@ bool prepare_sequence_fields(THD *thd, List<Create_field> *fields) new_field->char_length= field_info->length; new_field->comment= field_info->comment; new_field->flags= field_info->flags; + if (alter) + new_field->change = field_name; if (unlikely(fields->push_back(new_field))) DBUG_RETURN(TRUE); /* purify inspected */ } @@ -297,20 +455,18 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *org_table_list) Reprepare_observer *save_reprepare_observer; sequence_definition *seq= lex->create_info.seq_create_info; bool temporary_table= org_table_list->table != 0; + /* + seq is 0 if sequence was created with CREATE TABLE instead of + CREATE SEQUENCE + */ + bool create_new= !seq; Open_tables_backup open_tables_backup; Query_tables_list query_tables_list_backup; TABLE_LIST table_list; // For sequence table DBUG_ENTER("sequence_insert"); - /* - seq is 0 if sequence was created with CREATE TABLE instead of - CREATE SEQUENCE - */ - if (!seq) - { - if (!(seq= new (thd->mem_root) sequence_definition)) - DBUG_RETURN(TRUE); - } + if (create_new && !(seq= new (thd->mem_root) sequence_definition)) + DBUG_RETURN(TRUE); #ifdef WITH_WSREP if (WSREP_ON && seq->cache != 0) @@ -371,7 +527,15 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *org_table_list) else table= org_table_list->table; - seq->reserved_until= seq->start; + if (create_new) + { + seq->value_type= (*table->s->field)->type(); + seq->is_unsigned= (*table->s->field)->is_unsigned(); + /* We set reserved_until when creating a new sequence. */ + if (seq->check_and_adjust(thd, true)) + DBUG_RETURN(TRUE); + } + error= seq->write_initial_sequence(table); { uint save_unsafe_rollback_flags= @@ -416,9 +580,9 @@ SEQUENCE::~SEQUENCE() A sequence table can have many readers (trough normal SELECT's). We mark that we have a write lock in the table object so that - ha_sequence::ha_write() can check if we have a lock. If already locked, then + ha_sequence::write_row() can check if we have a lock. If already locked, then ha_write() knows that we are running a sequence operation. If not, then - ha_write() knows that it's an INSERT. + ha_write() knows that it's an INSERT statement. */ void SEQUENCE::write_lock(TABLE *table) @@ -588,15 +752,26 @@ void sequence_definition::adjust_values(longlong next_value) /* Check if add will make next_free_value bigger than max_value, taken into account that next_free_value or max_value addition - may overflow + may overflow. + + 0 <= to_add <= auto_increment_increment <= 65535 so we do not + need to cast to_add. */ - if (next_free_value > max_value - to_add || - next_free_value + to_add > max_value) + if ((is_unsigned && + (ulonglong) next_free_value > (ulonglong) max_value - to_add) || + (is_unsigned && + (ulonglong) next_free_value + to_add > (ulonglong) max_value) || + (!is_unsigned && next_free_value > max_value - to_add) || + (!is_unsigned && next_free_value + to_add > max_value)) next_free_value= max_value+1; else { next_free_value+= to_add; - DBUG_ASSERT(llabs(next_free_value % real_increment) == offset); + if (is_unsigned) + DBUG_ASSERT((ulonglong) next_free_value % real_increment == + (ulonglong) offset); + else + DBUG_ASSERT(llabs(next_free_value % real_increment) == offset); } } } @@ -718,10 +893,10 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) write_lock(table); res_value= next_free_value; - next_free_value= increment_value(next_free_value); + next_free_value= increment_value(next_free_value, real_increment); - if ((real_increment > 0 && res_value < reserved_until) || - (real_increment < 0 && res_value > reserved_until)) + if (within_bound(res_value, reserved_until, reserved_until, + real_increment > 0)) { write_unlock(table); DBUG_RETURN(res_value); @@ -738,30 +913,10 @@ longlong SEQUENCE::next_value(TABLE *table, bool second_round, int *error) overflow */ add_to= cache ? real_increment * cache : real_increment; - out_of_values= 0; - if (real_increment > 0) - { - if (reserved_until > max_value - add_to || - reserved_until + add_to > max_value) - { - reserved_until= max_value + 1; - out_of_values= res_value >= reserved_until; - } - else - reserved_until+= add_to; - } - else - { - if (reserved_until + add_to < min_value || - reserved_until < min_value - add_to) - { - reserved_until= min_value - 1; - out_of_values= res_value <= reserved_until; - } - else - reserved_until+= add_to; - } + reserved_until= increment_value(reserved_until, add_to); + out_of_values= !within_bound(res_value, max_value + 1, min_value - 1, + add_to > 0); if (out_of_values) { if (!cycle || second_round) @@ -850,15 +1005,14 @@ int SEQUENCE::set_value(TABLE *table, longlong next_val, ulonglong next_round, write_lock(table); if (is_used) - next_val= increment_value(next_val); + next_val= increment_value(next_val, real_increment); if (round > next_round) goto end; // error = -1 if (round == next_round) { - if (real_increment > 0 ? - next_val < next_free_value : - next_val > next_free_value) + if (within_bound(next_val, next_free_value, next_free_value, + real_increment > 0)) goto end; // error = -1 if (next_val == next_free_value) { @@ -879,9 +1033,8 @@ int SEQUENCE::set_value(TABLE *table, longlong next_val, ulonglong next_round, round= next_round; adjust_values(next_val); - if ((real_increment > 0 ? - next_free_value > reserved_until : - next_free_value < reserved_until) || + if (within_bound(reserved_until, next_free_value, next_free_value, + real_increment > 0) || needs_to_be_stored) { reserved_until= next_free_value; @@ -937,6 +1090,35 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) first_table)) DBUG_RETURN(TRUE); #endif /* WITH_WSREP */ + + if (new_seq->used_fields & seq_field_used_as) + { + /* This should have been prevented during parsing. */ + DBUG_ASSERT(!(new_seq->used_fields - seq_field_used_as)); + + first_table->lock_type= TL_READ_NO_INSERT; + first_table->mdl_request.set_type(MDL_SHARED_NO_WRITE); + Alter_info alter_info; + alter_info.flags= ALTER_CHANGE_COLUMN; + if (new_seq->prepare_sequence_fields(&alter_info.create_list, true)) + DBUG_RETURN(TRUE); + Table_specification_st create_info; + create_info.init(); + create_info.alter_info= &alter_info; + if (if_exists()) + thd->push_internal_handler(&no_such_table_handler); + error= mysql_alter_table(thd, &null_clex_str, &null_clex_str, + &create_info, first_table, &alter_info, 0, + (ORDER *) 0, 0, 0); + if (if_exists()) + { + trapped_errors= no_such_table_handler.safely_trapped_errors(); + thd->pop_internal_handler(); + } + /* Do we need to store the sequence value in table share, like below? */ + DBUG_RETURN(error); + } + if (if_exists()) thd->push_internal_handler(&no_such_table_handler); error= open_and_lock_tables(thd, first_table, FALSE, 0); @@ -972,28 +1154,42 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) /* Copy from old sequence those fields that the user didn't specified */ if (!(new_seq->used_fields & seq_field_used_increment)) new_seq->increment= seq->increment; + /* + We need to assign to foo_from_parser so that things get handled + properly in check_and_adjust() later + */ if (!(new_seq->used_fields & seq_field_used_min_value)) - new_seq->min_value= seq->min_value; + new_seq->min_value_from_parser= Longlong_hybrid(seq->min_value, seq->is_unsigned); if (!(new_seq->used_fields & seq_field_used_max_value)) - new_seq->max_value= seq->max_value; + new_seq->max_value_from_parser= Longlong_hybrid(seq->max_value, seq->is_unsigned); if (!(new_seq->used_fields & seq_field_used_start)) - new_seq->start= seq->start; + new_seq->start_from_parser= Longlong_hybrid(seq->start, seq->is_unsigned); if (!(new_seq->used_fields & seq_field_used_cache)) new_seq->cache= seq->cache; if (!(new_seq->used_fields & seq_field_used_cycle)) new_seq->cycle= seq->cycle; + /* This should have been prevented during parsing. */ + DBUG_ASSERT(!(new_seq->used_fields & seq_field_used_as)); + new_seq->value_type= seq->value_type; + new_seq->is_unsigned= seq->is_unsigned; /* If we should restart from a new value */ if (new_seq->used_fields & seq_field_used_restart) { if (!(new_seq->used_fields & seq_field_used_restart_value)) - new_seq->restart= new_seq->start; - new_seq->reserved_until= new_seq->restart; + new_seq->restart_from_parser= new_seq->start_from_parser; + /* + Similar to start, we just need to truncate reserved_until and + the errors will be reported in check_and_adjust if truncation + happens on the wrong end. + */ + new_seq->reserved_until= + new_seq->truncate_value(new_seq->restart_from_parser); } /* Let check_and_adjust think all fields are used */ new_seq->used_fields= ~0; - if (new_seq->check_and_adjust(0)) + if (new_seq->check_and_adjust(thd, 0)) { my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), first_table->db.str, diff --git a/sql/sql_sequence.h b/sql/sql_sequence.h index 29c589e67cd..a599439e808 100644 --- a/sql/sql_sequence.h +++ b/sql/sql_sequence.h @@ -25,12 +25,35 @@ #define seq_field_used_cycle 32 #define seq_field_used_restart 64 #define seq_field_used_restart_value 128 +#define seq_field_used_as 256 +#define seq_field_specified_min_value 512 +#define seq_field_specified_max_value 1024 /* Field position in sequence table for some fields we refer to directly */ #define NEXT_FIELD_NO 0 #define MIN_VALUE_FIELD_NO 1 #define ROUND_FIELD_NO 7 +#include "mysql_com.h" +#include "sql_type_int.h" + +class Create_field; +class Type_handler; + +struct Sequence_field_definition +{ + const char *field_name; + uint length; + const Type_handler *type_handler; + LEX_CSTRING comment; + ulong flags; +}; + +struct Sequence_row_definition +{ + Sequence_field_definition fields[9]; +}; + /** sequence_definition is used when defining a sequence as part of create */ @@ -39,33 +62,56 @@ class sequence_definition :public Sql_alloc { public: sequence_definition(): - min_value(1), max_value(LONGLONG_MAX-1), start(1), increment(1), - cache(1000), round(0), restart(0), cycle(0), used_fields(0) + min_value_from_parser(1, false), + max_value_from_parser(LONGLONG_MAX-1, false), start_from_parser(1, false), + increment(1), cache(1000), round(0), restart_from_parser(0, false), + cycle(0), used_fields(0), + /* + We use value type and is_unsigned instead of a handler because + Type_handler is incomplete, which we cannot initialise here with + &type_handler_slonglong. + */ + value_type(MYSQL_TYPE_LONGLONG), is_unsigned(false) {} longlong reserved_until; longlong min_value; longlong max_value; longlong start; + Longlong_hybrid min_value_from_parser; + Longlong_hybrid max_value_from_parser; + Longlong_hybrid start_from_parser; longlong increment; longlong cache; ulonglong round; longlong restart; // alter sequence restart value + Longlong_hybrid restart_from_parser; bool cycle; uint used_fields; // Which fields where used in CREATE + enum_field_types value_type; // value type of the sequence + bool is_unsigned; - bool check_and_adjust(bool set_reserved_until); + Type_handler const *value_type_handler(); + /* max value for the value type, e.g. 32767 for smallint. */ + longlong value_type_max(); + /* min value for the value type, e.g. -32768 for smallint. */ + longlong value_type_min(); + bool check_and_adjust(THD *thd, bool set_reserved_until); void store_fields(TABLE *table); void read_fields(TABLE *table); int write_initial_sequence(TABLE *table); int write(TABLE *table, bool all_fields); /* This must be called after sequence data has been updated */ void adjust_values(longlong next_value); + longlong truncate_value(const Longlong_hybrid& original); inline void print_dbug() { DBUG_PRINT("sequence", ("reserved: %lld start: %lld increment: %lld min_value: %lld max_value: %lld cache: %lld round: %lld", reserved_until, start, increment, min_value, max_value, cache, round)); } + static bool is_allowed_value_type(enum_field_types type); + bool prepare_sequence_fields(List<Create_field> *fields, bool alter); + protected: /* The following values are the values from sequence_definition @@ -107,31 +153,90 @@ public: longlong next_value(TABLE *table, bool second_round, int *error); int set_value(TABLE *table, longlong next_value, ulonglong round_arg, bool is_used); - longlong increment_value(longlong value) + + bool all_values_used; + seq_init initialized; + +private: + /** + Check that a value is within a relevant bound + + If increasing sequence, check that the value is lower than an + upper bound, otherwise check that the value is higher than a lower + bound. + + @param in value The value to check + @param in upper The upper bound + @param in lower The lower bound + @param in increasing Which bound to check + + @retval true The value is within the bound. + false The value is out of the bound. + */ + bool within_bound(const longlong value, const longlong upper, + const longlong lower, bool increasing) + { + return + (is_unsigned && increasing && (ulonglong) value < (ulonglong) upper) || + (is_unsigned && !increasing && (ulonglong) value > (ulonglong) lower) || + (!is_unsigned && increasing && value < upper) || + (!is_unsigned && !increasing && value > lower); + } + + /** + Increment a value, subject to truncation + + Truncating to the nearer value between max_value + 1 and min_value + - 1 + + @param in value The original value + @param in increment The increment to add to the value + + @return The value after increment and possible truncation + */ + longlong increment_value(longlong value, const longlong increment) { - if (real_increment > 0) + if (is_unsigned) { - if (value > max_value - real_increment || - value + real_increment > max_value) - value= max_value + 1; + if (increment > 0) + { + if (/* in case value + increment overflows */ + (ulonglong) value > + (ulonglong) max_value - (ulonglong) increment || + /* in case max_value - increment underflows */ + (ulonglong) value + (ulonglong) increment > + (ulonglong) max_value) + value= max_value + 1; + else + value+= increment; + } else - value+= real_increment; - } - else - { - if (value + real_increment < min_value || - value < min_value - real_increment) - value= min_value - 1; + { + if ((ulonglong) value - (ulonglong) (-increment) < + (ulonglong) min_value || + (ulonglong) value < + (ulonglong) min_value + (ulonglong) (-increment)) + value= min_value - 1; + else + value+= increment; + } + } else + if (increment > 0) + { + if (value > max_value - increment || value + increment > max_value) + value= max_value + 1; + else + value+= increment; + } else - value+= real_increment; - } + { + if (value + increment < min_value || value < min_value - increment) + value= min_value - 1; + else + value+= increment; + } return value; } - - bool all_values_used; - seq_init initialized; - -private: mysql_rwlock_t mutex; }; @@ -156,12 +261,10 @@ public: uint length; bool null_value; longlong value; + bool unsigned_flag; uchar table_version[MY_UUID_SIZE]; }; - -class Create_field; -extern bool prepare_sequence_fields(THD *thd, List<Create_field> *fields); extern bool check_sequence_fields(LEX *lex, List<Create_field> *fields); extern bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *table_list); #endif /* SQL_SEQUENCE_INCLUDED */ diff --git a/sql/sql_show.cc b/sql/sql_show.cc index 1fd31bd4947..fae01faa468 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2694,12 +2694,33 @@ static int show_create_sequence(THD *thd, TABLE_LIST *table_list, packet->append(STRING_WITH_LEN("CREATE SEQUENCE ")); append_identifier(thd, packet, &alias); + /* + Do not show " as <type>" in oracle mode as it is not supported: + https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-SEQUENCE.html + Also, do not show this if the type is the default type, + i.e. slonglong, for backward compatibility. + */ + if (!(sql_mode & MODE_ORACLE) && + seq->value_type_handler() != &type_handler_slonglong) + { + packet->append(STRING_WITH_LEN(" as ")); + packet->append(seq->value_type_handler()->name().lex_cstring()); + } packet->append(STRING_WITH_LEN(" start with ")); - packet->append_longlong(seq->start); + if (seq->is_unsigned) + packet->append_ulonglong(seq->start); + else + packet->append_longlong(seq->start); packet->append(STRING_WITH_LEN(" minvalue ")); - packet->append_longlong(seq->min_value); + if (seq->is_unsigned) + packet->append_ulonglong(seq->min_value); + else + packet->append_longlong(seq->min_value); packet->append(STRING_WITH_LEN(" maxvalue ")); - packet->append_longlong(seq->max_value); + if (seq->is_unsigned) + packet->append_ulonglong(seq->max_value); + else + packet->append_longlong(seq->max_value); packet->append(STRING_WITH_LEN(" increment by ")); packet->append_longlong(seq->increment); if (seq->cache) @@ -5512,6 +5533,36 @@ int fill_schema_schemata(THD *thd, TABLE_LIST *tables, COND *cond) DBUG_RETURN(0); } +static int get_schema_sequence_record(THD *thd, TABLE_LIST *tables, + TABLE *table, bool res, + const LEX_CSTRING *db_name, + const LEX_CSTRING *table_name) +{ + DBUG_ENTER("get_sequence_record"); + CHARSET_INFO *cs= system_charset_info; + restore_record(table, s->default_values); + sequence_definition *seq= tables->table->s->sequence; + if (tables->table->s->table_type == TABLE_TYPE_SEQUENCE) + { + const Type_handler *handler= seq->value_type_handler(); + table->field[0]->store(STRING_WITH_LEN("def"), cs); + table->field[1]->store(db_name->str, db_name->length, cs); + table->field[2]->store(table_name->str, table_name->length, cs); + table->field[3]->store(handler->name().lex_cstring(), cs); + table->field[4]->store(8 * handler->calc_pack_length(0)); + table->field[5]->store(2); + table->field[5]->set_notnull(); + table->field[6]->store(0); + table->field[6]->set_notnull(); + table->field[7]->store(seq->start, seq->is_unsigned); + table->field[8]->store(seq->min_value, seq->is_unsigned); + table->field[9]->store(seq->max_value, seq->is_unsigned); + table->field[10]->store(seq->increment, 0); + table->field[11]->store(seq->cycle); + DBUG_RETURN(schema_table_store_record(thd, table)); + } + DBUG_RETURN(0); +} static int get_schema_tables_record(THD *thd, TABLE_LIST *tables, TABLE *table, bool res, @@ -9419,6 +9470,31 @@ ST_FIELD_INFO proc_fields_info[]= }; +ST_FIELD_INFO sequence_fields_info[]= +{ + Column("SEQUENCE_CATALOG", Catalog(), NOT_NULL, + OPEN_FRM_ONLY), + Column("SEQUENCE_SCHEMA", Name(), NOT_NULL, + OPEN_FRM_ONLY), + Column("SEQUENCE_NAME", Name(), NOT_NULL, "Table", + OPEN_FRM_ONLY), + Column("DATA_TYPE", Name(), NOT_NULL), + Column("NUMERIC_PRECISION", SLong(21), NOT_NULL), + Column("NUMERIC_PRECISION_RADIX", SLong(21), NULLABLE), + Column("NUMERIC_SCALE", SLong(21), NULLABLE), + /* + Decimal types for these values to incorporate possibly unsigned + longlongs. + */ + Column("START_VALUE", Decimal(2100), NOT_NULL), + Column("MINIMUM_VALUE", Decimal(2100), NOT_NULL), + Column("MAXIMUM_VALUE", Decimal(2100), NOT_NULL), + Column("INCREMENT", SLonglong(21), NOT_NULL), + Column("CYCLE_OPTION", Yes_or_empty(), NOT_NULL), + CEnd() +}; + + ST_FIELD_INFO stat_fields_info[]= { Column("TABLE_CATALOG", Catalog(), NOT_NULL, OPEN_FRM_ONLY), @@ -9985,6 +10061,8 @@ ST_SCHEMA_TABLE schema_tables[]= fill_schema_schemata, make_schemata_old_format, 0, 1, -1, 0, 0}, {"SCHEMA_PRIVILEGES", Show::schema_privileges_fields_info, 0, fill_schema_schema_privileges, 0, 0, -1, -1, 0, 0}, + {"SEQUENCES", Show::sequence_fields_info, 0, + get_all_tables, make_old_format, get_schema_sequence_record, 1, 2, 0, 0}, {"SESSION_STATUS", Show::variables_fields_info, 0, fill_status, make_old_format, 0, 0, -1, 0, 0}, {"SESSION_VARIABLES", Show::variables_fields_info, 0, diff --git a/sql/sql_type.cc b/sql/sql_type.cc index 3759c0ba02f..1207ffd4fb8 100644 --- a/sql/sql_type.cc +++ b/sql/sql_type.cc @@ -43,7 +43,7 @@ Named_type_handler<Type_handler_short> type_handler_sshort("smallint"); Named_type_handler<Type_handler_long> type_handler_slong("int"); Named_type_handler<Type_handler_int24> type_handler_sint24("mediumint"); Named_type_handler<Type_handler_longlong> type_handler_slonglong("bigint"); -Named_type_handler<Type_handler_utiny> type_handler_utiny("tiny unsigned"); +Named_type_handler<Type_handler_utiny> type_handler_utiny("tinyint unsigned"); Named_type_handler<Type_handler_ushort> type_handler_ushort("smallint unsigned"); Named_type_handler<Type_handler_ulong> type_handler_ulong("int unsigned"); Named_type_handler<Type_handler_uint24> type_handler_uint24("mediumint unsigned"); diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index f0ed46646ae..87458122f44 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -200,6 +200,11 @@ void _CONCAT_UNDERSCORED(turn_parser_debug_on,yyparse)() ulonglong ulonglong_number; longlong longlong_number; uint sp_instr_addr; + /* + Longlong_hybrid does not have a default constructor, hence the + default value below. + */ + Longlong_hybrid longlong_hybrid_number= Longlong_hybrid(0, false); /* structs */ LEX_CSTRING lex_str; @@ -1466,7 +1471,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); ulonglong_num real_ulonglong_num %type <longlong_number> - longlong_num + sequence_value_num + +%type <longlong_hybrid_number> + sequence_value_hybrid_num sequence_truncated_value_hybrid_num %type <choice> choice @@ -2419,7 +2427,8 @@ create: { LEX *lex= thd->lex; - if (unlikely(lex->create_info.seq_create_info->check_and_adjust(1))) + if (unlikely(lex->create_info.seq_create_info-> + check_and_adjust(thd, 1))) { my_error(ER_SEQUENCE_INVALID_DATA, MYF(0), lex->first_select_lex()->table_list.first->db.str, @@ -2429,12 +2438,13 @@ create: } /* No fields specified, generate them */ - if (unlikely(prepare_sequence_fields(thd, - &lex->alter_info.create_list))) + if (unlikely( + lex->create_info.seq_create_info->prepare_sequence_fields( + &lex->alter_info.create_list, false))) MYSQL_YYABORT; /* CREATE SEQUENCE always creates a sequence */ - Lex->create_info.used_fields|= HA_CREATE_USED_SEQUENCE; + Lex->create_info.used_fields|= HA_CREATE_USED_SEQUENCE; Lex->create_info.sequence= 1; create_table_set_open_action_and_adjust_tables(lex); @@ -2605,66 +2615,100 @@ sequence_defs: ; sequence_def: - MINVALUE_SYM opt_equal longlong_num + AS int_type field_options + { + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_as)) + my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "AS")); + if ($3 & ZEROFILL_FLAG) + my_yyabort_error((ER_BAD_OPTION_VALUE, MYF(0), "ZEROFILL", + "AS")); + Lex->create_info.seq_create_info->value_type = $2->field_type(); + Lex->create_info.seq_create_info->is_unsigned = + $3 & UNSIGNED_FLAG ? true : false; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_as; + } + | MINVALUE_SYM opt_equal sequence_truncated_value_hybrid_num { - Lex->create_info.seq_create_info->min_value= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_min_value)) + my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); + Lex->create_info.seq_create_info->min_value_from_parser= $3; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_min_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_specified_min_value; } | NO_SYM MINVALUE_SYM { - if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value)) + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_min_value)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); - Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_min_value; } | NOMINVALUE_SYM { - if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_min_value)) + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_min_value)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); - Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_min_value; } - | MAXVALUE_SYM opt_equal longlong_num + | MAXVALUE_SYM opt_equal sequence_truncated_value_hybrid_num { - if (unlikely(Lex->create_info.seq_create_info->used_fields & - seq_field_used_max_value)) + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_max_value)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); - Lex->create_info.seq_create_info->max_value= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; + Lex->create_info.seq_create_info->max_value_from_parser= $3; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_max_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_specified_max_value; } | NO_SYM MAXVALUE_SYM { - if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value)) + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_max_value)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); - Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_max_value; } | NOMAXVALUE_SYM { - if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_max_value)) + if (unlikely(Lex->create_info.seq_create_info->used_fields & + seq_field_used_max_value)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); - Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_max_value; } - | START_SYM opt_with longlong_num + | START_SYM opt_with sequence_value_hybrid_num { if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_start)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "START")); - Lex->create_info.seq_create_info->start= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_start; + Lex->create_info.seq_create_info->start_from_parser= $3; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_start; } - | INCREMENT_SYM opt_by longlong_num + | INCREMENT_SYM opt_by sequence_value_num { if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_increment)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "INCREMENT")); Lex->create_info.seq_create_info->increment= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_increment; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_increment; } - | CACHE_SYM opt_equal longlong_num + | CACHE_SYM opt_equal sequence_value_num { if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_cache)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "CACHE")); Lex->create_info.seq_create_info->cache= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_cache; } | NOCACHE_SYM { @@ -2672,7 +2716,8 @@ sequence_def: seq_field_used_cache)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "CACHE")); Lex->create_info.seq_create_info->cache= 0; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_cache; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_cache; } | CYCLE_SYM { @@ -2680,7 +2725,8 @@ sequence_def: seq_field_used_cycle)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "CYCLE")); Lex->create_info.seq_create_info->cycle= 1; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_cycle; } | NOCYCLE_SYM { @@ -2688,7 +2734,8 @@ sequence_def: seq_field_used_cycle)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "CYCLE")); Lex->create_info.seq_create_info->cycle= 0; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_cycle; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_cycle; } | RESTART_SYM { @@ -2700,9 +2747,10 @@ sequence_def: if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_restart)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "RESTART")); - Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_restart; } - | RESTART_SYM opt_with longlong_num + | RESTART_SYM opt_with sequence_value_hybrid_num { if (unlikely(Lex->sql_command != SQLCOM_ALTER_SEQUENCE)) { @@ -2712,8 +2760,9 @@ sequence_def: if (unlikely(Lex->create_info.seq_create_info->used_fields & seq_field_used_restart)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "RESTART")); - Lex->create_info.seq_create_info->restart= $3; - Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart | seq_field_used_restart_value; + Lex->create_info.seq_create_info->restart_from_parser= $3; + Lex->create_info.seq_create_info->used_fields|= + seq_field_used_restart | seq_field_used_restart_value; } ; @@ -7119,6 +7168,13 @@ alter: { /* Create a generic ALTER SEQUENCE statment. */ Lex->m_sql_cmd= new (thd->mem_root) Sql_cmd_alter_sequence($3); + if ((Lex->create_info.seq_create_info->used_fields & + seq_field_used_as) && + (Lex->create_info.seq_create_info->used_fields - + seq_field_used_as)) + my_yyabort_error((ER_NOT_SUPPORTED_YET, MYF(0), + "ALTER SEQUENCE with both AS <type> and " + "something else.")); if (unlikely(Lex->m_sql_cmd == NULL)) MYSQL_YYABORT; } stmt_end {} @@ -9728,19 +9784,23 @@ column_default_non_parenthesized_expr: if (unlikely(!($$= Lex->create_item_func_lastval(thd, $3)))) MYSQL_YYABORT; } - | SETVAL_SYM '(' table_ident ',' longlong_num ')' + | SETVAL_SYM '(' table_ident ',' sequence_value_hybrid_num ')' { - if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, 0, 1)))) + if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, 0, + 1)))) MYSQL_YYABORT; } - | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ')' + | SETVAL_SYM '(' table_ident ',' sequence_value_hybrid_num ',' bool ')' { - if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, 0, $7)))) + if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, 0, + $7)))) MYSQL_YYABORT; } - | SETVAL_SYM '(' table_ident ',' longlong_num ',' bool ',' ulonglong_num ')' + | SETVAL_SYM '(' table_ident ',' sequence_value_hybrid_num ',' bool ',' + ulonglong_num ')' { - if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, $9, $7)))) + if (unlikely(!($$= Lex->create_item_func_setval(thd, $3, $5, $9, + $7)))) MYSQL_YYABORT; } ; @@ -12560,11 +12620,126 @@ real_ulong_num: | dec_num_error { MYSQL_YYABORT; } ; -longlong_num: - opt_plus NUM { int error; $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); } - | LONG_NUM { int error; $$= (longlong) my_strtoll10($1.str, (char**) 0, &error); } - | '-' NUM { int error; $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); } - | '-' LONG_NUM { int error; $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); } +/* + For simple sequence metadata values that are signed and do not need + truncation +*/ +sequence_value_num: + opt_plus NUM + { + int error; + $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); + } + | opt_plus LONG_NUM + { + int error; + $$= (longlong) my_strtoll10($2.str, (char**) 0, &error); + } + | '-' NUM + { + int error; + $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); + } + | '-' LONG_NUM + { + int error; + $$= -(longlong) my_strtoll10($2.str, (char**) 0, &error); + } + | '-' ULONGLONG_NUM + { + int error; + const ulonglong abs= my_strtoll10($2.str, (char**) 0, &error); + if (abs == 1 + (ulonglong) LONGLONG_MAX) + $$= LONGLONG_MIN; + else + thd->parse_error(ER_DATA_OUT_OF_RANGE); + } + ; + +/* + For sequence metadata values that may be unsigned but do not need + truncation (start, restart) +*/ +sequence_value_hybrid_num: + opt_plus NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + false); + } + | opt_plus LONG_NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + false); + } + | opt_plus ULONGLONG_NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + true); + } + | '-' NUM + { + int error; + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), + false); + } + | '-' LONG_NUM + { + int error; + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), + false); + } + | '-' ULONGLONG_NUM + { + int error; + const ulonglong abs= my_strtoll10($2.str, (char**) 0, &error); + if (abs == 1 + (ulonglong) LONGLONG_MAX) + $$= Longlong_hybrid(LONGLONG_MIN, false); + else + thd->parse_error(ER_DATA_OUT_OF_RANGE); + } + ; + +/* + For sequence metadata values that may be unsigned and need + truncation (maxvalue, minvalue) +*/ +sequence_truncated_value_hybrid_num: + opt_plus NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + false); + } + | opt_plus LONG_NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + false); + } + | opt_plus ULONGLONG_NUM + { + int error; + $$= Longlong_hybrid(my_strtoll10($2.str, (char**) 0, &error), + true); + } + | opt_plus DECIMAL_NUM { $$= Longlong_hybrid(ULONGLONG_MAX, true); } + | '-' NUM + { + int error; + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), + false); + } + | '-' LONG_NUM + { + int error; + $$= Longlong_hybrid(- my_strtoll10($2.str, (char**) 0, &error), + false); + } + | '-' ULONGLONG_NUM { $$= Longlong_hybrid(LONGLONG_MIN, false); } + | '-' DECIMAL_NUM { $$= Longlong_hybrid(LONGLONG_MIN, false); } ; ulonglong_num: |