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@mariadb.com> 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