[MDEV-30405] check JSON data transfer Created: 2023-01-13  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: Data types
Fix Version/s: 10.5

Type: Task Priority: Critical
Reporter: Oleksandr Byelkin Assignee: Lena Startseva
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File MDEV-30405-tests.patch    
Issue Links:
Relates
relates to MDEV-30646 View created via JSON_ARRAYAGG return... Open

 Description   

in 10.5 Item::val_json was introduced (i.e. json getting method).

But it was not added to reference Items used in aggregation and VIEW handling.

Check that JSON stay JSON (not converted to string) as:
1) result of aggregate (? - not sure that it is meaningful)
2) field by which aggregated
3) parameter of aggregate function
4) view field

Here is example of turning json to string:

 SELECT JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'));
 
 JSON_ARRAY(_UTF8 'str', JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'))
 
-["str", {"plugin": "unix_socket"}]
 
+["str", "{\"plugin\": \"unix_socket\"}"]

for each case there are 4cases:
1. constants:
1.1 with charset conversion
1.2 without
2. non-constants
2.1 with charset conversion
2.2 without

 Comments   
Comment by Lena Startseva [ 2023-01-13 ]

Possible cases (from version 10.5):

SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),JSON_OBJECT(_LATIN1 'number', _LATIN1'two')));
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),JSON_OBJECT(_LATIN1 'number', _LATIN1'two')))
 
{"number": "one"}:{"number": "two"}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),JSON_OBJECT('number', 'two')));
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),JSON_OBJECT('number', 'two')))
 
{"number": "one"}:{"number": "two"}


SELECT JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),JSON_OBJECT(_LATIN1 'number', _LATIN1'two'));
 
JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),JSON_OBJECT(_LATIN1 'number', _LATIN1'two'))
 
