Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.16, 10.2, 10.3
-
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)
|