MDEV-28453: SHOW commands are inconsistent for temporary tablesbb-10.9-anel

Reviewed by: <>

3 files changed, 85 insertions, 7 deletions

diff --git a/mysql-test/main/information_schema_temp_table.result b/mysql-test/main/information_schema_temp_table.result
index 778227cbb99..26f53c7b05b 100644
--- a/mysql-test/main/information_schema_temp_table.result
+++ b/mysql-test/main/information_schema_temp_table.result
@@ -143,3 +143,39 @@ drop database mysqltest;
 drop table test.base_in_test;
 drop table test.tmp_in_test;
 drop table test.tmp_in_test;
+#
+# MDEV-28453: SHOW commands are inconsistent for temporary tables
+#
+create database mysqltest;
+use mysqltest;
+create table t (a int, key(a)) engine=Aria;
+create temporary table t (b int, key(b)) engine=MyISAM;
+Warnings:
+Note	1050	Table 't' already exists
+create table base_table(t int);
+create temporary table tmp_table (b int, key(b));
+show tables;
+Tables_in_mysqltest
+tmp_table
+t
+base_table
+t
+show full tables;
+Tables_in_mysqltest	Table_type
+tmp_table	TEMPORARY TABLE
+t	TEMPORARY TABLE
+base_table	BASE TABLE
+t	BASE TABLE
+show table status;
+Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment	Max_index_length	Temporary
+tmp_table	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	#	#	#	latin1_swedish_ci	NULL			288230376151710720	Y
+t	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	#	#	#	latin1_swedish_ci	NULL			288230376151710720	Y
+base_table	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	#	#	#	latin1_swedish_ci	NULL			17179868160	N
+t	Aria	10	Page	0	0	8192	17592186011648	8192	0	NULL	#	#	#	latin1_swedish_ci	NULL	transactional=1		9007199254732800	N
+show columns in t;
+Field	Type	Null	Key	Default	Extra
+b	int(11)	YES	MUL	NULL	
+show index in t;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			NO
+drop database mysqltest;
diff --git a/mysql-test/main/information_schema_temp_table.test b/mysql-test/main/information_schema_temp_table.test
index a87416174f6..3c4c4703da0 100644
--- a/mysql-test/main/information_schema_temp_table.test
+++ b/mysql-test/main/information_schema_temp_table.test
@@ -150,3 +150,28 @@ drop table test.base_in_test;
 # We need first to drop temporary table that shadows the base table
 drop table test.tmp_in_test;
 drop table test.tmp_in_test;
+
+--echo #
+--echo # MDEV-28453: SHOW commands are inconsistent for temporary tables
+--echo #
+create database mysqltest;
+use mysqltest;
+create table t (a int, key(a)) engine=Aria;
+create temporary table t (b int, key(b)) engine=MyISAM;
+create table base_table(t int);
+create temporary table tmp_table (b int, key(b));
+
+# This should show all tables
+show tables;
+# This should show all tables with additional table_type
+show full tables; 
+# This is already showing all tables (not related to bug)
+--replace_column 12 # 13 # 14 #
+show table status;
+# This is showing temporary table as expected since it is shadowing base table
+show columns in t;
+# This is showing temporary table as expected since it is shadowing base table
+show index in t;
+ 
+# Cleanup
+drop database mysqltest;
\ No newline at end of file
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 4156b89e7fc..1b2c9c4a01b 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -4800,6 +4800,11 @@ end:
   @param[in]      table                    TABLE struct for I_S table
   @param[in]      db_name                  database name
   @param[in]      table_name               table name
+  @param[in]      check_temp               check for temporary tables
+                                           (option added because in the case when
+                                           temporary table has the same name of
+                                           base table we don't want to have 2 records
+                                           with the same table_type)
 
   @return         Operation status
     @retval       0           success
@@ -4808,7 +4813,8 @@ end:
 
 static int fill_schema_table_names(THD *thd, TABLE_LIST *tables,
                                    LEX_CSTRING *db_name,
-                                   LEX_CSTRING *table_name)
+                                   LEX_CSTRING *table_name,
+                                   bool check_temp=false)
 {
   TABLE *table= tables->table;
   if (db_name == &INFORMATION_SCHEMA_NAME)
@@ -4823,7 +4829,7 @@ static int fill_schema_table_names(THD *thd, TABLE_LIST *tables,
     bool is_sequence;
 
     if (ha_table_exists(thd, db_name, table_name, NULL, NULL,
-                        &hton, &is_sequence))
+                        &hton, &is_sequence) && !check_temp)
     {
       if (hton == view_pseudo_hton)
         table->field[3]->store(STRING_WITH_LEN("VIEW"), cs);
@@ -4833,7 +4839,12 @@ static int fill_schema_table_names(THD *thd, TABLE_LIST *tables,
         table->field[3]->store(STRING_WITH_LEN("BASE TABLE"), cs);
     }
     else
-      table->field[3]->store(STRING_WITH_LEN("ERROR"), cs);
+    {
+      if (table->s && IS_USER_TEMP_TABLE(table->s))
+        table->field[3]->store(STRING_WITH_LEN("TEMPORARY TABLE"), cs);
+      else
+        table->field[3]->store(STRING_WITH_LEN("ERROR"), cs);
+    }
 
     if (unlikely(thd->is_error() &&
                  thd->get_stmt_da()->sql_errno() == ER_NO_SUCH_TABLE))
@@ -5277,8 +5288,11 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
   init_alloc_root(PSI_INSTRUMENT_ME, &tmp_mem_root, SHOW_ALLOC_BLOCK_SIZE,
                   SHOW_ALLOC_BLOCK_SIZE, MY_THREAD_SPECIFIC);
 
-  /* Handling session temporary tables from the backup state */
-  if (schema_table_idx == SCH_TABLES && open_tables_state_backup.temporary_tables)
+  /* Handling session temporary tables from the backup state for table IS.tables
+     and SHOW TABLES commands.
+  */
+  if ((schema_table_idx == SCH_TABLES || schema_table_idx == SCH_TABLE_NAMES) && \
+      open_tables_state_backup.temporary_tables)
   {
     All_tmp_tables_list::Iterator it(*open_tables_state_backup.temporary_tables);
     TMP_TABLE_SHARE *share_temp;
@@ -5307,7 +5321,11 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond)
               if ((!partial_cond || partial_cond->val_int()) &&
                   (!wild || !wild_compare(table_name->str, wild, 0)))
               {
-                process_i_s_table_temporary_tables(thd, table, tmp_tbl);
+                if (schema_table_idx == SCH_TABLE_NAMES)
+                  fill_schema_table_names(thd, tables, &tmp_tbl->s->db,
+                                          &tmp_tbl->s->table_name, true);
+                else
+                  process_i_s_table_temporary_tables(thd, table, tmp_tbl);
                 break;
               }
             }
@@ -5883,7 +5901,6 @@ void process_i_s_table_temporary_tables(THD *thd, TABLE *table, TABLE *tmp_tbl)
   TABLE_LIST table_list;
   bzero((char*) &table_list, sizeof(TABLE_LIST));
   table_list.table= tmp_tbl;
-
   get_schema_tables_record(thd, &table_list, table,
                            0, &tmp_tbl->s->db, &tmp_tbl->s->table_name);
 }