- developers - lists.mariadb.org

[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

Re: [Maria-developers] 6e28a576913: move alloca() definition from all *.h files to one place
by Sergei Golubchik 10 Feb '23

10 Feb '23

Hi, Julius, this is the combined diff of both commits: git diff e1ce867e5ab0c^^ e1ce867e5ab0c On Feb 10, Julius Goryavsky wrote: > diff --git a/include/CMakeLists.txt b/include/CMakeLists.txt > index a7b98a11050..7d87c4a42b3 100644 > --- a/include/CMakeLists.txt > +++ b/include/CMakeLists.txt > @@ -104,6 +105,7 @@ ENDMACRO() > > INSTALL_COMPAT_HEADER(my_global.h "") > INSTALL_COMPAT_HEADER(my_config.h "") > +INSTALL_COMPAT_HEADER(my_alloca.h "") I wouldn't use INSTALL_COMPAT_HEADER for my_alloca.h > INSTALL_COMPAT_HEADER(my_sys.h "") > INSTALL_COMPAT_HEADER(mysql_version.h " > #include <mariadb_version.h> > diff --git a/include/my_alloca.h b/include/my_alloca.h > new file mode 100644 > index 00000000000..fec0320195e > --- /dev/null > +++ b/include/my_alloca.h > @@ -0,0 +1,37 @@ > +/* Copyright (c) 2023, MariaDB Corporation. > + > + This program is free software; you can redistribute it and/or modify > + it under the terms of the GNU General Public License as published by > + the Free Software Foundation; version 2 of the License. > + > + This program is distributed in the hope that it will be useful, > + but WITHOUT ANY WARRANTY; without even the implied warranty of > + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the > + GNU General Public License for more details. > + > + You should have received a copy of the GNU General Public License > + along with this program; if not, write to the Free Software > + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ > + > +#ifndef MY_ALLOCA_INCLUDED > +#define MY_ALLOCA_INCLUDED > + > +#ifdef _WIN32 > +#include <malloc.h> /*for alloca*/ > +#ifndef alloca When can alloca be defined? > +#define alloca _alloca > +#endif > +#else > +#include <stdlib.h> why stdlib.h? > +#ifdef HAVE_ALLOCA_H > +#include <alloca.h> > +#endif > +#endif > + > +#if defined(HAVE_ALLOCA) > +#if defined(__GNUC__) && !defined(HAVE_ALLOCA_H) && !defined(alloca) add a comment describing your MinGW use case when this #if is needed > +#define alloca __builtin_alloca > +#endif /* GNUC */ > +#endif > + > +#endif /* MY_ALLOCA_INCLUDED */ > diff --git a/include/mysql/service_encryption.h b/include/mysql/service_encryption.h > index 69d205a27e8..cab1418e1d6 100644 > --- a/include/mysql/service_encryption.h > +++ b/include/mysql/service_encryption.h > @@ -24,21 +24,19 @@ > *provider* (encryption plugin). > */ > > +#ifndef MYSQL_ABI_CHECK > +#include <my_alloca.h> > +#endif > + > #ifdef __cplusplus > extern "C" { > #endif > > #ifndef MYSQL_ABI_CHECK why not to put #include <my_alloca.h> here? > #ifdef _WIN32 > -#include <malloc.h> > #ifndef __cplusplus > #define inline __inline > #endif > -#else > -#include <stdlib.h> I suspect stdlib.h belongs here, not in my_alloca.h > -#ifdef HAVE_ALLOCA_H > -#include <alloca.h> > -#endif > #endif > #endif Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

1 0

Re: [Maria-developers] ceb75e9bd9e: MDEV-30203: Move mysql symlinks to different package (fixes)
by Sergei Golubchik 10 Feb '23

10 Feb '23

Hi, Daniel, On Feb 09, Daniel Black wrote: > revision-id: ceb75e9bd9e (mariadb-10.11.1-152-gceb75e9bd9e) > parent(s): 526fce28b90 > author: Daniel Black > committer: Daniel Black > timestamp: 2023-02-09 12:20:57 +1100 > message: > > MDEV-30203: Move mysql symlinks to different package (fixes) > > Fixes MDEV-30571 MariaDB-client does not pull MariaDB-compat anymore, conflicts with RHEL packages > > Fixes MDEV-30574 MariaDB-server conflicts with MariaDB-common from previous versions > > MariaDB-Server now depends on MariaDB-common 11.0+. > > MariaDB-{Client,Server} append to the Requirements rather than overriding them. > > MariaDB-Server package now depends on the MariaDB-client 11.0+. > > Because of moves between packages MariaDB-Client conflicts with > MariaDB-server < 11.0.0. > > diff --git a/cmake/cpack_rpm.cmake b/cmake/cpack_rpm.cmake > index 638f6b2dabb..e88a5493eac 100644 > --- a/cmake/cpack_rpm.cmake > +++ b/cmake/cpack_rpm.cmake > @@ -189,7 +189,7 @@ FOREACH(SYM_COMPONENT Server Client) > SET(CPACK_RPM_${SYM}_PACKAGE_ARCHITECTURE "noarch") > SET(CPACK_RPM_${SYM}_USER_FILELIST ${ignored}) > STRING(TOLOWER ${SYM_COMPONENT} SYM_COMPONENT_LOWER) > - SET(CPACK_RPM_${SYM}_PACKAGE_REQUIRES "MariaDB-${SYM_COMPONENT_LOWER} >= 11.0.0") > + SETA(CPACK_RPM_${SYM}_PACKAGE_REQUIRES "MariaDB-${SYM_COMPONENT_LOWER} >= 11.0.0") why? > SETA(CPACK_RPM_${SYM_COMPONENT_LOWER}_PACKAGE_RECOMMENDS "MariaDB-${SYM}") > ENDFOREACH() > > @@ -205,10 +205,10 @@ SETA(CPACK_RPM_client_PACKAGE_PROVIDES > "mysql-client" > "mytop") > SETA(CPACK_RPM_client_PACKAGE_CONFLICTS > - "MariaDB-server < 10.6.0") > + "MariaDB-server < 11.0.0") because of resolve-stack-dump, ok > SETA(CPACK_RPM_common_PACKAGE_CONFLICTS > - "MariaDB-server < 10.6.1") > + "MariaDB-server < 11.0.0") why? > SETA(CPACK_RPM_devel_PACKAGE_OBSOLETES > "MySQL-devel") Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1

