[MDEV-28334] SHOW TABLE STATUS shows all temporary tables ignoring database and conditions Created: 2022-04-17  Updated: 2023-12-08  Resolved: 2023-08-12

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Data Definition - Temporary
Affects Version/s: N/A
Fix Version/s: 11.2.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-12459 The information_schema tables for get... Closed
Relates
relates to MDEV-32973 SHOW TABLES LIKE shows temporary tabl... In Review

 Description    

create temporary table test.tmp_in_test (a int);
 
create database db;
 
use db;
 
# This should show tables from currently used DB and it is 'db'  - no temporary tables should be visible
 
show table status;
 
# The same as before
 
show table status in db;
 
# This shouldn't give any results
 
show table status like 'nonexisting';
 
 
 
# Cleanup
 
drop database db;

preview-10.9-MDEV-20119-misc c906db30

 
# This should be empty (since db is used)
 
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_in_test	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	2022-04-17 23:46:11	2022-04-17 23:46:11	NULL	latin1_swedish_ci	NULL			17179868160	Y
 
 
 
# This should be empty
 
show table status in db;
 
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_in_test	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	2022-04-17 23:46:11	2022-04-17 23:46:11	NULL	latin1_swedish_ci	NULL			17179868160	Y
 
 
 
# This should be empty
 
show table status like 'nonexisting';
 
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_in_test	MyISAM	10	Fixed	0	0	0	1970324836974591	1024	0	NULL	2022-04-17 23:46:11	2022-04-17 23:46:11	NULL	latin1_swedish_ci	NULL			17179868160	Y

Note that show tables is not exposing temporary tables, only non-temporary tables.
show table status should show temporary tables as an extension to show tables using temporary column.

 Comments   
Comment by Anel Husakovic [ 2022-04-18 ]

For show table status the code fill_schema_table_by_open() should be executed from int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond), but it is not since there is no base table and lsel->table_list.first == NULL

if (lsel && lsel->table_list.first)
 
  {
 
    error= fill_schema_table_by_open(thd, thd->mem_root, TRUE,
 
                                     table, schema_table,
 
                                     &lsel->table_list.first->db,
 
                                     &lsel->table_list.first->table_name,
 
                                     &open_tables_state_backup,
 
                                     can_deadlock);
 
    goto err;
 
  }

In prepare_schema_table we cannot populate lsel->table_list.first, it is meant only for base table, or we should now do it to accept temporary tables too?
Additionally https://mariadb.com/kb/en/show-table-status/ needs to be changed (non-temporary to temporary).

Comment by Elena Stepanova [ 2022-04-19 ]

While working on this, please determine how select from I_S.tables, show table status [in ...] and show tables [in ...] should correlate in regard to temporary tables, and maybe add a note about it to MDEV-12459 description, especially if it's anything but obvious.
I would expect all of them to show the same set of tables, but in addition to the difference above which is clearly a bug, show tables returns yet another set (no temporary tables at all), so it's definitely not obvious at the moment.

Comment by Anel Husakovic [ 2022-04-27 ]

For last check review: https://github.com/MariaDB/server/commit/b98aad19f52861e6c80893e26d360d3b8221fa34

Generated at Thu Feb 08 09:59:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.