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

JSON_TABLE: ERROR ON clauses are ignored if a column is not on select list

    XMLWordPrintable

    Details

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

      Description

      This query returns an error as it is supposed to, because of ERROR ON EMPTY clause for column a:

      MariaDB [test]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      ERROR 4175 (HY000): Field 'a' can't be set for JSON_TABLE 'jt'.
      

      However, if don't select the column a, it works:

      MariaDB [test]> SELECT o FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      +------+
      | o    |
      +------+
      |    1 |
      +------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.001 sec)
      

      Same applies for ERROR ON ERROR clauses.

      It is not quite clear from the standard what the expected behavior is. It only says that the clauses have the same semantics as for JSON_VALUE, but for JSON_VALUE the situation when the value is not selected is not applicable. Still, from the common sense alone, it just doesn't look right:

      bb-10.6-mdev17399-hf 160bd1691

      MariaDB [test]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      ERROR 4175 (HY000): Field 'a' can't be set for JSON_TABLE 'jt'.
       
      MariaDB [test]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      +----------+
      | COUNT(*) |
      +----------+
      |        1 |
      +----------+
      1 row in set (0.001 sec)
      

      MySQL 8.0.23 returns an error for all queries above:

      MySQL 8.0.23

      MySQL [(none)]> SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      ERROR 3665 (22035): Missing value for JSON_TABLE column 'a'
      MySQL [(none)]> SELECT o FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      ERROR 3665 (22035): Missing value for JSON_TABLE column 'a'
      MySQL [(none)]> SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
      ERROR 3665 (22035): Missing value for JSON_TABLE column 'a'
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: