maria-developers team mailing list archive

Thread
Date
Re: MDEV-28152: Features for sequence

To: Yuchen Pei <yuchen.pei@xxxxxxxxxxx>
From: Oleksandr Byelkin <sanja@xxxxxxxxxxx>
Date: Fri, 10 Mar 2023 11:59:03 +0100
Cc: maria-developers <maria-developers@xxxxxxxxxxxxxxxxxxx>
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-be-set-to-zero

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-SEQUENCE.html

       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:
Follow ups

Re: MDEV-28152: Features for sequence
From: Michael Widenius, 2023-03-20
Re: MDEV-28152: Features for sequence
From: Yuchen Pei, 2023-03-17
Re: MDEV-28152: Features for sequence
From: Yuchen Pei, 2023-03-15