TiDB does not show non default hash partitions in show create table

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `thash` (
  `id` bigint unsigned NOT NULL,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
PARTITION BY HASH (`id`)
(PARTITION pEven COMMENT = "Even ids",
 PARTITION pOdd COMMENT = "Odd ids");
SHOW CREATE TABLE thash;
SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,PARTITION_COMMENT /*T! , TIDB_PLACEMENT_POLICY_NAME, TIDB_DIRECT_PLACEMENT */ FROM information_schema.partitions WHERE TABLE_NAME = 'thash';

2. What did you expect to see? (Required)

mysql> CREATE TABLE `thash` (
    ->   `id` bigint unsigned NOT NULL,
    ->   `data` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> )
    -> PARTITION BY HASH (`id`)
    -> (PARTITION pEven COMMENT = "Even ids",
    ->  PARTITION pOdd COMMENT = "Odd ids");
Query OK, 0 rows affected (0,02 sec)

mysql> SHOW CREATE TABLE thash;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| thash | CREATE TABLE `thash` (
  `id` bigint unsigned NOT NULL,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`id`)
(PARTITION pEven COMMENT = 'Even ids' ENGINE = InnoDB,
 PARTITION pOdd COMMENT = 'Odd ids' ENGINE = InnoDB) */ |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,PARTITION_COMMENT /*T! , TIDB_PLACEMENT_POLICY_NAME, TIDB_DIRECT_PLACEMENT */ FROM information_schema.partitions WHERE TABLE_NAME = 'thash';
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+
| TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | PARTITION_COMMENT |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+
| thash      | pOdd           |                          2 | HASH             | `id`                 | NULL                  | Odd ids           |
| thash      | pEven          |                          1 | HASH             | `id`                 | NULL                  | Even ids          |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+
2 rows in set (0,00 sec)

Apart from the MySQL specifics.

3. What did you see instead (Required)

tidb> CREATE TABLE `thash` (
    ->   `id` bigint unsigned NOT NULL,
    ->   `data` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> )
    -> PARTITION BY HASH (`id`)
    -> (PARTITION pEven COMMENT = "Even ids",
    ->  PARTITION pOdd COMMENT = "Odd ids");
Query OK, 0 rows affected (0,01 sec)

tidb> SHOW CREATE TABLE thash;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| thash | CREATE TABLE `thash` (
  `id` bigint(20) unsigned NOT NULL,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH( `id` )
PARTITIONS 2 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

tidb> SELECT TABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,PARTITION_COMMENT /*T! , TIDB_PLACEMENT_POLICY_NAME, TIDB_DIRECT_PLACEMENT */ FROM information_schema.partitions WHERE TABLE_NAME = 'thash';
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+----------------------------+-----------------------+
| TABLE_NAME | PARTITION_NAME | PARTITION_ORDINAL_POSITION | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION | PARTITION_COMMENT | TIDB_PLACEMENT_POLICY_NAME | TIDB_DIRECT_PLACEMENT |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+----------------------------+-----------------------+
| thash      | pEven          |                          1 | HASH             | `id`                 |                       | Even ids          | NULL                       | NULL                  |
| thash      | pOdd           |                          2 | HASH             | `id`                 |                       | Odd ids           | NULL                       | NULL                  |
+------------+----------------+----------------------------+------------------+----------------------+-----------------------+-------------------+----------------------------+-----------------------+
2 rows in set (0,00 sec)

Here the names are missing from SHOW CREATE TABLE thash, but it did record the info as seen in the information_schema.partitions table.

4. What is your TiDB version? (Required)

Release Version: v5.4.0-alpha-150-g4fad89c46
Edition: Community
Git Commit Hash: 4fad89c
Git Branch: master
UTC Build Time: 2021-11-18 09:00:54
GoVersion: go1.16.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[sylzd]

/assign

[mjonss]

Also if trying different placement rules like 'PARTITION pEven PRIMARY_REGION = "us-west-1" REGIONS = "us-west-1,us-west-2"' it does not show up in information_schema.partitions, so I'm not sure it is stored or not? Also not sure if that is a separate bug or part of this bug.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.