[MDEV-32888] Inconsistent results involving JSON operations Created: 2023-11-26  Updated: 2023-11-27  Resolved: 2023-11-27

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.6.16, 10.4.33
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Andreas Hinderberger Assignee: Alexander Barkov
Resolution: Not a Bug Votes: 1
Labels: None
Environment:

Windows 11


 Description   

When executing the following statments on MariaDB 10.6.14

SET @json_1 = JSON_OBJECT('id', 'name');
 
SET @json_result_1 = JSON_OBJECT('test', @json_1);
 
SELECT @json_result_1;
 
SET @json_result_2 = JSON_OBJECT('test', JSON_OBJECT('id', 'name'));
 
SELECT @json_result_2;

I receive the following results:

For @json_result_1:

{"test": "{\"id\": \"name\"}"}

And for @json_result_2:

{"test": {"id": "name"}}

How does it come that I retrieve different result? Aren't those JSON string supposed to be internally all treated as TEXT?

 Comments   
Comment by Andreas Hinderberger [ 2023-11-26 ]

Forgot...

SET @json_1 = JSON_OBJECT('id', 'name');
 
SET @json_result_1 = JSON_OBJECT('test', JSON_EXTRACT(@json_1 , '$'));
 
SELECT @json_result_1;

This produces the same expected result as for @json_result_2

Comment by Sergei Golubchik [ 2023-11-27 ]

It behaves as expected. MariaDB knows that the output of JSON_OBJECT() is a json. But when you store it in a variable, it loses its "json-ness" and becomes a literal string.

To force a string to be interpreted as a json, you can use, indeed, JSON_EXTRACT, or, just JSON_COMPACT().

Comment by Andreas Hinderberger [ 2023-11-27 ]

In my opinion it should keep the "json-ness" or whenever JSON_OBJECT uses that "literal string" it should check whether it's a valid json and treat it like that?
That would make it way more intuitive.

Comment by Sergei Golubchik [ 2023-11-27 ]

Yes, but the value will lose its "json-ness" anyway when being stored in a table. Or in a stored routine variable. The reason for this — SQL Standard 2016, which very explicitly said

NOTE 116 — There is no SQL <data type> whose value space is SQL/JSON items or SQL/JSON sequences.

which is why MariaDB doesn't have a JSON data type.

In the newer SQL Standard, as far as I know, they've changed their minds, and wrote that there is a distinct data type for JSON. We'll need to implement that to be standard compatible again and then the behavior will be much more intuitive.

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