Bug #94468 Optimizer_Switch derived_merge=off causes table comments "... is not BASE TABLE"
Submitted: 26 Feb 2019 3:58 Modified: 21 May 2019 17:12
Reporter: Jesper wisborg Krogh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.13 OS:Any
Assigned to: CPU Architecture:Any

[26 Feb 2019 3:58] Jesper wisborg Krogh
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)
[21 May 2019 17:12] Paul DuBois
Posted by developer:
 
Fixed in 8.0.17.

With the derived_merge switch disabled in the value of the
optimizer_switch system variable, information retrieved for base
tables from INFORMATION_SCHEMA.TABLES displayed inappropriate
information in the TABLE_COMMENT column.