[MDEV-29751] JSON_EXTRACT produces weird "null" value Created: 2022-10-10  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Server
Affects Version/s: 10.3, 10.4, 10.9.3, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Lukas Eder Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Try this query:

select 
  a, b, 
  a = b, 
  a = 'null', a = '"null"', 
  b = 'null', b = '"null"'
 
from (
 
  select
 
    json_extract(j, '$.a') as a,
 
    json_extract(j, '$.b') as b
 
  from (
 
    select '{"a":null,"b":"null"}' as j
 
  ) t
 
) t
 
union all
 
select 
  json_extract(j, '$.a'), json_extract(j, '$.b'), 
  json_extract(j, '$.a') = json_extract(j, '$.b'), 
  json_extract(j, '$.a') = 'null', json_extract(j, '$.a') = '"null"', 
  json_extract(j, '$.b') = 'null', json_extract(j, '$.b') = '"null"'
 
from (
 
  select '{"a":null,"b":"null"}' as j
 
) t;

It produces this result:

|a   |b     |a = b|a = 'null'|a = '"null"'|b = 'null'|b = '"null"'|
 
|----|------|-----|----------|------------|----------|------------|
 
|null|"null"|0    |1         |0           |0         |1           |
 
|null|"null"|0    |1         |0           |1         |0           |

Both UNION ALL subqueries should produce the same results, and they do in MySQL, but don't in MariaDB. The problem is that JSON_EXTRACT seems to produce a '"null"' JSON string value that is equal to a 'null' SQL string in some cases, but not in others. The query shows that a workaround is to wrap the JSON_EXTRACT calls in a subquery.

Another workaround is to concatenate the JSON_EXTRACT result to '', to remove the weird JSON behaviour:

select 
  a, b, 
  a = b, 
  a = 'null', a = '"null"', 
  b = 'null', b = '"null"'
 
from (
 
  select
 
    json_extract(j, '$.a') as a,
 
    json_extract(j, '$.b') as b
 
  from (
 
    select '{"a":null,"b":"null"}' as j
 
  ) t
 
) t
 
union all
 
select 
  json_extract(j, '$.a'), json_extract(j, '$.b'), 
  json_extract(j, '$.a') = json_extract(j, '$.b'), 
  json_extract(j, '$.a') = 'null', json_extract(j, '$.a') = '"null"', 
  concat('', json_extract(j, '$.b')) = 'null', concat('', json_extract(j, '$.b')) = '"null"'
 
from (
 
  select '{"a":null,"b":"null"}' as j
 
) t;

In the long run, I really wish MariaDB just introduced a formal JSON type, like PostgreSQL did from the beginning, or Oracle / MySQL did after the fact.

 Comments   
Comment by Alice Sherepa [ 2022-10-13 ]

Thanks! I repeated as described, there are extra double quotes:

MariaDB [test]>  (select x = 'a1', x from (select json_extract('{"b":"a1"}', '$.b') as x) dt);
 
+----------+------+
 
| x = 'a1' | x    |
 
+----------+------+
 
|        0 | "a1" |
 
+----------+------+
 
1 row in set (0,001 sec)
 
 
 
MariaDB [test]>  select json_extract('{"b":"a1"}', '$.b') = 'a1';
 
+------------------------------------------+
 
| json_extract('{"b":"a1"}', '$.b') = 'a1' |
 
+------------------------------------------+
 
|                                        1 |
 
+------------------------------------------+
 
1 row in set (0,001 sec)

Generated at Thu Feb 08 10:11:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.