Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4, 10.9.3, 10.5, 10.6, 10.7, 10.8, 10.9
-
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.