{"number": ["one", "two"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT('number', 'one'),JSON_OBJECT('number', 'two'));
 
JSON_MERGE(JSON_OBJECT('number', 'one'),JSON_OBJECT('number', 'two'))
 
{"number": ["one", "two"]}


SELECT JSON_ARRAYAGG(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'));
 
JSON_ARRAYAGG(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket'))
 
[{"plugin": "unix_socket"}]
 
 
 
SELECT JSON_ARRAYAGG(JSON_OBJECT('plugin', 'unix_socket'));
 
JSON_ARRAYAGG(JSON_OBJECT('plugin', 'unix_socket'))
 
[{"plugin": "unix_socket"}]


CREATE TABLE t1 (a varchar(10), b varchar(10));
 
INSERT INTO t1 VALUES ("number","one"), ("number","two"), (_LATIN1'number',_LATIN1'three'),(_LATIN1'number',"four");
 
 
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
 
 
SELECT JSON_OBJECTAGG(a,b) FROM t1;
 
JSON_OBJECTAGG(a,b)
 
{"number":"one", "number":"two", "number":"three", "number":"four"}
 
 
 
SELECT JSON_OBJECTAGG(a,b) FROM v1;
 
JSON_OBJECTAGG(a,b)
 
{"number":"one", "number":"two", "number":"three", "number":"four"}
 
 
 
SELECT JSON_OBJECTAGG(v1.a,t1.b) FROM v1,t1;
 
JSON_OBJECTAGG(v1.a,t1.b)
 
{"number":"one", "number":"one", "number":"one", "number":"one", "number":"two", "number":"two", "number":"two", "number":"two", "number":"three", "number":"three", "number":"three", "number":"three", "number":"four", "number":"four", "number":"four", "number":"four"}
 
 
 
SELECT JSON_ARRAYAGG(b) from t1;
 
JSON_ARRAYAGG(b)
 
["one","two","three","four"]
 
 
 
SELECT JSON_ARRAYAGG(b) from v1;
 
JSON_ARRAYAGG(b)
 
["one","two","three","four"]
 
 
 
SELECT JSON_OBJECT(a, b) from t1;
 
JSON_OBJECT(a, b)
 
{"number": "one"}
 
{"number": "two"}
 
{"number": "three"}
 
{"number": "four"}
 
 
 
SELECT JSON_OBJECT(a, b) from v1;
 
JSON_OBJECT(a, b)
 
{"number": "one"}
 
{"number": "two"}
 
{"number": "three"}
 
{"number": "four"}
 
 
 
SELECT JSON_OBJECT(v1.a, t1.b) from v1,t1;
 
JSON_OBJECT(v1.a, t1.b)
 
{"number": "one"}
 
{"number": "one"}
 
{"number": "one"}
 
{"number": "one"}
 
{"number": "two"}
 
{"number": "two"}
 
{"number": "two"}
 
{"number": "two"}
 
{"number": "three"}
 
{"number": "three"}
 
{"number": "three"}
 
{"number": "three"}
 
{"number": "four"}
 
{"number": "four"}
 
{"number": "four"}
 
{"number": "four"}
 
 
 
SELECT JSON_ARRAYAGG(JSON_OBJECT(a, b)) from t1;
 
JSON_ARRAYAGG(JSON_OBJECT(a, b))
 
[{"number": "one"},{"number": "two"},{"number": "three"},{"number": "four"}]
 
 
 
SELECT JSON_ARRAYAGG(JSON_OBJECT(a, b)) from v1;
 
JSON_ARRAYAGG(JSON_OBJECT(a, b))
 
[{"number": "one"},{"number": "two"},{"number": "three"},{"number": "four"}]
 
 
 
SELECT JSON_ARRAYAGG(JSON_OBJECT(v1.a, t1.b)) from v1,t1;
 
JSON_ARRAYAGG(JSON_OBJECT(v1.a, t1.b))
 
[{"number": "one"},{"number": "one"},{"number": "one"},{"number": "one"},{"number": "two"},{"number": "two"},{"number": "two"},{"number": "two"},{"number": "three"},{"number": "three"},{"number": "three"},{"number": "three"},{"number": "four"},{"number": "four"},{"number": "four"},{"number": "four"}]
 
 
 
DROP VIEW v1;
 
DROP TABLE t1;


CREATE TABLE t1 (a json, b json);
 
INSERT INTO t1 VALUES ('{"number":"one"}','{"number":"two"}'), ('{"number":"one"}','{"number":"two"}');
 
 
 
CREATE VIEW v1 AS SELECT * FROM t1;
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",a,b) ORDER BY a DESC LIMIT 1) FROM t1;
 
GROUP_CONCAT(CONCAT_WS(":",a,b) ORDER BY a DESC LIMIT 1)
 
{"number":"one"}:{"number":"two"}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",a,b) ORDER BY a DESC LIMIT 1) FROM v1;
 
GROUP_CONCAT(CONCAT_WS(":",a,b) ORDER BY a DESC LIMIT 1)
 
{"number":"one"}:{"number":"two"}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",v1.a,t1.b) ORDER BY v1.a DESC LIMIT 1) FROM v1,t1;
 
GROUP_CONCAT(CONCAT_WS(":",v1.a,t1.b) ORDER BY v1.a DESC LIMIT 1)
 
{"number":"one"}:{"number":"two"}
 
 
 
SELECT JSON_ARRAYAGG(a) from t1;
 
JSON_ARRAYAGG(a)
 
[{"number":"one"},{"number":"one"}]
 
 
 
SELECT JSON_ARRAYAGG(a) from v1;
 
JSON_ARRAYAGG(a)
 
[{"number":"one"},{"number":"one"}]
 
 
 
SELECT JSON_MERGE(a , b) from t1;
 
JSON_MERGE(a , b)
 
{"number": ["one", "two"]}
 
{"number": ["one", "two"]}
 
 
 
SELECT JSON_MERGE(a , b) from v1;
 
JSON_MERGE(a , b)
 
{"number": ["one", "two"]}
 
{"number": ["one", "two"]}
 
 
 
SELECT JSON_MERGE(v1.a , t1.b) from v1,t1;
 
JSON_MERGE(v1.a , t1.b)
 
{"number": ["one", "two"]}
 
{"number": ["one", "two"]}
 
{"number": ["one", "two"]}
 
{"number": ["one", "two"]}
 
 
 
DROP VIEW v1;
 
DROP TABLE t1;

Comment by Lena Startseva [ 2023-01-13 ]

Cases with view created from json object:

CREATE VIEW v1 AS SELECT JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket') as v1_json;
 
 
 
SELECT v1_json FROM v1;
 
v1_json
 
{"plugin": "unix_socket"}
 
SELECT JSON_MERGE(v1_json,v1_json) FROM v1;
 
JSON_MERGE(v1_json,v1_json)
 
{"plugin": ["unix_socket", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'two'),JSON_OBJECT('number', 'one'),v1_json,v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'two'),JSON_OBJECT('number', 'one'),v1_json,v1_json)
 
{"number": ["two", "one"], "plugin": ["unix_socket", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT('plugin', 'one'),v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT('plugin', 'one'),v1_json)
 
{"plugin": ["one", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'two'),v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'two'),v1_json)
 
{"plugin": ["two", "unix_socket"]}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),v1_json)) FROM v1;
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),v1_json))
 
