MDEV-25230 JSON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed.

Handle multiple NESTED paths.

4 files changed, 44 insertions, 2 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 928756859a4..bf8d46183b1 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -543,5 +543,16 @@ CREATE TABLE t1 AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(a CHAR(16) PATH '$
 DROP TABLE t1;
 SET NAMES default;
 #
+# MDEV-25230 SON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed.
+#
+CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$' COLUMNS(NESTED PATH '$.*' COLUMNS(o FOR ORDINALITY)))) AS jt;
+SELECT * FROM v;
+o
+NULL
+SHOW CREATE VIEW v;
+View	Create View	character_set_client	collation_connection
+v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`o` AS `o` from JSON_TABLE('{}', '$' COLUMNS (NESTED PATH '$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (`o` FOR ORDINALITY)))) `jt`	latin1	latin1_swedish_ci
+DROP VIEW v;
+#
 # 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 35344c02e1d..aaec4341147 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -422,5 +422,14 @@ SET NAMES default;
 
 
 --echo #
+--echo # MDEV-25230 SON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed.
+--echo #
+
+CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$' COLUMNS(NESTED PATH '$.*' COLUMNS(o FOR ORDINALITY)))) AS jt;
+SELECT * FROM v;
+SHOW CREATE VIEW v;
+DROP VIEW v;
+
+--echo #
 --echo # End of 10.6 tests
 --echo #
diff --git a/sql/json_table.cc b/sql/json_table.cc
index 6f6c270137a..55425027c6f 100644
--- a/sql/json_table.cc
+++ b/sql/json_table.cc
@@ -1197,6 +1197,26 @@ void Table_function_json_table::get_estimates(ha_rows *out_rows,
 
 
 /*
+  Check if a column belongs to the nested path
+  or a path that nested into it.
+  It only supposed to be used in the Json_table_nested_path::print, and
+  since the nested path should have at least one field we
+  don't have to loop through the m_next_nested.
+*/
+bool Json_table_nested_path::column_in_this_or_nested(
+    const Json_table_column *jc) const
+{
+  const Json_table_nested_path *p;
+  for (p= this; p; p= p->m_nested)
+  {
+    if (jc->m_nest == p)
+      return TRUE;
+  }
+  return FALSE;
+}
+
+
+/*
   Print the string representation of the Json_nested_path object.
   Which is the COLUMNS(...) part of the JSON_TABLE definition.
  
@@ -1223,7 +1243,8 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str,
     return 1;
 
   /* loop while jc belongs to the current or nested paths. */
-  while(jc && (jc->m_nest == c_path || jc->m_nest == c_nested))
+  while(jc && (jc->m_nest == c_path ||
+        c_nested->column_in_this_or_nested(jc)))
   {
     if (first_column)
       first_column= FALSE;
@@ -1239,7 +1260,7 @@ int Json_table_nested_path::print(THD *thd, Field ***f, String *str,
     }
     else
     {
-      DBUG_ASSERT(jc->m_nest == c_nested);
+      DBUG_ASSERT(c_nested->column_in_this_or_nested(jc));
       if (str->append("NESTED PATH ") ||
           print_path(str, &jc->m_nest->m_path) ||
           str->append(' ') ||
diff --git a/sql/json_table.h b/sql/json_table.h
index 8988c05354a..4b375ea7867 100644
--- a/sql/json_table.h
+++ b/sql/json_table.h
@@ -97,6 +97,7 @@ private:
   /* The child NESTED PATH we're currently scanning */
   Json_table_nested_path *m_cur_nested;
 
+  bool column_in_this_or_nested(const Json_table_column *jc) const;
   friend class Table_function_json_table;
 };