Merge 10.3 into 10.4

6 files changed, 189 insertions, 0 deletions

diff --git a/mysql-test/suite/compat/oracle/r/parser.result b/mysql-test/suite/compat/oracle/r/parser.result
index 1c60c1434a3..0b573027c6d 100644
--- a/mysql-test/suite/compat/oracle/r/parser.result
+++ b/mysql-test/suite/compat/oracle/r/parser.result
@@ -607,5 +607,39 @@ ERROR HY000: Unknown system variable 'password'
 SELECT @@GLOBAL.role;
 ERROR HY000: Unknown system variable 'role'
 #
+# MDEV-22822 sql_mode="oracle" cannot declare without variable errors
+#
+# It's OK to have no declarations between DECLARE and BEGIN.
+#
+BEGIN
+DECLARE
+BEGIN
+NULL;
+END;
+EXCEPTION
+WHEN OTHERS THEN
+NULL;
+END;
+//
+DECLARE
+BEGIN
+NULL;
+EXCEPTION
+WHEN OTHERS THEN
+NULL;
+END;
+//
+BEGIN
+<<lab>>
+DECLARE
+BEGIN
+NULL;
+END;
+EXCEPTION
+WHEN OTHERS THEN
+NULL;
+END;
+//
+#
 # End of 10.3 tests
 #
diff --git a/mysql-test/suite/compat/oracle/t/parser.test b/mysql-test/suite/compat/oracle/t/parser.test
index 067fd9beb48..4d558c5d153 100644
--- a/mysql-test/suite/compat/oracle/t/parser.test
+++ b/mysql-test/suite/compat/oracle/t/parser.test
@@ -410,5 +410,52 @@ SELECT @@GLOBAL.role;
 
 
 --echo #
+--echo # MDEV-22822 sql_mode="oracle" cannot declare without variable errors
+--echo #
+--echo # It's OK to have no declarations between DECLARE and BEGIN.
+--echo #
+
+DELIMITER //;
+BEGIN
+  DECLARE
+  BEGIN
+    NULL;
+  END;
+EXCEPTION
+WHEN OTHERS THEN
+    NULL;
+END;
+//
+DELIMITER ;//
+
+
+DELIMITER //;
+DECLARE
+BEGIN
+  NULL;
+EXCEPTION
+WHEN OTHERS THEN
+  NULL;
+END;
+//
+DELIMITER ;//
+
+
+DELIMITER //;
+BEGIN
+<<lab>>
+  DECLARE
+  BEGIN
+    NULL;
+  END;
+EXCEPTION
+WHEN OTHERS THEN
+    NULL;
+END;
+//
+DELIMITER ;//
+
+
+--echo #
 --echo # End of 10.3 tests
 --echo #
diff --git a/mysql-test/suite/sql_sequence/alter.result b/mysql-test/suite/sql_sequence/alter.result
index 3cf085bc948..612e2201d26 100644
--- a/mysql-test/suite/sql_sequence/alter.result
+++ b/mysql-test/suite/sql_sequence/alter.result
@@ -238,3 +238,14 @@ 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;
+CREATE SEQUENCE s;
+ALTER TABLE s ORDER BY cache_size;
+ERROR HY000: Sequence 'test.s' table structure is invalid (ORDER BY)
+SELECT NEXTVAL(s);
+NEXTVAL(s)
+1
+DROP SEQUENCE s;
diff --git a/mysql-test/suite/sql_sequence/alter.test b/mysql-test/suite/sql_sequence/alter.test
index fd1809ccd2f..53f71018337 100644
--- a/mysql-test/suite/sql_sequence/alter.test
+++ b/mysql-test/suite/sql_sequence/alter.test
@@ -139,3 +139,24 @@ 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;
+
+#
+# MDEV-19320 Sequence gets corrupted and produces ER_KEY_NOT_FOUND (Can't
+# find record) after ALTER .. ORDER BY
+#
+
+CREATE SEQUENCE s;
+--error ER_SEQUENCE_INVALID_TABLE_STRUCTURE
+ALTER TABLE s ORDER BY cache_size;
+SELECT NEXTVAL(s);
+DROP SEQUENCE s;
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;
 
 #