| 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: | |
| 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 | ||
[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
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'