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

JSON_TABLE() does not return ER_JSON_TABLE_MULTIPLE_MATCHES when column path has multiple matches

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.6.3
    • Fix Version/s: 10.6
    • Component/s: JSON
    • Labels:
      None

      Description

      Since ERROR ON ERROR is set in the following query, I would expect it to fail with the error code ER_JSON_TABLE_MULTIPLE_MATCHES. However, the query succeeds, and JSON_TABLE() returns the first match:

      SELECT * 
      FROM JSON_TABLE(
         '[
              {"first_name": "John", "first_name": "Jonathan", "last_name": "Smith", "email": "john.smith@example.com"},
              {"first_name": "Jon", "last_name": "Smith", "email": "jon.smith@example.com"},
              {"first_name": "Johnny", "last_name": "Smith", "email": "john.smith@example.com"}
          ]', 
      '$[*]'
      COLUMNS(
         rowid FOR ORDINALITY,
         first_name VARCHAR(10) PATH '$.first_name' ERROR ON ERROR,
         last_name  VARCHAR(10) PATH '$.last_name' ERROR ON ERROR,
         email VARCHAR(50) PATH '$.email' ERROR ON ERROR
      )
      ) AS people;
      

      See here:

      +-------+------------+-----------+------------------------+
      | rowid | first_name | last_name | email                  |
      +-------+------------+-----------+------------------------+
      |     1 | John       | Smith     | john.smith@example.com |
      |     2 | Jon        | Smith     | jon.smith@example.com  |
      |     3 | Johnny     | Smith     | john.smith@example.com |
      +-------+------------+-----------+------------------------+
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              GeoffMontee Geoff Montee
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated: