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

JSON_TABLE returns an empty value for a valid Unicode escape and ignores ERROR ON ERROR

    XMLWordPrintable

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11, 11.4, 11.8, 12.3, 13.1, 12.3.3
    • 10.11, 11.4, 11.8, 12.3, 13.1
    • JSON
    • None
    • MariaDB 12.3.3 binary distribution on Linux x86_64

    Description

      I get an empty non-NULL value from JSON_TABLE when valid latin1 JSON contains the Unicode escape \u0424 and the output column uses utf8mb4. The same value is decoded as Ф (UTF-8 hex D0A4) by JSON_UNQUOTE, while JSON_TABLE also reports warning 4035 despite ERROR ON ERROR.

      This occurs when JSON_TABLE projects a Unicode escape from latin1 JSON into an utf8mb4 column.

      The following equivalent SQL reproduces the issue; the JSON_TABLE alias is incidental:

      SET NAMES utf8mb4;
      SELECT JSON_UNQUOTE(CONVERT('"\\u0424"' USING latin1)) AS unquote_control,
             HEX(JSON_UNQUOTE(CONVERT('"\\u0424"' USING latin1))) AS control_hex,
             JSON_VALID(CONVERT('["\\u0424"]' USING latin1)) AS valid_json;
      SELECT x, HEX(x) AS extracted_hex
      FROM JSON_TABLE(
             CONVERT('["\\u0424"]' USING latin1),
             '$[*]' COLUMNS(x VARCHAR(10) CHARACTER SET utf8mb4 PATH '$' ERROR ON ERROR)
           ) AS jt;
      SHOW WARNINGS;

      The recorded output was:

      unquote_control  control_hex  valid_json
      Ф                D0A4         1
      x  extracted_hex
       
      Level    Code  Message
      Warning  4035  Broken JSON string in argument 0 to function 'st_append_json' at position 0

      JSON_VALID confirms that the input is valid, and JSON_UNQUOTE demonstrates that \u0424 decodes to Ф with UTF-8 hex D0A4. JSON_TABLE should therefore return Ф and D0A4 for the utf8mb4 output column without a broken-JSON warning. If conversion were to fail, ERROR ON ERROR should not silently produce an empty value.

      Attachments

        Activity

          People

            rucha174 Rucha Deodhar
            faner fan
            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.