Description:
In the Server version: 8.0.22 MySQL Community Server - GPL
the optimize parsing SQL is wrong .
How to repeat:
Server version: 8.0.22 MySQL Community Server - GPL
I want to get the none primary key table list
first i run the SQL .it returns all table list .
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE';
+--------------+------------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+
| employees | departments |
| employees | dept2 |
| employees | dept_emp |
| employees | dept_emp2 |
| employees | dept_emp3 |
| employees | dept_manager |
| employees | emp1 |
| employees | emp2 |
| employees | emp3 |
| employees | employees |
| employees | salaries |
| employees | salaries2 |
| employees | salaries3 |
| employees | salaries4_up_20w |
| employees | t0522 |
| employees | t1 |
| employees | t11 |
| employees | t11_1 |
| employees | t12 |
| employees | t4 |
| employees | t_g1 |
| employees | t_group |
| employees | t_group2 |
| employees | t_group21 |
| employees | t_group3 |
| employees | t_group4 |
| employees | t_group5 |
| employees | t_group6 |
| employees | t_order |
| employees | t_time |
| employees | test1 |
| employees | titles |
| employees | txt_t1 |
| test | clone_progress |
| test | customer |
| test | district |
| test | history |
| test | item |
| test | new_orders |
| test | order_line |
| test | orders |
| test | stock |
| test | t1 |
| test | t2 |
| test | t3 |
| test | tb_task |
| test | tb_task_order |
| test | warehouse |
+--------------+------------------+
48 rows in set (0.00 sec)
2. this SQL returns primary key table list.
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
+--------------+---------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+---------------+
| employees | departments |
| employees | dept_emp |
| employees | dept_manager |
| employees | emp1 |
| employees | emp3 |
| employees | employees |
| employees | salaries |
| employees | salaries3 |
| employees | t1 |
| employees | t11 |
| employees | t12 |
| employees | t_group2 |
| employees | t_group21 |
| employees | t_group6 |
| employees | t_time |
| employees | titles |
| test | customer |
| test | district |
| test | item |
| test | new_orders |
| test | order_line |
| test | orders |
| test | stock |
| test | t1 |
| test | t3 |
| test | tb_task |
| test | tb_task_order |
| test | warehouse |
+--------------+---------------+
28 rows in set (0.00 sec)
3. use NOT IN i wana to get finally results.
SELECT
a.*, b.*
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE'
) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
b.TABLE_NAME IS NULL;
Empty set (0.00 sec)
In 5.7 runs the same SQL.
Server version: 5.7.28-log MySQL Community Server (GPL)
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE';
+--------------+-------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+-------------------------+
| employees | departments |
| employees | dept2 |
| employees | dept_emp |
| employees | dept_emp2 |
| employees | dept_emp3 |
| employees | dept_manager |
| employees | emp1 |
| employees | emp2 |
| employees | emp3 |
| employees | employees |
| employees | salaries |
| employees | salaries2 |
| employees | salaries3 |
| employees | salaries4_up_20w |
| employees | salaries5 |
| employees | salaries6 |
| employees | t1 |
| employees | t11 |
| employees | t11_1 |
| employees | t4 |
| employees | t_g1 |
| employees | t_group |
| employees | t_group2 |
| employees | t_group3 |
| employees | t_group4 |
| employees | t_group5 |
| employees | t_order |
| employees | t_time |
| employees | test1 |
| employees | titles |
| employees | tmp1 |
| employees | txt_t1 |
| test | clone_progress |
| test | disk_free |
| test | l |
| test | l2 |
| test | t1 |
| test | t2 |
| test | t_msg_history_2017_06_1 |
| test | tb_task |
| test | tb_task_order |
| test | ts_lock |
+--------------+-------------------------+
42 rows in set (0.00 sec)
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
+--------------+-------------------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+-------------------------+
| employees | departments |
| employees | dept_emp |
| employees | dept_manager |
| employees | emp1 |
| employees | emp3 |
| employees | employees |
| employees | salaries |
| employees | salaries3 |
| employees | salaries5 |
| employees | salaries6 |
| employees | t1 |
| employees | t11 |
| employees | t_time |
| employees | titles |
| test | disk_free |
| test | l |
| test | l2 |
| test | t_msg_history_2017_06_1 |
| test | tb_task |
| test | tb_task_order |
| test | ts_lock |
+--------------+-------------------------+
21 rows in set (0.00 sec)
SELECT
a.*, b.*
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE'
) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
b.TABLE_NAME IS NULL;
+--------------+------------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+--------------+------------+
| employees | dept2 | NULL | NULL |
| employees | dept_emp2 | NULL | NULL |
| employees | dept_emp3 | NULL | NULL |
| employees | emp2 | NULL | NULL |
| employees | salaries2 | NULL | NULL |
| employees | salaries4_up_20w | NULL | NULL |
| employees | t11_1 | NULL | NULL |
| employees | t4 | NULL | NULL |
| employees | t_g1 | NULL | NULL |
| employees | t_group | NULL | NULL |
| employees | t_group2 | NULL | NULL |
| employees | t_group3 | NULL | NULL |
| employees | t_group4 | NULL | NULL |
| employees | t_group5 | NULL | NULL |
| employees | t_order | NULL | NULL |
| employees | test1 | NULL | NULL |
| employees | tmp1 | NULL | NULL |
| employees | txt_t1 | NULL | NULL |
| test | clone_progress | NULL | NULL |
| test | t1 | NULL | NULL |
| test | t2 | NULL | NULL |
+--------------+------------------+--------------+------------+
21 rows in set (0.01 sec)
In 5.7
show warnings ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
root@mysql3357.sock>[employees]>show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`t`.`TABLE_NAME` AS `TABLE_NAME`,`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`c`.`TABLE_NAME` AS `TABLE_NAME`
from `information_schema`.`tables` `t`
left join (`information_schema`.`table_constraints` `c`) on(((`c`.`TABLE_NAME` = `t`.`TABLE_NAME`) and (`c`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA`)
and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`c`.`CONSTRAINT_TYPE` = 'PRIMARY KEY')))
where (isnull(`c`.`TABLE_NAME`) and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`t`.`TABLE_TYPE` = 'BASE TABLE'))
1 row in set (0.00 sec)
There is the isnull(`c`.`TABLE_NAME`)
8.0
+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
| 1 | PRIMARY | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | sch | NULL | ref | PRIMARY,catalog_id | catalog_id | 8 | mysql.cat.id | 6 | 100.00 | Using where; Using index |
| 1 | PRIMARY | tbl | NULL | ref | schema_id,type,type_2 | schema_id | 8 | mysql.sch.id | 47 | 67.78 | Using where |
| 1 | PRIMARY | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | Using index |
| 1 | PRIMARY | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | Using index |
| 1 | PRIMARY | stat | NULL | eq_ref | PRIMARY | PRIMARY | 388 | mysql.sch.name,mysql.tbl.name | 1 | 100.00 | Using where; Using index |
| 1 | PRIMARY | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index |
| 1 | PRIMARY | sch | NULL | eq_ref | PRIMARY,catalog_id | catalog_id | 202 | mysql.cat.id,func | 1 | 100.00 | Using where; Using index |
| 1 | PRIMARY | tbl | NULL | eq_ref | schema_id | schema_id | 202 | mysql.sch.id,func | 1 | 100.00 | Using where; Rematerialize (<derived6>) |
| 1 | PRIMARY | <derived6> | NULL | ref | <auto_key0> | <auto_key0> | 35 | const | 1 | 100.00 | NULL |
| 6 | DEPENDENT DERIVED | idx | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 1 | 40.00 | Using where |
| 7 | DEPENDENT UNION | fk | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 2 | 100.00 | Using index |
| 8 | DEPENDENT UNION | cc | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 1 | 100.00 | NULL |
+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
13 rows in set, 5 warnings (0.00 sec)
Message: /* select#1 */ select (`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`mysql`.`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`
,(`mysql`.`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`
from `mysql`.`tables` `tbl`
join `mysql`.`schemata` `sch`
join `mysql`.`catalogs` `cat`
left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`))
left join `mysql`.`tablespaces` `ts` on((`ts`.`id` = `tbl`.`tablespace_id`))
left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`)))
left join (`mysql`.`tables` `tbl`
join `mysql`.`schemata` `sch`
join `mysql`.`catalogs` `cat`
join lateral (/* select#6 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED`
from `mysql`.`indexes` `idx`
where ((`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,`mysql`.`idx`.`hidden`)))
union all /* select#7 */ select (`mysql`.`fk`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED`
from `mysql`.`foreign_keys` `fk` where (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`)
union all /* select#8 */ select `mysql`.`cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`mysql`.`cc`.`enforced` AS `ENFORCED`
from `mysql`.`check_constraints` `cc`
where (`mysql`.`cc`.`table_id` = `mysql`.`tbl`.`id`)) `constraints`) on(((`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`)
and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`constraints`.`CONSTRAINT_TYPE` = 'PRIMARY KEY')
and ((`mysql`.`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema'))
and ((`sch`.`name` collate utf8_tolower_ci) = (`mysql`.`sch`.`name` collate utf8_tolower_ci))
and ((`tbl`.`name` collate utf8_tolower_ci) = (`mysql`.`tbl`.`name` collate utf8_tolower_ci)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`))
and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`type` = 'BASE TABLE')
and ((`mysql`.`tbl`.`name` collate utf8_tolower_ci) is null) and ((`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema'))
and (0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
5 rows in set (0.00 sec)
In 8.0.22 There are no about TABLE_NAME IS NULL;
set session optimizer_switch='derived_merge=off'
Message: /* select#1 */ select `a`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`a`.`TABLE_NAME` AS `TABLE_NAME`,`b`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`b`.`TABLE_NAME` AS `TABLE_NAME`
from (/* select#2 */ select `information_schema`.`t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`t`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`TABLES` `t`) `a`
left join (/* select#3 */ select `information_schema`.`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`c`.`TABLE_NAME` AS `TABLE_NAME`
from `information_schema`.`TABLE_CONSTRAINTS` `c`) `b` on(((`b`.`TABLE_NAME` = `a`.`TABLE_NAME`) and (`b`.`TABLE_SCHEMA` = `a`.`TABLE_SCHEMA`)))
where (`b`.`TABLE_NAME` is null)
5 rows in set (0.00 sec)
+--------------+------------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+--------------+------------+
| employees | dept2 | NULL | NULL |
| employees | dept_emp2 | NULL | NULL |
| employees | dept_emp3 | NULL | NULL |
| employees | emp2 | NULL | NULL |
| employees | salaries2 | NULL | NULL |
| employees | salaries4_up_20w | NULL | NULL |
| employees | t0522 | NULL | NULL |
| employees | t11_1 | NULL | NULL |
| employees | t4 | NULL | NULL |
| employees | t_g1 | NULL | NULL |
| employees | t_group | NULL | NULL |
| employees | t_group3 | NULL | NULL |
| employees | t_group4 | NULL | NULL |
| employees | t_group5 | NULL | NULL |
| employees | t_order | NULL | NULL |
| employees | test1 | NULL | NULL |
| employees | txt_t1 | NULL | NULL |
| test | clone_progress | NULL | NULL |
| test | history | NULL | NULL |
| test | t2 | NULL | NULL |
+--------------+------------------+--------------+------------+
20 rows in set (0.00 sec)
Thanks.
Description: In the Server version: 8.0.22 MySQL Community Server - GPL the optimize parsing SQL is wrong . How to repeat: Server version: 8.0.22 MySQL Community Server - GPL I want to get the none primary key table list first i run the SQL .it returns all table list . SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES t WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND TABLE_TYPE = 'BASE TABLE'; +--------------+------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+------------------+ | employees | departments | | employees | dept2 | | employees | dept_emp | | employees | dept_emp2 | | employees | dept_emp3 | | employees | dept_manager | | employees | emp1 | | employees | emp2 | | employees | emp3 | | employees | employees | | employees | salaries | | employees | salaries2 | | employees | salaries3 | | employees | salaries4_up_20w | | employees | t0522 | | employees | t1 | | employees | t11 | | employees | t11_1 | | employees | t12 | | employees | t4 | | employees | t_g1 | | employees | t_group | | employees | t_group2 | | employees | t_group21 | | employees | t_group3 | | employees | t_group4 | | employees | t_group5 | | employees | t_group6 | | employees | t_order | | employees | t_time | | employees | test1 | | employees | titles | | employees | txt_t1 | | test | clone_progress | | test | customer | | test | district | | test | history | | test | item | | test | new_orders | | test | order_line | | test | orders | | test | stock | | test | t1 | | test | t2 | | test | t3 | | test | tb_task | | test | tb_task_order | | test | warehouse | +--------------+------------------+ 48 rows in set (0.00 sec) 2. this SQL returns primary key table list. SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' +--------------+---------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+---------------+ | employees | departments | | employees | dept_emp | | employees | dept_manager | | employees | emp1 | | employees | emp3 | | employees | employees | | employees | salaries | | employees | salaries3 | | employees | t1 | | employees | t11 | | employees | t12 | | employees | t_group2 | | employees | t_group21 | | employees | t_group6 | | employees | t_time | | employees | titles | | test | customer | | test | district | | test | item | | test | new_orders | | test | order_line | | test | orders | | test | stock | | test | t1 | | test | t3 | | test | tb_task | | test | tb_task_order | | test | warehouse | +--------------+---------------+ 28 rows in set (0.00 sec) 3. use NOT IN i wana to get finally results. SELECT a.*, b.* FROM ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES t WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND TABLE_TYPE = 'BASE TABLE' ) AS a LEFT JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' ) AS b USING (TABLE_SCHEMA, TABLE_NAME) WHERE b.TABLE_NAME IS NULL; Empty set (0.00 sec) In 5.7 runs the same SQL. Server version: 5.7.28-log MySQL Community Server (GPL) SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES t WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND TABLE_TYPE = 'BASE TABLE'; +--------------+-------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+-------------------------+ | employees | departments | | employees | dept2 | | employees | dept_emp | | employees | dept_emp2 | | employees | dept_emp3 | | employees | dept_manager | | employees | emp1 | | employees | emp2 | | employees | emp3 | | employees | employees | | employees | salaries | | employees | salaries2 | | employees | salaries3 | | employees | salaries4_up_20w | | employees | salaries5 | | employees | salaries6 | | employees | t1 | | employees | t11 | | employees | t11_1 | | employees | t4 | | employees | t_g1 | | employees | t_group | | employees | t_group2 | | employees | t_group3 | | employees | t_group4 | | employees | t_group5 | | employees | t_order | | employees | t_time | | employees | test1 | | employees | titles | | employees | tmp1 | | employees | txt_t1 | | test | clone_progress | | test | disk_free | | test | l | | test | l2 | | test | t1 | | test | t2 | | test | t_msg_history_2017_06_1 | | test | tb_task | | test | tb_task_order | | test | ts_lock | +--------------+-------------------------+ 42 rows in set (0.00 sec) SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' +--------------+-------------------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+-------------------------+ | employees | departments | | employees | dept_emp | | employees | dept_manager | | employees | emp1 | | employees | emp3 | | employees | employees | | employees | salaries | | employees | salaries3 | | employees | salaries5 | | employees | salaries6 | | employees | t1 | | employees | t11 | | employees | t_time | | employees | titles | | test | disk_free | | test | l | | test | l2 | | test | t_msg_history_2017_06_1 | | test | tb_task | | test | tb_task_order | | test | ts_lock | +--------------+-------------------------+ 21 rows in set (0.00 sec) SELECT a.*, b.* FROM ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES t WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND TABLE_TYPE = 'BASE TABLE' ) AS a LEFT JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLE_CONSTRAINTS c WHERE TABLE_SCHEMA NOT IN ( 'mysql', 'sys', 'information_schema', 'performance_schema' ) AND CONSTRAINT_TYPE = 'PRIMARY KEY' ) AS b USING (TABLE_SCHEMA, TABLE_NAME) WHERE b.TABLE_NAME IS NULL; +--------------+------------------+--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_SCHEMA | TABLE_NAME | +--------------+------------------+--------------+------------+ | employees | dept2 | NULL | NULL | | employees | dept_emp2 | NULL | NULL | | employees | dept_emp3 | NULL | NULL | | employees | emp2 | NULL | NULL | | employees | salaries2 | NULL | NULL | | employees | salaries4_up_20w | NULL | NULL | | employees | t11_1 | NULL | NULL | | employees | t4 | NULL | NULL | | employees | t_g1 | NULL | NULL | | employees | t_group | NULL | NULL | | employees | t_group2 | NULL | NULL | | employees | t_group3 | NULL | NULL | | employees | t_group4 | NULL | NULL | | employees | t_group5 | NULL | NULL | | employees | t_order | NULL | NULL | | employees | test1 | NULL | NULL | | employees | tmp1 | NULL | NULL | | employees | txt_t1 | NULL | NULL | | test | clone_progress | NULL | NULL | | test | t1 | NULL | NULL | | test | t2 | NULL | NULL | +--------------+------------------+--------------+------------+ 21 rows in set (0.01 sec) In 5.7 show warnings ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases | | 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+ 2 rows in set, 1 warning (0.01 sec) root@mysql3357.sock>[employees]>show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select `t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`t`.`TABLE_NAME` AS `TABLE_NAME`,`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`c`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`tables` `t` left join (`information_schema`.`table_constraints` `c`) on(((`c`.`TABLE_NAME` = `t`.`TABLE_NAME`) and (`c`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA`) and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`c`.`CONSTRAINT_TYPE` = 'PRIMARY KEY'))) where (isnull(`c`.`TABLE_NAME`) and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`t`.`TABLE_TYPE` = 'BASE TABLE')) 1 row in set (0.00 sec) There is the isnull(`c`.`TABLE_NAME`) 8.0 +----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+ | 1 | PRIMARY | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | PRIMARY | sch | NULL | ref | PRIMARY,catalog_id | catalog_id | 8 | mysql.cat.id | 6 | 100.00 | Using where; Using index | | 1 | PRIMARY | tbl | NULL | ref | schema_id,type,type_2 | schema_id | 8 | mysql.sch.id | 47 | 67.78 | Using where | | 1 | PRIMARY | col | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.collation_id | 1 | 100.00 | Using index | | 1 | PRIMARY | ts | NULL | eq_ref | PRIMARY | PRIMARY | 8 | mysql.tbl.tablespace_id | 1 | 100.00 | Using index | | 1 | PRIMARY | stat | NULL | eq_ref | PRIMARY | PRIMARY | 388 | mysql.sch.name,mysql.tbl.name | 1 | 100.00 | Using where; Using index | | 1 | PRIMARY | cat | NULL | index | PRIMARY | name | 194 | NULL | 1 | 100.00 | Using index | | 1 | PRIMARY | sch | NULL | eq_ref | PRIMARY,catalog_id | catalog_id | 202 | mysql.cat.id,func | 1 | 100.00 | Using where; Using index | | 1 | PRIMARY | tbl | NULL | eq_ref | schema_id | schema_id | 202 | mysql.sch.id,func | 1 | 100.00 | Using where; Rematerialize (<derived6>) | | 1 | PRIMARY | <derived6> | NULL | ref | <auto_key0> | <auto_key0> | 35 | const | 1 | 100.00 | NULL | | 6 | DEPENDENT DERIVED | idx | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 1 | 40.00 | Using where | | 7 | DEPENDENT UNION | fk | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 2 | 100.00 | Using index | | 8 | DEPENDENT UNION | cc | NULL | ref | table_id | table_id | 8 | mysql.tbl.id | 1 | 100.00 | NULL | +----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+ 13 rows in set, 5 warnings (0.00 sec) Message: /* select#1 */ select (`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`mysql`.`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA` ,(`mysql`.`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME` from `mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) left join `mysql`.`tablespaces` `ts` on((`ts`.`id` = `tbl`.`tablespace_id`)) left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`))) left join (`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` join `mysql`.`catalogs` `cat` join lateral (/* select#6 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`indexes` `idx` where ((`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,`mysql`.`idx`.`hidden`))) union all /* select#7 */ select (`mysql`.`fk`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` from `mysql`.`foreign_keys` `fk` where (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`) union all /* select#8 */ select `mysql`.`cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`mysql`.`cc`.`enforced` AS `ENFORCED` from `mysql`.`check_constraints` `cc` where (`mysql`.`cc`.`table_id` = `mysql`.`tbl`.`id`)) `constraints`) on(((`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`) and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`constraints`.`CONSTRAINT_TYPE` = 'PRIMARY KEY') and ((`mysql`.`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) and ((`sch`.`name` collate utf8_tolower_ci) = (`mysql`.`sch`.`name` collate utf8_tolower_ci)) and ((`tbl`.`name` collate utf8_tolower_ci) = (`mysql`.`tbl`.`name` collate utf8_tolower_ci)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`)) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`type` = 'BASE TABLE') and ((`mysql`.`tbl`.`name` collate utf8_tolower_ci) is null) and ((`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) and (0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`))) 5 rows in set (0.00 sec) In 8.0.22 There are no about TABLE_NAME IS NULL; set session optimizer_switch='derived_merge=off' Message: /* select#1 */ select `a`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`a`.`TABLE_NAME` AS `TABLE_NAME`,`b`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`b`.`TABLE_NAME` AS `TABLE_NAME` from (/* select#2 */ select `information_schema`.`t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`t`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`TABLES` `t`) `a` left join (/* select#3 */ select `information_schema`.`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`c`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`TABLE_CONSTRAINTS` `c`) `b` on(((`b`.`TABLE_NAME` = `a`.`TABLE_NAME`) and (`b`.`TABLE_SCHEMA` = `a`.`TABLE_SCHEMA`))) where (`b`.`TABLE_NAME` is null) 5 rows in set (0.00 sec) +--------------+------------------+--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_SCHEMA | TABLE_NAME | +--------------+------------------+--------------+------------+ | employees | dept2 | NULL | NULL | | employees | dept_emp2 | NULL | NULL | | employees | dept_emp3 | NULL | NULL | | employees | emp2 | NULL | NULL | | employees | salaries2 | NULL | NULL | | employees | salaries4_up_20w | NULL | NULL | | employees | t0522 | NULL | NULL | | employees | t11_1 | NULL | NULL | | employees | t4 | NULL | NULL | | employees | t_g1 | NULL | NULL | | employees | t_group | NULL | NULL | | employees | t_group3 | NULL | NULL | | employees | t_group4 | NULL | NULL | | employees | t_group5 | NULL | NULL | | employees | t_order | NULL | NULL | | employees | test1 | NULL | NULL | | employees | txt_t1 | NULL | NULL | | test | clone_progress | NULL | NULL | | test | history | NULL | NULL | | test | t2 | NULL | NULL | +--------------+------------------+--------------+------------+ 20 rows in set (0.00 sec) Thanks.