left join inserts unknown character

[mwpclark]

On redhat linux and mysql version: 3.23.58, I am using a left join statement:

SELECT _wy_short.*, t_specialty FROM _wy_short LEFT JOIN test_wy_short ON _wy_short.upin = test_wy_short.t_upin into outfile '/home/data/upin/_wy_short4.txt';

The outfile has an unknown character inserted at the join.

Running the statement without an outfile, the display is thrown off.

Downloading the outfile to windows, one text editor shows it as ?tab (questionmark tab), another text editor shows it as Ptab (linefeed tab). Microsoft word shows it as plain tab.

Any ideas?

Thanks

 

[mwpclark]

I have the beginnings of an answer to my own question. Looking at the outfile in pico, the unknown character appears as ^M. This apparently is "octal number 15
which is control-M".

I was able to delete the character from the outfile with the following perl substitution operation:

perl -e 's/\015//g' -i -p filename

So, how do I prevent mysql left join from creating this character?

 

[TonyGroves]

There should be no strange characters generated by that statement. Maybe there is strange data in your tables.

When you select into an outfile, MySQL inserts delimiters into the file, which by default are tabs separating the fields and newlines separating the records. If you want to use alternative delimiters, for example the traditional CSV delimiters of commas separating the fields, and string fields enclosed in double-quotes, you could use:[tt]
INTO OUTFILE 'filepath'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
[/tt]

 

[mwpclark]

That was my thought also. I suppose it's possible I got some dos characters into the infile from which a table was built using LOAD DATA INFILE, but I can't see them either with pico (the original infile) or mysql select. Pico shows the ^M in the outfile only. There is no problem with tab delimiters, they work fine.