Hi Oleksandr, Thanks for your comments. Please see below for my replies, with the urls to the updated patch and the diff with the previous one at https://jira.mariadb.org/browse/MDEV-28152?focusedCommentId=253629&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-253629 Meanwhile I realised I need to update the implementation of setval() too for unsigned types. Working on it. On Fri 2023-03-10 11:59:03 +0100, Oleksandr Byelkin wrote:

Hi, Yuchen!

As for first work for the server itself I find it is very good, but please read my notes below (many of them just code style ones)

JFYI if you fix something in the wrong or old code style, you should fix it, but please do not fix the style only things in the code you are not going to change.

It is one of the not yet written ( :( ) rules of our fix code guide. As well as code style defined only by old memory and examples in the code.

...

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/main/mysqldump.result b/mysql-test/main/mysqldump.result index 44ed94e43c9..41607ea3068 100644 --- a/mysql-test/main/mysqldump.result +++ b/mysql-test/main/mysqldump.result @@ -6369,16 +6369,16 @@ NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) # Show create before dump show create sequence d.s1; Table Create Table -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM show create sequence d.s2; Table Create Table -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM show create sequence d.s3; Table Create Table -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM show create sequence d.s4; Table Create Table -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM # Dump sequence without `--no-data` # Restore from mysqldump SETVAL(`s1`, 1101, 0) @@ -6392,16 +6392,16 @@ SETVAL(`s4`, 1401, 0) # Show create after restore show create sequence d.s1; Table Create Table -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM show create sequence d.s2; Table Create Table -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM show create sequence d.s3; Table Create Table -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM show create sequence d.s4; Table Create Table -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) 100 200 300 400 @@ -6418,16 +6418,16 @@ SETVAL(`s4`, 1401, 0) # Show create after restore `--no-data` show create sequence d.s1; Table Create Table -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM show create sequence d.s2; Table Create Table -s2 CREATE SEQUENCE `s2` start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM +s2 CREATE SEQUENCE `s2` as bigint start with 200 minvalue 200 maxvalue 1200 increment by 20 cache 1000 cycle ENGINE=MyISAM show create sequence d.s3; Table Create Table -s3 CREATE SEQUENCE `s3` start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM +s3 CREATE SEQUENCE `s3` as bigint start with 300 minvalue 300 maxvalue 1300 increment by 30 cache 1000 cycle ENGINE=MyISAM show create sequence d.s4; Table Create Table -s4 CREATE SEQUENCE `s4` start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM +s4 CREATE SEQUENCE `s4` as bigint start with 400 minvalue 400 maxvalue 1400 increment by 40 cache 1000 cycle ENGINE=MyISAM SELECT NEXTVAL(d.s1),NEXTVAL(d.s2),NEXTVAL(d.s3), NEXTVAL(d.s4); NEXTVAL(d.s1) NEXTVAL(d.s2) NEXTVAL(d.s3) NEXTVAL(d.s4) 100 200 300 400 @@ -6443,7 +6443,7 @@ SETVAL(`s4`, 1401, 0) 1401 show create sequence d2.s1; Table Create Table -s1 CREATE SEQUENCE `s1` start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM +s1 CREATE SEQUENCE `s1` as bigint start with 100 minvalue 100 maxvalue 1100 increment by 10 cache 1000 cycle ENGINE=MyISAM drop sequence d.s1, d.s2, d.s3, d.s4; drop database d; drop database d2; 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..03eece0b4ae 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,21 @@ 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 bigint NULL NULL 19 0 NULL NULL NULL bigint(21) select NEVER NULL

As I remember CYCLE_OPTION is boolean or YES/NO, why does it become bigint ?

Done. Changed to Yes_or_empty().

...

+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 DECLARED_DATA_TYPE 13 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL +def information_schema SEQUENCES DECLARED_NUMERIC_PRECISION 14 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL +def information_schema SEQUENCES DECLARED_NUMERIC_SCALE 15 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) select NEVER NULL

We do not need DECLARED_* it is only needed for feature T322 “Declared data type attributes” and we do not have it.

Done. Removed.

...

+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

good decision to make it decimal

Ack.

...

+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

I have not found tests of NUMERIC_* output for all supported by us types.

Done. Added to the sql_sequence.create testcase.

...

+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

I do not see CACHED reflected here, please add it.

That's because CACHED is not part of the specs as in the description of MDEV-28152, nor in some other dbms supporting information_schema.sequences [1][2][3]. Can you check whether it is in the standard? Would you still like me to add it? [1] https://learn.microsoft.com/en-us/sql/relational-databases/system-informatio... [2] https://www.postgresql.org/docs/current/infoschema-sequences.html [3] https://www.cockroachlabs.com/docs/stable/information-schema.html#sequences

