Bug #79554 Missing hints for derived tables

• Submitted: 8 Dec 2015 9:18
• Modified: 7 Sep 2016 13:56
• Reporter: Roy Lyseng
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.9
• OS: Any
• CPU Architecture: Any

[8 Dec 2015 9:18] Roy Lyseng

Description:
It is now possible to merge derived tables into the outer query.
But this is an unconditional process, when the merge is decided
to be possible. Hints for controlling whether to merge or materialize
the derived table are missing.

This is less of a problem for views, since views can be created with
an "algorithm" that tells whether to merge or materialize the view.

How to repeat:
create table t1(a int);
select *
from t1 join (select /* missing hint here */ *
              from t1) as t2
     on t1.a = t2.a;

Suggested fix:
N/A

[7 Sep 2016 13:56] Paul DuBois

Posted by developer:
 
Noted in 8.0.0 changelog.

The optimizer now supports table-level hints for specifying whether
derived tables or views should be merged into the outer query block
rather than materialized using an internal temporary table. Examples:

SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;