Patch#4 Sysschema fixbb-10.6-vicentiu

2 files changed, 114 insertions, 55 deletions

diff --git a/mysql-test/suite/sysschema/t/pr_table_exists.test b/mysql-test/suite/sysschema/t/pr_table_exists.test
index 83e1dc0b9d9..4766876703a 100644
--- a/mysql-test/suite/sysschema/t/pr_table_exists.test
+++ b/mysql-test/suite/sysschema/t/pr_table_exists.test
@@ -66,4 +66,97 @@ SELECT @tbl_type;
 --echo # We cannot send quoted identifer to the procedure, no table will be found
 CALL sys.table_exists('test', '`ab``c`', @tbl_type);
 SELECT @tbl_type;
-DROP TABLE `ab``c`;
\ No newline at end of file
+--echo # Remove temporary table
+DROP TABLE `ab``c`;
+CALL sys.table_exists('test', 'ab`c', @tbl_type);
+SELECT @tbl_type;
+--echo # Remove base table
+DROP TABLE `ab``c`;
+--echo # MDEV-12459: The information_schema tables for getting temporary tables
+--echo #             info is missing, at least for innodb, there is no
+--echo #             INNODB_TEMP_TABLE_INFO
+--echo #
+
+# Verify that temporary table will raise an warning and will shadow the base table
+CREATE TABLE t1 (id INT PRIMARY KEY);
+# Verify the base table and view is supported
+CALL sys.table_exists('test', 't1', @exists);
+SELECT @exists;
+CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
+CALL sys.table_exists('test', 't1', @exists);
+SELECT @exists;
+
+DROP TEMPORARY TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-28335: TABLE_TYPE for temporary sequences 
+--echo #             is the same as for permanent ones
+--echo # 
+
+CREATE TEMPORARY SEQUENCE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# If there is no shadowing with temporary table, result is table type
+SELECT @exists;
+CREATE TABLE t1 (id INT PRIMARY KEY);
+CALL sys.table_exists('test', 't1', @exists);
+# Before was a sequence, now should be temporary
+SELECT @exists;
+# It is not possible to create temporary table
+--error ER_TABLE_EXISTS_ERROR
+CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
+# It is not possible to create sequence over temporary sequence
+--error ER_TABLE_EXISTS_ERROR
+CREATE SEQUENCE t1;
+# This will drop temporary sequence
+DROP TABLE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# This will lead to base table
+SELECT @exists;
+# It is not possible to create a sequence over the base table
+--error ER_TABLE_EXISTS_ERROR
+CREATE SEQUENCE t1;
+# Let's test with temporary sequence instead
+CREATE TEMPORARY SEQUENCE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# Will return temporary as expected
+SELECT @exists;
+# Again droping the temporary sequence
+DROP TABLE t1;
+# Will lead to the base table
+CALL sys.table_exists('test', 't1', @exists);
+SELECT @exists;
+CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);
+# This will return temporary
+CALL sys.table_exists('test', 't1', @exists);
+SELECT @exists;
+# We cannot create [temporary] sequence over temporary table
+--error ER_TABLE_EXISTS_ERROR
+CREATE TEMPORARY SEQUENCE t1;
+--error ER_TABLE_EXISTS_ERROR
+CREATE SEQUENCE t1;
+DROP TEMPORARY TABLE t1;
+# Drop base table
+DROP TABLE t1;
+CREATE SEQUENCE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# Should be a sequence
+SELECT @exists;
+# Create an temporary table
+CREATE TEMPORARY TABLE t1(t int);
+CALL sys.table_exists('test', 't1', @exists);
+# Should shadow an sequence with temporary
+SELECT @exists;
+# Drop temporary table
+DROP TABLE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# Should again show the sequence
+SELECT @exists;
+CREATE TEMPORARY SEQUENCE t1;
+CALL sys.table_exists('test', 't1', @exists);
+# Should shadow an sequence with temporary
+SELECT @exists;
+# Drop temporary sequence
+DROP TABLE t1;
+# Drop an sequence
+DROP TABLE t1;
diff --git a/scripts/sys_schema/procedures/table_exists.sql b/scripts/sys_schema/procedures/table_exists.sql
index 0f7640329e7..4f7063e761d 100644
--- a/scripts/sys_schema/procedures/table_exists.sql
+++ b/scripts/sys_schema/procedures/table_exists.sql
@@ -39,7 +39,7 @@ CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE table_exists (
              in_table (VARCHAR(64)):
                The name of the table to check the existence of.
 
-             out_exists ENUM('''', ''BASE TABLE'', ''VIEW'', ''TEMPORARY''):
+             out_exists ENUM('''', ''BASE TABLE'', ''VIEW'', ''TEMPORARY'', ''SEQUENCE'', ''SYSTEM VIEW''):
                The return value: whether the table exists. The value is one of:
                  * ''''             - the table does not exist neither as a base table, view, sequence nor temporary table.
                  * ''BASE TABLE''   - the table name exists as a permanent base table table.
@@ -136,64 +136,30 @@ BEGIN
     DECLARE db_quoted VARCHAR(64);
     DECLARE table_quoted VARCHAR(64);
     DECLARE v_table_type VARCHAR(16) DEFAULT '';
-    DECLARE v_system_db BOOLEAN
-        DEFAULT LOWER(in_db) IN ('information_schema', 'performance_schema');
+    DECLARE v_table_type_num INT;
     DECLARE CONTINUE HANDLER FOR 1050 SET v_error = TRUE;
     DECLARE CONTINUE HANDLER FOR 1146 SET v_error = TRUE;
 
-    SET out_exists = '';
-    SET db_quoted = sys.quote_identifier(in_db);
-    SET table_quoted = sys.quote_identifier(in_table);
-
-    -- Verify whether the table name exists as a normal table
-    IF (EXISTS(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table)) THEN
-        -- Unfortunately the only way to determine whether there is also a temporary table is to try to create
-        -- a temporary table with the same name. If it succeeds the table didn't exist as a temporary table.
-        IF v_system_db = FALSE THEN
-            SET @sys.tmp.table_exists.SQL = CONCAT('CREATE TEMPORARY TABLE ',
-                                                    db_quoted,
-                                                    '.',
-                                                    table_quoted,
-                                                    '(id INT PRIMARY KEY)');
-            PREPARE stmt_create_table FROM @sys.tmp.table_exists.SQL;
-            EXECUTE stmt_create_table;
-            DEALLOCATE PREPARE stmt_create_table;
-
-            -- The temporary table was created, i.e. it didn't exist. Remove it again so we don't leave garbage around.
-            SET @sys.tmp.table_exists.SQL = CONCAT('DROP TEMPORARY TABLE ',
-                                                                db_quoted,
-                                                                '.',
-                                                                table_quoted);
-            PREPARE stmt_drop_table FROM @sys.tmp.table_exists.SQL;
-            EXECUTE stmt_drop_table;
-            DEALLOCATE PREPARE stmt_drop_table;
+    -- First check do we have multiple rows, what can happen if temporary table
+    -- is shadowing base table for example. In such scenario return temporary.
+    SET v_table_type_num = (SELECT COUNT(TABLE_TYPE) FROM information_schema.TABLES WHERE
+                            TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
+
+    IF v_table_type_num > 1 THEN
+        SET out_exists = 'TEMPORARY';
+    ELSE
+        SET v_table_type = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE
+                                TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
+        IF v_table_type is NULL
+        THEN
+            SET v_table_type='';
         END IF;
-        IF (v_error) THEN
-            SET out_exists = 'TEMPORARY';
+        -- Don't fail on table_type='SYSTEM VERSIONED'
+        -- but return 'BASE TABLE' for compatibility with existing tooling
+        IF v_table_type = 'SYSTEM VERSIONED' THEN
+            SET out_exists = 'BASE TABLE';
         ELSE
-            SET v_table_type = (SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = in_db AND TABLE_NAME = in_table);
-            -- Don't fail on table_type='SYSTEM VERSIONED'
-            -- but return 'BASE TABLE' for compatibility with existing tooling
-            IF v_table_type = 'SYSTEM VERSIONED' THEN
-                SET out_exists = 'BASE TABLE';
-            ELSE
-                SET out_exists = v_table_type;
-            END IF;
-        END IF;
-    ELSE
-        -- Check whether a temporary table exists with the same name.
-        -- If it does it's possible to SELECT from the table without causing an error.
-        -- If it does not exist even a PREPARE using the table will fail.
-        IF v_system_db = FALSE THEN
-            SET @sys.tmp.table_exists.SQL = CONCAT('SELECT COUNT(*) FROM ',
-                                                            db_quoted,
-                                                            '.',
-                                                            table_quoted);
-            PREPARE stmt_select FROM @sys.tmp.table_exists.SQL;
-            IF (NOT v_error) THEN
-                DEALLOCATE PREPARE stmt_select;
-                SET out_exists = 'TEMPORARY';
-            END IF;
+            SET out_exists = v_table_type;
         END IF;
     END IF;
 END$$