MDEV-28342: sys.create_synonym_db fails when a temporary table masks a base table

- This commit rely on MDEV-28391
- When temporary table shadows the base table, error is raised (it can be changed if needed),
  since the procedure is relying on creating the views and view cannot be created from the temporary table.

- Reviewed by: <wlad@mariadb.com>

3 files changed, 48 insertions, 3 deletions

diff --git a/mysql-test/suite/sysschema/r/pr_create_synonym_db.result b/mysql-test/suite/sysschema/r/pr_create_synonym_db.result
index bd7e4edc6e6..8c0e8dfa957 100644
--- a/mysql-test/suite/sysschema/r/pr_create_synonym_db.result
+++ b/mysql-test/suite/sysschema/r/pr_create_synonym_db.result
@@ -50,3 +50,17 @@ DROP TABLE test.t1;
 DROP TABLE test.t2;
 DROP TABLE `is`;
 DROP TABLE `ab``c`;
+#
+# MDEV-28342: sys.create_synonym_db fails
+#             when a temporary table masks a base table
+#
+create database db;
+use db;
+create table a(a int);
+create table t (b int);
+create table b(a int);
+create temporary table b (a int);
+call sys.create_synonym_db('db','db_copy');
+ERROR HY000: Table`db`.`b`shadows base table. View cannot be created! Terminating!
+drop database db;
+drop database db_copy;
diff --git a/mysql-test/suite/sysschema/t/pr_create_synonym_db.test b/mysql-test/suite/sysschema/t/pr_create_synonym_db.test
index 4fbe8759380..30c6f502e30 100644
--- a/mysql-test/suite/sysschema/t/pr_create_synonym_db.test
+++ b/mysql-test/suite/sysschema/t/pr_create_synonym_db.test
@@ -50,3 +50,20 @@ DROP TABLE test.t1;
 DROP TABLE test.t2;
 DROP TABLE `is`;
 DROP TABLE `ab``c`;
+
+--echo #
+--echo # MDEV-28342: sys.create_synonym_db fails
+--echo #             when a temporary table masks a base table
+--echo #
+
+create database db;
+use db;
+create table a(a int);
+create table t (b int);
+create table b(a int);
+create temporary table b (a int);
+--error ER_SIGNAL_EXCEPTION
+call sys.create_synonym_db('db','db_copy');
+
+drop database db;
+drop database db_copy;
diff --git a/scripts/sys_schema/procedures/create_synonym_db.sql b/scripts/sys_schema/procedures/create_synonym_db.sql
index 703ec9bff3b..e373a9b4ee6 100644
--- a/scripts/sys_schema/procedures/create_synonym_db.sql
+++ b/scripts/sys_schema/procedures/create_synonym_db.sql
@@ -97,9 +97,8 @@ BEGIN
     DECLARE v_db_err_msg TEXT;
     DECLARE v_table VARCHAR(64);
     DECLARE v_views_created INT DEFAULT 0;
-
-    DECLARE db_doesnt_exist CONDITION FOR SQLSTATE '42000';
-    DECLARE db_name_exists CONDITION FOR SQLSTATE 'HY000';
+    DECLARE v_table_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY') DEFAULT '';
+    DECLARE v_temp_table TEXT;
 
     DECLARE c_table_names CURSOR FOR 
         SELECT TABLE_NAME 
@@ -144,6 +143,21 @@ BEGIN
             LEAVE c_table_names;
         END IF;
 
+    -- Check does temporary table shadows the base table. If it is so, terminate.
+        CALL sys.table_exists(in_db_name, v_table, v_table_exists);
+        IF (v_table_exists = 'TEMPORARY') THEN
+            SET v_temp_table =
+            CONCAT(
+                'Table',
+                 sys.quote_identifier(in_db_name),
+                 '.',
+                 sys.quote_identifier(v_table),
+                 'shadows base table. View cannot be created! Terminating!');
+             SIGNAL SQLSTATE 'HY000'
+                 SET MESSAGE_TEXT = v_temp_table;
+             LEAVE c_table_names;
+        END IF;
+
         SET @create_view_stmt = CONCAT(
             'CREATE SQL SECURITY INVOKER VIEW ',
             sys.quote_identifier(in_synonym),