Bug #39201 INTO OUTFILE Statement
Submitted: 2 Sep 2008 21:22 Modified: 3 Sep 2008 5:36
Reporter: Arthur Mendonça Meskelis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.26-rc, 4.1, 5.0, 5.1, 6.0 bzr OS:Linux (2.6.24.5-smp)
Assigned to: CPU Architecture:Any
Tags: OUTFILE FIELDS TERMINATED
Triage: Triaged: D4 (Minor)

[2 Sep 2008 21:22] Arthur Mendonça Meskelis
Description:
Problem when you execute a query that include a 'INTO OUTFILE ? FIELDS TERMINATED BY ""' statement with a function (DATE_FORMAT, BIN, LENGTH) as a field.

How to repeat:
mysql> select date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S') into outfile '/tmp/test' fields terminated by "";

outside of mysql:
$ wc /tmp/test

" the size of the file should be 17 bytes (1 byte of newline) "

mysql> select left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16) into outfile '/tmp/test' fields terminated by '';

outside of mysql:
$ wc /tmp/test

"in this case the mysql executes ok"

mysql> select bin(8), bin(6) into outfile '/tmp/test' fields terminated by '';

outside of mysql:
$ wc /tmp/test
  1   2 129 /tmp/test

'another error'

mysql> select bin(8), bin(6) into outfile '/tmp/test1' fields terminated by 'p';

outside of mysql:
$ wc /tmp/test1
1 1 9 /tmp/test1
'when the char of fields terminated by is not empty mysql executes ok'
[2 Sep 2008 21:27] Arthur Mendonça Meskelis
ahn
the problem is that when you execute the select with INTO OUTFILE ? FIELDS TERMINATED BY "" (without char to separate the fields) and the last field is a FUNCTION mysql server adds some bytes (space) in the end of the line.
[3 Sep 2008 5:36] Sveta Smirnova
Thank you for the report.

Verified as described.

Test case:

$cat bug39201.test
select date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S');
--eval select date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S') into outfile '$MYSQL_TEST_DIR/var/tmp/bug39201_1' fields terminated by '';
--exec cat $MYSQL_TEST_DIR/var/tmp/bug39201_1
--exec wc $MYSQL_TEST_DIR/var/tmp/bug39201_1
--exec wc -l $MYSQL_TEST_DIR/var/tmp/bug39201_1

select left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16);
--eval select left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16) into outfile '$MYSQL_TEST_DIR/var/tmp/bug39201_2' fields terminated by '';
--exec cat $MYSQL_TEST_DIR/var/tmp/bug39201_2
--exec wc $MYSQL_TEST_DIR/var/tmp/bug39201_2
--exec wc -l $MYSQL_TEST_DIR/var/tmp/bug39201_2

select bin(8), bin(6);
--eval select bin(8), bin(6) into outfile '$MYSQL_TEST_DIR/var/tmp/bug39201_3' fields terminated by '';
--exec cat $MYSQL_TEST_DIR/var/tmp/bug39201_3
--exec wc $MYSQL_TEST_DIR/var/tmp/bug39201_3
--exec wc -l $MYSQL_TEST_DIR/var/tmp/bug39201_3

select bin(8), bin(6);
--eval select bin(8), bin(6) into outfile '$MYSQL_TEST_DIR/var/tmp/bug39201_4' fields terminated by 'p';
--exec cat $MYSQL_TEST_DIR/var/tmp/bug39201_4
--exec wc $MYSQL_TEST_DIR/var/tmp/bug39201_4
--exec wc -l $MYSQL_TEST_DIR/var/tmp/bug39201_4

Result:

=====mysql-5.0=====
=====bug39201=====
select date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S');
date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S')
02/09/2008181818
select date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S') into outfile '/Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_1' fields terminated by '';;
02/09/2008181818     
       1       1      22 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_1
       1 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_1
select left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16);
left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16)
02/09/2008181818
select left(date_format('2008-09-02 18:18:18', '%d/%m/%Y%H%i%S'),16) into outfile '/Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_2' fields terminated by '';;
02/09/2008181818
       1       1      17 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_2
       1 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_2
select bin(8), bin(6);
bin(8)  bin(6)
1000    110
select bin(8), bin(6) into outfile '/Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_3' fields terminated by '';;
1000                                                            110                                                             
       1       2     129 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_3
       1 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_3
select bin(8), bin(6);
bin(8)  bin(6)
1000    110
select bin(8), bin(6) into outfile '/Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_4' fields terminated by 'p';;
1000p110
       1       1       9 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_4
       1 /Users/apple/bzr/mysql-5.0/mysql-test/var/tmp/bug39201_4