...

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 +905,21 @@ 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) +NULL information_schema SEQUENCES CYCLE_OPTION bigint NULL NULL NULL NULL bigint(21) +NULL information_schema SEQUENCES DECLARED_DATA_TYPE int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES DECLARED_NUMERIC_PRECISION int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES DECLARED_NUMERIC_SCALE int NULL NULL NULL NULL int(21) 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..87cc440cdbc 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,21 @@ 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 bigint NULL NULL 19 0 NULL NULL NULL bigint(21) 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 DECLARED_DATA_TYPE 13 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL +def information_schema SEQUENCES DECLARED_NUMERIC_PRECISION 14 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) NEVER NULL +def information_schema SEQUENCES DECLARED_NUMERIC_SCALE 15 NULL YES int NULL NULL 10 0 NULL NULL NULL int(21) 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 +905,21 @@ 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) +NULL information_schema SEQUENCES CYCLE_OPTION bigint NULL NULL NULL NULL bigint(21) +NULL information_schema SEQUENCES DECLARED_DATA_TYPE int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES DECLARED_NUMERIC_PRECISION int NULL NULL NULL NULL int(21) +NULL information_schema SEQUENCES DECLARED_NUMERIC_SCALE int NULL NULL NULL NULL int(21) 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 60b708b8289..e6cd599ec40 100644 --- a/mysql-test/suite/sql_sequence/alter.result +++ b/mysql-test/suite/sql_sequence/alter.result @@ -14,7 +14,7 @@ next value for t1 alter sequence t1 start=50; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 2 1 9223372036854775806 50 1 0 0 0 @@ -24,7 +24,7 @@ next value for t1 alter sequence t1 minvalue=-100; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue -100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 3 -100 9223372036854775806 50 1 0 0 0 @@ -33,14 +33,14 @@ ERROR HY000: Sequence 'test.t1' has out of range value for options alter sequence t1 minvalue=100 start=100 restart=100; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 100 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 100 100 9223372036854775806 100 1 0 0 0 alter sequence t1 maxvalue=500; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 100 maxvalue 500 increment by 1 nocache nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 100 100 500 100 1 0 0 0 @@ -49,20 +49,20 @@ CREATE SEQUENCE t1 engine=myisam; alter sequence t1 nocache; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM alter sequence t1 cache=100; flush tables; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 100 nocycle ENGINE=MyISAM alter sequence t1 nocache; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM flush tables; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1 1 9223372036854775806 1 1 0 0 0 @@ -83,19 +83,19 @@ CREATE SEQUENCE t1 maxvalue=100 engine=myisam; alter sequence t1 no 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 +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM select * from t1; 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 alter sequence t1 cycle; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM alter sequence t1 nocycle; alter sequence t1 start=15 restart minvalue=10 maxvalue=20 cycle; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 15 minvalue 10 maxvalue 20 increment by 1 cache 1000 cycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 15 10 20 15 1 1000 1 0 @@ -129,7 +129,7 @@ CREATE SEQUENCE t1 maxvalue=100; alter sequence t1 increment=-2 start with 50 minvalue=-100; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 50 minvalue -100 maxvalue 100 increment by -2 cache 1000 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1 -100 100 50 -2 1000 0 0 @@ -159,7 +159,7 @@ next value for t1 alter sequence t1 start=100; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB +t1 CREATE SEQUENCE `t1` as bigint start with 100 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=InnoDB select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 11 1 9223372036854775806 100 1 10 0 0 @@ -185,15 +185,15 @@ next value for t1 alter table t1 comment="foo"; 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=InnoDB COMMENT='foo' +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' alter table t1 engine=myisam; 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 COMMENT='foo' +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='foo' alter table t1 engine=innodb; 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=InnoDB COMMENT='foo' +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB COMMENT='foo' select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 3001 1 9223372036854775806 1 1 1000 0 0 @@ -248,14 +248,186 @@ 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 +s CREATE SEQUENCE `s` as bigint 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 +s CREATE SEQUENCE `s` as bigint 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

Here (and for each type) this should be tested:

1. check maxvalue cycling and reaching maximum in case of not cycling which was inherited (126 in the case)

Done. Added to next.test.

2. alter maxvalue according the new type 2.1 make it higher then possible (get a error or fixing the value with a warning) 2.2 make it maximum allowed and test it works 2.3. make it lower (may be around middle range) and test if it works

Hmm, this did not work: create sequence s as tinyint; select next value for s; alter sequence s maxvalue 63; select next value for s; # error: ER_SEQUENCE_RUN_OUT drop sequence s; It is because in the existing sequence implementation (i.e. before my patches) that when you alter the maxvalue (and probably other properties too), that the sequence gets reset, as in all values between the current sequence value and the reserved_until will all be skipped, just like when you restart the server. It causes unintuitive behaviour like create sequence s; select next value for s; # 1 alter sequence s maxvalue 1234; select next value for s; # 1001, because cache size is 1000 and reserved_until is 1001 drop sequence s; Whether this is a bug or not, I have separated out this problem to https://jira.mariadb.org/browse/MDEV-30865. Another existing problem that has been separated out is https://jira.mariadb.org/browse/MDEV-30866.

...

+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` as bigint 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` as bigint 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 +drop sequence s;

Here it would be nice (before that drop) to change maximum_value and then repeat operation to be sure it is allowed.

alter sequence s maxvalue 126; alter sequence s as tinyint;

Done.

...

