MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set

Followup part#2: allocate the List object on the right mem-root, too.

2 files changed, 75 insertions, 2 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 1ebb90918f9..3ef87b3e24b 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -722,7 +722,43 @@ insert into t1 values ('Jeans',  '{"color": ["black"], "price": 60}');
 insert into t1 values ('Jeans',  '{"color": ["gray"], "price": 60}');
 insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}');
 insert into t1 values ('Shirt',  '{"color": ["black"], "price": 20}');
-select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color;
+select
+t.item_name,
+jt.*
+from
+(select
+t1.item_name,
+concat(
+concat(
+concat(
+"{\"color\": ",
+concat(
+concat("[\"",
+                      group_concat( jt.color separator "\", \"")
+                     ),
+                "\"]")
+),','
+        ),
+concat(concat("\"price\": ",jt.price),'}')
+) as item_props
+from
+t1,
+json_table(
+t1.item_props,
+'$' columns (
+nested path '$.color[*]' columns (color varchar(32) path '$'),
+price int path '$.price')
+) as jt
+group by
+t1.item_name, jt.price
+) as t,
+json_table(t.item_props,
+'$' columns (
+nested path '$.color[*]' columns (color varchar(32) path '$'),
+price int path '$.price')
+) as jt
+order by
+t.item_name, jt.price, jt.color;
 item_name	color	price
 Jeans	brown	50
 Jeans	green	50
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 39c97f73496..b8b16a750d1 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -623,7 +623,44 @@ insert into t1 values ('Jeans',  '{"color": ["gray"], "price": 60}');
 insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}');
 insert into t1 values ('Shirt',  '{"color": ["black"], "price": 20}');
 
-select t.item_name, jt.* from (select t1.item_name, concat(concat(concat("{\"color\": ",concat(concat("[\"",group_concat(jt.color separator "\", \"")),"\"]")),','),concat(concat("\"price\": ",jt.price),'}')) as item_props from t1, json_table(t1.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt group by t1.item_name, jt.price) as t, json_table(t.item_props, '$' columns (nested path '$.color[*]' columns (color varchar(32) path '$'), price int path '$.price')) as jt order by t.item_name, jt.price, jt.color;
+select
+  t.item_name,
+  jt.*
+from
+  (select
+     t1.item_name,
+     concat(
+        concat(
+          concat(
+            "{\"color\": ",
+            concat(
+               concat("[\"",
+                      group_concat( jt.color separator "\", \"")
+                     ),
+                "\"]")
+          ),','
+        ),
+      concat(concat("\"price\": ",jt.price),'}')
+     ) as item_props
+   from
+      t1,
+      json_table(
+         t1.item_props,
+         '$' columns (
+             nested path '$.color[*]' columns (color varchar(32) path '$'),
+             price int path '$.price')
+      ) as jt
+   group by
+     t1.item_name, jt.price
+  ) as t,
+
+  json_table(t.item_props,
+     '$' columns (
+       nested path '$.color[*]' columns (color varchar(32) path '$'),
+       price int path '$.price')
+  ) as jt
+order by
+  t.item_name, jt.price, jt.color;
 
 drop table t1;