MySQL Bugs: #7048: whitespace gets chopped off for char and varchar fields for "load data"

Bug #7048	whitespace gets chopped off for char and varchar fields for "load data"
Submitted:	6 Dec 2004 19:26	Modified:	6 Dec 2004 20:54
Reporter:	Mark nielsen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:

  ##Save this to test1.txt. Make sure there are spaces after "test1".
testspaces1         
testspaces2   2    

create database test1;
\r test1
create table table1 (field1 varchar(16));
load data local infile 'test1.txt' into table table1;

Now, run my script which I describe below, and I get this result. 
'testspaces1'
'testspaces2   2'

And the spaces aren't there at the end of the file. 
This problem occurs for char and varchar fields but not text fields. 
I tried to look through the documentation and bugs about whitespace issues at the end of fields, but I didn't find any problems. 

I don't have problems when I manually insert data. It is just from "load data".
Mark

How to repeat:
  ##Save this to test1.txt
  ## Conver the \t to tabs. 
test1           \ttest3
test2   2       \ttest4

create database test1;
create table table1 (field1 varchar(16));
load data local infile 'test1.txt' into table table1;

Now, run a perl script to get the data. 

#!/usr/bin/perl

use DBI;

my $dsn = "DBI:mysql:database=test1";
my $dbh = DBI->connect($dsn, 'root', '');

my $insert1 = "insert into table1 values('test3    ')";
my $sth = $dbh->prepare($insert1);
$sth->execute();

my $insert1 = "insert into table1 values('test4')";
my $sth = $dbh->prepare($insert1);
$sth->execute();

my $select = "select field1 as f1 from table1";
my $sth = $dbh->prepare($select);
$sth->execute();
while (my $row = $sth->fetchrow_hashref()) {    print "'$row->{'f1'}'\n";}

Suggested fix:
Just make it so spaces don't get truncated at the end of char and varchar fields when you load a file.

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:

See http://dev.mysql.com/doc/mysql/en/CHAR.html

  Values in VARCHAR columns are variable-length strings. You can declare a  
  VARCHAR column to be any length from 0 to 255, just as for CHAR columns. 
  (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.)  
  However, in contrast to CHAR, VARCHAR values are stored using only as 
  many characters as are needed, plus one byte to record the length. Values
  are not padded; instead, trailing spaces are removed when values are stored. 
  This space removal differs from the standard SQL specification.

As you can see this is documented behaviour but as we are aware that it is 
not standard compliant we are planing to change it in MySQL 5:

  http://dev.mysql.com/doc/mysql/en/TODO_MySQL_5.0.html

  * Add true VARCHAR support (column lengths longer than 255, and no stripping 
     of trailing whitespace). There is already support for this in the MyISAM storage 
     engine, but it is not yet available at the user level.

My bad, obviously. 
I thought I had managed to insert trailing spaces into a varchar field, but I think I mistyped my command and made field a text field. I tried it again with a new table and couldn't reproduce the results. 
Probably, on the mysqlimport webpage and the 'load form infile' webpages, it might be useful to make a note of it. I was looking there and didn't even think of the varchar type webpage. I thought it was a mysqlimport for load from infile problem.