| 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: | |
| 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 | |
[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)
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()