MDEV-28152 Features for sequencesbb-11.0-mdev-28152

- Add `as <int_type>` to sequence creation options
  - int_type can be signed or unsigned integer types, including
    tinyint, smallint, mediumint, int and bigint
  - Limitation: when alter sequence as <new_int_type>, cannot have any
    other alter options in the same statement
  - Limitation: increment remains signed longlong, and the hidden
    constraint (cache_size x abs(increment) < longlong_max) stays for
    unsigned types. This means for bigint unsigned, neither
    abs(increment) nor (cache_size x abs(increment)) can be between
    longlong_max and ulonglong_max
- Truncating maxvalue and minvalue from user input to the nearest max
  or min value of the type, plus or minus 1. When the truncation
  happens, a warning is emitted
- Information schema table for sequences

1 files changed, 97 insertions, 0 deletions

diff --git a/mysql-test/suite/sql_sequence/setval.test b/mysql-test/suite/sql_sequence/setval.test
index 1993bdbe077..b45533f63d1 100644
--- a/mysql-test/suite/sql_sequence/setval.test
+++ b/mysql-test/suite/sql_sequence/setval.test
@@ -154,3 +154,100 @@ DROP SEQUENCE s;
 
 
 --echo # End of 10.3 tests
+
+--echo #
+--echo # MDEV-28152 Features for sequence
+--echo #
+
+# out of bounds for the type: returns NULL
+create sequence s;
+select setval(s, 12345678901234567890);
+select nextval(s);
+drop sequence s;
+
+create sequence s increment -1;
+select setval(s, 12345678901234567890);
+select nextval(s);
+drop sequence s;
+
+create sequence s as smallint;
+select setval(s, 55555);
+select setval(s, -55555);
+select nextval(s);
+drop sequence s;
+
+create sequence s as smallint increment -1;
+select setval(s, 55555);
+select setval(s, -55555);
+select nextval(s);
+drop sequence s;
+
+create sequence s as bigint unsigned;
+select setval(s, -123);
+select nextval(s);
+drop sequence s;
+
+create sequence s as bigint unsigned increment -1;
+select setval(s, -123);
+select nextval(s);
+drop sequence s;
+
+# large setval values for bigint unsigned
+create sequence s as bigint unsigned;
+select setval(s, -123);
+select setval(s, 0);
+select nextval(s);
+select setval(s, 12345678901234567890);
+select nextval(s);
+select setval(s, 12345678901234567890);
+select nextval(s);
+select setval(s, 18446744073709551614, 0);
+select nextval(s);
+--error ER_SEQUENCE_RUN_OUT
+select nextval(s);
+--error ER_SEQUENCE_RUN_OUT
+select setval(s, 12345678901234567890, 0, 1);
+alter sequence s cycle;
+select setval(s, 12345678901234567890, 0, 1);
+select nextval(s);
+drop sequence s;
+
+# extreme setval values for bigint unsigned
+create sequence s as bigint unsigned;
+--error ER_PARSE_ERROR
+select setval(s, 18446744073709551616);
+select setval(s, 18446744073709551615);
+--error ER_SEQUENCE_RUN_OUT
+select nextval(s);
+drop sequence s;
+
+create sequence s as bigint unsigned;
+select setval(s, 18446744073709551615, 0);
+--error ER_SEQUENCE_RUN_OUT
+select nextval(s);
+drop sequence s;
+
+create sequence s as bigint unsigned cycle;
+select setval(s, 18446744073709551615);
+select nextval(s);
+drop sequence s;
+
+create sequence s as bigint unsigned cycle;
+select setval(s, 18446744073709551615, 0);
+select nextval(s);
+drop sequence s;
+
+# a small range for bigint unsigned
+create sequence s as bigint unsigned minvalue 123 maxvalue 456;
+select setval(s, 100);
+select nextval(s);
+select setval(s, 500);
+--error ER_SEQUENCE_RUN_OUT
+select nextval(s);
+drop sequence s;
+
+create sequence s as smallint;
+select setval(s, 32767);
+--error ER_SEQUENCE_RUN_OUT
+select nextval(s);
+drop sequence s;