Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28591

JSON_SET result truncated in some contexts

    XMLWordPrintable

    Details

      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):

      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
      ;
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            janezr-bens Janez Resnik
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.