MySQL Bugs: #53469: Concat eating up space characters

Bug #53469	Concat eating up space characters
Submitted:	6 May 2010 17:08	Modified:	31 Dec 2010 19:12
Reporter:	a b	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.67	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	concat, space

Description:
The concat function with the data type char(1) seems to drop space characters.

How to repeat:
The following function should return an exact copy if the input string, but it loses the spaces:

drop function if exists eatSpace;
delimiter //
create function eatSpace( myText text) returns text
begin
  declare r text default '';
  declare i int default 1;
  declare c char(1);
  while (i <= length(myText)) do 
    set c = substr(myText,i,1);
    set r = concat( r, c);
    set i = i + 1;
  end while;
  return r;
end
//
delimiter ;
select eatSpace('a b c');
==> 'abc'

Suggested fix:
Workaround: don't use char(1) for single character variables. In this case:

drop function if exists eatSpace;
delimiter //
create function eatSpace( myText text) returns text
begin
  declare r text default '';
  declare i int default 1;
  while (i <= length(myText)) do 
    set r = concat( r, substr(myText,i,1));
    set i = i + 1;
  end while;
  return r;
end
//
delimiter ;
select eatSpace('a b c');
==> 'a b c'

I think this is not a bug actually, as our manual (http://dev.mysql.com/doc/refman/5.0/en/char.html) clearly says:

"When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed."

Like this:

mysql> create table tc(c1 char(3));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into tc values (' '), ('  '), ('   ');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select c1, length(c1) from tc;
+------+------------+
| c1   | length(c1) |
+------+------------+
|      |          0 |
|      |          0 |
|      |          0 |
+------+------------+
3 rows in set (0.00 sec)

mysql> alter table tc modify c1 varchar(3);
Query OK, 3 rows affected (0.40 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> delete from tc;
Query OK, 3 rows affected (0.00 sec)

mysql> insert into tc values (' '), ('  '), ('   ');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select c1, length(c1) from tc;
+------+------------+
| c1   | length(c1) |
+------+------------+
|      |          1 |
|      |          2 |
|      |          3 |
+------+------------+
3 rows in set (0.00 sec)

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

use CONCAT_WS this meas concatenate with seperator explaned in http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

Closing as "Not a Bug" because the reason which Valeriy provided earlier.