Drop temporary table and base table with the same namebb-10.3-anel-PR796

8 files changed, 323 insertions, 8 deletions

diff --git a/mysql-test/main/drop-16253.result b/mysql-test/main/drop-16253.result
new file mode 100644
index 00000000000..66b9a15ffb2
--- /dev/null
+++ b/mysql-test/main/drop-16253.result
@@ -0,0 +1,129 @@
+#
+# MDEV-16253 Dropping base table and temporary table with the same name
+#
+#===== 1 Drop table/s without base or temp table =====
+DROP TABLE  t;
+ERROR 42S02: Unknown table 'test.t'
+DROP TABLE t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+#===== 2 Drop base table and without temp table =====
+CREATE TABLE t(t int);
+DROP TABLE t,t;
+ERROR 42000: Not unique table/alias: 't'
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t;
+#===== 3 Drop table/s without base and with temp table =====
+CREATE TEMPORARY TABLE t LIKE t;
+ERROR 42S02: Table 'test.t' doesn't exist
+CREATE OR REPLACE TABLE t LIKE t;
+ERROR 42000: Not unique table/alias: 't'
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+CREATE OR REPLACE TABLE t LIKE t;
+ERROR 42000: Not unique table/alias: 't'
+SHOW TABLES;
+Tables_in_test
+t
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TEMPORARY TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t;
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `t` int(11) DEFAULT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t;
+CREATE TEMPORARY TABLE t(t int);
+DROP TABLE t,t;
+ERROR 42S02: Unknown table 'test.t'
+CREATE TEMPORARY TABLE t(t int);
+DROP TABLE t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP table t,t1,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP table t,t1,t;
+ERROR 42S02: Unknown table 'test.t,test.t1'
+#===== 4 Drop table/s with base and temp table =====
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+DROP TABLE t,t;
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+DROP TABLE t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+DROP TABLE t,t,t,t;
+ERROR 42000: Not unique table/alias: 't'
+CREATE TEMPORARY TABLE t1(t1 int);
+CREATE TEMPORARY TABLE t2 LIKE t;
+DROP TABLE t,t1,t2,t;
+connect con1, localhost, root,,;
+connection con1;
+#===== 5 Drop with locking =====
+CREATE TABLE t(t int);
+LOCK TABLE t WRITE;
+SHOW TABLES;
+Tables_in_test
+t
+DROP TABLE t,t;
+ERROR 42000: Not unique table/alias: 't'
+CREATE TEMPORARY TABLE t LIKE t;
+DROP TABLE t,t;
+DROP TABLE IF EXISTS t;
+Warnings:
+Note	1051	Unknown table 'test.t'
+connection default;
+disconnect con1;
+#===== Check grants when dropping =====
+connect con1, localhost, root,,test;
+connection con1;
+create user foo;
+GRANT Select, Create temporary tables ON test.* TO 'foo'@'%';
+disconnect con1;
+connection default;
+CREATE TABLE t(t int);
+connect con1, localhost, foo,,test;
+show tables;
+Tables_in_test
+t
+CREATE TEMPORARY TABLE t(t int);
+DROP TABLE t;
+DROP TABLE t;
+ERROR 42000: DROP command denied to user 'foo'@'localhost' for table 't'
+disconnect con1;
+connect con1, localhost, root,,test;
+connection con1;
+drop user foo;
+disconnect con1;
+connection default;
+drop table if exists t;
diff --git a/mysql-test/main/drop-16253.test b/mysql-test/main/drop-16253.test
new file mode 100644
index 00000000000..04c51022026
--- /dev/null
+++ b/mysql-test/main/drop-16253.test
@@ -0,0 +1,136 @@
+--echo #
+--echo # MDEV-16253 Dropping base table and temporary table with the same name
+--echo #
+
+--echo #===== 1 Drop table/s without base or temp table =====
+
+--error ER_BAD_TABLE_ERROR # 1051
+DROP TABLE  t;
+
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t,t;
+
+--echo #===== 2 Drop base table and without temp table =====
+
+CREATE TABLE t(t int);
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t;
+SHOW CREATE TABLE t;
+
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t;
+SHOW CREATE TABLE t;
+
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t;
+SHOW CREATE TABLE t;
+DROP TABLE t;
+
+--echo #===== 3 Drop table/s without base and with temp table =====
+
+# Temporary table cannot be created without base table
+--error ER_NO_SUCH_TABLE
+CREATE TEMPORARY TABLE t LIKE t;
+--error ER_NONUNIQ_TABLE
+CREATE OR REPLACE TABLE t LIKE t;
+
+# Create base table and temporary table
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+--error ER_NONUNIQ_TABLE
+CREATE OR REPLACE TABLE t LIKE t;
+
+# Show temporary table first
+SHOW TABLES;
+SHOW CREATE TABLE t;
+DROP TABLE t;
+
+# Show base table
+SHOW CREATE TABLE t;
+DROP TABLE t;
+
+# Create temporary table and try to drop multiple tables
+CREATE TEMPORARY TABLE t(t int);
+--error ER_BAD_TABLE_ERROR
+DROP TABLE t,t;
+
+CREATE TEMPORARY TABLE t(t int);
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t,t;
+
+--error ER_NONUNIQ_TABLE
+DROP table t,t1,t,t;
+--error ER_BAD_TABLE_ERROR
+DROP table t,t1,t;
+
+--echo #===== 4 Drop table/s with base and temp table =====
+
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+DROP TABLE t,t;
+
+CREATE TABLE t(t int);
+CREATE TEMPORARY TABLE t LIKE t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t;
+--error ER_NONUNIQ_TABLE
+DROP TABLE t,t,t,t;
+
+CREATE TEMPORARY TABLE t1(t1 int);
+CREATE TEMPORARY TABLE t2 LIKE t;
+DROP TABLE t,t1,t2,t;
+
+connect con1, localhost, root,,;
+connection con1;
+
+--echo #===== 5 Drop with locking =====
+CREATE TABLE t(t int);
+LOCK TABLE t WRITE;
+SHOW TABLES;
+--ERROR ER_NONUNIQ_TABLE
+DROP TABLE t,t;
+
+CREATE TEMPORARY TABLE t LIKE t;
+DROP TABLE t,t;
+
+DROP TABLE IF EXISTS t;
+connection default;
+disconnect con1;
+
+--echo #===== Check grants when dropping =====
+
+connect con1, localhost, root,,test;
+connection con1;
+create user foo;
+GRANT Select, Create temporary tables ON test.* TO 'foo'@'%';
+disconnect con1;
+
+connection default;
+
+CREATE TABLE t(t int);
+
+connect con1, localhost, foo,,test;
+show tables;
+CREATE TEMPORARY TABLE t(t int);
+DROP TABLE t;
+--error ER_TABLEACCESS_DENIED_ERROR
+DROP TABLE t;
+disconnect con1;
+
+connect con1, localhost, root,,test;
+connection con1;
+drop user foo;
+disconnect con1;
+connection default;
+drop table if exists t;
+
diff --git a/mysql-test/main/drop.result b/mysql-test/main/drop.result
index 88e070af88c..ddb0a6cf265 100644
--- a/mysql-test/main/drop.result
+++ b/mysql-test/main/drop.result
@@ -182,10 +182,14 @@ Error	1051	Unknown table 'test.t1'
 # Bug#54282 Crash in MDL_context::upgrade_shared_lock_to_exclusive
 #
 DROP TABLE IF EXISTS t1;
