New-style Optimizer Hints: milestones 3.1 and 3.2

Implicit Query Block Names like in MySQL

Support addressing Query Blocks by their implicit name: select#N. Support is limited to the top-level query.
The following is not supported:

• addressing SELECTs inside VIEWs
• defining query block names in VIEWs.

TODO: If there is a non-recursive CTE that's used multiple times, the CTE will map into multiple SELECT objects with different numbers. Do we support addressing all of the SELECT objects? (I suspect we might support only the first one).

MySQL allows one to address query blocks by their names in form select#N

Lifting the example from MDEV-33281:

mysql>  explain  

 select /*+ JOIN_ORDER(@"select#2" twenty,ten) */  * 

 from 

    (select "ten"."a" AS "a" 

      from ("ten" join "twenty") 

      where ("ten"."a" = "twenty"."a") limit 1000 ) T;

Note: the '#' seems to make the command-line client think the rest of the line is a comment.

• MySQL doesn't allow implicit name addressing inside the VIEWs.
• (TiDB uses SEL_n names which look to be more user-friendly.)

MDEV-37513 will allow all SELECTs (including those inside VIEWs) to be addressed by their implicit names. This allows to do troubleshooting without altering (or copy-pasting) VIEW definitions. That is out-of-scope for this ticket.

Support Local Hints inside VIEWs

It is a challenge to name or refer to query blocks inside VIEWs.
But we could support hints that just address their current query block.

CREATE VIEW v1 AS

  SELECT /*+ IGNORE_INDEX(idx1) */ FROM t1 ... GROUP BY ... HAVING ...

In many cases, VIEWs have "optimization barriers", that is, for some parts of the VIEW the query plan doesn't really depend on how the VIEW is used. In that case, it makes sense to control a part of the query plan from the view definition.