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

[8 Oct 2010 17:00] Guilhem Bichot
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.
[8 Oct 2010 20:09] Guilhem Bichot
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.
[9 Oct 2010 9:27] Peter Laursen
I cannot reproduce with SQLyog and MySQL 5.1.51 on Windows

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

[9 Oct 2010 15:06] Guilhem Bichot
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.
[11 Oct 2010 10:29] Susanne Ebrecht
This could be a duplicate of bug #1406.
[11 Oct 2010 12:07] Guilhem Bichot
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.
[11 Oct 2010 16:19] MySQL Verification Team
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>
[21 Mar 2011 19:35] Paul DuBois
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