MDEV-38045: Implement implicit query block names for optimizer hints#4692
MDEV-38045: Implement implicit query block names for optimizer hints#4692
Conversation
1. `TABLE_LIST::init_derived` is called twice during derived tables processing: first time from `mysql_derived_init` and the second time from `mysql_derived_prepare`. Both times there is a check for whether an optimizer hint or switch setting allows a derived table to be merged. However, it is not necessary to restrict merging during the initialization call, it is enough to apply hints/switch setting during the preparation phase. 2. `open_normal_and_derived_tables()` runs processing of all phases of derived tables handling with a single call. But for future commits it is required to separate DT initialization from other phases. This commit implements the separation.
98aace9 to
a1c43dc
Compare
| if (const Qb_name_hint &qb_hint= hint) | ||
| { | ||
| if (qb_hint.resolve(pc)) | ||
| return true; |
There was a problem hiding this comment.
Is it possible (and worthwhile) to exercise this code path? In general, do we have any test cases that exercise what happens when hint resolution fails?
There was a problem hiding this comment.
Hmm, it doesn't look possible because Qb_name_hint::resolve() always returns false. Probably the function could be converted to void but I'd prefer to leave it as is to maintain uniformity with other resolve() functions. In fact, other resolve() functions are also not easy to test in this way, because they return true only in the case of malformed logic (when the switch cannot find a proper hint), and there's also DBUG_ASSERT(0) for such scenarios.
| else if (const Qb_name_hint &qb_hint __attribute__((unused)) = hint) | ||
| { | ||
| // QB_NAME hints have been resolved earlier | ||
| continue; |
There was a problem hiding this comment.
Can we assert here that the hint is resolved?
There was a problem hiding this comment.
Good idea, added an assertion
sql/sql_lex.h
Outdated
| #include "sql_class.h" // enum enum_column_usage | ||
| #include "select_handler.h" | ||
| #include "rpl_master_info_file.h" // Master_info_file | ||
| #include "opt_hints_structs.h" |
There was a problem hiding this comment.
I was able to build "from scratch" without this #include, are you sure we need it?
There was a problem hiding this comment.
Indeed, this include is not needed any more. Removed
sql/sql_lex.h
Outdated
| create_info.set(options); | ||
| return main_select_push() || check_create_options(options); | ||
| } | ||
|
|
There was a problem hiding this comment.
Consider reverting this line and, if the #include above isn't needed, then this file as a whole will have no changes in this commit.
This patch implements support for implicit query block (QB) names in optimizer hints, allowing hints to reference query blocks and tables within derived tables, views and CTEs without requiring explicit QB_NAME hints. Examples. -- Addressing a table inside a derived table using implicit QB name select /*+ no_index(t1@dt) */ * from (select * from t1 where a > 10) as DT; -- this is an equivalent to: select /*+ no_index(t1@dt) */ * from (select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT; -- Addressing a query block corresponding to the derived table select /*+ no_bnl(@dt) */ * from (select * from t1, t2 where t.1.a > t2.a) as DT; -- View create view v1 as select * from t1 where a > 10 and b > 100; -- referencing a table inside a view by implicit QB name: select /*+ index_merge(t1@v1 idx_a, idx_b) */ * from v1, t2 where v1.a = t2.a; -- equivalent to: create view v1 as select /*+ qb_name(qb_v1) */ * from t1 where a > 10 and b > 100; select /*+ index_merge(t1@qb_v1 idx_a, idx_b) */ * from v1, t2 where v1.a = t2.a; -- CTE with aless100 as (select a from t1 where b <100) select /*+ index(t1@aless100) */ * from aless100; -- equivalent to: with aless100 as (select /*+ qb_name(aless100) */ a from t1 where b <100) select /*+ index(t1@aless100) */ * from aless100; Limitations: - Only SELECT statements support implicit QB names. DML operations (UPDATE, DELETE, INSERT) only support explicit QB names
a1c43dc to
1fb571d
Compare
DaveGosselin-MariaDB
left a comment
There was a problem hiding this comment.
LGTM but please let @spetrunia have a look before submitting.
| @brief | ||
| Perform "Hint Resolution" for Optimizer Hints (see opt_hints.h for | ||
| definition) | ||
| definition) for both early and late stages. |
There was a problem hiding this comment.
There's no more early or late stage distinction, please update the comment.
This patch implements support for implicit query block (QB) names in
optimizer hints, allowing hints to reference query blocks and tables
within derived tables, views and CTEs without requiring explicit
QB_NAME hints.
Examples.
Limitations:
(UPDATE, DELETE, INSERT) only support explicit QB names