Details
-
New Feature
-
Status: In Review (View Workflow)
-
Critical
-
Resolution: Unresolved
-
Q2/2025 Development
Description
Implement table-level hints:
MERGE, NO_MERGE
|
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN
|
in the same way they're done in MySQL 8. See MySQL docs at:
https://dev.mysql.com/doc/refman/8.4/en/derived-condition-pushdown-optimization.html
https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html#optimizer-hints-table-level
The logic for enabling the hints is expected to be very basic - hook at the same place where the check for optimizer_switch is, override the optimizer_switch setting.
The code to base on is MDEV-35504, look at other similar level hints and copy the approach.
Attachments
Issue Links
- split from
-
MDEV-33281 Implement optimizer hints like in MySQL 8
-
- In Progress
-
Activity
oleg.smirnov we indeed produce warnings for the wrong_name example you gave above. But we don't include the wrong name hints in the Note message for the query. That's because the hints are stored by table name and when we fix the hints, we look them up only by the table alias, which must be known because it is associated with a TABLE instance. We don't find the wrong names at that point because they don't have TABLEs that exist, so they are not fixed. Later, during warning generation, we skip any unfixed hints (see Opt_hints::print).
MariaDB [test]> explain extended SELECT /*+ no_derived_condition_pushdown(wrong_name) */a, b FROM (SELECT i as a, j as b FROM (select i*10 as i, j*5 as j from v1) dt_in) AS dt_out WHERE a < 3 AND b > 8;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
1 row in set, 2 warnings (5.091 sec)
|
|
|
MariaDB [test]> show warnings\G
|
*************************** 1. row ***************************
|
Level: Warning
|
Code: 4209
|
Message: Unresolved table name `wrong_name`@`select#1` for NO_DERIVED_CONDITION_PUSHDOWN hint
|
*************************** 2. row ***************************
|
Level: Note
|
Code: 1003
|
Message: select `test`.`t1`.`i` * 10 AS `a`,`test`.`t1`.`j` * 5 AS `b` from `test`.`t1` where `test`.`t1`.`i` * 10 < 3 and `test`.`t1`.`j` * 5 > 8
|
2 rows in set (0.001 sec)
|
We can alter this behavior by removing this check from the start of Opt_hints::print:
/* Do not print the hint if we couldn't attach it to its object */
|
if (!is_fixed())
|
return;
|
If we do that, then we generate warnings like this:
MariaDB [test]> explain extended SELECT /*+ no_derived_condition_pushdown(wrong_name) */a, b FROM (SELECT i as a, j as b FROM (select i*10 as i, j*5 as j from v1) dt_in) AS dt_out WHERE a < 3 AND b > 8;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
1 row in set, 2 warnings (0.010 sec)
|
|
|
MariaDB [test]> show warnings\G
|
*************************** 1. row ***************************
|
Level: Warning
|
Code: 4209
|
Message: Unresolved table name `wrong_name`@`select#1` for NO_DERIVED_CONDITION_PUSHDOWN hint
|
*************************** 2. row ***************************
|
Level: Note
|
Code: 1003
|
Message: select /*+ NO_DERIVED_CONDITION_PUSHDOWN(`wrong_name`@`select#1`) */ `test`.`t1`.`i` * 10 AS `a`,`test`.`t1`.`j` * 5 AS `b` from `test`.`t1` where `test`.`t1`.`i` * 10 < 3 and `test`.`t1`.`j` * 5 > 8
|
2 rows in set (0.001 sec)
|
Gosselin, it's fine that we don't print unresolved or invalid hints in the expanded query text (warning level "Note"). No need to change this behavior.
Looking at this piece of code:
void Opt_hints_qb::fix_hints_for_table(TABLE_LIST *table_list) |
{
|
Opt_hints_table *tab= static_cast<Opt_hints_table *>(find_by_name(table_list->alias)); |
...
|
if (is_fixed()) |
{
|
if (tab && !tab->is_fixed()) |
{
|
...
|
table_list->opt_hints_qb= this; |
table_list->opt_hints_table= tab;
|
==> set_fixed(Opt_hints::Fixed_state::DELAYED);
|
}
|
...
|
Are you setting this `delayed` state for the whole query block? Shouldn't it be set at the table level? There may be more than one derived table in a query block and I'm not sure the logic will handle it correctly.
For example, query
explain extended SELECT /*+ NO_MERGE(dt) NO_MERGE(wrong_name)*/ * FROM |
(SELECT i as a, j as b FROM t1) AS dt, (SELECT i as a, j as b FROM t1) AS dt2 |
WHERE dt.a = dt2.a and dt.b = dt2.b AND dt.a < 3 AND dt.b > 8; |
does not produce warning about missing `wrong_name` table.
I am also thinking whether we can always fix table-level hints against `TABLE_LIST` instead of `TABLE`. One obvious problem is we need `TABLE::key_info` to resolve index-level hints and it's not clear how to get to it from `TABLE_LIST`...
Hey oleg.smirnov, thank you for your thoughtful feedback. To address your concern, I want to mention a comment at the top of that code block you cited which I've copied here for convenience:
This instance will have been marked as fixed on the basis of its
|
attachment to a SELECT_LEX (during get_qb_hints) but that is
|
insufficient to consider it fixed for the case where a TABLE
|
instance is required but not yet available. If the associated
|
table isn't yet fixed, then reset this instance's fixed value to
|
DELAYED. Later, during fix_hints_for_table(TABLE*), the hint and
|
its corresponding TABLE instance will be marked with fixed=FIXED.
|
To expand on that comment a bit:
- opt_hints_table has not yet been created at the point where it is needed at call-sites (see table.cc:10116 where this new method fix_hints_for_table is called)
- opt_hints_qb does exist and can be used to temporarily fix hints for the table, by setting them to the DELAYED state where they'll be properly fixed later
- temporarily fixing hints relies on TABLE_LIST which does not (as you correctly pointed out) have everything needed to avoid putting hints into the DELAYED state
- A post-condition of Opt_hints_qb::fix_hints_for_table is that Opt_hints_qb instances will be in the DELAYED state, unless they're fixed and their corresponding Opt_hints_table instance exists and is also fixed.
Before my change, fixing hints for tables relied on Opt_hints_qb::fix_hints_for_table(TABLE*) overload implementation. My new overload for that method, Opt_hints_qb::fix_hints_for_table(TABLE_LIST*) is implemented at the same layer in the abstraction. Table-level hint fixing is also done on Opt_hints_qb instance (see the setup_tables function in sql_base.cc). There, in setup_tables, we check if query block hints are initialized and if there is no opt_hints_table, then create the new opt_hints_table instance by calling fix_hints_for_table. I'm doing the same, but adding to the condition that the opt_hints_table instance may not exist or if it does, that hints were delayed.
A consequence of my approach with TABLE_LIST appears to be the missing warning that you point out. Maybe that can be fixed? Or maybe it is a limitation of the hints architecture? I'm looking to see how we can solve that problem.
oleg.smirnov maybe this is related to the TABLE_LIST hint API that I added, I will check.