Bug #102949 values of TABLE_CONSTRAINTS are wrong when it is joined with STATISTICS
Submitted: 12 Mar 2021 10:29 Modified: 9 Apr 2021 5:43
Reporter: Kouhei Yanagita Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2021 10:29] Kouhei Yanagita
Description:
When information_schema.statistics is joined on table_constraints,
values of table_constraints.constraint_name are not correct.

mysql> USE demo;
Database changed

mysql> CREATE TABLE `a` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `id_a` int NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `index_id_a` (`id_a`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE `b` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `id_b` int NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   UNIQUE KEY `index_id_b` (`id_b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> USE information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT s.table_name, s.column_name, s.index_name FROM statistics s WHERE s.table_schema = 'demo';
+------------+-------------+------------+
| TABLE_NAME | COLUMN_NAME | INDEX_NAME |
+------------+-------------+------------+
| a          | id          | PRIMARY    |
| a          | id_a        | index_id_a |
| b          | id          | PRIMARY    |
| b          | id_b        | index_id_b |
+------------+-------------+------------+
4 rows in set (0.00 sec)

mysql> SELECT tc.table_name, tc.constraint_name FROM table_constraints tc WHERE tc.table_schema = 'demo';
+------------+-----------------+
| TABLE_NAME | CONSTRAINT_NAME |
+------------+-----------------+
| a          | index_id_a      |
| a          | PRIMARY         |
| b          | index_id_b      |
| b          | PRIMARY         |
+------------+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT s.table_name, s.column_name, s.index_name, tc.table_name, tc.constraint_name FROM statistics s LEFT OUTER JOIN table_constraints tc ON tc.table_schema = s.table_schema WHERE s.table_schema = 'demo';
+------------+-------------+------------+------------+-----------------+
| TABLE_NAME | COLUMN_NAME | INDEX_NAME | TABLE_NAME | CONSTRAINT_NAME |
+------------+-------------+------------+------------+-----------------+
| a          | id          | PRIMARY    | a          | index_id_a      |
| a          | id          | PRIMARY    | a          | PRIMARY         |
| a          | id          | PRIMARY    | b          | index_id_a      |
| a          | id          | PRIMARY    | b          | PRIMARY         |
| a          | id_a        | index_id_a | a          | index_id_a      |
| a          | id_a        | index_id_a | a          | PRIMARY         |
| a          | id_a        | index_id_a | b          | index_id_a      |
| a          | id_a        | index_id_a | b          | PRIMARY         |
| b          | id          | PRIMARY    | a          | index_id_a      |
| b          | id          | PRIMARY    | a          | PRIMARY         |
| b          | id          | PRIMARY    | b          | index_id_a      |
| b          | id          | PRIMARY    | b          | PRIMARY         |
| b          | id_b        | index_id_b | a          | index_id_a      |
| b          | id_b        | index_id_b | a          | PRIMARY         |
| b          | id_b        | index_id_b | b          | index_id_a      |
| b          | id_b        | index_id_b | b          | PRIMARY         |
+------------+-------------+------------+------------+-----------------+
16 rows in set (0.00 sec)

index_id_b in CONSTRAINT_NAME is changed with index_id_a.

When I try with MySQL 5.7.33, the values are correct.

mysql> SELECT s.table_name, s.column_name, s.index_name, tc.table_name, tc.constraint_name FROM statistics s LEFT OUTER JOIN table_constraints tc ON tc.table_schema = s.table_schema WHERE s.table_schema = 'demo';
+------------+-------------+------------+------------+-----------------+
| table_name | column_name | index_name | table_name | constraint_name |
+------------+-------------+------------+------------+-----------------+
| a          | id          | PRIMARY    | a          | PRIMARY         |
| a          | id_a        | index_id_a | a          | PRIMARY         |
| b          | id          | PRIMARY    | a          | PRIMARY         |
| b          | id_b        | index_id_b | a          | PRIMARY         |
| a          | id          | PRIMARY    | a          | index_id_a      |
| a          | id_a        | index_id_a | a          | index_id_a      |
| b          | id          | PRIMARY    | a          | index_id_a      |
| b          | id_b        | index_id_b | a          | index_id_a      |
| a          | id          | PRIMARY    | b          | PRIMARY         |
| a          | id_a        | index_id_a | b          | PRIMARY         |
| b          | id          | PRIMARY    | b          | PRIMARY         |
| b          | id_b        | index_id_b | b          | PRIMARY         |
| a          | id          | PRIMARY    | b          | index_id_b      |
| a          | id_a        | index_id_a | b          | index_id_b      |
| b          | id          | PRIMARY    | b          | index_id_b      |
| b          | id_b        | index_id_b | b          | index_id_b      |
+------------+-------------+------------+------------+-----------------+
16 rows in set (0.01 sec)

How to repeat:
CREATE DATABASE demo;
USE demo;

CREATE TABLE `a` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_a` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_id_a` (`id_a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `b` (
  `id` int NOT NULL AUTO_INCREMENT,
  `id_b` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_id_b` (`id_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

USE information_schema;

SELECT s.table_name, s.column_name, s.index_name FROM statistics s WHERE s.table_schema = 'demo';
SELECT tc.table_name, tc.constraint_name FROM table_constraints tc WHERE tc.table_schema = 'demo';
SELECT s.table_name, s.column_name, s.index_name, tc.table_name, tc.constraint_name FROM statistics s LEFT OUTER JOIN table_constraints tc ON tc.table_schema = s.table_schema WHERE s.table_schema = 'demo';
[12 Mar 2021 11:22] MySQL Verification Team
Hello Kouhei,

Thank you for the report and feedback.

regards,
Umesh
[9 Apr 2021 5:43] Erlend Dahl
Duplicate of

Bug#101460 Wrong result produced when left joining information_schema tables

(Fixed in the upcoming 8.0.24 release)