Merge 10.7 into 10.8

4 files changed, 47 insertions, 23 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 8359d6b8a39..d6a02ccddba 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -57,14 +57,14 @@ Jeans	{"color": "blue", "price": 50}	blue
 select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
 ERROR 42S22: Unknown column 't1.item_props' in 'JSON_TABLE argument'
 DROP TABLE t1;
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 101 on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 a	b
 101	11
 101	111
 2	22
 2	222
 3	NULL
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 202 on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 a	b
 NULL	11
 NULL	111
@@ -957,6 +957,20 @@ converted	original
 Warnings:
 Warning	1264	Out of range value for column 'converted' at row 2
 Warning	1366	Incorrect integer value: 'foo' for column ``.`(temporary)`.`converted` at row 3
+select * from
+json_table('[{"color": "blue",  "price": { "high": 10, "low": 5}},
+               {"color": "white",   "price": "pretty low"},
+               {"color": "yellow",  "price": 256.20},
+               {"color": "red",   "price": { "high": 20, "low": 8}}]',
+'$[*]' columns(color varchar(100) path '$.color',
+price json path '$.price'
+                        )
+) as T;
+color	price
+blue	{ "high": 10, "low": 5}
+white	"pretty low"
+yellow	256.20
+red	{ "high": 20, "low": 8}
 #
 # MDEV-27696 Json table columns accept redundant COLLATE syntax
 #
diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result
index ca9ef4793a5..7b942f9a041 100644
--- a/mysql-test/suite/json/r/json_table_mysql.result
+++ b/mysql-test/suite/json/r/json_table_mysql.result
@@ -40,7 +40,7 @@ id	jpath	jsn_path	jexst
 2	2	2	0
 3	33	{"x":33}	1
 4	0	0	0
-5	66	NULL	0
+5	66	[1,2]	0
 select * from
 json_table(
 '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
@@ -55,11 +55,11 @@ jsn_path json path '$.a' default '{"x":33}' on empty,
 jexst int exists path '$.b')
 ) as tt;
 id	jpath_i	jpath_r	jsn_path	jexst
-1	3	3	3	0
+1	3	3	"3"	0
 2	2	2	2	0
 3	33	33.3	{"x":33}	1
 4	0	0.33	0.33	0
-5	0	0	asd	0
+5	0	0	"asd"	0
 Warnings:
 Warning	1366	Incorrect integer value: 'asd' for column ``.`(temporary)`.`jpath_i` at row 5
 Warning	1366	Incorrect double value: 'asd' for column ``.`(temporary)`.`jpath_r` at row 5
@@ -78,7 +78,7 @@ id	jpath	jsn_path	jexst
 2	2	2	0
 3	33	{"x":33}	1
 4	0	0	0
-5	66	NULL	0
+5	66	[1,2]	0
 select * from
 json_table(
 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
@@ -88,7 +88,7 @@ json_path json path '$.a',
 jexst int exists path '$.b')
 ) as tt;
 id	jpath	json_path	jexst
-1	3	3	0
+1	3	"3"	0
 2	2	2	0
 3	NULL	NULL	1
 4	0	0	0
@@ -319,24 +319,24 @@ id1	jpath	jexst	id2	id3	jpath_3	id4	jpath_4
 1	3	0	2	1	a1	NULL	NULL
 1	3	0	2	2	a2	NULL	NULL
 1	3	0	3	1	c	NULL	NULL
-1	3	0	NULL	NULL	NULL	1	NULL
-1	3	0	NULL	NULL	NULL	2	NULL
-1	3	0	NULL	NULL	NULL	3	NULL
+1	3	0	NULL	NULL	NULL	1	{"ll":["b1","b2","b3"]}
+1	3	0	NULL	NULL	NULL	2	{"ll": ["a1","a2"]}
+1	3	0	NULL	NULL	NULL	3	{"ll":["c"]}
 2	2	0	1	1	1	NULL	NULL
 2	2	0	1	2	11	NULL	NULL
 2	2	0	1	3	111	NULL	NULL
 2	2	0	2	1	2	NULL	NULL
-2	2	0	NULL	NULL	NULL	1	NULL
-2	2	0	NULL	NULL	NULL	2	NULL
+2	2	0	NULL	NULL	NULL	1	{"ll":[1,11,111]}
+2	2	0	NULL	NULL	NULL	2	{"ll":[2]}
 3	NULL	1	1	1	zzz	NULL	NULL
-3	NULL	1	NULL	NULL	NULL	1	NULL
+3	NULL	1	NULL	NULL	NULL	1	{"ll":["zzz"]}
 4	0	0	1	1	0.1	NULL	NULL
 4	0	0	1	2	0.01	NULL	NULL
 4	0	0	2	1	0.02	NULL	NULL
 4	0	0	2	2	0.002	NULL	NULL
 4	0	0	2	3	0.0002	NULL	NULL
-4	0	0	NULL	NULL	NULL	1	NULL
-4	0	0	NULL	NULL	NULL	2	NULL
+4	0	0	NULL	NULL	NULL	1	{"ll":[0.1,0.01]}
+4	0	0	NULL	NULL	NULL	2	{"ll":[0.02,0.002,0.0002]}
 ord should be 1,1,1,2, which tells that first two values of 'l' are
 from the same object, and next two are from different objects
 SELECT *
@@ -555,10 +555,12 @@ JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULL ON ERROR)) jt' at line 2
 SELECT * FROM
 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 ON EMPTY)) jt' at line 2
+x
+0
 SELECT * FROM
 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 ON ERROR)) jt' at line 2
+x
+NULL
 SELECT * FROM
 JSON_TABLE('{}', '$' COLUMNS (x DATE
 PATH '$.x'
@@ -608,7 +610,7 @@ SELECT * FROM JSON_TABLE('{"a":"1"}',
 o FOR ORDINALITY)) AS jt
 WHERE o = 1;
 jpath	o
-1	1
+"1"	1
 #
 # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
 #
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index dbb9f43ab17..a6392b7bfff 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -30,9 +30,9 @@ select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(
 
 DROP TABLE t1;
 
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 101 on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
-select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
+select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 202 on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
 select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
@@ -814,6 +814,16 @@ select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted
  
 select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;
 
+select * from
+  json_table('[{"color": "blue",  "price": { "high": 10, "low": 5}},
+               {"color": "white",   "price": "pretty low"},
+               {"color": "yellow",  "price": 256.20},
+               {"color": "red",   "price": { "high": 20, "low": 8}}]',
+             '$[*]' columns(color varchar(100) path '$.color',
+                          price json path '$.price'
+                        )
+             ) as T;
+
 --echo #
 --echo # MDEV-27696 Json table columns accept redundant COLLATE syntax
 --echo #
diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test
index aaf123c6f7c..9f77ad964f3 100644
--- a/mysql-test/suite/json/t/json_table_mysql.test
+++ b/mysql-test/suite/json/t/json_table_mysql.test
@@ -453,13 +453,11 @@ SELECT * FROM
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
 
-# The DEFAULT value must be a string on JSON format for now.
---error 1064
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
---error 1064
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
+# We don't accept dates in DEFAULT
 --error 1064
 SELECT * FROM
   JSON_TABLE('{}', '$' COLUMNS (x DATE