+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 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; diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test index 3afad38a9e5..4949075f492 100644 --- a/mysql-test/suite/sql_sequence/alter.test +++ b/mysql-test/suite/sql_sequence/alter.test @@ -162,12 +162,63 @@ ALTER TABLE s ORDER BY cache_size; SELECT NEXTVAL(s); DROP SEQUENCE s;

-# -# MDEV-28152 Features for sequence -#

+--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; +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; + +# 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; diff --git a/mysql-test/suite/sql_sequence/alter_notembedded.result b/mysql-test/suite/sql_sequence/alter_notembedded.result index f3e1f5f18cd..c2ea6633d1d 100644 --- a/mysql-test/suite/sql_sequence/alter_notembedded.result +++ b/mysql-test/suite/sql_sequence/alter_notembedded.result @@ -12,7 +12,7 @@ select nextval(s1); ERROR 42000: INSERT command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` show create sequence s1; Table Create Table -s1 CREATE SEQUENCE `s1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +s1 CREATE SEQUENCE `s1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM alter sequence s1 restart 50; ERROR 42000: ALTER command denied to user 'normal_1'@'localhost' for table `s_db`.`s1` connection default; diff --git a/mysql-test/suite/sql_sequence/aria.result b/mysql-test/suite/sql_sequence/aria.result index cfc7d946772..1e42be58240 100644 --- a/mysql-test/suite/sql_sequence/aria.result +++ b/mysql-test/suite/sql_sequence/aria.result @@ -2,7 +2,7 @@ set @@default_storage_engine="aria"; CREATE SEQUENCE t1 cache=10; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=Aria +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 10 nocycle ENGINE=Aria select NEXT VALUE for t1,seq from seq_1_to_20; NEXT VALUE for t1 seq 1 1 diff --git a/mysql-test/suite/sql_sequence/concurrent_create.result b/mysql-test/suite/sql_sequence/concurrent_create.result index 2473abef37d..e28c98e46be 100644 --- a/mysql-test/suite/sql_sequence/concurrent_create.result +++ b/mysql-test/suite/sql_sequence/concurrent_create.result @@ -22,6 +22,20 @@ 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; +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..b4def8d7ce5 100644 --- a/mysql-test/suite/sql_sequence/concurrent_create.test +++ b/mysql-test/suite/sql_sequence/concurrent_create.test @@ -38,6 +38,20 @@ 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; +drop table s2; +execute stmt; +select * from s2; +DROP SEQUENCE s1, s2;

You also better check second execution of CREATE SEQUENCE with the type in SP/PS (probably not in this test file)

Do you mean the following? Why not in this test file? 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 e6a382ec3bf..f2d376774a7 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -4,7 +4,7 @@ Note 1051 Unknown table 'test.t1' create or replace sequence t1 engine=myisam; 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 +t1 CREATE SEQUENCE `t1` as bigint 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` ( @@ -23,7 +23,7 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si create or replace sequence t1 engine=innodb; 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=InnoDB +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -42,7 +42,7 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si create or replace sequence t1 engine=maria; 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=Aria +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -65,59 +65,59 @@ ERROR 42S02: Table 'test.t1' doesn't exist create or replace sequence t1 start with 10; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 10 1 9223372036854775806 10 1 1000 0 0 create or replace sequence t1 minvalue=11; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 11 minvalue 11 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 11 11 9223372036854775806 11 1 1000 0 0 create or replace sequence t1 maxvalue=13 increment by -1; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 13 minvalue -9223372036854775807 maxvalue 13 increment by -1 cache 1000 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 13 -9223372036854775807 13 13 -1 1000 0 0 create or replace sequence t1 increment by -1 cache 100; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 100 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count -1 -9223372036854775807 -1 -1 -1 100 0 0 create or replace sequence t1 cycle; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1 1 9223372036854775806 1 1 1000 1 0 create or replace sequence t1 nocycle; 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 +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM select * from t1; 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 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 +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM create or replace sequence t1 cycle minvalue= 14; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 14 minvalue 14 maxvalue 9223372036854775806 increment by 1 cache 1000 cycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 14 14 9223372036854775806 14 1 1000 1 0 create or replace sequence t1 cycle 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 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 cycle ENGINE=MyISAM drop sequence t1; create sequence if not exists t1; create sequence if not exists t1 start with 10; @@ -128,81 +128,21 @@ next_not_cached_value minimum_value maximum_value start_value increment cache_si 1 1 9223372036854775806 1 1 1000 0 0 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 +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM

For backward compatibility (it is present in mariadbdump) and reducing sizeof the patch we can not print "as bigint" if it is bigint, because it is default.

Done.

...

create or replace sequence t1 start with 10 minvalue=10 maxvalue=11 nocache cycle; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue 10 maxvalue 11 increment by 1 nocache cycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 10 10 11 10 1 0 1 0 create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cycle increment by 10; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 10 minvalue -10 maxvalue 11 increment by 10 cache 10 cycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 10 -10 11 10 10 10 1 0 -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

Why was it deleted?

It is moved to below - search for "# Truncating out-of-bound numbers for minvalue and maxvalue"

...

create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE; create or replace sequence t1 start with 10 maxvalue 10; create or replace sequence t1 start with 10 minvalue 10; @@ -213,7 +153,7 @@ drop sequence if exists t1; create sequence t1 increment by 0; show create sequence t1; Table Create Table -t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 0 cache 1000 nocycle ENGINE=MyISAM select * from t1; next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count 1 1 9223372036854775806 1 0 1000 0 0 @@ -293,7 +233,7 @@ flush tables; create or replace sequence t1 comment= "test 1"; 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 COMMENT='test 1' +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 1' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -309,7 +249,7 @@ t1 CREATE TABLE `t1` ( create or replace sequence t1 comment= "test 2" min_rows=1 max_rows=2; 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 COMMENT='test 2' +t1 CREATE SEQUENCE `t1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM COMMENT='test 2' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -338,7 +278,7 @@ CREATE TABLE t1 ( ) sequence=1; 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 +t1 CREATE SEQUENCE `t1` as bigint 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` ( @@ -373,7 +313,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, @@ -607,7 +547,7 @@ t CREATE TABLE `t` ( ) ENGINE=MyISAM SEQUENCE=1 show create sequence t; Table Create Table -t CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +t CREATE SEQUENCE `t` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM drop tables t, s; # # MDEV-13714 SEQUENCE option fix @@ -772,3 +712,439 @@ 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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` as bigint 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 tiny unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM

