Bug #21152 a char type field don't save trailing spaces

• Submitted: 19 Jul 2006 14:16
• Modified: 19 Jul 2006 16:16
• Reporter: Lu Tao
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.11beta
• OS: Solaris (Solaris)
• CPU Architecture: Any
• Tags: char, ending spaces

[19 Jul 2006 14:16] Lu Tao

Description:
we know a char() type field should have FIXED length as it was defined, and if insert a string whose length less than field size, some spaces should be added at the end of string.
oracle do it well.
SQL> create table ta(name char(10));

Table created.

SQL> insert into ta values (' Tom Kyte ');

1 row created.

SQL> select * from ta;

NAME
----------
 Tom Kyte

SQL> select concat(name,' is a good man') from ta;

CONCAT(NAME,'ISAGOODMAN'
------------------------
 Tom Kyte  is a good man

SQL>  select length(name) from ta;

LENGTH(NAME)
------------
          10

SQL>  update ta set name=trim(name);

1 row updated.

SQL> select length(name) from ta;

LENGTH(NAME)
------------
          10

SQL> SQL> select concat(name,' is a good man') from ta;

CONCAT(NAME,'ISAGOODMAN'
------------------------
Tom Kyte   is a good man

SQL> 
but mysql seems to regard char() as varchar(), trim ending spaces automaticly

How to repeat:
create table ta(name char(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ta values (' Tom Kyte ');
Query OK, 1 row affected (0.00 sec)

mysql> select length(name) from ta;
+--------------+
| length(name) |
+--------------+
|            9 | 
+--------------+
1 row in set (0.00 sec)

mysql> update ta set name=trim(name);
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select length(name) from ta;
+--------------+
| length(name) |
+--------------+
|            8 | 
+--------------+
1 row in set (0.00 sec)

mysql> select concat(name,' is a good man') from ta;
+-------------------------------+
| concat(name,' is a good man') |
+-------------------------------+
| Tom Kyte is a good man        | 
+-------------------------------+
1 row in set (0.00 sec)

Suggested fix:
do as oracle does

[19 Jul 2006 16:01] Hartmut Holzgraefe

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 

See also http://dev.mysql.com/doc/refman/5.0/en/char.html

[19 Jul 2006 16:16] Lu Tao

I notice
The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces ARE removed.
but why retrieved values trailing spaces ARE removed?