[Maria-developers] MDEV-30218: On what should be passed as 'rows' to filtering code.
by Sergey Petrunia 01 Feb '23

01 Feb '23

Hello Igor, So I'm looking at the bb-10.4-release-igor tree with your latest patch: commit c4fa6c3c4eaacefd9bf50b5c88c2c72474a15bc5 (HEAD -> bb-10.4-release-igor, origin/bb-10.4-release-igor) Author: Igor Babaev <igor(a)askmonty.org> Date: Tue Jan 31 13:14:53 2023 -0800 MDEV-30218 Incorrect optimization for rowid_filtering Correction over the last patch for this MDEV. I modify the code to add a printout: diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 4bdfb659513..2cd4495bc03 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7965,6 +7965,8 @@ best_access_path(JOIN *join, table->best_range_rowid_filter_for_partial_join(start_key->key, rows, access_cost_factor); + trace_access_idx.add("ROWS_FOR_FILTER", rows); + trace_access_idx.add("REAL_ROWS_FOR_FILTER", tmp); if (filter) { tmp-= filter->get_adjusted_gain(rows) - filter->get_cmp_gain(rows); then, I'm trying this example: create table t1 (a int, b int); insert into t1 select seq, seq from seq_1_to_10000; create table t2 (a int, key(a)); insert into t2 select seq from seq_1_to_10000; insert into t2 select * from t2; insert into t2 select * from t2; analyze table t2 persistent for all; create table t3 ( pk int primary key, a int, b int, unique key(a), key(b) ); insert into t3 select seq, seq, seq from seq_1_to_100000; set optimizer_trace=1; explain select straight_join * from t1, t2, t3 where t2.a=t1.a and t3.a=t1.b and t3.b < 5000; +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10157 | Using where | | 1 | SIMPLE | t2 | ref | a | a | 5 | j1.t1.a | 4 | Using index | | 1 | SIMPLE | t3 | eq_ref|filter | a,b | a|b | 5|5 | j1.t1.b | 1 (5%) | Using where; Using rowid filter | +------+-------------+-------+---------------+---------------+------+---------+---------+--------+---------------------------------+ good so far. select * from information_schema.optimizer_trace; shows this (note the added printouts in caps): { "plan_prefix": ["t1", "t2"], "table": "t3", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "a", "ROWS_FOR_FILTER": 40628, "REAL_ROWS_FOR_FILTER": 10157, "rowid_filter_key": "b", "rows": 1, "cost": 9498, "chosen": true }, Let's run the query and check what really happens: ANALYZE FORMAT=JSON select straight_join * from t1, t2, t3 where t2.a=t1.a and t3.a=t1.b and t3.b < 5000; shows: ... "table": { "table_name": "t3", "access_type": "eq_ref", "key": "a", ... "rowid_filter": { ... "r_lookups": 10000, ... }, "r_loops": 40000, ... So, ROWS_FOR_FILTER=40K REAL_ROWS_FOR_FILTER=10K which of the numbers is a closer match here for the observed value of r_lookups= 10K ? BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

