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

JSON_TABLE can't handle an array properly

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.6.1
    • 10.6.9
    • JSON
    • Docker image : mariadb:10.6.1
      Platform : Amazon Elastic Container Service (ECS)
      OS: Amazon Linux 2

    Description

      JSON_TABLE does not process a JSON array properly for a JSON type path.

      Use case example:

      SELECT * FROM
          JSON_TABLE('[{"id": 1,"first_name": "John", "last_name": "Smith","email": ["john.smith@example.com","johnsmith@example.com"], "contact_since": 2010},
          {"id": 2,"first_name": "Jon", "last_name": "Smith","email": ["jon.smith@example.com","jonsmith@example.com"], "new_contact": true, "contact_since": 2015},
          {"id": 3,"first_name": "Johnny", "last_name": "Smith","email": ["johnny.smith@example.com","johnnysmith@example.com"], "new_contact": true, "contact_since": 2017}]',
            "$[*]"
            COLUMNS(
              rowid FOR ORDINALITY,
              email JSON PATH "$.email" DEFAULT '[]' ON EMPTY
             )
          ) AS contact;
      

      Expected Output:

      +-------+---------------------------------------------------------+
      | rowid | email                                                   |
      +-------+---------------------------------------------------------+
      |     1 | ["john.smith@example.com", "johnsmith@example.com"]     |
      |     2 | ["jon.smith@example.com", "jonsmith@example.com"]       |
      |     3 | ["johnny.smith@example.com", "johnnysmith@example.com"] |
      +-------+---------------------------------------------------------+
      

      Actual Output:

      +-------+-------+
      | rowid  | email  |
      +-------+-------+
      |     1     | NULL   |
      |     2     | NULL   |
      |     3     | NULL   |
      +-------+-------+
      

      MySQL 8 generates expected output.

      Attachments

        Issue Links

          Activity

            The observed behavior is what is expected of the current code. The SQL Standard specifies that it is an error when column path points to a non-scalar value. The default "ON ERROR" behavior is to emit NULL, this is why the query produces NULLs.

            MySQL's behavior is their extension. It is very reasonable though, and we are already looking at implementing it, see MDEV-25875.

            psergei Sergei Petrunia added a comment - The observed behavior is what is expected of the current code. The SQL Standard specifies that it is an error when column path points to a non-scalar value. The default "ON ERROR" behavior is to emit NULL, this is why the query produces NULLs. MySQL's behavior is their extension. It is very reasonable though, and we are already looking at implementing it, see MDEV-25875 .
            rucha174 Rucha Deodhar added a comment -

            SELECT * FROM
            JSON_TABLE('[{"id": 1,"first_name": "John", "last_name": "Smith","email": ["john.smith@example.com","johnsmith@example.com"], "contact_since": 2010},
                {"id": 2,"first_name": "Jon", "last_name": "Smith","email": ["jon.smith@example.com","jonsmith@example.com"], "new_contact": true, "contact_since": 2015},
                {"id": 3,"first_name": "Johnny", "last_name": "Smith","email": ["johnny.smith@example.com","johnnysmith@example.com"], "new_contact": true, "contact_since": 2017}]',
            "$[*]"
                  COLUMNS(
            rowid FOR ORDINALITY,
            email JSON PATH "$.email" DEFAULT '[]' ON EMPTY
            )
            ) AS contact;
            rowid	email
            1	["john.smith@example.com","johnsmith@example.com"]
            2	["jon.smith@example.com","jonsmith@example.com"]
            3	["johnny.smith@example.com","johnnysmith@example.com"]
            main.temp                                [ pass ]      1
            
            

            Can't repeat anymore on 10.6 commit hash : 654236c06d231461c66e2f3c5c4fd3b35cba3869

            rucha174 Rucha Deodhar added a comment - SELECT * FROM JSON_TABLE('[{ "id" : 1 , "first_name" : "John" , "last_name" : "Smith" , "email" : [ "john.smith@example.com" , "johnsmith@example.com" ], "contact_since" : 2010 }, { "id" : 2 , "first_name" : "Jon" , "last_name" : "Smith" , "email" : [ "jon.smith@example.com" , "jonsmith@example.com" ], "new_contact" : true , "contact_since" : 2015 }, { "id" : 3 , "first_name" : "Johnny" , "last_name" : "Smith" , "email" : [ "johnny.smith@example.com" , "johnnysmith@example.com" ], "new_contact" : true , "contact_since" : 2017 }]', "$[*]" COLUMNS( rowid FOR ORDINALITY, email JSON PATH "$.email" DEFAULT '[]' ON EMPTY ) ) AS contact; rowid email 1 [ "john.smith@example.com" , "johnsmith@example.com" ] 2 [ "jon.smith@example.com" , "jonsmith@example.com" ] 3 [ "johnny.smith@example.com" , "johnnysmith@example.com" ] main.temp [ pass ] 1 Can't repeat anymore on 10.6 commit hash : 654236c06d231461c66e2f3c5c4fd3b35cba3869
            rucha174 Rucha Deodhar added a comment -

            Seems it is fixed already. Can't repeat anymore on commit: 654236c06d231461c66e2f3c5c4fd3b35cba3869

            rucha174 Rucha Deodhar added a comment - Seems it is fixed already. Can't repeat anymore on commit: 654236c06d231461c66e2f3c5c4fd3b35cba3869

            People

              rucha174 Rucha Deodhar
              dvohra Deepak Vohra
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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