Description:
When you disable derived_merge in the optimizer switch, the information_schema.TABLES view for base tables will start to include table comments about a view not being a base table:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config';
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT: 'sys.statements_with_temp_tables' is not BASE TABLE
1 row in set (0.03 sec)
How to repeat:
1. Perform clean install of 8.0.13 (also reproduced with 8.0.16) using default configuration.
2. Connect to MySQL and query information_schema.TABLES for the sys.sys_config table:
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT:
1 row in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT:
1 row in set (0.00 sec)
3. Disable the derived_merge optimizer switch and try again:
mysql> SET optimizer_switch='derived_merge=off'; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT:
1 row in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT: 'sys.statements_with_temp_tables' is not BASE TABLE
1 row in set (0.03 sec)
4. Enable derived_merge again and query information_schema.TABLES again:
mysql> SET optimizer_switch='derived_merge=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT:
1 row in set (0.00 sec)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G
*************************** 1. row ***************************
TABLE_SCHEMA: sys
TABLE_NAME: sys_config
TABLE_TYPE: BASE TABLE
TABLE_COMMENT:
1 row in set (0.00 sec)
Description: When you disable derived_merge in the optimizer switch, the information_schema.TABLES view for base tables will start to include table comments about a view not being a base table: mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'; *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 'sys.statements_with_temp_tables' is not BASE TABLE 1 row in set (0.03 sec) How to repeat: 1. Perform clean install of 8.0.13 (also reproduced with 8.0.16) using default configuration. 2. Connect to MySQL and query information_schema.TABLES for the sys.sys_config table: mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 1 row in set (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 1 row in set (0.00 sec) 3. Disable the derived_merge optimizer switch and try again: mysql> SET optimizer_switch='derived_merge=off'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 1 row in set (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 'sys.statements_with_temp_tables' is not BASE TABLE 1 row in set (0.03 sec) 4. Enable derived_merge again and query information_schema.TABLES again: mysql> SET optimizer_switch='derived_merge=on'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 1 row in set (0.00 sec) mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sys' AND TABLE_NAME = 'sys_config'\G *************************** 1. row *************************** TABLE_SCHEMA: sys TABLE_NAME: sys_config TABLE_TYPE: BASE TABLE TABLE_COMMENT: 1 row in set (0.00 sec)