[Maria-developers] Unable to reset my mariadb root password.
by sanaboyina sarvani 01 Feb '23

01 Feb '23

Hello, I have just installed mariadb in my macos system. After installation it asks for the root password which I have not set yet. Tried to reset the root password but that did not work. So, how to know my mariadb root password? Is it possible to reset the password? Thank you. Regards

1 0

[Maria-developers] Optimizer call followup: negative gains from rowid filters
by Sergey Petrunia 31 Jan '23

31 Jan '23

Hello, Just taking notes from the optimizer call: Check out this branch: 10.4-mdev30218-print-negative-gain (git log -3 output below) It has: - 10.4-release contents - then mdev30218_bb-10.4-release.diff patch provided by Igor - then a debug patch by me. I run: ./mtr --mem --parallel=5 --force main/rowid_filter*test grep -rn filter_gain var/ I get this: negative gains from filters: var/4/log/mysqld.1.err:52:filter_gain:-0.0569276, query=select 1 var/4/log/mysqld.1.err:58:filter_gain:-0.0569276, query=explain extended select 1 var/4/log/mysqld.1.err:64:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3) var/4/log/mysqld.1.err:65:filter_gain:-0.250816, query=EXPLAIN EXTENDED var/4/log/mysqld.1.err:67:filter_gain:-10.207, query=explain var/4/log/mysqld.1.err:69:filter_gain:-10.207, query=SELECT * FROM t1 WHERE a > 0 AND b=0 var/4/log/mysqld.1.err:70:filter_gain:-1.17502, query=select count(*) from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:71:filter_gain:-1.17502, query=explain extended select count(*) from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:72:filter_gain:-1.17502, query=select * from t1 where a between 21 and 30 and b=2 var/4/log/mysqld.1.err:73:filter_gain:-2.18259, query=SELECT * FROM t1 var/4/log/mysqld.1.err:77:filter_gain:-2.18259, query=EXPLAIN EXTENDED SELECT * FROM t1 var/4/log/mysqld.1.err:81:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* var/4/log/mysqld.1.err:90:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* var/4/log/mysqld.1.err:99:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.* var/4/log/mysqld.1.err:108:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.* var/4/log/mysqld.1.err:117:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.* var/3/log/mysqld.1.err:51:filter_gain:-0.0569276, query=select 1 var/3/log/mysqld.1.err:57:filter_gain:-0.0569276, query=explain extended select 1 var/3/log/mysqld.1.err:63:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3) var/3/log/mysqld.1.err:64:filter_gain:-0.250816, query=EXPLAIN EXTENDED var/3/log/mysqld.1.err:66:filter_gain:-12.7213, query=explain var/3/log/mysqld.1.err:68:filter_gain:-12.7213, query=SELECT * FROM t1 WHERE a > 0 AND b=0 === git log -3 output === commit 82d5977b9fd6be5f5152d6831cd744fc739a2f24 (HEAD -> 10.4-mdev30218-print-negative-gain, origin/10.4-mdev30218-print-negative-gain, bb-10.4-release) Author: Sergei Petrunia <sergey(a)mariadb.com> Date: Tue Jan 31 20:11:17 2023 +0300 Debug patch: print filter_gain if it is less than 0. commit 47a40d90627dcf3c870cecd33c345c2e224986af Author: Sergei Petrunia <sergey(a)mariadb.com> Date: Tue Jan 31 20:07:02 2023 +0300 Apply mdev30218_bb-10.4-release.diff commit c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7 (origin/bb-10.4-release) Author: Oleksandr Byelkin <sanja(a)mariadb.com> Date: Mon Jan 30 11:49:42 2023 +0100 Fix number of rows passing in case of EQ_REF BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net

