diff options
Diffstat (limited to 'mysql-test/suite/sql_sequence/create.result')
| -rw-r--r-- | mysql-test/suite/sql_sequence/create.result | 631 |
1 files changed, 624 insertions, 7 deletions
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result index 9326d7016c9..5b888993368 100644 --- a/mysql-test/suite/sql_sequence/create.result +++ b/mysql-test/suite/sql_sequence/create.result @@ -182,15 +182,13 @@ create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NO MINVALUE' at line 1 create or replace sequence t1 start with 10 min_value=1 NO MINVALUE; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NO MINVALUE' at line 1 -create sequence t1 start with 10 maxvalue=9223372036854775807; -ERROR HY000: Sequence 'test.t1' has out of range value for options -create sequence t1 start with 10 minvalue=-9223372036854775808; -ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '9223372036854775808' at line 1 create sequence t1 RESTART WITH 10; ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RESTART' at line 1 create or replace sequence t1 start with 10 NO MINVALUE minvalue=1; -drop sequence t1; -create sequence t1; +ERROR HY000: Option 'MINVALUE' used twice in statement +create sequence s increment by 3234567890123456789; +ERROR HY000: Sequence 'test.s' has out of range value for options +create or replace sequence t1; show fields from t1; Field Type Null Key Default Extra next_not_cached_value bigint(21) NO NULL @@ -317,7 +315,7 @@ CREATE OR REPLACE TABLE t1 ( `cycle_option` tinyint(1) unsigned NOT NULL, `cycle_count` bigint(21) NOT NULL ) sequence=1; -ERROR HY000: Sequence 'test.t1' table structure is invalid (next_not_cached_value) +ERROR HY000: Sequence 'test.t1' table structure is invalid (minimum_value) CREATE OR REPLACE TABLE t1 ( `next_not_cached_value` bigint(21) NOT NULL, `minimum_value` bigint(21) NOT NULL, @@ -716,3 +714,622 @@ CREATE TRIGGER s1 BEFORE UPDATE ON seq1 FOR EACH ROW SET @a= 5; ERROR HY000: Trigger's 'seq1' is a view, temporary table or sequence DROP SEQUENCE seq1; # End of 10.4 test +###### +# MDEV-28152 Features for sequence +###### +# ----- +# Truncating out-of-bound numbers for minvalue and maxvalue +# ----- +create or replace sequence t1 minvalue -999999999999999999999; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue -9223372036854775808; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue -9223372036854775807; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775807 minvalue -9223372036854775807 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue 9223372036854775805; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 9223372036854775805 minvalue 9223372036854775805 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 minvalue 9223372036854775806; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9223372036854775807; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9223372036854775808; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 minvalue 9999999999999999999999; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -999999999999999999999 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775808 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775807 increment by -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 maxvalue -9223372036854775806 increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -9223372036854775806 minvalue -9223372036854775807 maxvalue -9223372036854775806 increment by -1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775806; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775807; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9223372036854775808; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 maxvalue 9999999999999999999999; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +# ----- +# Create with value types +# ----- +create or replace sequence t1 as tinyint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint start with 1 minvalue 1 maxvalue 126 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` tinyint(5) NOT NULL, + `minimum_value` tinyint(5) NOT NULL, + `maximum_value` tinyint(5) NOT NULL, + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as smallint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with 1 minvalue 1 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as mediumint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint start with 1 minvalue 1 maxvalue 8388606 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) NOT NULL, + `minimum_value` mediumint(10) NOT NULL, + `maximum_value` mediumint(10) NOT NULL, + `start_value` mediumint(10) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as int; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int start with 1 minvalue 1 maxvalue 2147483646 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` tinyint(5) unsigned NOT NULL, + `minimum_value` tinyint(5) unsigned NOT NULL, + `maximum_value` tinyint(5) unsigned NOT NULL, + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as smallint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as mediumint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as int unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int unsigned start with 1 minvalue 1 maxvalue 4294967294 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) unsigned NOT NULL, + `minimum_value` int(12) unsigned NOT NULL, + `maximum_value` int(12) unsigned NOT NULL, + `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as tinyint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint start with -1 minvalue -127 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` tinyint(5) NOT NULL, + `minimum_value` tinyint(5) NOT NULL, + `maximum_value` tinyint(5) NOT NULL, + `start_value` tinyint(5) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as smallint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with -1 minvalue -32767 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as mediumint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint start with -1 minvalue -8388607 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) NOT NULL, + `minimum_value` mediumint(10) NOT NULL, + `maximum_value` mediumint(10) NOT NULL, + `start_value` mediumint(10) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as int; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int start with -1 minvalue -2147483647 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) NOT NULL, + `minimum_value` int(12) NOT NULL, + `maximum_value` int(12) NOT NULL, + `start_value` int(12) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as bigint; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 254 minvalue 1 maxvalue 254 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` tinyint(5) unsigned NOT NULL, + `minimum_value` tinyint(5) unsigned NOT NULL, + `maximum_value` tinyint(5) unsigned NOT NULL, + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as smallint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 65534 minvalue 1 maxvalue 65534 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as mediumint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 16777214 minvalue 1 maxvalue 16777214 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as int unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as int unsigned start with 4294967294 minvalue 1 maxvalue 4294967294 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` int(12) unsigned NOT NULL, + `minimum_value` int(12) unsigned NOT NULL, + `maximum_value` int(12) unsigned NOT NULL, + `start_value` int(12) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 increment -1 as bigint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 18446744073709551614 minvalue 1 maxvalue 18446744073709551614 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as tinyint zerofill; +ERROR HY000: Incorrect value 'ZEROFILL' for option 'AS' +create or replace sequence t1 as bigint unsigned start with 12345678901234567890; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 12345678901234567890 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint unsigned increment 12345678901234567; +ERROR HY000: Sequence 'test.t1' has out of range value for options +# ----- +# value types + truncating +# ----- +create or replace sequence t1 minvalue -23 maxvalue 9999 as tinyint unsigned; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` tinyint(5) unsigned NOT NULL, + `minimum_value` tinyint(5) unsigned NOT NULL, + `maximum_value` tinyint(5) unsigned NOT NULL, + `start_value` tinyint(5) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 minvalue -32768 maxvalue 32767 as smallint; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint start with -32767 minvalue -32767 maxvalue 32766 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) NOT NULL, + `minimum_value` smallint(7) NOT NULL, + `maximum_value` smallint(7) NOT NULL, + `start_value` smallint(7) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 minvalue 0 maxvalue 65535 as smallint unsigned; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as smallint unsigned start with 1 minvalue 1 maxvalue 65534 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` smallint(7) unsigned NOT NULL, + `minimum_value` smallint(7) unsigned NOT NULL, + `maximum_value` smallint(7) unsigned NOT NULL, + `start_value` smallint(7) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 minvalue -12345678901234 as mediumint unsigned maxvalue 12345678901234; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as mediumint unsigned start with 1 minvalue 1 maxvalue 16777214 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` mediumint(10) unsigned NOT NULL, + `minimum_value` mediumint(10) unsigned NOT NULL, + `maximum_value` mediumint(10) unsigned NOT NULL, + `start_value` mediumint(10) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint unsigned minvalue -12345678901234 maxvalue 12345678901234; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MINVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 12345678901234 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +# ----- +# indistinguishable values during parsing if we did not pass back Longlong_hybrid from the parser. +# ----- +create or replace sequence t1 as bigint maxvalue -1 increment by -1; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with -1 minvalue -9223372036854775807 maxvalue -1 increment by -1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint maxvalue 18446744073709551615; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) NOT NULL, + `minimum_value` bigint(21) NOT NULL, + `maximum_value` bigint(21) NOT NULL, + `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +create or replace sequence t1 as bigint unsigned maxvalue -1; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 as bigint unsigned maxvalue 18446744073709551615; +Warnings: +Note 1292 Truncated incorrect INTEGER value: 'MAXVALUE' +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `next_not_cached_value` bigint(21) unsigned NOT NULL, + `minimum_value` bigint(21) unsigned NOT NULL, + `maximum_value` bigint(21) unsigned NOT NULL, + `start_value` bigint(21) unsigned NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used', + `increment` bigint(21) NOT NULL COMMENT 'increment value', + `cache_size` bigint(21) unsigned NOT NULL, + `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed', + `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done' +) ENGINE=MyISAM SEQUENCE=1 +# ----- +# value types + out of range start +# ----- +create or replace sequence t1 start with -123456789012345678901 as tinyint unsigned; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123456789012345678901 as tinyint unsigned' at line 1 +create or replace sequence t1 start with -1 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 0 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as bigint unsigned start with 1 minvalue 1 maxvalue 18446744073709551614 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 start with 1 as tinyint unsigned; +show create sequence t1; +Table Create Table +t1 CREATE SEQUENCE `t1` as tinyint unsigned start with 1 minvalue 1 maxvalue 254 increment by 1 cache 1000 nocycle ENGINE=MyISAM +create or replace sequence t1 start with 254 as tinyint unsigned; +create or replace sequence t1 start with 255 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 256 as tinyint unsigned; +ERROR HY000: Sequence 'test.t1' has out of range value for options +create or replace sequence t1 start with 123456789012345678901 as tinyint unsigned; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '123456789012345678901 as tinyint unsigned' at line 1 +drop sequence t1; +# ----- +# information_schema.sequences +# ----- +create sequence s1 as tinyint unsigned increment by 23; +create sequence s2 start with 42 minvalue -9223372036854775807; +create sequence s3 as bigint unsigned start with 12345678901234567890 cycle; +create sequence s4 as tinyint; +create sequence s5 as smallint; +create sequence s6 as mediumint; +create sequence s7 as int; +create sequence s8 as bigint; +create sequence s9 as tinyint unsigned; +create sequence s10 as smallint unsigned; +create sequence s11 as mediumint unsigned; +create sequence s12 as int unsigned; +create sequence s13 as bigint unsigned; +select * from information_schema.sequences order by sequence_name; +SEQUENCE_CATALOG SEQUENCE_SCHEMA SEQUENCE_NAME DATA_TYPE NUMERIC_PRECISION NUMERIC_PRECISION_RADIX NUMERIC_SCALE START_VALUE MINIMUM_VALUE MAXIMUM_VALUE INCREMENT CYCLE_OPTION +def test s1 tinyint unsigned 8 2 0 1 1 254 23 0 +def test s10 smallint unsigned 16 2 0 1 1 65534 1 0 +def test s11 mediumint unsigned 24 2 0 1 1 16777214 1 0 +def test s12 int unsigned 32 2 0 1 1 4294967294 1 0 +def test s13 bigint unsigned 64 2 0 1 1 18446744073709551614 1 0 +def test s2 bigint 64 2 0 42 -9223372036854775807 9223372036854775806 1 0 +def test s3 bigint unsigned 64 2 0 12345678901234567890 1 18446744073709551614 1 1 +def test s4 tinyint 8 2 0 1 1 126 1 0 +def test s5 smallint 16 2 0 1 1 32766 1 0 +def test s6 mediumint 24 2 0 1 1 8388606 1 0 +def test s7 int 32 2 0 1 1 2147483646 1 0 +def test s8 bigint 64 2 0 1 1 9223372036854775806 1 0 +def test s9 tinyint unsigned 8 2 0 1 1 254 1 0 +drop sequence s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13; |