maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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