MySQL Bugs: #57341: Query in EXPLAIN EXTENDED shows wrong characters

Bug #57341	Query in EXPLAIN EXTENDED shows wrong characters
Submitted:	8 Oct 2010 17:00	Modified:	21 Mar 2011 19:35
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	next-mr-bugfixing	OS:	Linux
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
EXPLAIN EXTENDED produces a query visible in SHOW WARNINGS as designed; this query can contain wrong characters if the original query used certain characters and character sets.

How to repeat:
Using revision-id:marc.alff@oracle.com-20101007211641-tq8ozhwefl5dkzq2
BUILD/compile-pentium64-valgrind-max.
I set up a terminal like in BUG#57306; start mysqld like this:
cd mysql-test
./mtr --mem alias --start &
then I connect to it using options like in BUG#57306:
../client/mysql -S ./var/tmp/mysqld.1.sock test --default-character-set=latin1 -uroot
and I do
mysql> explain extended select 'ÁÂÃÄÅ' from dual where concat(rand(),'ÁÂÃÄÅ')='ÁÂÃÄÅ';
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------+
| Level | Code | Message                                                                            |
+-------+------+------------------------------------------------------------------------------------+
| Note  | 1003 | select '�����' AS `ÁÂÃÄÅ` from DUAL  where (concat(rand(),'�����') = '�����') |
+-------+------+------------------------------------------------------------------------------------+
mysql> show variables like "character%";
+--------------------------+---------------------------------------------------------------------------+
| Variable_name            | Value                                                                     |
+--------------------------+---------------------------------------------------------------------------+
| character_set_client     | latin1                                                                    |
| character_set_connection | latin1                                                                    |
| character_set_database   | latin1                                                                    |
| character_set_filesystem | binary                                                                    |
| character_set_results    | latin1                                                                    |
| character_set_server     | latin1                                                                    |
| character_set_system     | utf8                                                                      |
| character_sets_dir       | /home/mysql_src/bzrrepos_new/mysql-next-mr-bugfixing2/sql/share/charsets/ |
+--------------------------+---------------------------------------------------------------------------+

As you can see, in SHOW WARNINGS:
| Note  | 1003 | select '�����' AS `ÁÂÃÄÅ` from DUAL  where (concat(rand(),'�����') = '�����') |
the argument of SELECT and the WHERE condition have wrong characters.

EXPLAIN EXTENDED builds its query like this (sql_parse.cc):
        char buff[1024];
        String str(buff,(uint32) sizeof(buff), system_charset_info);
        str.length(0);
        thd->lex->unit.print(&str, QT_ORDINARY);
        str.append('\0');
        push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
                     ER_YES, str.ptr());
We see that output of unit::print() is requested in utf8 (system_charset_info); looks like unit::print() fails to honour this request and should be fixed.

I cannot reproduce with SQLyog and MySQL 5.1.51 on Windows

Attachment: sqlyog.jpg (image/jpeg, text), 45.25 KiB.

please notify me when/how this bug is fixed, it impacts optimizer trace. In the optimizer trace, it's the "AS xxx" part which comes out wrong.

This could be a duplicate of bug #1406.

Hello. BUG#1406 is said to have been fixed by WL#751. The tree which I used to repeat this bug contains WL#751. So it does not look like a duplicate.

Thank you for the bug report. Verified as described:

miguel@tikal:~/bzr/next-mr-bugfixing/mysql-test$ ../client/mysql -S ./var/tmp/mysqld.1.sock test --default-character-set=latin1 -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.99-m5-debug-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> explain extended select 'ÁÂÃÄÅ' from dual where
    -> concat(rand(),'ÁÂÃÄÅ')='ÁÂÃÄÅ';
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------+
| Level | Code | Message                                                                            |
+-------+------+------------------------------------------------------------------------------------+
| Note  | 1003 | select '�����' AS `ÁÂÃÄÅ` from DUAL  where (concat(rand(),'�����') = '�����') |
+-------+------+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Noted in 5.5.11, 5.6.2 changelogs.

SHOW WARNINGS output following EXPLAIN EXTENDED could include
unprintable characters. 

CHANGESET - http://lists.mysql.com/commits/132486