Sergei Petrunia added a comment - 2025-03-05 20:04 Maybe, using something like this select json_detailed(json_extract(trace, '$**.original_condition' )) AS JS from information_schema.optimizer_trace; select trace from information_schema.optimizer_trace; would produce shorter and more readable output? It shows original_condition from both selects without telling which is which (not perfect) but at least it doesn't show equality_propagation steps.

Oleg Smirnov added a comment - 2025-03-10 14:17 I believe we're supposed to generate warnings about wrong derived tables names? --source include/have_sequence.inc   create table t1 select seq as i, 10*seq as j from seq_1_to_10; create view v1 as select * from t1; set session optimizer_switch= 'derived_merge=off' ;   explain extended SELECT /*+ merge(wrong_name) */ a, b FROM ( SELECT /*+ merge(wrong_name) */ 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; For some reason I can't see any warning here.

Dave Gosselin added a comment - 2025-03-10 14:52 oleg.smirnov maybe this is related to the TABLE_LIST hint API that I added, I will check.

Dave Gosselin added a comment - 2025-03-13 20:59 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)

Oleg Smirnov added a comment - 2025-03-14 12:59 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.