Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28453

SHOW commands are inconsistent for temporary tables

    XMLWordPrintable

Details

    Description

      even though MDEV-28334 JIRA entry is still in review, apparently the fix is already in the branch, as the behavior has changed. It still doesn't seem to be consistent though, so if it's intentional, then it needs to be documented at least.

      drop temporary table if exists t;
      drop table if exists t;
      create table t (a int, key(a)) engine=Aria;
      create temporary table t (b int, key(b)) engine=MyISAM;
      show tables;
      show full tables;
      show table status;
      show columns in t;
      show index in t;
       
      # Cleanup
      drop temporary table t;
      drop table t;
      

      In the above, show tables shows one table:

      bb-10.9-anel 32274c3e8a4

      MariaDB [test]> show tables;
      +----------------+
      | Tables_in_test |
      +----------------+
      | t              |
      +----------------+
      

      We can't know for sure which it is. show full tables shows the base table (so probably show tables did too):

      MariaDB [test]> show full tables;
      +----------------+------------+
      | Tables_in_test | Table_type |
      +----------------+------------+
      | t              | BASE TABLE |
      +----------------+------------+
      1 row in set (0.001 sec)
      

      show table status shows both tables:

      MariaDB [test]> 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 |
      +------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------+---------+--------------------+-----------+
      | t    | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 1970324836974591 |         1024 |         0 |           NULL | 2022-05-02 14:55:09 | 2022-05-02 14:55:09 | NULL       | latin1_swedish_ci |     NULL |                 |         | 288230376151710720 | Y         |
      | t    | Aria   |      10 | Page       |    0 |              0 |        8192 |   17592186011648 |         8192 |         0 |           NULL | 2022-05-02 14:55:09 | 2022-05-02 14:55:09 | NULL       | latin1_swedish_ci |     NULL | transactional=1 |         |   9007199254732800 | N         |
      +------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+-----------------+---------+--------------------+-----------+
      

      show columns and show index show the temporary table:

      MariaDB [test]> show columns in t;
      +-------+---------+------+-----+---------+-------+
      | Field | Type    | Null | Key | Default | Extra |
      +-------+---------+------+-----+---------+-------+
      | b     | int(11) | YES  | MUL | NULL    |       |
      +-------+---------+------+-----+---------+-------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> 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      |
      +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
      1 row in set (0.001 sec)
      

      Attachments

        Issue Links

          Activity

            People

              anel Anel Husakovic
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.