developers

Thread Start a new thread

Download

developers@lists.mariadb.org

March 2023

6 participants
5 discussions

[Maria-developers] MariaDB // Temporal tables
by Robert Palm 02 Oct '24

02 Oct '24

2 2

[Maria-developers] How to get started with GSOC contribution
by Komala Elusuri 25 Mar '23

25 Mar '23

Respected sir, I am Komala, a computer science undergraduate from India. I am new to open source contributions but I am well aware of python, C, C++, Java,JavaScript and Databases. I did certifications on Google cloud computing foundation with Kubernetes and Python projects from Infosys springboard. I have experience as an intern from Smart Knower. I would love to contribute to your organisation but could you please tell me how to get started? Hoping to hear from you soon. Regards Komala

1 0

Re: [Maria-developers] 940d028521f: MDEV-30164 System variable for default collations
by Sergei Golubchik 21 Mar '23

21 Mar '23

Hi, Alexander, I'm sorry it took a while. I'm still thinking about the whole thing, it's a rather big change for a really fringe functionality. But I failed to come up with something better so far. Code-wise the patch is mostly fine. See few small comments below, and one slightly larger comment re. replication. On Mar 07, Alexander Barkov wrote: > revision-id: 940d028521f (mariadb-10.11.1-4-g940d028521f) > parent(s): 71dedd0ebcd > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2022-12-14 20:00:22 +0400 > message: > > MDEV-30164 System variable for default collations > > This patch adds a way to override default collations > (or "character set collations") for desired character sets. > > diff --git a/sql/lex_charset.h b/sql/lex_charset.h > --- a/sql/lex_charset.h > +++ b/sql/lex_charset.h > @@ -544,6 +699,20 @@ struct Lex_exact_charset_extended_collation_attrs_st > { > return m_ci; > } > + CHARSET_INFO *charset_info(const Charset_collation_map_st &map) const > + { > + switch (m_type) > + { > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_CHARACTER_SET: > + return map.get_collation_for_charset(m_ci); > + case TYPE_EMPTY: Lex_exact_charset_extended_collation_attrs_st::TYPE_EMPTY (or all case labels without a struct name) > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_CHARACTER_SET_COLLATE_EXACT: > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_COLLATE_CONTEXTUALLY_TYPED: COLLATE DEFAULT is TYPE_COLLATE_CONTEXTUALLY_TYPED. shouldn't it use the map too? > + case Lex_exact_charset_extended_collation_attrs_st::TYPE_COLLATE_EXACT: > + break; > + } > + return m_ci; > + } > Type type() const > { > return m_type; > diff --git a/sql/log_event.h b/sql/log_event.h > --- a/sql/log_event.h > +++ b/sql/log_event.h > @@ -2147,6 +2153,8 @@ class Query_log_event: public Log_event > bool sql_mode_inited; > bool charset_inited; > > + LEX_CSTRING character_set_collations_str; better LEX_CUSTRING, don't you think? It's not even a _str. > + > uint32 flags2; > sql_mode_t sql_mode; > ulong auto_increment_increment, auto_increment_offset; > diff --git a/sql/log_event_server.cc b/sql/log_event_server.cc > --- a/sql/log_event_server.cc > +++ b/sql/log_event_server.cc > @@ -1194,6 +1194,14 @@ bool Query_log_event::write() > int2store(start+2, auto_increment_offset); > start+= 4; > } > + > + if (thd && thd->variables.character_set_collations.count()) > + { > + *start++= Q_COLLATIONS_SESSION; > + size_t len= thd->variables.character_set_collations.to_binary((char*)start); > + start+= len; > + } Perhaps, detect if it's needed? A cheap way of doing it would be to extend your Elem_st with a query_id. And every time you find_elem_by_charset, you set this elem's query_id to thd->query_id. And here you write only elements with the current query id. If any. Another approach would be to have a bitmap, like uchar used_default_coll_mapping; and in find_elem_by_charset() you set the bit, like used_default_coll_mapping |= 1 << i; and then, again, print affected collations, if any. Most often used_default_coll_mapping will likely be zero one more question. In, say, 10.10->11.1 replication master and slave will have different default collations, but thd->variables.character_set_collations will not reflect that. How do you plan to solve it? > + > if (charset_inited) > { > *start++= Q_CHARSET_CODE; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 3

Re: [Maria-developers] MDEV-28152: Features for sequence
by Oleksandr Byelkin 21 Mar '23

21 Mar '23

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 ? > +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. > +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 > +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. > +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. > 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) 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 > +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; > +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) > + > # > # 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. > 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? > 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)). > +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. > 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. > + 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-b… We use // only for one line coments please use /* */ above (please fx it in all your cases) > + 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 > +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) > 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) > + start= truncate_value(start_from_parser); Do we check that start value fit in [minvalue, maxvalue] range? > > 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?) > + 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? 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<Create_field> *fields) > uint field_count; > uint field_no; > const char *reason; > + Sequence_row_definition row_structure; > DBUG_ENTER("check_sequence_fields"); > > field_count= fields->elements; > - if (field_count != array_elements(sequence_structure)-1) > + if (!field_count) > + { > + reason= "Wrong number of columns"; > + 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. > goto err; > @@ -236,7 +324,7 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *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<Create_field> *fields) > true Failure (out of memory) > */ > > -bool prepare_sequence_fields(THD *thd, List<Create_field> *fields) > +bool sequence_definition::prepare_sequence_fields(List<Create_field> *fields, bool alter) too long line > { > - 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<Create_field> *fields) > new_field->char_length= field_info->length; > new_field->comment= field_info->comment; > new_field->flags= field_info->flags; > + if (alter) > + new_field->change = field_name; > if (unlikely(fields->push_back(new_field))) > DBUG_RETURN(TRUE); /* purify inspected */ > } > @@ -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? > + 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? > + 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 > + 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?) > + 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) > + 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) > + 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<Create_field> *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<Create_field> *fields); > extern bool check_sequence_fields(LEX *lex, List<Create_field> *fields); > extern bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *table_list); > #endif /* SQL_SEQUENCE_INCLUDED */ > diff --git a/sql/sql_show.cc b/sql/sql_show.cc > index 1fd31bd4947..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 > + https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/CREATE-… you also probably should not show it if it is default (BIGINT), see other comment about it in the beginning. 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". > + */ > + 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) > + 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) > + 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 <longlong_number> > - sequence_value_num sequence_truncated_value_num > + sequence_value_num > + > +%type <longlong_hybrid_number> > + 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 > + my_yyabort_error((ER_NOT_SUPPORTED_YET, MYF(0), "ALTER SEQUENCE with both AS <type> and something else.")); Above lie is to long > 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 > 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). > +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:

3 6

Re: [Maria-developers] 8c1ad2a9fe9: MDEV-30633 DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow
by Sergei Golubchik 08 Mar '23

08 Mar '23

Hi, Alexander, please add tests with fractional seconds. remember, that in UTC the max timestamp(2) is 2038-01-19 03:14:07.99 and max timestamp(6) is 2038-01-19 03:14:07.999999 On Mar 08, Alexander Barkov wrote: > revision-id: 8c1ad2a9fe9 (mariadb-10.11.1-48-g8c1ad2a9fe9) > parent(s): ce4a289f1c3 > author: Alexander Barkov > committer: Alexander Barkov > timestamp: 2023-02-13 17:25:18 +0400 > message: > > MDEV-30633 DATETIME to TIMESTAMP conversion to return maximum timestamp on overflow a bit more verbose description would be nice > diff --git a/mysql-test/main/events_bugs.result b/mysql-test/main/events_bugs.result > --- a/mysql-test/main/events_bugs.result > +++ b/mysql-test/main/events_bugs.result > @@ -35,12 +35,34 @@ SET NAMES latin1; > set @a=3; > CREATE PROCEDURE p_16 () CREATE EVENT e_16 ON SCHEDULE EVERY @a SECOND DO SET @a=5; > ERROR HY000: Recursion of EVENT DDL statements is forbidden when body is present > +SET time_zone='+00:00'; > +SET timestamp=UNIX_TIMESTAMP('2023-02-13 00:00:00'); > create event e_55 on schedule at 99990101000000 do drop table t; > -ERROR HY000: Incorrect AT value: '99990101000000' Hmm, why did you not keep it an error? > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '9999-01-01 00:00:00' > +Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. > diff --git a/mysql-test/main/func_time.result b/mysql-test/main/func_time.result > --- a/mysql-test/main/func_time.result > +++ b/mysql-test/main/func_time.result > @@ -599,19 +599,25 @@ Warnings: > Warning 1292 Truncated incorrect unixtime value: '2147483648' > select unix_timestamp('2039-01-20 01:00:00'); > unix_timestamp('2039-01-20 01:00:00') > -NULL > +2147483647 > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2039-01-20 01:00:00' it's consistent with MariaDB [test]> select cast(1e30 as int); +---------------------+ | cast(1e30 as int) | +---------------------+ | 9223372036854775807 | +---------------------+ 1 row in set, 1 warning (0.000 sec) Note (Code 1916): Got overflow when converting '1e30' to SIGNED BIGINT. Value truncated good. Except that it's Note vs Warning. Want to unify this somehow? > select unix_timestamp('1968-01-20 01:00:00'); > unix_timestamp('1968-01-20 01:00:00') > NULL isn't it strange? That overflow is capped to max value, while underflow is NULL? > diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result > --- a/mysql-test/main/select.result > +++ b/mysql-test/main/select.result > @@ -3766,11 +3766,15 @@ AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; > id select_type table type possible_keys key key_len ref rows Extra > 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 > 1 SIMPLE t1 range ts ts 4 NULL 2 Using index condition; Using where > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2999-12-31 00:00:00' Looks questionable. The query compares timestamp with a datetime. I think in this case timestamp should be casted to a datetime (so, no warning), not vice versa. > SELECT * FROM t1 LEFT JOIN t2 ON (t1.a=t2.a) WHERE t1.a=30 > AND t1.ts BETWEEN t2.dt1 AND t2.dt2 > AND t1.ts BETWEEN "2006-01-01" AND "2006-12-31"; > a ts a dt1 dt2 > 30 2006-01-03 23:00:00 30 2006-01-01 00:00:00 2999-12-31 00:00:00 > +Warnings: > +Warning 1292 Truncated incorrect timestamp value: '2999-12-31 00:00:00' > DROP TABLE t1,t2; > create table t1 (a bigint unsigned); > insert into t1 values > diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc > --- a/sql/item_timefunc.cc > +++ b/sql/item_timefunc.cc > @@ -2793,11 +2793,17 @@ bool Item_func_convert_tz::get_date(THD *thd, MYSQL_TIME *ltime, > return true; > > { > - uint not_used; > - my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, &not_used); > + uint error_code; > + my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, &error_code); > ulong sec_part= ltime->second_part; > - /* my_time_tmp is guaranteed to be in the allowed range */ > - if (my_time_tmp) > + /* > + my_time_tmp is guaranteed to be in the allowed range. > + Don't perform the conversion in case the source DATETIME was above > + TIMESTAMP_MAX_VALUE (and was truncated to TIMESTAMP_MAX_VALUE). why not? > + */ > + if (my_time_tmp && > + (my_time_tmp != TIMESTAMP_MAX_VALUE || > + error_code != ER_WARN_DATA_OUT_OF_RANGE)) why `my_time_tmp != TIMESTAMP_MAX_VALUE` ? shouldn't error_code check be sufficient? > to_tz->gmt_sec_to_TIME(ltime, my_time_tmp); > /* we rely on the fact that no timezone conversion can change sec_part */ > ltime->second_part= sec_part; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0