MySQL Bugs: #95698: JSON columns are returned with binary charset

Bug #95698	JSON columns are returned with binary charset
Submitted:	7 Jun 2019 22:40	Modified:	4 May 6:30
Reporter:	Daniel Book	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.16, 5.7.26	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When returning the value of a JSON column in a SELECT statement, the type of the column in the result is incorrectly marked as a charset of 63 (binary) instead of utf8mb4 as requested by the default character_set_results setting. This results in the client being unable to decode the JSON from the appropriate character set as it would with other string responses. While JSON is conceptually binary data, it is treated in MySQL as encoded text, as can be seen in how it is handled in query input and the return of the JSON_OBJECT() function (which does get marked with the correct charset, as shown here: https://github.com/jhthorsen/mojo-mysql/issues/57#issuecomment-470780673)

How to repeat:
A test script is shown here: https://github.com/jhthorsen/mojo-mysql/issues/57#issuecomment-471255826 and the charset marked on the returned column is noted here: https://github.com/jhthorsen/mojo-mysql/issues/57#issuecomment-471310940

It can be reproduced by running the queries directly, and examining the bytes of the response to the SELECT.

Suggested fix:
JSON columns returned in SELECT statements should be marked correctly like any other string return.

Hello Daniel,

Thank you for the report!

regards,
Umesh

= 8.0.16

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
Field   1:  `jdoc`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       JSON
Collation:  binary (63)
Length:     4294967295
Max_length: 36
Decimals:   0
Flags:      BLOB BINARY

+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| {"key1": "value1", "key2": "value2"} |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> \s
--------------
bin/mysql  Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Connection id:          12
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.16 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4

- 5.7.26 (default charset/collation)

mysql> use test
Database changed
mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
Field   1:  `jdoc`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       JSON
Collation:  binary (63)
Length:     4294967295
Max_length: 36
Decimals:   0
Flags:      BLOB BINARY

+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| {"key1": "value1", "key2": "value2"} |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> \s
--------------
bin/mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          more
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql_ushastry.sock
Uptime:                 28 sec

Hello, is there any update to the status of this issue? Thank you