Don't allow illegal create options for SEQUENCE

MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S ||
           (0xFUL & mode) == LOCK_X' failed in lock_rec_lock

5 files changed, 97 insertions, 1 deletions

diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result
index 3cf085bc948..2ac8ac07994 100644
--- a/mysql-test/suite/sql_sequence/alter.result
+++ b/mysql-test/suite/sql_sequence/alter.result
@@ -238,3 +238,7 @@ select next value for t1;
 next value for t1
 90
 drop sequence t1;
+CREATE SEQUENCE t1 engine=innodb;
+ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value);
+ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints)
+DROP SEQUENCE t1;
diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test
index fd1809ccd2f..7132c45ef07 100644
--- a/mysql-test/suite/sql_sequence/alter.test
+++ b/mysql-test/suite/sql_sequence/alter.test
@@ -139,3 +139,13 @@ select next value for t1;
 alter sequence t1 restart with 90;
 select next value for t1;
 drop sequence t1;
+
+#
+# MDEV-19977 Assertion `(0xFUL & mode) == LOCK_S || (0xFUL & mode) == LOCK_X'
+# failed in lock_rec_lock
+#
+
+CREATE SEQUENCE t1 engine=innodb;
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+ALTER IGNORE TABLE t1 ADD CHECK (start_value < minimum_value);
+DROP SEQUENCE t1;
diff --git a/mysql-test/suite/sql_sequence/create.result b/mysql-test/suite/sql_sequence/create.result
index 55d45a75abf..14464c60e99 100644
--- a/mysql-test/suite/sql_sequence/create.result
+++ b/mysql-test/suite/sql_sequence/create.result
@@ -375,6 +375,40 @@ CREATE OR REPLACE TABLE t1 (
 key key1 (next_not_cached_value)
 ) sequence=1;
 ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any keys)
+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,
+`increment` bigint(21) NOT NULL,
+`cache_size` bigint(21) unsigned NOT NULL,
+`cycle_option` tinyint(1) unsigned NOT NULL,
+`cycle_count` bigint(21) NOT NULL,
+CHECK (start_value < minimum_value)
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (Sequence tables cannot have any constraints)
+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 CHECK (start_value < minimum_value),
+`increment` bigint(21) NOT NULL,
+`cache_size` bigint(21) unsigned NOT NULL,
+`cycle_option` tinyint(1) unsigned NOT NULL,
+`cycle_count` bigint(21) NOT NULL
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (start_value)
+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,
+`increment` bigint(21) NOT NULL,
+`cache_size` bigint(21) unsigned NOT NULL,
+`cycle_option` tinyint(1) unsigned NOT NULL,
+`cycle_count` bigint(21) generated always as (1) virtual
+) sequence=1;
+ERROR HY000: Sequence 'test.t1' table structure is invalid (cycle_count)
 drop sequence if exists t1;
 Warnings:
 Note	4091	Unknown SEQUENCE: 'test.t1'
diff --git a/mysql-test/suite/sql_sequence/create.test b/mysql-test/suite/sql_sequence/create.test
index 1bc62117526..2c41fb3658b 100644
--- a/mysql-test/suite/sql_sequence/create.test
+++ b/mysql-test/suite/sql_sequence/create.test
@@ -270,6 +270,48 @@ CREATE OR REPLACE TABLE t1 (
   key key1 (next_not_cached_value)
 ) sequence=1;
 
+# Check constraint
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+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,
+  `increment` bigint(21) NOT NULL,
+  `cache_size` bigint(21) unsigned NOT NULL,
+  `cycle_option` tinyint(1) unsigned NOT NULL,
+  `cycle_count` bigint(21) NOT NULL,
+  CHECK (start_value < minimum_value)
+) sequence=1;
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+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 CHECK (start_value < minimum_value),
+  `increment` bigint(21) NOT NULL,
+  `cache_size` bigint(21) unsigned NOT NULL,
+  `cycle_option` tinyint(1) unsigned NOT NULL,
+  `cycle_count` bigint(21) NOT NULL
+) sequence=1;
+
+
+# Virtual field
+
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+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,
+  `increment` bigint(21) NOT NULL,
+  `cache_size` bigint(21) unsigned NOT NULL,
+  `cycle_option` tinyint(1) unsigned NOT NULL,
+  `cycle_count` bigint(21) generated always as (1) virtual
+) sequence=1;
+
 drop sequence if exists t1;
 
 #
diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc
index 96c1cd19433..68d9efb3093 100644
--- a/sql/sql_sequence.cc
+++ b/sql/sql_sequence.cc
@@ -203,6 +203,11 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields)
     reason= "Sequence tables cannot have any keys";
     goto err;
   }
+  if (lex->alter_info.check_constraint_list.elements > 0)
+  {
+    reason= "Sequence tables cannot have any constraints";
+    goto err;
+  }
 
   for (field_no= 0; (field= it++); field_no++)
   {
@@ -210,7 +215,8 @@ bool check_sequence_fields(LEX *lex, List<Create_field> *fields)
     if (my_strcasecmp(system_charset_info, field_def->field_name,
                       field->field_name.str) ||
         field->flags != field_def->flags ||
-        field->type_handler() != field_def->type_handler)
+        field->type_handler() != field_def->type_handler ||
+        field->check_constraint || field->vcol_info)
     {
       reason= field->field_name.str;
       goto err;