1 0

Re: [Maria-developers] dcd69cbd19a: MDEV-29447 MDEV-26285 Refactor spider_db_mbase_util::open_item_func
by Sergei Golubchik 30 Jan '23

30 Jan '23

Hi, Yuchen, Thanks! There are two kinds of comments below, you'll see which is which. I realize that this patch mainly just moves parts of spider code around. Some of my comments apply to the new code that didn't exist before and appeared only in this patch. It's what needs to be fixed (or discussed, if you disagree) before pushing. Other comments apply to the old spider code that this patch moved. If you'd like you can say "I'm just moving spider code around" and ignore all comments of that kind. /Sergei On Jan 22, Yuchen Pei wrote: > commit dcd69cbd19a > Author: Yuchen Pei <yuchen.pei(a)mariadb.com> > Date: Tue Jan 3 16:24:04 2023 +1100 > > MDEV-29447 MDEV-26285 Refactor spider_db_mbase_util::open_item_func > > Porting commit 3836098c29ef1b7ff9d5fbde99b690eab73a0df1 (MDEV-26285) Because 3836098c29ef will not be, apparently, pushed anywhere, you'd better put a self-containing comment here. May be use the comment of 3836098c29ef? Note that you shouldn't push your 10.4-11.0 versions of the patch directly, only push the 10.3 one (when ok) and let it propagate to 11.0 through merges. Your 10.4-11.0 patches are very useful, though, they will serve as guidelines for whoever will merge this change up. > to current versions 10.3+ to fix a problem (MDEV-29447) where field > items that are arguments of a func item may be used before created / > initialised. > > Signed-off-by: Yuchen Pei <yuchen.pei(a)mariadb.com> > > diff --git a/storage/spider/spd_db_mysql.cc b/storage/spider/spd_db_mysql.cc > index e942d1d9063..a886a4e6e99 100644 > --- a/storage/spider/spd_db_mysql.cc > +++ b/storage/spider/spd_db_mysql.cc > @@ -4035,6 +4042,170 @@ int spider_db_mbase_util::open_item_func( > uint alias_length, > bool use_fields, > spider_fields *fields > +) { > + DBUG_ENTER("spider_db_mbase_util::check_item_func"); incorrect function name in DBUG_ENTER > + > + int error = check_item_func(item_func, spider, alias, > + alias_length, use_fields, fields); > + if (error) > + DBUG_RETURN(error); > + if (!str) > + DBUG_RETURN(0); > + > + DBUG_RETURN(print_item_func(item_func, spider, str, alias, > + alias_length, use_fields, fields)); > +} > + > +namespace { > + bool item_func_is_timestampdiff( We generally use static in these cases. Why would an anonymous namespace be better? > + const char *func_name, > + int func_name_length > + ) { > + return func_name_length == 13 && > + !strncasecmp("timestampdiff", func_name, func_name_length); We build with rtti nowadays, so you can use dynamic_cast instead. and you won't need func_name and func_name_length. Ideally you won't need a check for timestampdiff at all > + } > + > + bool not_func_should_be_skipped( > + Item_func *item_func > + ){ > + DBUG_ENTER("not_func_should_be_skipped"); add DBUG_ASSERT(item_func->functype() == Item_func::NOT_FUNC); > + Item **item_list = item_func->arguments(); > + > + if (item_list[0]->type() == Item::COND_ITEM) > + { > + DBUG_PRINT("info",("spider item_list[0] is COND_ITEM")); > + Item_cond *item_cond = (Item_cond *) item_list[0]; > + if (item_cond->functype() == Item_func::COND_AND_FUNC) > + { > + DBUG_PRINT("info",("spider item_cond is COND_AND_FUNC")); > + List_iterator_fast<Item> lif(*(item_cond->argument_list())); > + bool has_expr_cache_item = FALSE; > + bool has_isnotnull_func = FALSE; > + bool has_other_item = FALSE; > + while(Item *item = lif++) > + { > + if ( > + item->type() == Item::EXPR_CACHE_ITEM > + ) { > + DBUG_PRINT("info",("spider EXPR_CACHE_ITEM")); > + has_expr_cache_item = TRUE; > + } else > + if ( > + item->type() == Item::FUNC_ITEM && > + ((Item_func *) item)->functype() == Item_func::ISNOTNULL_FUNC > + ) { > + DBUG_PRINT("info",("spider ISNOTNULL_FUNC")); > + has_isnotnull_func = TRUE; > + } else { > + DBUG_PRINT("info",("spider has other item")); > + DBUG_PRINT("info",("spider COND type=%d", item->type())); > + has_other_item = TRUE; > + } > + } > + if (has_expr_cache_item && has_isnotnull_func && !has_other_item) So, "not func should be skipped" if it's NOT (expr1 AND expr2 AND ...) and all expr's are either IS NOT NULL or a cached subquery. Why? IS NULL, IS NOT FALSE, non-cached subqueries, everything else is fine, IS NOT NULL if it is followed by `AND 2>1` is fine, OR is fine. Doesn't make much sense to me. > + { > + DBUG_PRINT("info",("spider NOT EXISTS skip")); > + DBUG_RETURN(TRUE); > + } > + } > + } > + DBUG_RETURN(FALSE); > + } > +} > + > +/** > + Check if the given item_func and its arguments can be pushed down to > + a data node. This function is recursive because we need to also check > + the arguments of the item_func. > + > + @return 0: success, otherwise: error > + */ > +int spider_db_mbase_util::check_item_func( > + Item_func *item_func, > + ha_spider *spider, > + const char *alias, > + uint alias_length, > + bool use_fields, > + spider_fields *fields > +) { > + DBUG_ENTER("spider_db_mbase_util::check_item_func"); > + > + Item_func::Functype func_type = item_func->functype(); > + DBUG_PRINT("info",("spider functype = %d", func_type)); > + > + const char *func_name = (char*) item_func->func_name(); > + int func_name_length = strlen(func_name); > + DBUG_PRINT("info",("spider func_name = %s", func_name)); > + > + /* The blacklist of the functions that cannot be pushed down */ > + if ( > + func_type == Item_func::TRIG_COND_FUNC || > + func_type == Item_func::CASE_SEARCHED_FUNC || > + func_type == Item_func::CASE_SIMPLE_FUNC Does CASE need to be skipped at all? > + ) { > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::NOT_FUNC) > + { > + /* Why the following check is necessary? */ indeed > + if(not_func_should_be_skipped(item_func)) > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::UDF_FUNC) old code used switch here. I think it looks more natural than a sequence of if's. A compiler might optimize them internally into a switch, but I'd better do it explicitly. > + { > + int use_pushdown_udf = spider_param_use_pushdown_udf( > + spider->trx->thd, spider->share->use_pushdown_udf); > + if (!use_pushdown_udf) { > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + } > + > + if (func_type == Item_func::FT_FUNC) { > + if (spider_db_check_ft_idx(item_func, spider) == MAX_KEY) > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + > + if (func_type == Item_func::UNKNOWN_FUNC) > + { > + if (item_func_is_timestampdiff(func_name, func_name_length)) { it doesn't seem that it needs to be skipped at all > + DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > + } > + } > + /* End of the blacklist */ > + > + /* Check the arguments recursively */ > + if (uint item_count = item_func->argument_count()) > + { > + Item **item_list = item_func->arguments(); > + for (uint roop_count = 0; roop_count < item_count; roop_count++) > + { > + Item *item = item_list[roop_count]; > + if (int error_num = spider_db_print_item_type(item, NULL, spider, NULL, > + alias, alias_length, dbton_id, use_fields, fields)) it'd be more logical to invoke check_item_func() here instead of spider_db_print_item_type with str==NULL > + DBUG_RETURN(error_num); > + } > + } > + > + DBUG_RETURN(0); > +} > + > +/** > + The function print the string corresponding to the given item_func to str. > + The function is assumed to be called only when the check by the function > + check_item_func() has passed. > + > + @return 0: success, otherwise: error > + */ > +int spider_db_mbase_util::print_item_func( > + Item_func *item_func, > + ha_spider *spider, > + spider_string *str, > + const char *alias, > + uint alias_length, > + bool use_fields, > + spider_fields *fields > ) { > int error_num; > Item *item, **item_list = item_func->arguments(); > @@ -4049,13 +4220,14 @@ int spider_db_mbase_util::open_item_func( > last_str_length = SPIDER_SQL_NULL_CHAR_LEN; > int use_pushdown_udf; > bool merge_func = FALSE; > - DBUG_ENTER("spider_db_mbase_util::open_item_func"); > - if (str) > - { > + DBUG_ENTER("spider_db_mbase_util::print_item_func"); > + DBUG_ASSERT(!check_item_func(item_func, spider, alias, alias_length, > + use_fields, fields) && str); Make it two asserts please. One for !check_item_func(...) and the other DBUG_ASSERT(str); Generally, always try to split assert into as many separate asserts as possible, don't do assert(A && B); So that when it fails it'd provide as much information as possible about what exactly went wrong. > + > if (str->reserve(SPIDER_SQL_OPEN_PAREN_LEN)) > DBUG_RETURN(HA_ERR_OUT_OF_MEM); > str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - } > + > DBUG_PRINT("info",("spider functype = %d", item_func->functype())); > switch (item_func->functype()) > { > @@ -4374,94 +4523,12 @@ int spider_db_mbase_util::open_item_func( > { > if (!strncasecmp("utc_timestamp", func_name, func_name_length)) pretty much all that code can use dynamic_cast nowadays > { > - if (str) > str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); > DBUG_RETURN(spider_db_open_item_string(item_func, NULL, spider, str, > alias, alias_length, dbton_id, use_fields, fields)); > } else if (!strncasecmp("timestampdiff", func_name, func_name_length)) > { > -#ifdef ITEM_FUNC_TIMESTAMPDIFF_ARE_PUBLIC but it _is_ public, so you can push down Item_func_timestamp_diff all right > - Item_func_timestamp_diff *item_func_timestamp_diff = > - (Item_func_timestamp_diff *) item_func; > - if (str) > - { > - const char *interval_str; > - uint interval_len; > - switch (item_func_timestamp_diff->int_type) > - { > - case INTERVAL_YEAR: > - interval_str = SPIDER_SQL_YEAR_STR; > - interval_len = SPIDER_SQL_YEAR_LEN; > - break; > - case INTERVAL_QUARTER: > - interval_str = SPIDER_SQL_QUARTER_STR; > - interval_len = SPIDER_SQL_QUARTER_LEN; > - break; > - case INTERVAL_MONTH: > - interval_str = SPIDER_SQL_MONTH_STR; > - interval_len = SPIDER_SQL_MONTH_LEN; > - break; > - case INTERVAL_WEEK: > - interval_str = SPIDER_SQL_WEEK_STR; > - interval_len = SPIDER_SQL_WEEK_LEN; > - break; > - case INTERVAL_DAY: > - interval_str = SPIDER_SQL_DAY_STR; > - interval_len = SPIDER_SQL_DAY_LEN; > - break; > - case INTERVAL_HOUR: > - interval_str = SPIDER_SQL_HOUR_STR; > - interval_len = SPIDER_SQL_HOUR_LEN; > - break; > - case INTERVAL_MINUTE: > - interval_str = SPIDER_SQL_MINUTE_STR; > - interval_len = SPIDER_SQL_MINUTE_LEN; > - break; > - case INTERVAL_SECOND: > - interval_str = SPIDER_SQL_SECOND_STR; > - interval_len = SPIDER_SQL_SECOND_LEN; > - break; > - case INTERVAL_MICROSECOND: > - interval_str = SPIDER_SQL_MICROSECOND_STR; > - interval_len = SPIDER_SQL_MICROSECOND_LEN; > - break; > - default: > - interval_str = ""; > - interval_len = 0; > - break; > - } > - str->length(str->length() - SPIDER_SQL_OPEN_PAREN_LEN); > - if (str->reserve(func_name_length + SPIDER_SQL_OPEN_PAREN_LEN + > - interval_len + SPIDER_SQL_COMMA_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(func_name, func_name_length); > - str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - str->q_append(interval_str, interval_len); > - str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); > - } > - if ((error_num = spider_db_print_item_type(item_list[0], NULL, spider, > - str, alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_COMMA_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_COMMA_STR, SPIDER_SQL_COMMA_LEN); > - } > - if ((error_num = spider_db_print_item_type(item_list[1], NULL, spider, > - str, alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_CLOSE_PAREN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_CLOSE_PAREN_STR, > - SPIDER_SQL_CLOSE_PAREN_LEN); > - } > - DBUG_RETURN(0); > -#else > DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > -#endif > } > } else if (func_name_length == 14) > { > @@ -5038,84 +4996,17 @@ int spider_db_mbase_util::open_item_func( > func_name = (char*)item_func->func_name(); > func_name_length = strlen(func_name); > } > - } > break; > case Item_func::CASE_SEARCHED_FUNC: > case Item_func::CASE_SIMPLE_FUNC: > -#ifdef ITEM_FUNC_CASE_PARAMS_ARE_PUBLIC Item_func_case looks somewhat different today, but I suspect it can be pushed too > - Item_func_case *item_func_case = (Item_func_case *) item_func; > - if (str) > - { > - if (str->reserve(SPIDER_SQL_CASE_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_CASE_STR, SPIDER_SQL_CASE_LEN); > - } > - if (item_func_case->first_expr_num != -1) > - { > - if ((error_num = spider_db_print_item_type( > - item_list[item_func_case->first_expr_num], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - for (roop_count = 0; roop_count < item_func_case->ncases; > - roop_count += 2) > - { > - if (str) > - { > - if (str->reserve(SPIDER_SQL_WHEN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_WHEN_STR, SPIDER_SQL_WHEN_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[roop_count], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - if (str) > - { > - if (str->reserve(SPIDER_SQL_THEN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_THEN_STR, SPIDER_SQL_THEN_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[roop_count + 1], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - if (item_func_case->else_expr_num != -1) > - { > - if (str) > - { > - if (str->reserve(SPIDER_SQL_ELSE_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_ELSE_STR, SPIDER_SQL_ELSE_LEN); > - } > - if ((error_num = spider_db_print_item_type( > - item_list[item_func_case->else_expr_num], NULL, spider, str, > - alias, alias_length, dbton_id, use_fields, fields))) > - DBUG_RETURN(error_num); > - } > - if (str) > - { > - if (str->reserve(SPIDER_SQL_END_LEN + SPIDER_SQL_CLOSE_PAREN_LEN)) > - DBUG_RETURN(HA_ERR_OUT_OF_MEM); > - str->q_append(SPIDER_SQL_END_STR, SPIDER_SQL_END_LEN); > - str->q_append(SPIDER_SQL_CLOSE_PAREN_STR, > - SPIDER_SQL_CLOSE_PAREN_LEN); > - } > - DBUG_RETURN(0); > -#else > DBUG_RETURN(ER_SPIDER_COND_SKIP_NUM); > -#endif > case Item_func::JSON_EXTRACT_FUNC: > func_name = (char*) item_func->func_name(); > func_name_length = strlen(func_name); > - if (str) > - { > if (str->reserve(func_name_length + SPIDER_SQL_OPEN_PAREN_LEN)) > DBUG_RETURN(HA_ERR_OUT_OF_MEM); > str->q_append(func_name, func_name_length); > str->q_append(SPIDER_SQL_OPEN_PAREN_STR, SPIDER_SQL_OPEN_PAREN_LEN); > - } > func_name = SPIDER_SQL_COMMA_STR; > func_name_length = SPIDER_SQL_COMMA_LEN; > separator_str = SPIDER_SQL_COMMA_STR; Regards, Sergei VP of MariaDB Server Engineering and security(a)mariadb.org

2 1