Bug #95698 JSON columns are returned with binary charset

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