+connect con1,localhost,root,,;
+connection con1;
 CREATE TABLE t1 (a INT);
 LOCK TABLE t1 WRITE;
 DROP TABLE t1, t1;
 ERROR 42000: Not unique table/alias: 't1'
+disconnect con1;
+connection default;
 UNLOCK TABLES;
 DROP TABLE t1;
 #
diff --git a/mysql-test/main/drop.test b/mysql-test/main/drop.test
index 6f506dd7215..5bb1cd92f05 100644
--- a/mysql-test/main/drop.test
+++ b/mysql-test/main/drop.test
@@ -269,11 +269,14 @@ SHOW WARNINGS;
 DROP TABLE IF EXISTS t1;
 --enable_warnings
 
+connect con1,localhost,root,,;
+connection con1;
 CREATE TABLE t1 (a INT);
 LOCK TABLE t1 WRITE;
 --error ER_NONUNIQ_TABLE
 DROP TABLE t1, t1;
-
+disconnect con1;
+connection default;
 UNLOCK TABLES;
 DROP TABLE t1;
 
diff --git a/mysql-test/main/temp_table.result b/mysql-test/main/temp_table.result
index 69f3b8e5155..0af3adeb36a 100644
--- a/mysql-test/main/temp_table.result
+++ b/mysql-test/main/temp_table.result
@@ -481,8 +481,7 @@ CREATE TEMPORARY TABLE temp_t1 LIKE t1;
 SELECT * FROM temp_t1;
 i
 CREATE TEMPORARY TABLE t1 LIKE t1;
-ERROR 42000: Not unique table/alias: 't1'
-DROP TABLE temp_t1, t1;
+DROP TABLE temp_t1, t1, t1;
 #
 # Create temporary table as base table.
 #
diff --git a/mysql-test/main/temp_table.test b/mysql-test/main/temp_table.test
index bd3bba34f89..c6ad9d11aff 100644
--- a/mysql-test/main/temp_table.test
+++ b/mysql-test/main/temp_table.test
@@ -527,9 +527,8 @@ CREATE TABLE t1(i INT) ENGINE=INNODB;
 INSERT INTO t1 VALUES(1);
 CREATE TEMPORARY TABLE temp_t1 LIKE t1;
 SELECT * FROM temp_t1;
---error ER_NONUNIQ_TABLE
 CREATE TEMPORARY TABLE t1 LIKE t1;
-DROP TABLE temp_t1, t1;
+DROP TABLE temp_t1, t1, t1;
 
 --echo #
 --echo # Create temporary table as base table.
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 59b4e148005..9d2324c1438 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4792,7 +4792,6 @@ mysql_execute_command(THD *thd)
         }
       }
     }
-    
     /* DDL and binlog write order are protected by metadata locks. */
     res= mysql_rm_table(thd, first_table, lex->if_exists(), lex->tmp_table(),
                         lex->table_type == TABLE_TYPE_SEQUENCE);
@@ -7966,6 +7965,7 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
   LEX_CSTRING alias_str;
   LEX *lex= thd->lex;
   DBUG_ENTER("add_table_to_list");
+  int same_name= 0;
 
   if (unlikely(!table))
     DBUG_RETURN(0);				// End of memory
@@ -8068,8 +8068,23 @@ TABLE_LIST *st_select_lex::add_table_to_list(THD *thd,
                                   tables->alias.str) &&
                    !cmp(&ptr->db, &tables->db) && ! tables->sequence))
       {
-	my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str.str); /* purecov: tested */
-	DBUG_RETURN(0);				/* purecov: tested */
+        if ((lex->sql_command==SQLCOM_DROP_TABLE && thd->temporary_tables) ||
+            (lex->create_info.like() && !lex->create_info.or_replace()))
+        {
+          /* In case of dropping tables/create table like, 
+           * it is allowed to have same name for base  and temp tables */
+          if (same_name>1)
+          {
+            my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str.str); /* purecov: tested */
+            DBUG_RETURN(0);
+          }
+          same_name++;
+        }
+        else
+        {
+          my_error(ER_NONUNIQ_TABLE, MYF(0), alias_str.str); /* purecov: tested */
+          DBUG_RETURN(0);				/* purecov: tested */
+        }
       }
     }
   }
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8642bd47da5..5d42f5a1adb 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2049,6 +2049,35 @@ bool mysql_rm_table(THD *thd,TABLE_LIST *tables, bool if_exists,
       {
         LEX_CSTRING db_name= table->db;
         LEX_CSTRING table_name= table->table_name;
+        TABLE_LIST *tab_same;
+        int found;
+        found=0;
+        for (tab_same= table->next_local; tab_same; tab_same= tab_same->next_local)
+        {
+          /*  Try to find the tables with the same name.
+           *  In that case the base table is the same as temporary table.
+           *  Temporary tables are checked first.
+          */
+          if (tab_same->table)
+          {
+            if (!strcmp(table->get_db_name(), tab_same->get_db_name()) &&
+                !strcmp(table->get_table_name(), tab_same->get_table_name()))
+            {
+              found++;
+              if (found > 1)
+              {
+                my_error(ER_NONUNIQ_TABLE, MYF(0), table->get_table_name());
+                DBUG_RETURN(true);
+              }
+              else
+              {
+                thd->mark_tmp_table_as_free_for_reuse(table->table);
+                table->table= NULL;
+              }
+            }
+          }
+        }
+
         if (table->open_type == OT_BASE_ONLY ||
             !thd->find_temporary_table(table))
           (void) delete_statistics_for_table(thd, &db_name, &table_name);
@@ -2329,6 +2358,7 @@ int mysql_rm_table_no_locks(THD *thd, TABLE_LIST *tables, bool if_exists,
     DBUG_ASSERT(!(thd->locked_tables_mode &&
                   table->open_type != OT_BASE_ONLY &&
                   thd->find_temporary_table(table) &&
+                  is_temporary_table(table) &&
                   table->mdl_request.ticket != NULL));
 
     if (table->open_type == OT_BASE_ONLY || !is_temporary_table(table) ||