Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
JSON support.
1. json_object('name', json_column )
2. json_object('name',longtext_column)
These two SHOULD give different results, because the longtext_column is a string so it gets quoted and quotes inside it get escaped, whereas the json_column is SEMANTICALLY a not necessarily a string and should not get quoted when it is an json object or json array.
Mariadb's support for JSON uses 'json' as a alias for longtext and therefore produces the same result for 1 & 2.
I know you are going to close this as a duplicate! BUT every discussion I have started or seen with MariaDB folk gets the answer "Our tests show that there is no performance difference between storing json internally as binary or a string", which has NOTHING to do with the problem. [I'd prefer you remain file-compatible with MySQL, but the internal representation is INTERNAL]. The problem here is that the JSON SEMANTICS.
Isn't it possible for you to implement the semantics correctly? (independently of your decision on internal representation). It would require functions like json_object to be sensitive to whether a column or expression is typed "json".
(I have a ton of procedures and functions that use JSON on MySQL5.7 and a ton of servers that I'd like to get to MariaDB), but I cant because if this problem. You have so much more good stuff I want to be able to use!)
Attachments
Issue Links
- relates to
-
MDEV-17832 Protocol: extensions for Pluggable types and JSON, GEOMETRY
-
- Closed
-
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
You are right, "no performance difference" has nothing to do with this issue. And we never said that we didn't create JSON type because there was "no performance difference". The internal representation is plain text because there was no performance difference, but I agree, internal representation is internal.
The reason why we don't have a true JSON data type is because SQL Standard explicitly forbids it. In the Part 2 "Foundation" of SQL:2016, in the Section 4.46 "JSON data handling in SQL" it says unambiguously:
Note that Oracle, DB2, and SQL Server follow the standard here. For example, here's the manual for the JSON_QUERY function:
Oracle:
DB2:
SQLServer:
SQL Server manual doesn't say it must be a text literal, but, like others, it says what happens when the expression is not a valid JSON, which implies it's a text literal, and not a specialized type
So, we've made a conscious decision to follow the SQL Standard (like Oracle, DB2, SQL Server, and others).
But at the moment not everything from the SQL Standard is implemented yet. The Standard specifies that one can optionally specify FORMAT JSON after the value, to specify that it is JSON, not an arbitrary text. In your example it would've been json_object('name',json_column FORMAT JSON) vs json_object('name', longtext_column).
We did not implement it yet, but exactly the same functionality can be achieved with our JSON formatting functions. Compare:
MariaDB [test]> select json_object('foo', '{"bar": 5}') as_text;
+-------------------------+
| as_text |
+-------------------------+
| {"foo": "{\"bar\": 5}"} |
+-------------------------+
1 row in set (0.001 sec)
MariaDB [test]> select json_object('foo',json_compact('{"bar": 5}')) as_json;
+---------------------+
| as_json |
+---------------------+
| {"foo": {"bar": 5}} |
+---------------------+
1 row in set (0.001 sec)