You better also check the minimum possible value for each signed and unsigned type (I see default 1 in most cases here, it would be nice to see legal minimums checked (illegal as I can see tested later)).

Done. See the added tests with increment -1.

...

+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 as tinyint zerofill; +ERROR HY000: Incorrect value 'ZEROFILL' for option 'AS' +create or replace sequence t1 as mediumint unsigned increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +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 +# ----- +# 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 tiny 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` as bigint 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` as bigint 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 tiny 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; +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; +select * from information_schema.sequences; +SEQUENCE_CATALOG SEQUENCE_SCHEMA SEQUENCE_NAME DATA_TYPE NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE START_VALUE MINIMUM_VALUE MAXIMUM_VALUE INCREMENT CYCLE_OPTION DECLARED_DATA_TYPE DECLARED_NUMERIC_PRECISION DECLARED_NUMERIC_SCALE +def test s3 bigint unsigned 64 2 0 12345678901234567890 1 18446744073709551614 1 1 NULL NULL NULL +def test s2 bigint 64 2 0 42 -9223372036854775807 9223372036854775806 1 0 NULL NULL NULL +def test s1 tiny unsigned 8 2 0 1 1 254 23 0 NULL NULL NULL +drop sequence s1, s2, s3; diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test index 54e181483d6..388b2fd3fe9 100644 --- a/mysql-test/suite/sql_sequence/create.test +++ b/mysql-test/suite/sql_sequence/create.test @@ -71,41 +71,6 @@ create or replace sequence t1 start with 10 minvalue=-10 maxvalue=11 cache=10 cy show create sequence t1; select * from t1;

-# Truncating out-of-bound numbers for minvalue and maxvalue -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; - # NO MINVALUE, NO MAXVALUE create or replace sequence t1 start with 10 NO MAXVALUE NO MINVALUE;

@@ -582,3 +547,160 @@ 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; +#zerofill is not supported +--error ER_BAD_OPTION_VALUE +create or replace sequence t1 as tinyint zerofill; +#an unsigned sequence has to have positive increment +--error ER_SEQUENCE_INVALID_DATA +create or replace sequence t1 as mediumint unsigned increment by -1; +#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; + +--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; + +# 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; +select * from information_schema.sequences; +drop sequence s1, s2, s3; diff --git a/mysql-test/suite/sql_sequence/mysqldump.result b/mysql-test/suite/sql_sequence/mysqldump.result index 0199bb7162f..3316fdac5ae 100644 --- a/mysql-test/suite/sql_sequence/mysqldump.result +++ b/mysql-test/suite/sql_sequence/mysqldump.result @@ -3,9 +3,9 @@ CREATE TABLE t1(a INT, KEY (a)) KEY_BLOCK_SIZE=1024; insert into t1 values (1),(2); CREATE SEQUENCE x1 engine=innodb; # dump whole database -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; SELECT SETVAL(`a1`, 1, 0); -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; SELECT SETVAL(`x1`, 1, 0); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -18,9 +18,9 @@ INSERT INTO `t1` VALUES (1), (2); # dump by tables order 1 -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; SELECT SETVAL(`a1`, 1, 0); -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; SELECT SETVAL(`x1`, 1, 0); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -33,9 +33,9 @@ INSERT INTO `t1` VALUES (1), (2); # dump by tables order 2 -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; SELECT SETVAL(`a1`, 1, 0); -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; SELECT SETVAL(`x1`, 1, 0); /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -59,9 +59,9 @@ INSERT INTO `t1` VALUES (1), (2); # dump by tables only sequences -CREATE SEQUENCE `a1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; +CREATE SEQUENCE `a1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=Aria; SELECT SETVAL(`a1`, 1, 0); -CREATE SEQUENCE `x1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; +CREATE SEQUENCE `x1` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB; SELECT SETVAL(`x1`, 1, 0); # end of dumps DROP TABLE a1,t1,x1; diff --git a/mysql-test/suite/sql_sequence/next.result b/mysql-test/suite/sql_sequence/next.result index 9d55921006b..76f42143305 100644 --- a/mysql-test/suite/sql_sequence/next.result +++ b/mysql-test/suite/sql_sequence/next.result @@ -548,3 +548,92 @@ 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 tiny 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 tiny 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 +drop sequence t1; diff --git a/mysql-test/suite/sql_sequence/next.test b/mysql-test/suite/sql_sequence/next.test index a80f9fad561..f2054e5f116 100644 --- a/mysql-test/suite/sql_sequence/next.test +++ b/mysql-test/suite/sql_sequence/next.test @@ -297,3 +297,36 @@ 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; + +drop sequence t1;

