Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6.1
-
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
- relates to
-
MDEV-25875 JSON_TABLE: extract document fragment into JSON column
-
- Closed
-
-
MDEV-17399 Add support for JSON_TABLE
-
- Closed
-
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.