{"number": "one"}:{"plugin": "unix_socket"}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),v1_json)) FROM v1;
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),v1_json))
 
{"number": "one"}:{"plugin": "unix_socket"}
 
 
 
DROP VIEW v1;


CREATE VIEW v1 AS SELECT JSON_OBJECT('plugin','unix_socket') as v1_json;
 
 
 
SELECT v1_json FROM v1;
 
v1_json
 
{"plugin": "unix_socket"}
 
 
 
SELECT JSON_MERGE(v1_json,v1_json) FROM v1;
 
JSON_MERGE(v1_json,v1_json)
 
{"plugin": ["unix_socket", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'two'),JSON_OBJECT('number', 'one'),v1_json,v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT(_LATIN1 'number', _LATIN1'two'),JSON_OBJECT('number', 'one'),v1_json,v1_json)
 
{"number": ["two", "one"], "plugin": ["unix_socket", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT('plugin', 'one'),v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT('plugin', 'one'),v1_json)
 
{"plugin": ["one", "unix_socket"]}
 
 
 
SELECT JSON_MERGE(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'two'),v1_json) FROM v1;
 
JSON_MERGE(JSON_OBJECT(_LATIN1 'plugin', _LATIN1'two'),v1_json)
 
{"plugin": ["two", "unix_socket"]}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),v1_json)) FROM v1;
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT(_LATIN1 'number', _LATIN1'one'),v1_json))
 
{"number": "one"}:{"plugin": "unix_socket"}
 
 
 
SELECT GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),v1_json)) FROM v1;
 
GROUP_CONCAT(CONCAT_WS(":",JSON_OBJECT('number', 'one'),v1_json))
 
{"number": "one"}:{"plugin": "unix_socket"}
 
 
 
DROP VIEW v1;

Comment by Lena Startseva [ 2023-01-13 ]

Cases with view created from json object with incorrect behavior:

CREATE VIEW v1 AS SELECT JSON_OBJECT(_LATIN1 'plugin', _LATIN1'unix_socket') as v1_json;
 
 
 
SELECT JSON_ARRAYAGG(v1_json) FROM v1;
 
JSON_ARRAYAGG(v1_json)
 
["{\"plugin\": \"unix_socket\"}"]
 
 
 
DROP VIEW v1;


CREATE VIEW v1 AS SELECT JSON_OBJECT('plugin','unix_socket') as v1_json;
 
 
 
SELECT JSON_ARRAYAGG(v1_json) FROM v1;
 
JSON_ARRAYAGG(v1_json)
 
["{\"plugin\": \"unix_socket\"}"]
 
 
 
DROP VIEW v1;

Comment by Lena Startseva [ 2023-01-16 ]

sanja, plz, review cases in MDEV-30405-tests.patch

Comment by Oleksandr Byelkin [ 2023-01-18 ]

Looks OK, but it would be nice to add at least a pair of words what each group of tests is testing (for future)

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