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

              Assignee:
              monty Michael Widenius
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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