[MDEV-27899] NULL values from JSON NESTED PATH with JSON column Created: 2022-01-30  Updated: 2023-01-04  Resolved: 2023-01-04

Status: Closed
Project: MariaDB Server
Component/s: JSON, Server
Affects Version/s: 10.8.2, 10.6, 10.7, 10.8
Fix Version/s: 10.6.12, 10.7.8

Type: Bug Priority: Major
Reporter: Francesco Chicchiriccò Assignee: Rucha Deodhar
Resolution: Fixed Votes: 0
Labels: None
Environment:

Docker official image https://hub.docker.com/_/mariadb


 Description   

The following SELECT statement:

    SELECT u.id as any_id, u.*, attrs.*
 
    FROM SyncopeUser u, JSON_TABLE(COALESCE(plainAttrs, '[{}]'), '$[*]' COLUMNS (
 
    plainSchema VARCHAR(255) PATH '$.schema',
 
    NESTED PATH '$.values[*]' COLUMNS (
 
    binaryValue LONGBLOB PATH '$.binaryValue',
 
    booleanValue INT PATH '$.booleanValue',
 
    dateValue BIGINT(20) PATH '$.dateValue',
 
    doubleValue DOUBLE PATH '$.doubleValue',
 
    longValue BIGINT(20) PATH '$.longValue',
 
    stringValue VARCHAR(255) PATH '$.stringValue'),
 
    attrUniqueValue JSON PATH '$.uniqueValue')
 
    ) AS attrs

returns only NULL values for the attrUniqueValue column, even though the value from the plainAttrs column are as follows:

[
 
  {
 
    "values": [
 
      {
 
        "stringValue": "Rossini"
 
      }
 
    ],
 
    "schema": "surname"
 
  },
 
  {
 
    "values": [
 
      {
 
        "dateValue": 1243288800000
 
      },
 
      {
 
        "dateValue": 1274824800000
 
      }
 
    ],
 
    "schema": "loginDate"
 
  },
 
  {
 
    "uniqueValue": {
 
      "stringValue": "Gioacchino Rossini"
 
    },
 
    "schema": "fullname"
 
  },
 
  {
 
    "uniqueValue": {
 
      "stringValue": "rossini@apache.org"
 
    },
 
    "schema": "userId"
 
  }
 
]

The same exact query run against MySQL 8.0 produces the expected result, e.g. for some rows the uniqueValue column being a JSON column with values:

    {
 
      "stringValue": "rossini@apache.org"
 
    }

or

    {
 
      "stringValue": "Gioacchino Rossini"
 
    }



 Comments   
Comment by Elena Stepanova [ 2022-04-08 ]

In reality it doesn't return NULL as a result, it returns NULL as an indication of an error. It is clearer when ERROR ON ERROR is used:

create table SyncopeUser (plainAttrs JSON);
 
insert into SyncopeUser values (
 
'[
 
  {
 
    "values": [
 
      {
 
        "stringValue": "Rossini"
 
      }
 
    ],
 
    "schema": "surname"
 
  },
 
  {
 
    "values": [
 
      {
 
        "dateValue": 1243288800000
 
      },
 
      {
 
        "dateValue": 1274824800000
 
      }
 
    ],
 
    "schema": "loginDate"
 
  },
 
  {
 
    "uniqueValue": {
 
      "stringValue": "Gioacchino Rossini"
 
    },
 
    "schema": "fullname"
 
  },
 
  {
 
    "uniqueValue": {
 
      "stringValue": "rossini@apache.org"
 
    },
 
    "schema": "userId"
 
  }
 
]');
 
 
 
SELECT attrs.*
 
  FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS (
 
  attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR)
 
  ) AS attrs;
 
 
 
# Cleanup
 
DROP TABLE SyncopeUser;

10.6 4e1ca388

 
query 'SELECT attrs.*
 
FROM SyncopeUser u, JSON_TABLE(plainAttrs, '$[*]' COLUMNS (
 
attrUniqueValue JSON PATH '$.uniqueValue' ERROR ON ERROR)
 
) AS attrs' failed: ER_JSON_TABLE_SCALAR_EXPECTED (4178): Can't store an array or an object in the scalar column 'attrUniqueValue' of JSON_TABLE 'attrs'.

In MySQL it works, because they have the logic for JSON column type in a JSON table, but MariaDB doesn't, it treats it the same way as TEXT.
I remember discussions about it before, but not the result, so I'll leave it to holyfoot – maybe there is already a task for implementing it.

Comment by Francesco Chicchiriccò [ 2022-12-26 ]

This issue looks fixed in 10.10.2: can you please confirm?

Comment by Rucha Deodhar [ 2023-01-04 ]

Already fixed in 10.6

Generated at Thu Feb 08 09:56:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.