| Bug #64298 | ascii does not work on multibyte characters | ||
|---|---|---|---|
| Submitted: | 10 Feb 2012 20:24 | Modified: | 11 Feb 2012 9:03 |
| Reporter: | Miran Cvenkel | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.1.59, 5.5.20 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[11 Feb 2012 9:03]
Valeriy Kravchuk
Thank you for the problem report. Indeed, these results look unexpected:
macbook-pro:5.5 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 11
Server version: 5.5.20-debug Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select ascii('Шума'),SUBSTR('Шума',1,1),SUBSTR('Шума',2,1),SUBSTR('Шума',3,1),SUBSTR('Шума',4,1)\G
*************************** 1. row ***************************
ascii('Шума'): 208
SUBSTR('Шума',1,1): Ш
SUBSTR('Шума',2,1): у
SUBSTR('Шума',3,1): м
SUBSTR('Шума',4,1): а
1 row in set (0.00 sec)
mysql> select
-> ascii('Шума'),ascii(SUBSTR('Шума',1,1)),ascii(SUBSTR('Шума',2,1)),ascii(SUBSTR('Шума',3,1)),ascii(SUBSTR('Шума',4,1))
->
-> \G
*************************** 1. row ***************************
ascii('Шума'): 208
ascii(SUBSTR('Шума',1,1)): 208
ascii(SUBSTR('Шума',2,1)): 209
ascii(SUBSTR('Шума',3,1)): 208
ascii(SUBSTR('Шума',4,1)): 208
1 row in set (0.10 sec)
mysql> show variables like 'char%';
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.08 sec)
[11 Feb 2012 9:21]
Peter Laursen
Another example:
-- all those return '195'
SELECT ASCII('æ');
SELECT ASCII('ø');
SELECT ASCII('å');
SELECT ASCII('ä');
SELECT ASCII('ö');
SELECT ASCII('ü');
SHOW VARIABLES LIKE 'char%';
/*
Variable_name Value
------------------------ ---------------------------------------------------------
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\
*/
If ASCII() -function is not supposed to work with arguments outside the ascii-range an error like 'argument out of range' should be returned IMHO. And if it is supposed to work then it needs clarification if it is character_set_server, character_set_connection or whatever character_set_* encoding that is used for the lookup/calculation.
Peter
(not a MySQL person)
[30 Aug 2015 17:06]
Daniël van Eeden
Also affects MySQL 5.7.8
Description: QUOTE: ASCII() Return numeric value of left-most character test this: select ascii('Шума'),ascii(SUBSTR('Шума',1,1)),ascii(SUBSTR('Шума',2,1)),ascii(SUBSTR('Шума',3,1)),ascii(SUBSTR('Шума',4,1)) How to repeat: see description