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

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
@@ -543,6 +543,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 dcd55a8860c..68161ca688d 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 0275a821cf3..42ab81048ec 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -6101,7 +6101,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)
@@ -6193,9 +6194,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);
@@ -6404,8 +6407,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)
@@ -6475,7 +6479,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,
@@ -6492,8 +6497,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 *