MySQL Bugs: #48980: mysql client field length for utf8 is twice number of letters (is

Bug #48980	mysql client field length for utf8 is twice number of letters (is
Submitted:	23 Nov 2009 10:00	Modified:	24 Nov 2009 6:47
Reporter:	Peter Volkov	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.0.84, 5.0, 5.1 bzr	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
It looks like mysql client uses LENGTH instead of CHAR_LENGTH for field formatting.

mysql> select a,CHAR_LENGTH(a),LENGTH(a),HEX(a) from `test`;
+--------+----------------+-----------+--------------+
| a      | CHAR_LENGTH(a) | LENGTH(a) | HEX(a)       |
+--------+----------------+-----------+--------------+
| абв    |              3 |         6 | D0B0D0B1D0B2 | 
+--------+----------------+-----------+--------------+
1 row in set (0.00 sec)

The first field length is 6+2 instead of 3+2.
This additional space makes following fields hard to read.

My locale is ru_RU.UTF-8.

How to repeat:
 $ mysql --default-character-set=utf8 test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.0.84 Gentoo Linux mysql-5.0.84-r1

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

mysql> use U
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create database U;
+----------+------------------------------------------------------------+
| Database | Create Database                                            |
+----------+------------------------------------------------------------+
| U        | CREATE DATABASE `U` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table test (a text);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values ('абв');
Query OK, 1 row affected (0.00 sec)

mysql> select a,CHAR_LENGTH(a),LENGTH(a),HEX(a) from `test`;
+--------+----------------+-----------+--------------+
| a      | CHAR_LENGTH(a) | LENGTH(a) | HEX(a)       |
+--------+----------------+-----------+--------------+
| абв    |              3 |         6 | D0B0D0B1D0B2 | 
+--------+----------------+-----------+--------------+
1 row in set (0.00 sec)

The length of a field is for 6 letters instead of 3.

mysql> show create table `test`;
+-------+-----------------------------------------------------------------------+
| Table | Create Table                                                          |
+-------+-----------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `a` text
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

And if I add more letters I'll have:

mysql> insert into test values ('абвгдеё');
Query OK, 1 row affected (0.01 sec)

mysql> select a,CHAR_LENGTH(a),LENGTH(a),HEX(a) from `test` ;
+----------------+----------------+-----------+------------------------------+
| a              | CHAR_LENGTH(a) | LENGTH(a) | HEX(a)                       |
+----------------+----------------+-----------+------------------------------+
| абв            |              3 |         6 | D0B0D0B1D0B2                 | 
| абвгдеё        |              7 |        14 | D0B0D0B1D0B2D0B3D0B4D0B5D191 | 
+----------------+----------------+-----------+------------------------------+
2 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read bug #7616 for explanation.

Thank you. I saw that bug before and I don't think it is relevant here and to avoid mixing with that bug I even showed explicitly that I've started mysql with --default-character-set=utf8 option. Also note that same problem is there in answer of Alexander Barkov:

mysql> select * from test;
+--------+
| a      |
+--------+
| aaa    |
     ^^^ - this space is a bug reported here.

Thank you for the feedback.

I can not repeat described behavior if start mysql client with option --default-character set. Screenshot will be uploaded shortly.

Have you issued SET NAMES UTF8; before running SELECT query? Which package do you use (filename you downloaded)? Could you please try current version 5.0.88 available at our download site and check if bug still exists in your environment?

correct call of client

Attachment: correct_call.jpg (image/jpeg, text), 69.64 KiB.

incorrect call of client

Attachment: incorrect_call.jpg (image/jpeg, text), 60.34 KiB.

Take a look at your at _correct_call.jpg_  screenshot. It has bug I'm talking about here. Note space after абв and before |

Again:
+--------+
| a      |
+--------+
| абв    |

     ^^^ this three spaces are bug.

The number of spaces is equal to three only in case of three letter, and if you put even my rather short name there: Пётр Волков you'll see:

mysql> select * from test;
+-----------------------+
| a                     |
+-----------------------+
| абв                   | 
| абвгдеё               | 
| Пётр Волков           | 
             ^^^^^^^^^^ - how many space is here... :(

And this space makes following fields hard to read since they may occur on next lines...

And just compare how it should look in case of english letters:

mysql> select * from test;
+--------+
| a      |
+--------+
| abc    | 
| abcdef | 
+--------+
2 rows in set (0.00 sec)

after letter f exactly single space and then |

HTH.

correct_call.jpg with bug highlighted

Attachment: correct_call.jpg (image/jpeg, text), 70.75 KiB.

Thank you for the feedback.

Got your point. Set as "Verified".

Reproducable on 5.6.21.

mysql [test2] > CREATE TABLE `test` (
    ->   `a` text
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql [test2] > insert into test values ('абвгдеё');
Query OK, 1 row affected (0.01 sec)

mysql [test2] > select * from test;
+----------------+
| a              |
+----------------+
| абвгдеё        |
+----------------+
1 row in set (0.00 sec)