MySQL Bugs: #40320: select into outfile mistake with multiline strings

Bug #40320	select into outfile mistake with multiline strings
Submitted:	24 Oct 2008 20:49	Modified:	30 Oct 2008 18:02
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.67, 4.1, 5.0, 5.1, 6.0 bzr, 8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
A multiline strings select's into outfile with one escape character too much and accordingly does not import! Additionally a NULL-line is created when importing with LOAD DATA

How to repeat:
DROP TABLE IF EXISTS `tab10`;

CREATE TABLE `tab10` (
  `chief_id` int(11) NOT NULL,
  `val1` varchar(20) default NULL,
  PRIMARY KEY  (`chief_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `tab10`(`chief_id`,`val1`) values (2,'a');
insert  into `tab10`(`chief_id`,`val1`) values (4,'b\"c');
insert  into `tab10`(`chief_id`,`val1`) values (6,'d\r\ne');

select * from tab10 into outfile  'C:\\tab10.csv'  fields escaped by '"' terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

truncate table tab10;

load data local infile 'C:\\escape.csv' into table `tab10` fields escaped by '"' terminated by ',' optionally enclosed by '"' lines terminated by '\r\n';

show warnings;
/*
Level      Code  Message                                                     
-------  ------  ------------------------------------------------------------
Warning    1366  Incorrect integer value: 'e"' for column 'chief_id' at row 4
Warning    1261  Row 4 doesn't contain data for all columns  
*/

select * from tab10; -- last row is truncated before <newline> 

/*
chief_id  val1  
--------  ------
       0  (NULL)  -- what is this?
       2  a     
       4  b"c   
       6  d       -- last row is truncated before <newline>
*(

Suggested fix:
two issues

1) an empty row with values (0,NULL) is created
2) truncation issue: because there is one " (doublequote) too much after 'd'!

A customer tells that the escapes etc. here are required to import the data into a spreadsheet program. Issue 2) prevents that import to that program.

Additionally SELECT INTO OUTFILE and LOAD DATA INFILE should work together with MySQL server and recreate data exactly!

Thank you for the report.

Verified as described.

Now as 4.1(.22) is also affected and 4.1.x is out of active maintenaince, am I right in assuming that there will never be released updated 4.1 binaries with a fix for this?

There is a nice workaround for this bug. Escape and termination string should be choosed in such a manner that this bug would be avoided.

Please note what I wrote:

"A customer tells that the escapes etc. here are required to import the
data into a spreadsheet program."  So there is absolutely *no option* to select escape, delimiter and enclose character otherwise! 

BTW: Refer to: 
http://www.rfc-editor.org/rfc/rfc4180.txt

Bug #110815 marked as duplicate of this one.