It is also interesting to see rolling over maximum value.

Done.

...

diff --git a/mysql-test/suite/sql_sequence/replication.result b/mysql-test/suite/sql_sequence/replication.result index 94b1c72b9e0..2cfc246f2bf 100644 --- a/mysql-test/suite/sql_sequence/replication.result +++ b/mysql-test/suite/sql_sequence/replication.result @@ -149,7 +149,7 @@ CREATE TABLE `s2` ( ) ENGINE=myisam DEFAULT CHARSET=latin1 sequence=1; show create sequence s2; Table Create Table -s2 CREATE SEQUENCE `s2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +s2 CREATE SEQUENCE `s2` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM drop sequence s2; ########################################### select sequence syntax test @@ -188,7 +188,7 @@ alter table s2 rename to s2_1; rename table s2_1 to s2_2; show create sequence s2_2; Table Create Table -s2_2 CREATE SEQUENCE `s2_2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +s2_2 CREATE SEQUENCE `s2_2` as bigint start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM select * from s2_2; 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 diff --git a/mysql-test/suite/sql_sequence/slave_nextval.result b/mysql-test/suite/sql_sequence/slave_nextval.result index bfbc472e117..36efa85d920 100644 --- a/mysql-test/suite/sql_sequence/slave_nextval.result +++ b/mysql-test/suite/sql_sequence/slave_nextval.result @@ -4,7 +4,7 @@ CREATE SEQUENCE s; INSERT INTO s VALUES (1,1,4,1,1,1,0,0); show create sequence s; Table Create Table -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 4 increment by 1 cache 1 nocycle ENGINE=MyISAM +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 4 increment by 1 cache 1 nocycle ENGINE=MyISAM SELECT NEXTVAL(s); NEXTVAL(s) 1 @@ -38,7 +38,7 @@ CREATE SEQUENCE s; INSERT INTO s VALUES (1,1,3,1,1,1,1,0); show create sequence s; Table Create Table -s CREATE SEQUENCE `s` start with 1 minvalue 1 maxvalue 3 increment by 1 cache 1 cycle ENGINE=MyISAM +s CREATE SEQUENCE `s` as bigint start with 1 minvalue 1 maxvalue 3 increment by 1 cache 1 cycle ENGINE=MyISAM SELECT NEXTVAL(s); NEXTVAL(s) 1 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/sql_sequence.cc b/sql/sql_sequence.cc index 11be2e1a691..c5754dc9670 100644 --- a/sql/sql_sequence.cc +++ b/sql/sql_sequence.cc @@ -30,15 +30,6 @@ #include "wsrep_mysqld.h" #endif

-struct Field_definition -{ - const char *field_name; - uint length; - const Type_handler *type_handler; - LEX_CSTRING comment; - ulong flags; -}; - /* Structure for all SEQUENCE tables

@@ -48,30 +39,87 @@ struct Field_definition a column named NEXTVAL. */

+#define MAX_AUTO_INCREMENT_VALUE 65535 + +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 FL (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG) +#define FLV (NOT_NULL_FLAG | NO_DEFAULT_VALUE_FLAG | flag_unsigned)

FL was a bad name (probably meant flag) and so you added even worse FLV (what it mean?)

Please make both probably not long, but at least a bit meaningful.

Done.

...

