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

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.3
    • 10.6
    • JSON
    • 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

              rucha174 Rucha Deodhar
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.