# name       : show_temp.patch
# introduced : 11 or before
# maintainer : Yasufumi
#
#!!! notice !!!
# Any small change to this file in the main branch
# should be done or reviewed by the maintainer!
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -570,6 +570,7 @@
   SCH_EVENTS,
   SCH_FILES,
   SCH_GLOBAL_STATUS,
+  SCH_GLOBAL_TEMPORARY_TABLES,
   SCH_GLOBAL_VARIABLES,
   SCH_KEY_COLUMN_USAGE,
   SCH_OPEN_TABLES,
@@ -591,6 +592,7 @@
   SCH_TABLE_CONSTRAINTS,
   SCH_TABLE_NAMES,
   SCH_TABLE_PRIVILEGES,
+  SCH_TEMPORARY_TABLES,
   SCH_TRIGGERS,
   SCH_USER_PRIVILEGES,
   SCH_VARIABLES,
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -2951,6 +2951,7 @@
   {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
   {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
   {"show_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS},
+  {"show_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_TABLES]), SHOW_LONG_STATUS},
   {"show_triggers",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},
   {"show_variables",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},
   {"show_warnings",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS},
@@ -7670,6 +7671,7 @@
 PSI_mutex_key key_LOCK_des_key_file;
 #endif /* HAVE_OPENSSL */
 
+PSI_mutex_key key_LOCK_temporary_tables;
 PSI_mutex_key key_BINLOG_LOCK_index, key_BINLOG_LOCK_prep_xids,
   key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi,
   key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create,
@@ -7723,6 +7725,7 @@
   { &key_LOCK_system_variables_hash, "LOCK_system_variables_hash", PSI_FLAG_GLOBAL},
   { &key_LOCK_table_share, "LOCK_table_share", PSI_FLAG_GLOBAL},
   { &key_LOCK_thd_data, "THD::LOCK_thd_data", 0},
+  { &key_LOCK_temporary_tables, "THD::LOCK_temporary_tables", 0},
   { &key_LOCK_user_conn, "LOCK_user_conn", PSI_FLAG_GLOBAL},
   { &key_LOCK_uuid_generator, "LOCK_uuid_generator", PSI_FLAG_GLOBAL},
   { &key_LOG_LOCK_log, "LOG::LOCK_log", 0},
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -194,6 +194,7 @@
     When a command is added here, be sure it's also added in mysqld.cc
     in "struct show_var_st status_vars[]= {" ...
   */
+  SQLCOM_SHOW_TEMPORARY_TABLES,
   /* This should be the last !!! */
   SQLCOM_END
 };
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -348,6 +348,9 @@
    sql_command_flags[SQLCOM_SHOW_TABLES]=       (CF_STATUS_COMMAND |
                                                  CF_SHOW_TABLE_COMMAND |
                                                  CF_REEXECUTION_FRAGILE);
+   sql_command_flags[SQLCOM_SHOW_TEMPORARY_TABLES]=       (CF_STATUS_COMMAND |
+                                                 CF_SHOW_TABLE_COMMAND |
+                                                 CF_REEXECUTION_FRAGILE);
   sql_command_flags[SQLCOM_SHOW_TABLE_STATUS]= (CF_STATUS_COMMAND |
                                                 CF_SHOW_TABLE_COMMAND |
                                                 CF_REEXECUTION_FRAGILE);
@@ -1520,6 +1523,8 @@
 
   case SCH_TABLE_NAMES:
   case SCH_TABLES:
+  case SCH_TEMPORARY_TABLES:
+  case SCH_GLOBAL_TEMPORARY_TABLES:
   case SCH_VIEWS:
   case SCH_TRIGGERS:
   case SCH_EVENTS:
@@ -2110,6 +2115,7 @@
   }
   case SQLCOM_SHOW_DATABASES:
   case SQLCOM_SHOW_TABLES:
+  case SQLCOM_SHOW_TEMPORARY_TABLES:
   case SQLCOM_SHOW_TRIGGERS:
   case SQLCOM_SHOW_TABLE_STATUS:
   case SQLCOM_SHOW_OPEN_TABLES:
@@ -4919,6 +4925,8 @@
 
   case SCH_TABLE_NAMES:
   case SCH_TABLES:
+  case SCH_TEMPORARY_TABLES:
+  case SCH_GLOBAL_TEMPORARY_TABLES:
   case SCH_VIEWS:
   case SCH_TRIGGERS:
   case SCH_EVENTS:
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2692,6 +2692,7 @@
     break;
   case SQLCOM_SHOW_TABLES:
   case SQLCOM_SHOW_TABLE_STATUS:
+  case SQLCOM_SHOW_TEMPORARY_TABLES:
   case SQLCOM_SHOW_TRIGGERS:
   case SQLCOM_SHOW_EVENTS:
     thd->make_lex_string(&lookup_field_values->db_value, 
@@ -3283,6 +3284,231 @@
   return (uint) OPEN_FULL_TABLE;
 }
 
+/**
+  @brief          Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db]
+
+  @param[in]      thd                      thread handler
+  @param[in]      schema_table             I_S table
+
+  @return         Operation status
+    @retval       0                        success
+    @retval       1                        error
+*/
+int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
+{
+  char tmp[128];
+  String buffer(tmp,sizeof(tmp), thd->charset());
+  LEX *lex= thd->lex;
+  Name_resolution_context *context= &lex->select_lex.context;
+
+  if (thd->lex->option_type == OPT_GLOBAL) {
+    ST_FIELD_INFO *field_info= &schema_table->fields_info[0];
+    Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
+    if (add_item_to_list(thd, field))
+      return 1;
+    field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info);
+  }
+
+  ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
+  buffer.length(0);
+  buffer.append(field_info->old_name);
+  buffer.append(lex->select_lex.db);
+
+  if (lex->wild && lex->wild->ptr())
+  {
+    buffer.append(STRING_WITH_LEN(" ("));
+    buffer.append(lex->wild->ptr());
+    buffer.append(')');
+  }
+
+  Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);    
+  if (add_item_to_list(thd, field))
+    return 1;
+
+  field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
+  return 0;
+}
+
+/**
+  @brief          Fill records for temporary tables by reading info from table object
+
+  @param[in]      thd                      thread handler
+  @param[in]      table                    I_S table
+  @param[in]      tmp_table                temporary table
+  @param[in]      db                       database name
+
+  @return         Operation status
+    @retval       0                        success
+    @retval       1                        error
+*/
+
+static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only)
+{
+  CHARSET_INFO *cs= system_charset_info;
+  DBUG_ENTER("store_temporary_table_record");
+
+  if (db && my_strcasecmp(cs, db, tmp_table->s->db.str))
+    DBUG_RETURN(0);
+
+  restore_record(table, s->default_values);
+
+  //session_id
+  table->field[0]->store((longlong) thd->thread_id, TRUE);
+
+  //database
+  table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs);
+
+  //table
+  table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs);
+
+  if (table_name_only)
+    DBUG_RETURN(schema_table_store_record(thd, table));
+
+  //engine
+  handler *handle= tmp_table->file;
+  char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN");
+  table->field[3]->store(engineType, strlen(engineType), cs);
+
+  //name
+  if (tmp_table->s->path.str) {
+    char *p=strstr(tmp_table->s->path.str, "#sql");
+    int len=tmp_table->s->path.length-(p-tmp_table->s->path.str);
+    table->field[4]->store(p, min(FN_REFLEN, len), cs);
+  }
+
+  // file stats
+  handler *file= tmp_table->file;
+
+  if (file) {
+
+    MYSQL_TIME time;
+
+    /**
+        TODO: InnoDB stat(file) checks file on short names within data dictionary
+        rather than using full path, because of that, temp files created in
+        TMPDIR will not have access/create time as it will not find the file
+
+        The fix is to patch InnoDB to use full path
+    */
+    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
+
+    table->field[5]->store((longlong) file->stats.records, TRUE);
+    table->field[5]->set_notnull();
+
+    table->field[6]->store((longlong) file->stats.mean_rec_length, TRUE);
+    table->field[7]->store((longlong) file->stats.data_file_length, TRUE);
+    table->field[8]->store((longlong) file->stats.index_file_length, TRUE);
+    if (file->stats.create_time)
+    {
+      thd->variables.time_zone->gmt_sec_to_TIME(&time,
+                                                (my_time_t) file->stats.create_time);
+      table->field[9]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[9]->set_notnull();
+    }
+    if (file->stats.update_time)
+    {
+      thd->variables.time_zone->gmt_sec_to_TIME(&time,
+                                                (my_time_t) file->stats.update_time);
+      table->field[10]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
+      table->field[10]->set_notnull();
+    }
+  }
+
+  DBUG_RETURN(schema_table_store_record(thd, table));
+}
+
+/**
+  @brief          Fill I_S tables with global temporary tables
+
+  @param[in]      thd                      thread handler
+  @param[in]      tables                   I_S table
+  @param[in]      cond                     'WHERE' condition
+
+  @return         Operation status
+    @retval       0                        success
+    @retval       1                        error
+*/
+
+static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  DBUG_ENTER("fill_global_temporary_tables");
+
+  mysql_mutex_lock(&LOCK_thread_count);
+
+  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
+  I_List_iterator<THD> it(threads);
+  THD *thd_item;
+  TABLE *tmp;
+
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+  Security_context *sctx= thd->security_ctx;
+  uint db_access;
+#endif
+ 
+  while ((thd_item=it++)) {
+    mysql_mutex_lock(&thd_item->LOCK_temporary_tables);
+    for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) {
+
+#ifndef NO_EMBEDDED_ACCESS_CHECKS
+      if (test_all_bits(sctx->master_access, DB_ACLS))
+        db_access=DB_ACLS;
+      else
+        db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access);
+
+      if (!(db_access & DB_ACLS) && check_grant_db(thd,tmp->s->db.str)) {
+        //no access for temp tables within this db for user
+        continue;
+      }
+#endif
+
+      THD *t= tmp->in_use;
+      tmp->in_use= thd;
+
+      if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
+        tmp->in_use= t;
+        mysql_mutex_unlock(&thd_item->LOCK_temporary_tables);
+        mysql_mutex_unlock(&LOCK_thread_count); 
+        DBUG_RETURN(1);
+      }
+
+      tmp->in_use= t;
+    }
+    mysql_mutex_unlock(&thd_item->LOCK_temporary_tables);
+  }
+
+  mysql_mutex_unlock(&LOCK_thread_count); 
+  DBUG_RETURN(0);
+}
+
+/**
+  @brief          Fill I_S tables with session temporary tables
+
+  @param[in]      thd                      thread handler
+  @param[in]      tables                   I_S table
+  @param[in]      cond                     'WHERE' condition
+
+  @return         Operation status
+    @retval       0                        success
+    @retval       1                        error
+*/
+
+int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
+{
+  DBUG_ENTER("fill_temporary_tables");
+
+  if (thd->lex->option_type == OPT_GLOBAL)
+    DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond));
+
+  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
+  TABLE *tmp;
+
+  for (tmp=thd->temporary_tables; tmp; tmp=tmp->next) {
+    if (store_temporary_table_record(thd, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
+      DBUG_RETURN(1);
+    }
+  }
+  DBUG_RETURN(0);
+}
 
 /**
    Try acquire high priority share metadata lock on a table (with
@@ -7046,6 +7272,25 @@
   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 };
 
+ST_FIELD_INFO temporary_table_fields_info[]=
+{
+  {"SESSION_ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Session", SKIP_OPEN_TABLE},
+  {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Db", SKIP_OPEN_TABLE},
+  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Temp_tables_in_", SKIP_OPEN_TABLE},
+  {"ENGINE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Engine", OPEN_FRM_ONLY},
+  {"NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Name", SKIP_OPEN_TABLE},
+  {"TABLE_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
+   MY_I_S_UNSIGNED, "Rows", OPEN_FULL_TABLE},
+  {"AVG_ROW_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 
+   MY_I_S_UNSIGNED, "Avg Row", OPEN_FULL_TABLE},
+  {"DATA_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 
+   MY_I_S_UNSIGNED, "Data Length", OPEN_FULL_TABLE},
+  {"INDEX_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, 
+   MY_I_S_UNSIGNED, "Index Size", OPEN_FULL_TABLE},
+  {"CREATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Create Time", OPEN_FULL_TABLE},
+  {"UPDATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Update Time", OPEN_FULL_TABLE},
+  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
+};
 
 ST_FIELD_INFO columns_fields_info[]=
 {
@@ -7660,6 +7905,9 @@
    hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
   {"GLOBAL_STATUS", variables_fields_info, create_schema_table,
    fill_status, make_old_format, 0, 0, -1, 0, 0},
+  {"GLOBAL_TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, 
+   fill_global_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
+   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
   {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,
    fill_variables, make_old_format, 0, 0, -1, 0, 0},
   {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
@@ -7709,6 +7957,9 @@
    get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
   {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
    fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
+  {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table,
+   fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
+   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
   {"TRIGGERS", triggers_fields_info, create_schema_table,
    get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,
    OPEN_TRIGGER_ONLY|OPTIMIZE_I_S_TABLE},
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10898,6 +10898,15 @@
              if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))
                MYSQL_YYABORT;
            }
+         | opt_var_type TEMPORARY TABLES opt_db
+           {
+             LEX *lex= Lex;
+             lex->sql_command= SQLCOM_SHOW_TEMPORARY_TABLES;
+             lex->option_type= $1;
+             lex->select_lex.db= $4;
+             if (prepare_schema_table(YYTHD, lex, 0, SCH_TEMPORARY_TABLES))
+               MYSQL_YYABORT;
+           }
          | opt_full TRIGGERS_SYM opt_db wild_and_where
            {
              LEX *lex= Lex;
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -234,6 +234,7 @@
 extern PSI_mutex_key key_LOCK_des_key_file;
 #endif
 
+extern PSI_mutex_key key_LOCK_temporary_tables;
 extern PSI_mutex_key key_BINLOG_LOCK_index, key_BINLOG_LOCK_prep_xids,
   key_delayed_insert_mutex, key_hash_filo_lock, key_LOCK_active_mi,
   key_LOCK_connection_count, key_LOCK_crypt, key_LOCK_delayed_create,
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1651,12 +1651,16 @@
   if (!mysql_bin_log.is_open())
   {
     TABLE *tmp_next;
+
+    mysql_mutex_lock(&thd->LOCK_temporary_tables);
     for (table= thd->temporary_tables; table; table= tmp_next)
     {
       tmp_next= table->next;
       close_temporary(table, 1, 1);
     }
     thd->temporary_tables= 0;
+    mysql_mutex_unlock(&thd->LOCK_temporary_tables);
+
     DBUG_RETURN(FALSE);
   }
 
@@ -1669,6 +1673,8 @@
 
   memcpy(buf, stub, stub_len);
 
+  mysql_mutex_lock(&thd->LOCK_temporary_tables);
+
   /*
     Insertion sort of temp tables by pseudo_thread_id to build ordered list
     of sublists of equal pseudo_thread_id
@@ -1790,6 +1796,8 @@
     thd->variables.option_bits&= ~OPTION_QUOTE_SHOW_CREATE; /* restore option */
   thd->temporary_tables=0;
 
+  mysql_mutex_unlock(&thd->LOCK_temporary_tables);
+
   DBUG_RETURN(error);
 }
 
@@ -2167,6 +2175,8 @@
                           table->s->db.str, table->s->table_name.str,
                           (long) table, table->alias));
 
+  mysql_mutex_lock(&thd->LOCK_temporary_tables);
+
   if (table->prev)
   {
     table->prev->next= table->next;
@@ -2193,6 +2203,9 @@
     slave_open_temp_tables--;
   }
   close_temporary(table, free_share, delete_table);
+
+  mysql_mutex_unlock(&thd->LOCK_temporary_tables);
+
   DBUG_VOID_RETURN;
 }
 
@@ -5932,6 +5945,7 @@
   if (add_to_temporary_tables_list)
   {
     /* growing temp list at the head */
+    mysql_mutex_lock(&thd->LOCK_temporary_tables);
     tmp_table->next= thd->temporary_tables;
     if (tmp_table->next)
       tmp_table->next->prev= tmp_table;
@@ -5939,6 +5953,7 @@
     thd->temporary_tables->prev= 0;
     if (thd->slave_thread)
       slave_open_temp_tables++;
+    mysql_mutex_unlock(&thd->LOCK_temporary_tables);
   }
   tmp_table->pos_in_table_list= 0;
   DBUG_PRINT("tmptable", ("opened table: '%s'.'%s' 0x%lx", tmp_table->s->db.str,
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -836,6 +836,8 @@
   active_vio = 0;
 #endif
   mysql_mutex_init(key_LOCK_thd_data, &LOCK_thd_data, MY_MUTEX_INIT_FAST);
+  mysql_mutex_init(key_LOCK_temporary_tables, &LOCK_temporary_tables,
+                   MY_MUTEX_INIT_FAST);
 
   /* Variables with default values */
   proc_info="login";
@@ -1348,6 +1350,7 @@
   db= NULL;
   free_root(&transaction.mem_root,MYF(0));
   mysql_mutex_destroy(&LOCK_thd_data);
+  mysql_mutex_destroy(&LOCK_temporary_tables);
 #ifndef DBUG_OFF
   dbug_sentry= THD_SENTRY_GONE;
 #endif  
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -1002,6 +1002,11 @@
     XXX Why are internal temporary tables added to this list?
   */
   TABLE *temporary_tables;
+  /**
+     Protects temporary_tables.
+  */
+  mysql_mutex_t LOCK_temporary_tables;
+
   TABLE *derived_tables;
   /*
     During a MySQL session, one can lock tables in two modes: automatic
--- /dev/null
+++ b/mysql-test/r/percona_show_temp_tables.result
@@ -0,0 +1,58 @@
+drop table if exists t1,t2,t3;
+drop database if exists showtemp;
+create database if not exists showtemp;
+use test;
+create temporary table t1(id int);
+create temporary table t2(id int);
+create temporary table showtemp.t3(id int);
+insert into t1 values(10),(20),(30),(40);
+insert into showtemp.t3 values(999);
+show temporary tables;
+Temp_tables_in_test
+t2
+t1
+show temporary tables from test;
+Temp_tables_in_test
+t2
+t1
+show temporary tables in showtemp;
+Temp_tables_in_showtemp
+t3
+select table_schema, table_name, engine, table_rows from Information_schema.temporary_tables;
+table_schema	table_name	engine	table_rows
+showtemp	t3	MyISAM	1
+test	t2	MyISAM	0
+test	t1	MyISAM	4
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+table_schema	table_name	engine	table_rows
+showtemp	t3	MyISAM	1
+test	t2	MyISAM	0
+test	t1	MyISAM	4
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='showtemp';
+table_schema	table_name	engine	table_rows
+showtemp	t3	MyISAM	1
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='temp';
+table_schema	table_name	engine	table_rows
+drop table if exists showtemp.t2;
+create temporary table t1(id int);
+create temporary table showtemp.t2(id int);
+show temporary tables;
+Temp_tables_in_test
+t1
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+table_schema	table_name	engine	table_rows
+showtemp	t2	MyISAM	0
+test	t1	MyISAM	0
+showtemp	t3	MyISAM	1
+test	t2	MyISAM	0
+test	t1	MyISAM	4
+drop table showtemp.t2;
+drop table t1;
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+table_schema	table_name	engine	table_rows
+showtemp	t3	MyISAM	1
+test	t2	MyISAM	0
+test	t1	MyISAM	4
+drop table t1, t2;
+drop table showtemp.t3;
+drop database showtemp;
--- /dev/null
+++ b/mysql-test/t/percona_show_temp_tables.test
@@ -0,0 +1,65 @@
+# Uses GRANT commands that usually disabled in embedded server
+-- source include/not_embedded.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+#
+# Test of SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN] DB and 
+# Information_schema.temporary_tables and global_temporary_tables
+#
+
+connect(stcon1,localhost,root,,test);
+connect(stcon2,localhost,root,,test);
+
+connection stcon1;
+
+--disable_warnings
+drop table if exists t1,t2,t3;
+drop database if exists showtemp;
+create database if not exists showtemp;
+--enable_warnings
+
+use test;
+create temporary table t1(id int);
+create temporary table t2(id int);
+create temporary table showtemp.t3(id int);
+insert into t1 values(10),(20),(30),(40);
+insert into showtemp.t3 values(999);
+
+show temporary tables;
+# "Session" is not same value always. mysql-test cannot test it always.
+#show global temporary tables;
+show temporary tables from test;
+show temporary tables in showtemp;
+select table_schema, table_name, engine, table_rows from Information_schema.temporary_tables;
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='showtemp';
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='temp';
+
+connection stcon2;
+
+--disable_warnings
+drop table if exists showtemp.t2;
+--enable_warnings
+create temporary table t1(id int);
+create temporary table showtemp.t2(id int);
+show temporary tables;
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+drop table showtemp.t2;
+drop table t1;
+
+disconnect stcon2;
+
+connection stcon1;
+select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
+
+drop table t1, t2;
+drop table showtemp.t3;
+drop database showtemp;
+
+connection default;
+disconnect stcon1;
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc