Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
-
None
Description
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):
- 10.7.3-MariaDB-1:10.7.3+maria~focal (demo: https://onecompiler.com/mariadb/3y4apwte4)
- 10.3.28-MariaDB-1:10.3.28+maria~focal
Tested mysql versions (ok):
- 8.0.27 (demo: https://onecompiler.com/mysql/3y4anrx6f)
- 5.7.22 (demo: https://www.db-fiddle.com/f/7Uh69n2uFwzSQ7zix4H9ZJ/0)
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 |
;
|
Attachments
Issue Links
- duplicates
-
-
- Confirmed
-