revision-id: 6dfeeee6fd3f60c53d9afaef13cdf4bc1fa1f6da (mariadb-10.5.2-576-g6dfeeee6fd3) parent(s): a534d27a1c110a868d434741910eafb678a2a0ea author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-12 16:43:45 +0300 message: MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view Table_function_json_table::m_dep_tables attempts to cache the value of m_json->used_tables(), poorly. Remove the cache and use the value directly. --- mysql-test/suite/json/r/json_table.result | 43 +++++++++++++++++++++++++++++++ mysql-test/suite/json/t/json_table.test | 41 +++++++++++++++++++++++++++++ sql/json_table.cc | 20 +------------- sql/json_table.h | 19 +++++++------- 4 files changed, 95 insertions(+), 28 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index d913da18c50..8789f4ced8d 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -750,5 +750,48 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1) drop table t1; # +# MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT, c TEXT); +INSERT INTO t2 VALUES (1,'{}'),(2,'[]'); +CREATE VIEW v2 AS SELECT * FROM t2; +SELECT * +FROM +t1 RIGHT JOIN +t2 AS tt +LEFT JOIN +JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +SELECT * +FROM +t1 RIGHT JOIN +v2 AS tt +LEFT JOIN +JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +SELECT * +FROM +t1 RIGHT JOIN +v2 AS tt +LEFT JOIN +JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt +ON tt.b = jt.o +ON t1.a = tt.b; +a b c o +1 1 {} 1 +2 2 [] NULL +DROP VIEW v2; +DROP TABLE t1, t2; +# # 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 83cbe61b6ef..e91e89ab72f 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -644,6 +644,47 @@ WHERE drop table t1; +--echo # +--echo # MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT, c TEXT); +INSERT INTO t2 VALUES (1,'{}'),(2,'[]'); +CREATE VIEW v2 AS SELECT * FROM t2; + +SELECT * +FROM + t1 RIGHT JOIN + t2 AS tt + LEFT JOIN + JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +SELECT * +FROM + t1 RIGHT JOIN + v2 AS tt + LEFT JOIN + JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt + ON tt.b = jt.o + ON t1.a = tt.b; + +DROP VIEW v2; +DROP TABLE t1, t2; + --echo # --echo # End of 10.6 tests --echo # diff --git a/sql/json_table.cc b/sql/json_table.cc index b89f6fecbd6..e8e50347fa7 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -1324,20 +1324,6 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, jc->m_field->charset= jc->m_explicit_cs; } } - - m_dep_tables= m_json->used_tables(); - - if (m_dep_tables) - { - t->no_cache= TRUE; - if (unlikely(m_dep_tables & sql_table->get_map())) - { - /* Table itself is used in the argument. */ - my_error(ER_WRONG_USAGE, MYF(0), "JSON_TABLE", "argument"); - return TRUE; - } - } - return FALSE; } @@ -1475,12 +1461,8 @@ int Table_function_json_table::print(THD *thd, TABLE_LIST *sql_table, void Table_function_json_table::fix_after_pullout(TABLE_LIST *sql_table, st_select_lex *new_parent, bool merge) { - sql_table->dep_tables&= ~m_dep_tables; - m_json->fix_after_pullout(new_parent, &m_json, merge); - m_dep_tables= m_json->used_tables(); - - sql_table->dep_tables|= m_dep_tables; + sql_table->dep_tables= used_tables(); } diff --git a/sql/json_table.h b/sql/json_table.h index 90fc667731f..04b0e6c07d2 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -208,8 +208,16 @@ class Table_function_json_table : public Sql_alloc st_select_lex *new_parent, bool merge); void update_used_tables() { m_json->update_used_tables(); } - table_map used_tables() const { return m_dep_tables; } - bool join_cache_allowed() const { return !m_dep_tables; } + table_map used_tables() const { return m_json->used_tables(); } + bool join_cache_allowed() const + { + /* + Can use join cache when we have an outside reference. + If there's dependency on any other table or randomness, + cannot use it. + */ + return !(used_tables() & ~OUTER_REF_TABLE_BIT); + } void get_estimates(ha_rows *out_rows, double *scan_time, double *startup_cost); @@ -242,13 +250,6 @@ class Table_function_json_table : public Sql_alloc /* Context to be used for resolving the first argument. */ Name_resolution_context *m_context; - /* - the JSON argument can be taken from other tables. - We have to mark these tables as dependent so the - mask of these dependent tables is calculated in ::setup(). - */ - table_map m_dep_tables; - /* Current NESTED PATH level being parsed */ Json_table_nested_path *cur_parent;