maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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<TABLE_LIST> 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