Bug #85188 warnings of explain extended does not convert charset
Submitted: 27 Feb 2017 2:33 Modified: 27 Feb 2017 7:12
Reporter: Kaiwang CHen (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.34,5.6.35, 5.7.17 OS:Any
Assigned to: CPU Architecture:Any

[27 Feb 2017 2:33] Kaiwang CHen
Description:
SHOW WARNINGS immediately after EXPLAIN EXTENDED displays optimizer result in the 'Message' field.  When the WHERE clause of the original sql uses a unique key, the optimizer will replace WHERE clause with 'where 1' and replace items in SELECT clause with actual values. The Message field of SHOW WARNINGS is expected to be of a certain charset ('UTF-8' in a utf8 connection), however, the actual bytes of item is always of its definition charset, for example,for a VARCHAR(10) in a table with GBK as DEFAULT CHARSET, the actual bytes is of GBK rather than UTF-8, which causes messy characters.

How to repeat:
CREATE TABLE `t_gbk` (
  `id` int(11) NOT NULL,
  `str` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk

set names utf8;
insert t_gbk values (1, '中国');

select str from t_gbk where id = 1;
+--------+
| str    |
+--------+
| 中国   |
+--------+

explain extended select str from t_gbk where id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_gbk
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select '�й�' AS `str` from `employees`.`t_gbk` where 1
1 row in set (0.00 sec)

Notice the messy characters in the message field. We can pipe to iconv to get them right.

./msb_5_6_34/use employees -e 'set names utf8; explain extended select str from t_gbk where id = 1\G show warnings\G' | iconv -f gbk -t utf8
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_gbk
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select '中国' AS `str` from `employees`.`t_gbk` where 1
[27 Feb 2017 7:12] MySQL Verification Team
Hello Kaiwang CHen,

Thank you for the report.
Observed with 5.6.35, 5.7.17 builds.

Thanks,
Umesh