revision-id: 00be4236ebb2ee64bf0438842164616cfb349ae8 (mariadb-10.5.2-575-g00be4236ebb) parent(s): f5f6af6bee4271f48c76a5b3724a974e426a5c70 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-12 16:18:21 +0300 message: MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails The query used subquery of this form: SELECT ... WHERE EXISTS( SELECT ... FROM JSON_TABLE(outer_ref, ..) as JT WHERE trivial_correlation_cond) EXISTS-to-IN conversion code was unable to see that the subquery will still be correlated after the trivial_correlation is removed, which eventually caused a crash due to unability to construct a query plan. Fixed by making Item_subselect::walk() also walk Table Functions's arguments. --- mysql-test/suite/json/r/json_table.result | 16 +++++++++++++++ mysql-test/suite/json/t/json_table.test | 17 ++++++++++++++++ sql/item_subselect.cc | 33 +++++++++++++++++++++++++++++++ sql/json_table.cc | 6 ++++++ sql/json_table.h | 3 +++ 5 files changed, 75 insertions(+) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index 9f1d1bce99e..ed9f5f01026 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -734,5 +734,21 @@ Shirt blue 20 Shirt white 20 drop table t1; # +# MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails +# +CREATE TABLE t1 (a INT, b TEXT); +INSERT INTO t1 VALUES (1,'{}'),(2,'[]'); +explain +SELECT * +FROM t1 +WHERE +EXISTS(SELECT * +FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt +WHERE jt.o = t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1) +drop table t1; +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index aa4e7397a6a..83cbe61b6ef 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -627,6 +627,23 @@ select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"col drop table t1; +--echo # +--echo # MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails +--echo # + +CREATE TABLE t1 (a INT, b TEXT); +INSERT INTO t1 VALUES (1,'{}'),(2,'[]'); + +explain +SELECT * +FROM t1 +WHERE + EXISTS(SELECT * + FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt + WHERE jt.o = t1.a); + +drop table t1; + --echo # --echo # End of 10.6 tests --echo # diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 76b09efec17..e4e8bf2a1e8 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -719,6 +719,34 @@ bool Item_subselect::unknown_splocal_processor(void *argument) } +static +int walk_table_functions_for_list(Item_processor processor, + bool walk_subquery, void *argument, + List<TABLE_LIST>& join_list) +{ + List_iterator<TABLE_LIST> li(join_list); + int res; + while (TABLE_LIST *table= li++) + { + if (Table_function_json_table *tf= table->table_function) + { + if ((res= tf->walk_items(processor, walk_subquery, argument))) + { + return res; + } + } + if (table->nested_join) + { + if ((res= walk_table_functions_for_list(processor, walk_subquery, + argument, + table->nested_join->join_list))) + return res; + } + } + return 0; +} + + bool Item_subselect::walk(Item_processor processor, bool walk_subquery, void *argument) { @@ -751,6 +779,11 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, argument)) return 1; /* TODO: why does this walk WHERE/HAVING but not ON expressions of outer joins? */ + /* Consider walking ON epxression in walk_table_functions_for_list */ + + if (walk_table_functions_for_list(processor, walk_subquery, argument, + *lex->join_list)) + return 1; while ((item=li++)) { diff --git a/sql/json_table.cc b/sql/json_table.cc index dae1c694d78..b89f6fecbd6 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1341,6 +1341,12 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, return FALSE; } +int Table_function_json_table::walk_items(Item_processor processor, + bool walk_subquery, void *argument) +{ + return m_json->walk(processor, walk_subquery, argument); +} + void Table_function_json_table::get_estimates(ha_rows *out_rows, double *scan_time, double *startup_cost) diff --git a/sql/json_table.h b/sql/json_table.h index 07d53c55638..90fc667731f 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -200,6 +200,9 @@ class Table_function_json_table : public Sql_alloc /*** Name resolution functions ***/ int setup(THD *thd, TABLE_LIST *sql_table, SELECT_LEX *s_lex); + int walk_items(Item_processor processor, bool walk_subquery, + void *argument); + /*** Functions for interaction with the Query Optimizer ***/ void fix_after_pullout(TABLE_LIST *sql_table, st_select_lex *new_parent, bool merge);