Bug #70394 MySQL JSON UDFs: json_replace can generate invalid target JSON

• Submitted: 22 Sep 2013 1:28
• Modified: 5 Mar 2014 22:44
• Reporter: Roland Bouman
• Status: Closed
• Category: MySQL Server: JSON User-defined function ( UDF )
• Severity: S4 (Feature request)
• Version: 0.2.0
• OS: Any
• Assigned to: Sveta Smirnova
• CPU Architecture: Any
• Tags: mysql-json-udfs-0.2.0

[22 Sep 2013 1:28] Roland Bouman

Description:
json_replace does a string replacement - it does not escape the value, or interpret numbers, or interpret nulls

How to repeat:
set @json = '{
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": {
          "ID": "SGML",
          "SortAs": "SGML",
          "GlossTerm": "Standard Generalized Markup Language",
          "Acronym": "SGML",
          "Abbrev": "ISO 8879:1986",
          "GlossDef": {
            "para": "A meta-markup language, used to create markup languages such as DocBook.",
            "GlossSeeAlso": ["GML", "XML"]
          },
          "GlossSee": "markup"
        }
      }
    }
  }
}';

EXAMPLE #1:
-----------
select json_replace(@json, 'glossary', 'GlossDiv', 'GlossList', 'GlossEntry', '');

result:

  {
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry":
      }
    }
  }

This is invalid JSON. Expected:

  {
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": ""
      }
    }
  }

EXAMPLE #2:
-----------
select json_replace(@json, 'glossary', 'GlossDiv', 'GlossList', 'GlossEntry', NULL);

  {
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry":
      }
    }
  }

expected:

  {
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": null
      }
    }
  }

EXAMPLE #3:
-----------
select json_replace(@json, 'glossary', 'GlossDiv', 'GlossList', 'GlossEntry', 1);
ERROR 1123 (HY000): Can't initialize function 'json_replace'; JSON_REPLACE accepts only string arguments

Maybe this is by design but it does not seem intuitive. Expected result:

  {
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": 1
      }
    }
  }

EXAMPLE #4:
-----------
select json_replace(@json, 'glossary', 'GlossDiv', 'GlossList', 'GlossEntry', 'note there is a 
linebreak here');

{
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry":note there is a 
linebreak here
      }
    }
  }

note that the string ends up in the doc without quotes, and the linebreak is not escaped. Expected:

{
  "glossary": {
    "title": "example glossary",
    "GlossDiv": {
      "title": "S",
      "GlossList": {
        "GlossEntry": "note there is a\nlinebreak here"
      }
    }
  }

Suggested fix:
1) Please preserve type information of the value (let numbers be javascript numbers, let strings be javascript strings, let NULL be javascript null)
2) Do not generate invalid json

[22 Sep 2013 5:26] MySQL Verification Team

Hello Roland,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[6 Nov 2013 8:58] Sveta Smirnova

This is known limitation of this version: you should use '""' instead.

[5 Mar 2014 22:43] Sveta Smirnova

Posted by developer:
 
Fixed in version 0.3.1.

Now new values, passed to functions JSON_APPEND, JSON_REPLACE, JSON_SET as numbers, empty strings or NULLs are converted to JSON objects. Objects, passed as strings are not converted: you are still responsible to put quotes around string values. This is done by purpose, because it is not possible to distinguish if value, passed as '[1, 2, 3' should be really string "[1, 2, 3" or this is a typo and user should insert closing bracket instead.

Also functions JSON_APPEND, JSON_REPLACE, JSON_SET check if element which needs to be inserted is valid JSON value. If it is not: NULL returned.