New-style Optimizer Hints: milestone 3

This MDEV covers hints features that are not yet implemented. These features will be addressed across multiple development sprints.

Milestones

MS 3.1: Implicit Query Block Names like in MySQL

See MDEV-37511 for details.

MS 3.2: Support Local Hints inside VIEWs

See MDEV-37511 for details.

MS 3.3: Hint Pushdown into VIEWs

See MDEV-37512 for details.

MS 3.4: Make it possible to use Hints with VIEWs that have no QB_NAMEs.

See MDEV-37513 for details.

Ancillary Tasks

MySQL hints that are not supported

Need to check if it makes sense to support them.

• HASH_JOIN, NO_HASH_JOIN: it's better to not use those until we get something hash-join-like?
• SET_VAR: Can be implemented for compatibility.
• RESOURCE_GROUP: cannot be done as MariaDB doesn't support resource groups.

Check if we need more hints for optimizations

We've had MDEV-26485 "Add flexible optimizer hints support for every setting in the optimizer_switch and more" (which we have closed).

A list of optimizer_switch flags which do not have a matched hint:

derived_with_keys=on,

partial_match_rowid_merge=on,partial_match_table_scan=on,

subquery_cache=on,

outer_join_with_cache=on,

semijoin_with_cache=on,

join_cache_incremental=on,

join_cache_hashed=on,

join_cache_bka=on,

optimize_join_buffer_size=on,

table_elimination=on,

extended_keys=on,

not_null_range_scan=off,

hash_join_cardinality=on,

cset_narrowing=on,

sargable_casefold=on

Probably it doesn't make sense to add a hint for every setting. Most of these flags are not ever touched. Need to go through the list and check if we need something to be implemented still?