MySQL Bugs: #11229: excape characters not written correctly to outfile in SELECT INTO OUTFILE

Bug #11229	excape characters not written correctly to outfile in SELECT INTO OUTFILE
Submitted:	10 Jun 2005 8:53	Modified:	22 Aug 2005 7:18
Reporter:	Kai Sautter (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.x	OS:	Linux (Suse Linux and Win XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I am trying to create a script file from within a SQL script which requires me to also write tab and new line characters into the file.

(1) If I escape the backslash "\\" (resulting in "\\t" for tab) the result is "\\t" in the file (see script 1 below).

(2) If I do not escape the backslash "\" (resulting in "\t" for tab) the result is "\<tab>" (see script 2 below).

I could understand results for (1) where characters are parsed as is, but (2) is indicating, that the escape sequence is getting parsed somehow, but the "\" indicating that the following character is a command character is includid in the result.

I have run these scripts against various versions of mysql 5.0 server on both Windows XP and Suse Linux. The command line client is running on Windows XP. 

How to repeat:
script (1):
select concat('select  FIELD1 into outfile \'/somepath/ABCD_',
	repeat('0', length(cast(FIELD2 as char))), FIELD2, '_',
	repeat('0', length(cast(FIELD3 as char))), FIELD3, '.txt\' fields terminated by \'\\t\' lines terminated by \'\\r\\n\' from TABLE2 WHERE FIELD10 = ', FIELD10, ' AND FIELD11= ', FIELD11, ';')
into outfile '/somepath/somefile.sql' lines terminated by '\r\n'
      from TABLE1

script (2):
select concat('select  FIELD1 into outfile \'/somepath/ABCD_',
	repeat('0', length(cast(FIELD2 as char))), FIELD2, '_',
	repeat('0', length(cast(FIELD3 as char))), FIELD3, '.txt\' fields terminated by \'\t\' lines terminated by \'\r\n\' from TABLE2 WHERE FIELD10 = ', FIELD10, ' AND FIELD11= ', FIELD11, ';')
into outfile '/somepath/somefile.sql' lines terminated by '\r\n'
      from TABLE1

Suggested fix:
Adapt output routine on server to elimitate "\" with escape character sequences.

Could you please provide the definition of table ?

According to mysql documentation both scripts store right results into files.
Please read URL: http://dev.mysql.com/doc/mysql/en/string-syntax.html

I had a look at the documentation quoted in your answer. It describes the results I also would expect. Yet the results I get deviate, at least to my understanding.

I have done it with two very simple examples:

Example 1:
select 'bla\tbla' as ' ' into outfile 'c:/temp/testt.txt';
This should result in the text "bla<tab>bla" being inserted into the file. The actual result is: "bla\	bla", so "bla\<tab>bla". In my interpretation of the documented features, this should either result in "bla<tab>bla" as seen when respesenting the results as table result (without INTO OUTFILE) or in "bla\tbla" (so a fully escaped sequence). The result, we are observing, is a mix of both.

Example 2:
select 'bla\\tbla' as ' ' into outfile 'c:/temp/testbst.txt';
This should result in the text "bla\tbla" being inserted into the file. The actual result is: "bla\\tbla". This at least is a more predictable result, as it mirrors exacly the input and could be used as input itself again.

To conclude, based on the documentation, I can predict the results of example 2 based on the manual, but the result of example 1 is for me not predictable, if I apply the same logic as for example 2.

I still believe that this is a bug.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see bug#11959