|
I have some weird issues with truncated json being updated using "INSERT ... SELECT ... ON DUPLICATE KEY UPDATE b=VALUES(b)" while all is fine if I run just "SELECT ...".
Trying to create simple test case, I discovered this
problem with JSON_SET: in some contexts allocates enough for base and key, forgets to allocate for value (same also with JSON_INSERT and JSON_REPLACE).
Truncation point is dependent on the longest row without that JSON_SET value part.
(I have no simple test case for my original problem atm.)
Details:
- environment: any?
- no errors or warnings
- all versions with json_set supported?
- my.cnf: any/default? (also see demo links)
- wrong and expected result: see below
- test case: see below, see demo
Tested mariadb versions (truncated):
Tested mysql versions (ok):
Test case:
select @@version;
|
(select 3 a,json_set('{"demo":1}','$.w','some text') b)
|
#union all (select 4,json_object('w','some text'))
|
union all (select 5,'any text longer than json_set result')
|
union all (select 6,json_set('{"demo":1}','$.w','any text longer than json_set result'))
|
;
|
select 6 a,json_set('{"demo":1}','$.w','any text longer than json_set result') b
|
;
|
|
(select 3 a,json_insert('{"demo":1}','$.w','some text') b)
|
#union all (select 4,json_object('w','some text'))
|
union all (select 5,'any text longer than json_set result')
|
union all (select 6,json_insert('{"demo":1}','$.w','any text longer than json_set result'))
|
;
|
select 6 a,json_insert('{"demo":1}','$.w','any text longer than json_set result') b
|
;
|
|
(select 3 a,json_replace('{"demo":1,"w":5}','$.w','some text') b)
|
#union all (select 4,json_object('w','some text'))
|
union all (select 5,'any text longer than json_set result')
|
union all (select 6,json_replace('{"demo":1,"w":5}','$.w','any text longer than json_set result'))
|
;
|
select 6 a,json_replace('{"demo":1,"w":5}','$.w','any text longer than json_set result') b
|
;
|
Output from mariadb (third row is truncated):
@@version
|
10.7.3-MariaDB-1:10.7.3+maria~focal
|
a b
|
3 {"demo": 1, "w": "some text"}
|
5 any text longer than json_set result
|
6 {"demo": 1, "w": "any text longer th
|
a b
|
6 {"demo": 1, "w": "any text longer than json_set result"}
|
a b
|
3 {"demo": 1, "w": "some text"}
|
5 any text longer than json_set result
|
6 {"demo": 1, "w": "any text longer th
|
a b
|
6 {"demo": 1, "w": "any text longer than json_set result"}
|
a b
|
3 {"demo": 1, "w": "some text"}
|
5 any text longer than json_set result
|
6 {"demo": 1, "w": "any text longer th
|
a b
|
6 {"demo": 1, "w": "any text longer than json_set result"}
|
Output from mysql (third row is ok):
@@version
|
8.0.27
|
a b
|
3 {"w": "some text", "demo": 1}
|
5 any text longer than json_set result
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
a b
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
a b
|
3 {"w": "some text", "demo": 1}
|
5 any text longer than json_set result
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
a b
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
a b
|
3 {"w": "some text", "demo": 1}
|
5 any text longer than json_set result
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
a b
|
6 {"w": "any text longer than json_set result", "demo": 1}
|
Also, try changing second row into something shorter or something longer. Or-else comment out a=5 and uncomment a=4.
And try changing $.demo static value into something longer like 1 -> 11111.
Workaround:
JSON_MERGE_PATCH seems to work fine.
(select 3 a,json_merge_patch('{"demo":1,"w":5}','{"w":"some text"}') b)
|
#union all (select 4,json_object('w','some text'))
|
union all (select 5,'any text longer than json_set result')
|
union all (select 6,json_merge_patch('{"demo":1,"w":5}','{"w":"any text longer than json_set result"}'))
|
;
|
select 6 a,json_merge_patch('{"demo":1,"w":5}','{"w":"any text longer than json_set result"}') b
|
;
|
|