Bug #56402 QUOTE() forgets closing quote
Submitted: 31 Aug 2010 12:10 Modified: 31 Aug 2010 12:15
Reporter: Ralf Neubauer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.90, 5.1.44, 5.1.51-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[31 Aug 2010 12:10] Ralf Neubauer
Description:
QUOTE() forgets the closing quote, if the CHAR(1) column has charset utf8 and the result is sorted or used as subselect.

Just
     select a, QUOTE(a) q
     from quotest
or charset latin1 work as expected.

mysql> select t.*, length(q), char_length(q)
    -> from (
    -> select a, QUOTE(a) q
    -> from quotest
    -> ) t;
+---+------+-----------+----------------+
| a | q    | length(q) | char_length(q) |
+---+------+-----------+----------------+
|   | ''   |         2 |              2 |
| a | 'a   |         2 |              2 |
| b | 'b   |         2 |              2 |
+---+------+-----------+----------------+
3 rows in set (0.00 sec)

mysql> select version(), a, QUOTE(a) q
    -> from quotest
    -> order by rand();
+--------------------------+---+------+
| version()                | a | q    |
+--------------------------+---+------+
| 5.0.90-enterprise-gpl-nt | b | 'b   |
| 5.0.90-enterprise-gpl-nt |   | ''   |
| 5.0.90-enterprise-gpl-nt | a | 'a   |
+--------------------------+---+------+
3 rows in set (0.00 sec)

also happens with

mysql> select version(), a, QUOTE(a) q
    -> from quotest
    -> order by rand();
+------------------+---+----+
| version()        | a | q  |
+------------------+---+----+
| 5.1.44-community | b | 'b |
| 5.1.44-community | a | 'a |
| 5.1.44-community |   | '' |
+------------------+---+----+
3 rows in set (0.02 sec)

mysql> show variables like '%char%';
...
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8 |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | utf8 |
| character_set_system     | utf8 |

How to repeat:
drop temporary table if exists quotest;
create temporary table quotest (a char(1) not null)  DEFAULT CHARSET=utf8
select '' a union all select 'a' a union all select 'b' a;
select t.*, length(q), char_length(q)
from (
select a, QUOTE(a) q
from quotest
) t;
select version(), a, QUOTE(a) q
from quotest
order by rand()
[31 Aug 2010 12:15] Valeriy Kravchuk
Verified with current mysql-5.1 from bzr on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.51-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> drop temporary table if exists quotest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create temporary table quotest (a char(1) not null)  DEFAULT CHARSET=utf8    -> select '' a union all select 'a' a union all select 'b' a;
Query OK, 3 rows affected (0.37 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select t.*, length(q), char_length(q)
    -> from (
    -> select a, QUOTE(a) q
    -> from quotest
    -> ) t;
+---+----+-----------+----------------+
| a | q  | length(q) | char_length(q) |
+---+----+-----------+----------------+
|   | '' |         2 |              2 |
| a | 'a |         2 |              2 |
| b | 'b |         2 |              2 |
+---+----+-----------+----------------+
3 rows in set (0.02 sec)

mysql> select version(), a, QUOTE(a) q
    -> from quotest
    -> order by rand();
+------------------+---+----+
| version()        | a | q  |
+------------------+---+----+
| 5.1.51-debug-log | a | 'a |
| 5.1.51-debug-log |   | '' |
| 5.1.51-debug-log | b | 'b |
+------------------+---+----+
3 rows in set (0.02 sec)

mysql>  select a, QUOTE(a) q
    ->      from quotest
    -> ;
+---+-----+
| a | q   |
+---+-----+
|   | ''  |
| a | 'a' |
| b | 'b' |
+---+-----+
3 rows in set (0.00 sec)