Bug #89473 Inconsistent metadata from derived tables usage.

• Submitted: 30 Jan 2018 16:56
• Modified: 30 Jan 2018 18:14
• Reporter: Filipe Silva
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.21
• OS: Any
• CPU Architecture: Any

[30 Jan 2018 16:56] Filipe Silva

Description:
Hi,

The subqueries materialization feature introduced in 5.7.6 seems to be causing some metadata inconsistency between very similar queries. As a result, connectors may return wrong (or unexpected) metadata information to client application.

See also Bug#89442 for implications in Connector/J.

See also related Bug#79641, fixed in MySQL 5.7.17.

I tested MySQL 5.7.21 and MySQL 8.0.4 and both deliver the same results.

How to repeat:
mysql> create table tbl (id INT);
Query OK, 0 rows affected (0,02 sec)

mysql> SELECT t2.id FROM (SELECT id FROM tbl) t2;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `t2`
Org_table:  `tbl`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM 

0 rows in set (0,00 sec)
# NOTES:
# The metadata correctly indicates Database = `Test` and Org_table = `tbl`
# This is the expected behavior in all following cases

mysql> SELECT t2.id FROM (SELECT id FROM tbl t1) t2;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `t2`
Org_table:  `t1`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM 

0 rows in set (0,00 sec)
# NOTES:
# Just by adding an alias to table in the sub-query - "t1", the metadata now returns Org_table = `t1`
# Database has the same value as before.

mysql> SELECT t2.id FROM (SELECT DISTINCT t1.id FROM tbl t1) t2;
Field   1:  `id`
Catalog:    `def`
Database:   ``
Table:      `t2`
Org_table:  `tbl`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NUM 

0 rows in set (0,00 sec)
# NOTES:
# By including the DISTINCT clause, the Org_table is now returned as in the first case
# But the Database identification is now empty
# We get the same metadata with or without the "t1" alias.

Suggested fix:
If possible, all three queries should return the same metadata as in first query.