Details
Description
JSON functions seem be fine with processing UTF8MB4 characters. The only exception is JSON_UNQUOTE which tries to produce UTF8MB3 and so destroys any non-mb3 characters.
Testcase: (the smiley is this character: https://www.compart.com/en/unicode/U+1F60A)
set names utf8mb4; |
set @v='{ "color":"😊" }'; |
select @v, collation(@v); |
select json_valid(@v); |
select json_extract(@v,'$.color'), collation(json_extract(@v,'$.color')); |
select json_unquote(json_extract(@v,'$.color')), collation(json_unquote(json_extract(@v,'$.color'))); |
Run it and:
MariaDB [test]> set names utf8mb4;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> set @v='{ "color":"😊" }';
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
MariaDB [test]> select @v, collation(@v);
|
+--------------------+-----------------------+
|
| @v | collation(@v) |
|
+--------------------+-----------------------+
|
| { "color":"😊" } | utf8mb4_uca1400_ai_ci |
|
+--------------------+-----------------------+
|
1 row in set (0.001 sec)
|
Note that JSON_VALID and JSON_EXTRACT work:
MariaDB [test]> select json_valid(@v);
|
+----------------+
|
| json_valid(@v) |
|
+----------------+
|
| 1 |
|
+----------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [test]> select json_extract(@v,'$.color'), collation(json_extract(@v,'$.color'));
|
+----------------------------+---------------------------------------+
|
| json_extract(@v,'$.color') | collation(json_extract(@v,'$.color')) |
|
+----------------------------+---------------------------------------+
|
| "😊" | utf8mb4_uca1400_ai_ci |
|
+----------------------------+---------------------------------------+
|
1 row in set (0.001 sec)
|
But try removing the quotes and the smiley is lost:
MariaDB [test]> select json_unquote(json_extract(@v,'$.color')), collation(json_unquote(json_extract(@v,'$.color')));
|
+------------------------------------------+-----------------------------------------------------+
|
| json_unquote(json_extract(@v,'$.color')) | collation(json_unquote(json_extract(@v,'$.color'))) |
|
+------------------------------------------+-----------------------------------------------------+
|
| ? | utf8mb3_general_ci |
|
+------------------------------------------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
Attachments
Issue Links
- relates to
-
MDEV-35616 Add basic optimizer support for virtual columns
-
- In Testing
-