Bug #102536 MySQL 8.0.22 System view wrong result
Submitted: 9 Feb 2:16 Modified: 9 Feb 13:50
Reporter: 송화 SONGHUA Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:MySQL 8.0.22 OS:Linux
Assigned to: CPU Architecture:Any
Tags: Optimize

[9 Feb 2:16] 송화 SONGHUA
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.
[9 Feb 13:50] MySQL Verification Team
Hi Mr. Songhua,

Thank you for your bug report.

However, there are already several bugs that are reported where optimiser does not work properly with I_S on these issue. Those queries worked in 5.7 , but not in 8.0.

Your bug is a duplicate of the following one:

https://bugs.mysql.com/bug.php?id=102298