maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

I've rewritten it using the json->select_lex->leaf_tables as you suggested.
So the JSON_TABLE is calculated properly and all the dependencies seem to
be marked.
Still your testcase query fails the same way, so needs some more
investigation. To me it
looks more like optimizer issue than the JSON_TABLE-s.

Best regards.
HF


On Fri, Mar 12, 2021 at 2:21 AM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:

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