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

Note: Set to Major because of ER_TABLE_IN_FK_CHECK, otherwise it would have been Trivial to Minor at most.

Another case of an expected error but an obscure error message, which can be painfully difficult to analyze with complex JSON documents:

select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;

MariaDB responds to it with

bb-10.6-mdev17399-psergey2 8b533cc1d5
MariaDB [test]> select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;
ERROR 4175 (HY000): Field 'a' can't be set for JSON_TABLE 't'.

and SHOW WARNINGS only makes it worse:

MariaDB [test]> show warnings;

+-------+------+--------------------------------------------+

| Level | Code | Message                                    |

+-------+------+--------------------------------------------+

| Error | 4175 | Field 'a' can't be set for JSON_TABLE 't'. |

| Error | 1725 | Table is being used in foreign key check   |

+-------+------+--------------------------------------------+

2 rows in set (0.000 sec)

Out of these, ER_JSON_TABLE_ERROR_ON_FIELD is obscure and non-descriptive, but ER_TABLE_IN_FK_CHECK is just wrong.

MySQL returns a much more user-friendly message without any unrelated noise:

MySQL 8.0.23
MySQL [(none)]> select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;
ERROR 3666 (2203F): Can't store an array or an object in the scalar JSON_TABLE column 'a'
MySQL [(none)]> show warnings;
+-------+------+-----------------------------------------------------------------------+
\| Level \| Code \| Message \|
+-------+------+-----------------------------------------------------------------------+
\| Error \| 3666 \| Can't store an array or an object in the scalar JSON_TABLE column 'a' \|
+-------+------+-----------------------------------------------------------------------+
1 row in set (0.000 sec)

[MDEV-25146] JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object Created: 2021-03-15 Updated: 2021-04-21 Resolved: 2021-03-26
Status:	Closed
Project:	MariaDB Server
Component/s:	JSON
Affects Version/s:	N/A
Fix Version/s:	10.6.0