maria-developers team mailing list archive

[Alexey Botchkov <holyfoot@xxxxxxxxxxx>]

Ok, fixed that as you said.
See the last commit to the bb-10.6-mdev17399-hf for details

Best regards.
HF

On Tue, Feb 23, 2021 at 3:57 PM Sergey Petrunia <sergey@xxxxxxxxxxx> wrote:

> On Mon, Feb 22, 2021 at 09:59:54PM +0300, Sergey Petrunia wrote:
> > == On table dependencies: table elimination ==
> >
> > create table t20 (a int not null);
> > create table t21 (a int not null primary key, js varchar(100));
> >
> > insert into t20 values (1),(2);
> > insert into t21 values (1, '{"a":100}');
> >
> > explain
> > select
> >   t20.a, jt1.ab
> > from
> >   t20
> >   left join t21 on t20.a=t21.a
> >   join JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) AS jt1;
> >
> >
> +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
> > | id   | select_type | table | type | possible_keys | key  | key_len |
> ref  | rows | Extra                      |
> >
> +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
> > |    1 | SIMPLE      | t20   | ALL  | NULL          | NULL | NULL    |
> NULL | 2    |                            |
> > |    1 | SIMPLE      | jt1   | ALL  | NULL          | NULL | NULL    |
> NULL | 40   | Table function: json_table |
> >
> +------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
> >
> > Here we can see an apparently invalid query plan: table t21 was
> eleminated
> > even if JSON_TABLE uses it.
>
> We can do with a simple rule: "do not eliminate tables that are used as
> argument for any table function".
>
> I think this should be achieved as follows: in eliminate_tables(), look
> at the code that collects a bitmap of tables used in the select list (to
> prevent them from being eliminated).
>
>   /* Add tables referred to from the select list */
>   List_iterator<Item> it(join->fields_list);
>   while ((item= it++))
>     used_tables |= item->used_tables();
>
> Right below this, add a loop which does the same for table functions.
>
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
>
>
>