maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Alexey,

First reactions to the JSON_TABLE patch:

== CI Build is failing ==

Please check

http://buildbot.askmonty.org/buildbot/grid?category=main&branch=bb-10.6-mdev17399-hf

the JSON_TABLE tests are failing.

== On testcases ==

I see now the patch has tests in 3 files:

1. mysql-test/main/json_table_mysql.test
2. mysql-test/main/json_table_mysql2.test
3. mysql-test/suite/json/t/json_table.test

#1 and #2 are both fixed copies of MySQL's test. Please remove one of them (the
one that has less coverage).
Then, can we have both files in the same testsuite?

== On Table_function_json_table::ready_for_lookup() ==

Now that we have end_lateral_table, do we need to use
Table_function_json_table::ready_for_lookup? This looks like two solutions for
[almost] the same problem?

I see that this example from json_table_mysql.test still uses the code path
with ready_for_lookup() : 

--error ER_BAD_FIELD_ERROR
UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
  SET jt1.a=1;

Here, the name resolution for "jt1.a" is done without adjustment for
end_lateral_table.

Do we need the ready_for_lookup() trick here?  (If we do, are we fine with
m_setup_done to be set to true but never reset back to false? How will this
work with PS?)

== On opt_sum.cc ==

The diff has this:

diff --git a/sql/opt_sum.cc b/sql/opt_sum.cc
index af2d9ddc2e7..d021c651196 100644
--- a/sql/opt_sum.cc
+++ b/sql/opt_sum.cc
@@ -299,6 +299,7 @@ int opt_sum_query(THD *thd,
         tl->schema_table)
     {
       maybe_exact_count&= MY_TEST(!tl->schema_table &&
+//mdev-17399                      !tl->table_function &&
                                   (tl->table->file->ha_table_flags() &
                                    HA_HAS_RECORDS));
       is_exact_count= FALSE;

Please remove this if it is no longer needed.

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

== On table dependencies: regular subqueries ==

But it doesn't work for regular subqueries either (and this is unexpected):

insert into t21 values (3, '{"a":300}');

explain
select 
  a, 
  (select jt1.ab 
   from JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) as jt1
  )
from t21;

+------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                      |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+
|    1 | PRIMARY     | t21   | ALL  | NULL          | NULL | NULL    | NULL | 2    |                            |
|    2 | SUBQUERY    | jt1   | ALL  | NULL          | NULL | NULL    | NULL | 40   | Table function: json_table |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------------------+

Note the 'SUBQUERY', that is, the subquery is considered uncorrelated? 
This needs to be investigated.

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net