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';
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';