Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.16, 10.2(EOL), 10.3(EOL)
-
None
Description
Let's say that we have the following table:
CREATE TABLE json_test (
|
json_col TEXT
|
) DEFAULT CHARSET=latin1;
|
|
|
INSERT INTO json_test VALUES ('{"key1":"Sí"}');
|
We can get the value of key1 by executing the following:
SELECT JSON_VALUE(json_col, '$.key1') FROM json_test;
|
And this works fine:
MariaDB [db1]> SELECT JSON_VALUE(json_col, '$.key1') FROM json_test;
|
+--------------------------------+
|
| JSON_VALUE(json_col, '$.key1') |
|
+--------------------------------+
|
| Sí |
|
+--------------------------------+
|
1 row in set (0.00 sec)
|
But if we combine this with string functions, then the character set gets messed up. For example, run the following queries instead:
SELECT REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') FROM json_test;
|
SELECT CONCAT(json_value(json_col, '$.key1'), 'a') FROM json_test;
|
And we get something unexpected:
MariaDB [db1]> SELECT REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') FROM json_test;
|
+-----------------------------------------------------+
|
| REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') |
|
+-----------------------------------------------------+
|
| SÃ |
|
+-----------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [db1]> SELECT CONCAT(json_value(json_col, '$.key1'), 'a') FROM json_test;
|
+---------------------------------------------+
|
| CONCAT(json_value(json_col, '$.key1'), 'a') |
|
+---------------------------------------------+
|
| SÃa |
|
+---------------------------------------------+
|
1 row in set (0.00 sec)
|
We can workaround this problem by explicitly converting the JSON_VALUE return value to a given character set:
MariaDB [db1]> SELECT REPLACE(CONVERT(JSON_VALUE(json_col, '$.key1') USING utf8), 'null', '') FROM json_test;
|
+-------------------------------------------------------------------------+
|
| REPLACE(CONVERT(JSON_VALUE(json_col, '$.key1') USING utf8), 'null', '') |
|
+-------------------------------------------------------------------------+
|
| Sí |
|
+-------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Status | Open [ 1 ] | Confirmed [ 10101 ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] | |
| Assignee | Alexander Barkov [ bar ] |
| Assignee | Alexander Barkov [ bar ] | Alexey Botchkov [ holyfoot ] |
| Status | Confirmed [ 10101 ] | In Progress [ 3 ] |
| issue.field.resolutiondate | 2018-08-05 15:08:34.0 | 2018-08-05 15:08:34.499 |
| Fix Version/s | 10.2.17 [ 23111 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | In Progress [ 3 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 88674 ] | MariaDB v4 [ 154739 ] |
| Zendesk Related Tickets | 183139 |