MySQL Bugs: #5412: text columns: trailing spaces behave differently than documented

Bug #5412	text columns: trailing spaces behave differently than documented
Submitted:	4 Sep 2004 18:38	Modified:	21 Mar 2005 23:43
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.20	OS:	Linux (Debian GNU/Linux)
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Documentation states:
  http://dev.mysql.com/doc/mysql/en/BLOB.html
  > There is no trailing-space removal for BLOB and TEXT columns when values are 
  > stored or retrieved.

But in reality when selecting with or without an index on this text column, trailing spaces in the table are ignored although still present according to mysqldump.

How to repeat:
    CREATE TABLE `makes` (
          `make_desc` text NOT NULL
     ) TYPE=MyISAM
     INSERT INTO makes VALUE ("Chevy");
     INSERT INTO makes VALUE ("Chevy "); -- one trailing space

  mysql> SELECT * FROM makes WHERE make_desc = "Chevy";
  +-----------+
  | make_desc |
  +-----------+
  | Chevy     |
  | Chevy     |
  +-----------+
  2 rows in set (0.00 sec)
^^^^^^^^^^ Should be only ONE column

But the space is still present according to mysqldump:
INSERT INTO `makes` VALUES ('Chevy');
INSERT INTO `makes` VALUES ('Chevy ');  -- here is a trailing space

 

Suggested fix:
none

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.20a-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE makes (
    -> make_desc text NOT NULL
    -> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO makes VALUEs ("Chevy");
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO makes VALUEs ("Chevy ");
Query OK, 1 row affected (0.00 sec)

mysql> select length(make_desc) from makes;
+-------------------+
| length(make_desc) |
+-------------------+
|                 5 |
|                 6 |
+-------------------+
2 rows in set (0.02 sec)

mysql> select * from makes where make_desc like "Chevy";
+-----------+
| make_desc |
+-----------+
| Chevy     |
+-----------+
1 row in set (0.01 sec)

Hello Miguel

Why didn't you try the SELECT statement I used in my example? With "LIKE" it works, yes, but I was complaining about " = " which shows a wrong behaviour (i.e. 2 instead of 1 row)!

-christian-

P.S.: Here again, compare "=" and "LIKE":
--------------
DROP TABLE IF EXISTS makes
--------------

--------------
CREATE TABLE makes (make_desc text not null) TYPE=MyISAM
--------------

--------------
INSERT INTO makes VALUES ("Chevy")
--------------

--------------
INSERT INTO makes VALUES ("Chevy ")
--------------

--------------
SELECT length(make_desc) FROM makes WHERE make_desc = "Chevy"
--------------

length(make_desc)
5
6
--------------
SELECT * FROM makes WHERE make_desc = "Chevy"
--------------

make_desc
Chevy
Chevy 
--------------
SELECT length(make_desc) FROM makes WHERE make_desc like "Chevy"
--------------

length(make_desc)
5
--------------
SELECT * FROM makes WHERE make_desc like "Chevy"
--------------

make_desc
Chevy

Hi Christian,

I did my own select after your repeat sample failed with the insert
statement with clause VALUE(s).

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

Additional info:

TEXT columns remembers end space, but comparision are done according to the same rules as CHAR/VARCHAR (In 4.1 and above according to the rules of the used collation).
This means that strings (that are not binary) are padded with end space on comparision, which your test cases shows.
MS-SQL server does the comparsion in a similar manner.