+ return {{{"next_not_cached_value", len, handler, empty, FLV}, + {"minimum_value", len, handler, empty, FLV}, + {"maximum_value", len, handler, empty, FLV}, + {"start_value", len, handler, + {STRING_WITH_LEN("start value when sequences is created or value " + "if RESTART is used")}, FLV}, + {"increment", 21, &type_handler_slonglong, + {STRING_WITH_LEN("increment value")}, FL}, + {"cache_size", 21, &type_handler_ulonglong, empty, + 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 FLV +#undef FL +} + +bool sequence_definition::is_allowed_value_type(enum_field_types type) +{ + 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; + } +}

-static Field_definition sequence_structure[]= +Type_handler const *sequence_definition::value_type_handler() { - {"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} -}; + const Type_handler *handler= Type_handler::get_handler_by_field_type(value_type); + return is_unsigned ? handler->type_handler_unsigned() : handler; +}

-#undef FL +longlong sequence_definition::value_type_max() +{ + // value_type != MYSQL_TYPE_LONGLONG to avoid undefined behaviour + // https://stackoverflow.com/questions/9429156/by-left-shifting-can-a-number-be...

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ return is_unsigned && value_type != MYSQL_TYPE_LONGLONG ? + ~(~0ULL << 8 * value_type_handler()->calc_pack_length(0)) : + ~value_type_min(); +}

+longlong sequence_definition::value_type_min() { + return is_unsigned ? 0 : + ~0ULL << (8 * value_type_handler()->calc_pack_length(0) - 1); +}

-#define MAX_AUTO_INCREMENT_VALUE 65535 +/* + Truncate `original` to `result`. + 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() +*/

Please use your standard description of all functions/methods

/** One-line description

Here longer multiline description of the function and maybe how it works

@param paramater1 parameter1 description @param param2 param2 description

@note Some notes about function and or implementation can be multiline.

@return description what it return */

it also can be instead of @return returning value description @retval TRUE error @retval FALSE everything OK

Done.

...

+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. @@ -82,49 +130,66 @@ static Field_definition sequence_structure[]= true invalid */

+// from_parser: whether to check foo_from_parser or foo, where foo in +// {min_value, max_value, ...}

Above method comment is in old style. You want to fix it (add a note), do just rewrite it please in the new style /** (see my example in this review)

Done.

...

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");

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 + If min_value is not set, set it to value_type_min()+1 or 1, depending on real_increment */ - 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 ? value_type_min()+1 : 1; + else + { + min_value= truncate_value(min_value_from_parser); + if ((is_unsigned && (ulonglong) min_value <= (ulonglong) value_type_min()) ||

above line is too long (at least should fit in 80 better in 72 character)

...

+ (!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 + If max_value is not set, set it to value_type_max()-1 or -1, depending on real_increment */ - if (!(used_fields & seq_field_used_max_value)) - max_value= real_increment < 0 ? -1 : LONGLONG_MAX-1; - - if (max_value == LONGLONG_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= LONGLONG_MAX - 1; - } - if (min_value == LONGLONG_MIN) + if (!(used_fields & seq_field_specified_max_value)) + max_value= real_increment < 0 ? -1 : value_type_max()-1; + else { - push_warning_printf( - thd, Sql_condition::WARN_LEVEL_NOTE, ER_TRUNCATED_WRONG_VALUE, - ER_THD(thd, ER_TRUNCATED_WRONG_VALUE), "INTEGER", "MINVALUE"); - min_value= LONGLONG_MIN + 1; + 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.

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ start= truncate_value(start_from_parser);

Do we check that start value fit in [minvalue, maxvalue] range?

It is checked below, in the two big ifs.

...

if (set_reserved_until) reserved_until= start; @@ -136,12 +201,23 @@ bool sequence_definition::check_and_adjust(THD

*thd, bool set_reserved_until)

...

llabs(real_increment) : MAX_AUTO_INCREMENT_VALUE);

- if (max_value >= start && - max_value > min_value && + // Common case for error, signed or unsigned. + if (!is_allowed_value_type(value_type) || cache < 0) + DBUG_RETURN(TRUE); + + // TODO: check for cache < (ULONGLONG_MAX - max_increment) /

max_increment

Why above is in TODO, IMHO it is not so difficult to make (maybe I do not see something?)

It is a hidden constraint that I didn't understand at the time of writing this comment. I've figured it out and added the check and tests for it.

...

+ if (is_unsigned && (ulonglong) max_value >= (ulonglong) start && + (ulonglong) max_value > (ulonglong) min_value && + (ulonglong) start >= (ulonglong) min_value && + // Just like the case in signed, where a positive sequence + // cannot have a negatvie increment, an unsigned sequence is + // positive, so the increment has to be positive

Why we can not count backward for unsigned sequence?

I think we can. We can count down a positive sequence in the existing implementation: create sequence s start with 200 maxvalue 200 minvalue 100 increment -1; However I was worried about inconsistency with the signed case: If we do create sequence s increment 1; we get a sequence starting at 1, with minvalue 1 and maxvalue longlong_max - 1. If we do create sequence s increment -1; we get a sequence starting at -1, with maxvalue -1 and minvalue longlong_min + 1. So it is like we cut the whole range of a signed type into half depending on the positivity of the increment. In unsigned types it does not make sense to cut the type in half. So in my updated patch, I have implemented so that we just count from minvalue or maxvalue depending on the sign of the increment: create sequence s as bigint unsigned increment 1; will create a sequence starting at 1 with minvalue 1 and maxvalue ulonglong_max - 1, and create sequence s as bigint unsigned increment -1; will create a sequence starting at ulonglong_max - 1 with minvalue 1 and maxvalue ulonglong_max - 1

Taking into account that increment probably should not exceed half of allowed range we always can store it as bigint (or decimal if we should support sequences with 1 value in them).

We use // only for one line coments please use /* */ above (please fx it in all your cases)

...

+ (real_increment > 0 && (ulonglong) reserved_until >= (ulonglong) min_value)) + DBUG_RETURN(FALSE); + + 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 && + cache < (LONGLONG_MAX - max_increment) / max_increment && ((real_increment > 0 && reserved_until >= min_value) || (real_increment < 0 && reserved_until <= max_value))) DBUG_RETURN(FALSE); @@ -165,6 +241,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(); @@ -181,10 +262,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); @@ -210,10 +291,17 @@ bool check_sequence_fields(LEX *lex, List *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"; + goto err; + } + row_structure= sequence_structure(fields->head()->type_handler()); + if (field_count != array_elements(row_structure.fields)-1) { reason= "Wrong number of columns";

We do not have only english speaking users, it was done bad before and now we have chance to fix it and make it correct via localised errors.

Done.

...

goto err; @@ -236,7 +324,7 @@ bool check_sequence_fields(LEX *lex,

List *fields)

...

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 || @@ -265,12 +353,13 @@ bool check_sequence_fields(LEX *lex,

List *fields)

...

true Failure (out of memory) */

-bool prepare_sequence_fields(THD *thd, List *fields) +bool sequence_definition::prepare_sequence_fields(List

*fields, bool alter)

too long line

Done.

...

{ - Field_definition *field_info; DBUG_ENTER("prepare_sequence_fields"); + const Sequence_row_definition row_def= sequence_structure(value_type_handler());

We put DBUG_ENTER after variables definition (C code heritage)

...

- 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, @@ -285,6 +374,8 @@ bool prepare_sequence_fields(THD *thd, List *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 */ } @@ -313,20 +404,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) @@ -387,7 +476,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(); + // fixme: why do we need true here?

because it is other copy so we reset it to the start, right?

Right, it's because we are creating a new sequence, just like in the .yy file it also passes true to check_and_adjust()

...

+ if (seq->check_and_adjust(thd, true)) + DBUG_RETURN(TRUE); + } + error= seq->write_initial_sequence(table); { uint save_unsafe_rollback_flags= @@ -432,9 +529,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) @@ -734,10 +831,9 @@ 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_bounds(res_value, reserved_until, reserved_until, real_increment > 0)) { write_unlock(table); DBUG_RETURN(res_value); @@ -754,30 +850,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; - } + // TODO: consider extracting this refactoring to a separate earlier commit.

You have send it on review, so I suppose you take decision about above?

Yup. I've included it in the patch for this task.

...

+ reserved_until= increment_value(reserved_until, add_to); + out_of_values= !within_bounds(res_value, max_value + 1, min_value - 1, add_to > 0); if (out_of_values) { if (!cycle || second_round) @@ -866,7 +942,7 @@ 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 @@ -953,6 +1029,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 shouldn't happen as it should have been prevented during + // parsing.

If something should not happened you better put DEBUG_ASSERT to notice in debug version if something goes wrong.

Chenge comment stile if you want to leave it

Done.

...

+ if (new_seq->used_fields - seq_field_used_as) + DBUG_RETURN(TRUE); + + 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?

I do not understand comment above. (what it is about?)

I am asking whether we should do `seq->copy(new_seq);` as done below when altering non-as-type. The problem is we do not have access to seq which requires opening the table. My testing seems to indicate there is no need.

...

+ DBUG_RETURN(error); + } + if (if_exists()) thd->push_internal_handler(&no_such_table_handler); error= open_and_lock_tables(thd, first_table, FALSE, 0); @@ -989,22 +1094,30 @@ bool Sql_cmd_alter_sequence::execute(THD *thd) if (!(new_seq->used_fields & seq_field_used_increment)) new_seq->increment= seq->increment; if (!(new_seq->used_fields & seq_field_used_min_value)) - new_seq->min_value= seq->min_value; + new_seq->min_value_from_parser= seq->min_value_from_parser; if (!(new_seq->used_fields & seq_field_used_max_value)) - new_seq->max_value= seq->max_value; + new_seq->max_value_from_parser= seq->max_value_from_parser; if (!(new_seq->used_fields & seq_field_used_start)) - new_seq->start= seq->start; + new_seq->start_from_parser= seq->start_from_parser; 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; + if (!(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.

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ new_seq->reserved_until= new_seq->truncate_value(new_seq->restart_from_parser); }

/* Let check_and_adjust think all fields are used */ diff --git a/sql/sql_sequence.h b/sql/sql_sequence.h index e2968cc20ae..0de92a90639 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,20 +62,37 @@ 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.

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ 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; + // TODO: allow unsigned in restart 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; + + 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); @@ -60,12 +100,16 @@ class sequence_definition :public Sql_alloc 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 *fields, bool alter); + protected: /* The following values are the values from sequence_definition @@ -107,24 +151,54 @@ class SEQUENCE :public sequence_definition 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 within_bounds(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); + } + + 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) + { + // in case value + increment overflows + if ((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; }

@@ -159,9 +233,6 @@ class SEQUENCE_LAST_VALUE uchar table_version[MY_UUID_SIZE]; };

- -class Create_field; -extern bool prepare_sequence_fields(THD *thd, List *fields); extern bool check_sequence_fields(LEX *lex, List *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..12e4f655515 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -2694,12 +2694,29 @@ 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:

In case of multiline comment /* should be on separate line

Done.

...

+ https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-S...

you also probably should not show it if it is default (BIGINT), see other comment about it in the beginning.

Done.

BTW add test of making sequence with/without oracle mode and dumping and restoring (mysqldump) them for all types. You can find examples of the tests by "ls mysql-test/main/*dump*.test".

Done.

...

+ */ + if (!(sql_mode & MODE_ORACLE)) + { + 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 +5529,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 +9466,29 @@ 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.

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ 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", SLonglong(21), NOT_NULL), + Column("DECLARED_DATA_TYPE", SLong(21), NULLABLE), + Column("DECLARED_NUMERIC_PRECISION", SLong(21), NULLABLE), + Column("DECLARED_NUMERIC_SCALE", SLong(21), NULLABLE), + CEnd() +}; + + ST_FIELD_INFO stat_fields_info[]= { Column("TABLE_CATALOG", Catalog(), NOT_NULL, OPEN_FRM_ONLY), @@ -9985,6 +10055,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_yacc.yy b/sql/sql_yacc.yy index e3298a4a6c1..27ce8bcdbfc 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -200,6 +200,9 @@ 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.

We use // only for one line coments please use /* */ above (please fx it in all your cases)

Done.

...

+ Longlong_hybrid longlong_hybrid_number= Longlong_hybrid(0, false);

/* structs */ LEX_CSTRING lex_str; @@ -1466,7 +1469,10 @@ bool my_yyoverflow(short **a, YYSTYPE **b, size_t *yystacksize); ulonglong_num real_ulonglong_num

%type - sequence_value_num sequence_truncated_value_num + sequence_value_num + +%type + sequence_value_hybrid_num sequence_truncated_value_hybrid_num

%type <choice> choice

@@ -2429,8 +2435,8 @@ 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 */ @@ -2605,13 +2611,25 @@ sequence_defs: ;

sequence_def: - MINVALUE_SYM opt_equal sequence_truncated_value_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 { 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= $3; + 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 { @@ -2625,13 +2643,14 @@ sequence_def: my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MINVALUE")); Lex->create_info.seq_create_info->used_fields|= seq_field_used_min_value; } - | MAXVALUE_SYM opt_equal sequence_truncated_value_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)) my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); - Lex->create_info.seq_create_info->max_value= $3; + 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 { @@ -2645,12 +2664,12 @@ sequence_def: my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "MAXVALUE")); Lex->create_info.seq_create_info->used_fields|= seq_field_used_max_value; } - | START_SYM opt_with sequence_value_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->start_from_parser= $3; Lex->create_info.seq_create_info->used_fields|= seq_field_used_start; } | INCREMENT_SYM opt_by sequence_value_num @@ -2705,7 +2724,7 @@ sequence_def: my_yyabort_error((ER_DUP_ARGUMENT, MYF(0), "RESTART")); Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart; } - | RESTART_SYM opt_with sequence_value_num + | RESTART_SYM opt_with sequence_value_hybrid_num { if (unlikely(Lex->sql_command != SQLCOM_ALTER_SEQUENCE)) { @@ -2715,7 +2734,7 @@ 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->restart_from_parser= $3; Lex->create_info.seq_create_info->used_fields|= seq_field_used_restart | seq_field_used_restart_value; } ; @@ -7122,6 +7141,8 @@ 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))

Above lie is to long

Done.

...

+ my_yyabort_error((ER_NOT_SUPPORTED_YET, MYF(0), "ALTER SEQUENCE with both AS <type> and something else."));

Above lie is to long

Done.

...

if (unlikely(Lex->m_sql_cmd == NULL)) MYSQL_YYABORT; } stmt_end {} @@ -12563,6 +12584,7 @@ real_ulong_num: | dec_num_error { MYSQL_YYABORT; } ;

+// For simple sequence metadata values that are signed and do not need

truncation

Above lie is to long

Done.

...

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); } @@ -12579,15 +12601,74 @@ sequence_value_num: } ;

-sequence_truncated_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); } - | opt_plus ULONGLONG_NUM { $$= LONGLONG_MAX; } - | opt_plus DECIMAL_NUM { $$= LONGLONG_MAX; } - | '-' 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 { $$= LONGLONG_MIN; } - | '-' DECIMAL_NUM { $$= LONGLONG_MIN; } +// For sequence metadata values that may be unsigned but do not need truncation (start, restart)

Above lie is to long (it looks like you have them a lot, so please fix all lines to fit in 80 (better 72 character).

Done.

...

+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:

Best, Yuchen

Hi! I couple of small comments in addition to Sanjas review:

...

+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();

Please remove all 'else' above. not needed and makes lines shorter. Please cache also original.value() in a local variable. Yes, it's a currently defined as { return m_value; } but as this may change in the future, it's better to be sure and store in a variable. Doing that will also make the code text sligtly shorter: Please also adjust the indentention to be as follows: return (value > value_type_max() ? value_type_max() : value < value_type_min() ? value_type_min() value); In other words - Operators last on the line - Long operations should be surronded by braces to make it easier for the editor to align things properly.

...

diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e3298a4a6c1..27ce8bcdbfc 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2605,13 +2611,25 @@ sequence_defs: ;

sequence_def: - MINVALUE_SYM opt_equal sequence_truncated_value_num + AS int_type field_options + {

Please create a local variable: sequence_definition *seq= lex->create_info.seq_create_info; and use it below. Makes the long lines much shorter!

...

+ 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; + }

Regards, Monty