Hi Alexey,
diff --git a/sql/opt_table_elimination.cc b/sql/opt_table_elimination.cc index 3958797ec44..f2497d524ec 100644 --- a/sql/opt_table_elimination.cc +++ b/sql/opt_table_elimination.cc @@ -637,6 +637,16 @@ void eliminate_tables(JOIN *join) List_iterator<Item> it(join->fields_list); while ((item= it++)) used_tables |= item->used_tables(); + + { + List_iterator
it(*join->join_list); + TABLE_LIST *tbl; + while ((tbl= it++)) + { + if (tbl->table_function) + used_tables|= tbl->table_function->used_tables(); + } + }
This only walks the tables that are "at the top level" of the join. if a JSON_TABLE(...) is inside a nested outer join, it will not be found. Please walk select_lex->leaf_tables instead. Please add the testcase (I provide one below). Please also add a comment, clarifying what is being done, something like: Table function JSON_TABLE() can have references to other tables. Do not eliminate the tables that JSON_TABLE() refers to. Note: the JSON_TABLE itself cannot be eliminated as it doesn't have unique keys. == Testcase == create table t20 (a int not null); create table t21 (a int not null primary key, js varchar(100)); insert into t20 select seq from seq_1_to_100; insert into t21 select a, '{"a":100}' from t20; create table t31(a int); create table t32(b int); insert into t31 values (1); insert into t32 values (1); explain select t20.a, jt1.ab from t20 left join t21 on t20.a=t21.a join (t31 left join (t32 join JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) AS jt1) on t31.a<3); BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net