Bug #1297 trailing whitespaces not inserted in table row
Submitted: 16 Sep 2003 7:20 Modified: 17 Sep 2003 5:10
Reporter: Ingo Schmitt Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.12 / 4.0.15 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[16 Sep 2003 7:20] Ingo Schmitt
Description:
When inserting Data in a char (not varchar!) field trailing whitspaces are stripped off.

How to repeat:
Create a table similar to our table:

CREATE TABLE `t_westfalia_artikel_import` (
  `aiber` char(2) NOT NULL default '',
  `aiarnr` int(10) NOT NULL default '0',
  `aiers` int(9) default NULL,
  `aityp` int(1) default NULL,
  `aibez1` char(34) default NULL,
  `aibez2` char(34) default NULL,
  `aiwhgr` int(10) NOT NULL default '0',
  `aiwgr` int(10) NOT NULL default '0',
  `aifanr` int(10) NOT NULL default '0',
  `aigew` char(7) default NULL,
  `aiinkz` char(1) default NULL,
  `aialt` char(1) default NULL,
  `aibdan` char(7) default NULL,
  `aigrv` char(1) default NULL,
  `aigrzm` char(2) default NULL,
  `aigrbu` char(2) default NULL,
  `aibekz` int(1) default NULL,
  `aibstt` char(2) default NULL,
  `aibsmm` char(2) default NULL,
  `aibsjj` char(4) default NULL,
  `aibest` char(7) default NULL,
  `aiwkz` int(1) default NULL,
  `aiprei` decimal(9,2) default NULL,
  `aispr1` decimal(9,2) default NULL,
  `aispr2` decimal(9,2) default NULL,
  `aispr3` decimal(9,2) default NULL,
  `aisme1` char(7) default NULL,
  `aisme2` char(7) default NULL,
  `aisme3` char(7) default NULL,
  `aiknr1` char(5) default NULL,
  `aisei1` char(4) default NULL,
  `aiknr2` char(5) default NULL,
  `aisei2` char(4) default NULL,
  `aiknr3` char(5) default NULL,
  `aisei3` char(4) default NULL,
  `aiknr4` char(5) default NULL,
  `aisei4` char(4) default NULL,
  `aiknr5` char(5) default NULL,
  `aisei5` char(4) default NULL,
  `aiknr6` char(5) default NULL,
  `aisei6` char(4) default NULL,
  `aistat` char(1) default NULL,
  `aigara` decimal(3,1) default NULL,
  `aifatxt` char(120) default NULL,
  `afill1` char(6) default NULL,
  `afgrat` char(1) default NULL,
  `afill3` char(6) default NULL,
  `afill4` char(1) default NULL,
  `afill5` char(20) default NULL,
  `aiarnr_str` char(20) default NULL,
  `status` tinyint(1) default NULL,
  `fehler` tinyint(1) default NULL,
  `bearbdatum` datetime default NULL,
  UNIQUE KEY `airn` (`aiarnr`),
  KEY `aiber` (`aiber`),
  KEY `aiers` (`aiers`),
  KEY `aityp` (`aityp`),
  KEY `aiwhgr` (`aiwhgr`),
  KEY `aiwgr` (`aiwgr`),
  KEY `aifanr` (`aifanr`),
  KEY `bearbdatum` (`bearbdatum`),
  KEY `aifatxt` (`aifatxt`),
  KEY `aiarnr_str` (`aiarnr_str`)
) TYPE=InnoDB;

And insert some Data:

 insert into t_westfalia_artikel_import (aiber,aiarnr,aiers,aityp,aibez1,aibez2,aiwhgr,aiwgr,aifanr,aigew,aiinkz,aialt,aibdan,aigrv,aigrzm,aigrbu,aibekz,aibstt,aibsmm,aibsjj,aibest,aiwkz,aiprei,aispr1,aispr2,aispr3,aisme1,aisme2,aisme3,aiknr1,aisei1,aiknr2,aisei2,aiknr3,aisei3,aiknr4,aisei4,aiknr5,aisei5,aiknr6,aisei6,aistat,aigara ,aifatxt,afill1,afgrat,afill3,afill4,afill5,fehler) VALUES ('58','000917385','000000000',' ','ERSATZ-NÄGEL 20 MM 5000 STÜCK/    ','VERPACKUNG                        ','010','107','000917369','0000940',' ',' ','0000000',' ','00','00','1','00','00','0000','0000000','2','4.75','0.00','0.00','0.00','0000000','0000000','0000000','04727','0053','04370','0136','04198','0194','04066','0045','03597','0047','03682','0134','1','0.0 ','                                                                                                                        ','000000',' ','      ',' ','0','0');

If you selct data from the field aibez1 you shold get "ERSATZ-NÄGEL 20 MM 5000 STÜCK/    ", but you get "ERSATZ-NÄGEL 20 MM 5000 STÜCK/". 

(select aibez1, length(aibez1) from t_westfalia_artikel_import) 
The length returns 30 Chars instead of 34 Chars, the value of aibez1 is "ERSATZ-NÄGEL 20 MM 5000 STÜCK/".

Suggested fix:
Return the Value with the trailing whitspaces
[16 Sep 2003 7:21] Ingo Schmitt
Create Statement

Attachment: table_create.sql (application/octet-stream, text), 2.47 KiB.

[16 Sep 2003 15:11] Lenz Grimmer
This is a known and documented deficiency of MySQL - see http://www.mysql.com/doc/en/Open_bugs.html:

"All string columns, except BLOB and TEXT columns, automatically have all trailing spaces removed when retrieved. For CHAR types this is okay, and may be regarded as a feature according to SQL-92. The bug is that in MySQL Server, VARCHAR columns are treated the same way."
[17 Sep 2003 5:03] Alexander Keremidarski
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
[17 Sep 2003 5:10] Alexander Keremidarski
We have plans to implement true VARCHAR support according to ANSI specification in future releases.