Details
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.