Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25146

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON
    • Labels:
      None

      Description

      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)
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: