Reproducible as described
also repeatable with JSON_INSERT, JSON_REPLACE:
MariaDB [test]> select json_replace('{"a":1}','$.a','some text');
|
+-------------------------------------------+
|
| json_replace('{"a":1}','$.a','some text') |
|
+-------------------------------------------+
|
| {"a": "some text"} |
|
+-------------------------------------------+
|
1 row in set (0,001 sec)
|
|
|
MariaDB [test]> with cte as (select json_replace('{"a":1}','$.a','some text')) select * from cte;
|
+-------------------------------------------+
|
| json_replace('{"a":1}','$.a','some text') |
|
+-------------------------------------------+
|
| {"a": "some te |
|
+-------------------------------------------+
|
1 row in set (0,003 sec)
|
|
|
MariaDB [test]> select json_insert('{"a":1}','$.b','some text');
|
+------------------------------------------+
|
| json_insert('{"a":1}','$.b','some text') |
|
+------------------------------------------+
|
| {"a": 1, "b": "some text"} |
|
+------------------------------------------+
|
1 row in set (0,001 sec)
|
|
|
MariaDB [test]> with cte as (select json_insert('{"a":1}','$.b','some text')) select * from cte;
|
+------------------------------------------+
|
| json_insert('{"a":1}','$.b','some text') |
|
+------------------------------------------+
|
| {"a": 1, "b": |
|
+------------------------------------------+
|
1 row in set (0,002 sec)
|
Also repeatable with UNION (please check MDEV-28591):
MariaDB [test]> select 1 union select json_replace('{"a":1}','$.a','some text');
|
+----------------+
|
| 1 |
|
+----------------+
|
| 1 |
|
| {"a": "some te |
|
+----------------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select json_set('{"a":1}','$.b','some text') union (select 1);
|
+---------------------------------------+
|
| json_set('{"a":1}','$.b','some text') |
|
+---------------------------------------+
|
| {"a": 1, "b": |
|
| 1 |
|
+---------------------------------------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select json_set('{"a":1}','$.b','some text') union (select json_set('{}','$.d','some text2'));
|
+---------------------------------------+
|
| json_set('{"a":1}','$.b','some text') |
|
+---------------------------------------+
|
| {"a": 1, "b": |
|
| {"d": "some te |
|
+---------------------------------------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select json_insert('{"a":1}','$.b',1) union select 1111111111111;
|
+--------------------------------+
|
| json_insert('{"a":1}','$.b',1) |
|
+--------------------------------+
|
| {"a": 1, "b": |
|
| 1111111111111 |
|
+--------------------------------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select json_insert('{"a":1}','$.b',1) union select 111111111111111;
|
+--------------------------------+
|
| json_insert('{"a":1}','$.b',1) |
|
+--------------------------------+
|
| {"a": 1, "b": 1 |
|
| 111111111111111 |
|
+--------------------------------+
|
2 rows in set (0,002 sec)
|
|
|
MariaDB [test]> select json_insert('{"a":1}','$.b',1) union select 11111111111111111;
|
+--------------------------------+
|
| json_insert('{"a":1}','$.b',1) |
|
+--------------------------------+
|
| {"a": 1, "b": 1} |
|
| 11111111111111111 |
|
+--------------------------------+
|
2 rows in set (0,002 sec)
|
|
`JSON_REMOVE` is another one with similar issues when json document should not change:
MariaDB [test]> select json_remove('{"a":123}','$.b') union select 1;
|
+--------------------------------+
|
| json_remove('{"a":123}','$.b') |
|
+--------------------------------+
|
| {"a": 123 |
|
| 1 |
|
+--------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_remove('["a",1,"some text"]','$[10]') union select 1;
|
+--------------------------------------------+
|
| json_remove('["a",1,"some text"]','$[10]') |
|
+--------------------------------------------+
|
| ["a", 1, "some text |
|
| 1 |
|
+--------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_remove('{"a":[{"b":"some text"}]}','$.a[10]') union select 1;
|
+----------------------------------------------------+
|
| json_remove('{"a":[{"b":"some text"}]}','$.a[10]') |
|
+----------------------------------------------------+
|
| {"a": [{"b": "some text"} |
|
| 1 |
|
+----------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_remove('{"a":[{"b":"some text"}]}','$.a[10]');
|
+----------------------------------------------------+
|
| json_remove('{"a":[{"b":"some text"}]}','$.a[10]') |
|
+----------------------------------------------------+
|
| {"a": [{"b": "some text"}]} |
|
+----------------------------------------------------+
|
1 row in set (0.00 sec)
|
select json_remove('{"a":123,"b":1}','$.b') union select 1; |
select json_remove('["a",1,"some text"]','$[0]') union select 1; |
select json_remove('[{"a":1},{"b":"some text"}]','$[0]') union select 1; |
select json_remove('{"a":[{"b":"some text"}]}','$.a[0]') union select 1; |
#<-- all four ok |
Both JSON_ARRAY_INSERT and JSON_ARRAY_APPEND have similar issues as well:
MariaDB [test]> select json_array_insert('[]','$[0]',json_extract('{"a":111}','$')) union select 1;
|
+--------------------------------------------------------------+
|
| json_array_insert('[]','$[0]',json_extract('{"a":111}','$')) |
|
+--------------------------------------------------------------+
|
| [{"a": 111 |
|
| 1 |
|
+--------------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_array_append('[]','$',json_extract('{"a":1}','$')) union select 1;
|
+---------------------------------------------------------+
|
| json_array_append('[]','$',json_extract('{"a":1}','$')) |
|
+---------------------------------------------------------+
|
| [{"a": |
|
| 1 |
|
+---------------------------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_array_append('[]','$',json_extract('{"a":1}','$'));
|
+---------------------------------------------------------+
|
| json_array_append('[]','$',json_extract('{"a":1}','$')) |
|
+---------------------------------------------------------+
|
| [{"a": 1}] |
|
+---------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Adding JSON_EXTRACT to the list as well:
MariaDB [test]> select json_extract('{"a":1}','$') union select 1;
|
+-----------------------------+
|
| json_extract('{"a":1}','$') |
|
+-----------------------------+
|
| {"a": 1 |
|
| 1 |
|
+-----------------------------+
|
2 rows in set (0.00 sec)
|
JSON_TYPE has similar issues. Also, some interesting effects on CONCAT:
MariaDB [test]> select json_type('1') union all select 1;
|
+----------------+
|
| json_type('1') |
|
+----------------+
|
| INTE |
|
| 1 |
|
+----------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select json_type('1') union all select 0+json_extract('1','$');
|
+----------------+
|
| json_type('1') |
|
+----------------+
|
| INTEGER |
|
| 1 |
|
+----------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select concat('a',json_type('1')) union all select 1;
|
+----------------------------+
|
| concat('a',json_type('1')) |
|
+----------------------------+
|
| aINTE |
|
| 1 |
|
+----------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select concat('a',json_type('1'),'bcdef') union all select 1;
|
+------------------------------------+
|
| concat('a',json_type('1'),'bcdef') |
|
+------------------------------------+
|
| aINTEGERbc |
|
| 1 |
|
+------------------------------------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> select concat(json_type('1'),'bcdef') union all select 1;
|
+--------------------------------+
|
| concat(json_type('1'),'bcdef') |
|
+--------------------------------+
|
| bcdef |
|
| 1 |
|
+--------------------------------+
|
2 rows in set (0.00 sec)
|
Relates to https://jira.mariadb.org/browse/MDEV-28947
Hi,
is not only with union or cte
on 10.6.10 and 10.6.11 if I do :
SELECT JSON_SET("{}","$.key1",IF(JSON_VALUE(dbfield, "$.key2")< 100, 1, 0));
where dbfield is mediumtext or longtext
I get truncated result like {"key1
if I do :
SELECT JSON_SET("{}","$.key1",IF(JSON_VALUE('
', "$.key2")< 100, 1, 0));
everything is ok
also if i add some space on first {}, like :
SELECT JSON_SET("{} ","$.key1",IF(JSON_VALUE(dbfield, "$.key2")< 100, 1, 0));
is working.
10.6.9 doesn't have this issue.
@Adrian,
please check if JSON_COMPACT fix works in your case:
SELECT JSON_COMPACT(JSON_SET("{}","$.key1",IF(JSON_VALUE(dbfield, "$.key2")< 100, 1, 0))); |
Tried, same result, also I tried with JSON_COMPACT on dbfield, the same result.
I made more tests.
The query that i gave is only the select... the actual query is:
SELECT
tabl1.col1,
"some_value1",
0,
"{}",
JSON_SET('{}',"$.key1",IF(JSON_VALUE(tabl1.col2), "$.key2")< 100, 1, 0)) ,
UNIX_TIMESTAMP(),
UNIX_TIMESTAMP()
FROM tabl1
INNER JOIN `tabl2` ON `tabl2`.`col1` = `tabl1`.`col1` and `tabl2`.`col2`=`tabl1`.`col3`
INNER JOIN `tabl3` ON `tabl3`.`col1` = `tabl2`.`col3`
WHERE `tabl3`.`col2` = "some_val"
AND `tabl2`.`col4` = "some_val"
AND `tabl1`.`col6` = "some val"
GROUP BY tabl1.col1
if i make only the select is working
if i make select and first join is working
if i make select and both join is working
if i make select and both join and GROUP BY is NOT working
if i make select and first join and GROUP BY is working
WHERE doesn't seem to impact anything
working meaning the JSON_SET('{}',"$.key1",IF(JSON_VALUE(tabl1.col2), "$.key2")< 100, 1, 0)) returns correct json, not truncated 
I think it is alo relate to MDEV-26167