MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object.

More informative messages added.
Do not issue additional messages if already handled.

4 files changed, 29 insertions, 15 deletions

diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result
index 00f4ecf0c8d..3b38d748b05 100644
--- a/mysql-test/suite/json/r/json_table.result
+++ b/mysql-test/suite/json/r/json_table.result
@@ -88,7 +88,7 @@ a	b
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
-ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
+ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 'jt'.
 select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo;
 a
 0.0
@@ -573,5 +573,13 @@ id
 3
 drop table json_table;
 #
+# MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object.
+#
+select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;
+ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'.
+show warnings;
+Level	Code	Message
+Error	4177	Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'.
+#
 # End of 10.6 tests
 #
diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result
index b1db299c94e..24cc5fa64b6 100644
--- a/mysql-test/suite/json/r/json_table_mysql.result
+++ b/mysql-test/suite/json/r/json_table_mysql.result
@@ -115,7 +115,7 @@ json_table(
 jpath varchar(100) path '$.a' error on error,
 jexst int exists path '$.b')
 ) as tt;
-ERROR HY000: Field 'jpath' can't be set for JSON_TABLE 'tt'.
+ERROR HY000: Can't store an array or an object in the scalar column 'jpath' of JSON_TABLE 'tt'.
 select * from
 json_table(
 '!#@$!@#$',
@@ -396,18 +396,18 @@ v	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIE
 DROP VIEW v;
 SELECT * FROM JSON_TABLE('"asdf"',
 '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
-ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
+ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 'jt'.
 SELECT * FROM
 JSON_TABLE('[{"a":1},{"a":2}]',
 '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
-ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
+ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
 SELECT * FROM
 JSON_TABLE('[{"a":1},{"a":2}]',
 '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
-ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
+ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
 SELECT * FROM
 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
-ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
+ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 'jt'.
 SELECT * FROM
 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
 a
@@ -811,7 +811,7 @@ SELECT * FROM
 JSON_TABLE('[3.14159]',
 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
 ) AS alias2;
-ERROR HY000: Field 'col18' can't be set for JSON_TABLE 'alias2'.
+ERROR HY000: Can't store an array or an object in the scalar column 'col18' of JSON_TABLE 'alias2'.
 SELECT * FROM
 JSON_TABLE('[0.9]',
 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test
index 4b0293f6bea..7d7085c7e89 100644
--- a/mysql-test/suite/json/t/json_table.test
+++ b/mysql-test/suite/json/t/json_table.test
@@ -41,7 +41,7 @@ select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222
 --error ER_JSON_SYNTAX
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_SCALAR_EXPECTED
 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
 
 #
@@ -447,5 +447,12 @@ select * from json_table;
 drop table json_table;
 
 --echo #
+--echo # MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object.
+--echo #
+--error ER_JSON_TABLE_SCALAR_EXPECTED
+select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;
+show warnings;
+
+--echo #
 --echo # End of 10.6 tests
 --echo #
diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test
index 0d322c8b286..821d262283d 100644
--- a/mysql-test/suite/json/t/json_table_mysql.test
+++ b/mysql-test/suite/json/t/json_table_mysql.test
@@ -91,7 +91,7 @@ select * from
              jexst int exists path '$.b')
   ) as tt;
 
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_SCALAR_EXPECTED
 select * from
   json_table(
     '[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
@@ -327,20 +327,19 @@ SELECT * FROM v;
 SHOW CREATE VIEW v;
 DROP VIEW v;
 
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_SCALAR_EXPECTED
 SELECT * FROM JSON_TABLE('"asdf"',
                          '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
-#--error ER_WRONG_JSON_TABLE_VALUE
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_MULTIPLE_MATCHES
 SELECT * FROM
   JSON_TABLE('[{"a":1},{"a":2}]',
              '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
 # psergey-added:
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_MULTIPLE_MATCHES
 SELECT * FROM
   JSON_TABLE('[{"a":1},{"a":2}]',
              '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_SCALAR_EXPECTED
 SELECT * FROM
   JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
 SELECT * FROM
@@ -722,7 +721,7 @@ SELECT * FROM
                 ) AS alias2;
 
 #--error ER_JT_VALUE_OUT_OF_RANGE
---error ER_JSON_TABLE_ERROR_ON_FIELD
+--error ER_JSON_TABLE_SCALAR_EXPECTED
 SELECT * FROM
   JSON_TABLE('[3.14159]',
               '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)