revision-id: 7cd264346f188cad6e781d87f379b1145000f28e (mariadb-10.5.2-570-g7cd264346f1) parent(s): afada16d0bdaac63e514ea963104a67dd21ae644 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-08 20:21:34 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set Followup: also handle NATURAL JOIN, extend the new approach with Name_resolution_context::ignored_tables --- mysql-test/suite/json/r/json_table.result | 17 +++++++++++++++++ mysql-test/suite/json/t/json_table.test | 21 +++++++++++++++++++++ sql/sql_acl.cc | 2 +- sql/sql_base.cc | 21 +++++++++++++-------- sql/sql_base.h | 4 ++-- 5 files changed, 54 insertions(+), 11 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index de93d55087d..da4eed6150e 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -658,6 +658,23 @@ json_table((select concat(a,js) from t2), ERROR 42S22: Unknown column 'js' in 'field list' drop table t1,t2; # +# Now, a testcase with JSON_TABLEs inside NATURAL JOIN +# +create table t1 (a int, b int); +create table t2 (a int, c int); +select * from +t1, +( t2 +natural join +( +json_table(JT2.d, '$' COLUMNS (d for ordinality)) as JT +natural join +json_table(JT.d, '$' COLUMNS (d for ordinality)) as JT2 +) +); +ERROR 42S22: Unknown column 'JT2.d' in 'JSON_TABLE argument' +drop table t1, t2; +# # MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view # SELECT * FROM diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 2520c1c97ba..37a88f1d1ee 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -542,6 +542,27 @@ from drop table t1,t2; +--echo # +--echo # Now, a testcase with JSON_TABLEs inside NATURAL JOIN +--echo # + +create table t1 (a int, b int); +create table t2 (a int, c int); + +--error ER_BAD_FIELD_ERROR +select * from + t1, + ( t2 + natural join + ( + json_table(JT2.d, '$' COLUMNS (d for ordinality)) as JT + natural join + json_table(JT.d, '$' COLUMNS (d for ordinality)) as JT2 + ) + ); + +drop table t1, t2; + --echo # --echo # MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view --echo # diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc index 3785732b6a8..8f096f4074d 100644 --- a/sql/sql_acl.cc +++ b/sql/sql_acl.cc @@ -6966,7 +6966,7 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list, Field *f=find_field_in_table_ref(thd, table_list, column->column.ptr(), column->column.length(), column->column.ptr(), NULL, NULL, - NULL, TRUE, FALSE, + table_map(0), NULL, TRUE, FALSE, &unused_field_idx, FALSE, &dummy); if (unlikely(f == (Field*)0)) { diff --git a/sql/sql_base.cc b/sql/sql_base.cc index c30c94fb799..a859f83c5f7 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6097,7 +6097,8 @@ Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, Item **ref, + const char *table_name, table_map ignored_tables, + Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, bool register_tree_change, TABLE_LIST **actual_table) @@ -6189,9 +6190,11 @@ find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, TABLE_LIST *table; while ((table= it++)) { + if (table->table && (table->table->map & ignored_tables)) + continue; if ((fld= find_field_in_table_ref(thd, table, name, length, item_name, - db_name, table_name, ref, - check_privileges, allow_rowid, + db_name, table_name, ignored_tables, + ref, check_privileges, allow_rowid, cached_field_index_ptr, register_tree_change, actual_table))) DBUG_RETURN(fld); @@ -6400,8 +6403,9 @@ find_field_in_tables(THD *thd, Item_ident *item, } else found= find_field_in_table_ref(thd, table_ref, name, length, item->name.str, - NULL, NULL, ref, check_privileges, - TRUE, &(item->cached_field_index), + NULL, NULL, ignored_tables, ref, + check_privileges, TRUE, + &(item->cached_field_index), register_tree_change, &actual_table); if (found) @@ -6471,7 +6475,8 @@ find_field_in_tables(THD *thd, Item_ident *item, continue; Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, - item->name.str, db, table_name, ref, + item->name.str, db, table_name, + ignored_tables, ref, (thd->lex->sql_command == SQLCOM_SHOW_FIELDS) ? false : check_privileges, @@ -6488,8 +6493,8 @@ find_field_in_tables(THD *thd, Item_ident *item, thd->clear_error(); cur_field= find_field_in_table_ref(thd, cur_table, name, length, - item->name.str, db, table_name, ref, - false, + item->name.str, db, table_name, + ignored_tables, ref, false, allow_rowid, &(item->cached_field_index), register_tree_change, diff --git a/sql/sql_base.h b/sql/sql_base.h index 765bf426b45..1836c07497a 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -202,8 +202,8 @@ Field * find_field_in_table_ref(THD *thd, TABLE_LIST *table_list, const char *name, size_t length, const char *item_name, const char *db_name, - const char *table_name, Item **ref, - bool check_privileges, bool allow_rowid, + const char *table_name, table_map ignored_tables, + Item **ref, bool check_privileges, bool allow_rowid, uint *cached_field_index_ptr, bool register_tree_change, TABLE_LIST **actual_table); Field *