Bug #86900 load data result mismatch when escaped by character is 't'

• Submitted: 3 Jul 2017 6:51
• Modified: 4 Jul 2017 7:33
• Reporter: phoenix Zhang (OCA)
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.7.13
• OS: Any
• CPU Architecture: Any

[3 Jul 2017 6:51] phoenix Zhang

Description:
For SELECT INTO OUTFILE and LOAD DATA, the default escaped by character is '\\', however, it can be set on any other character. 
When do SELECT INTO OUTFILE, and set it to 't', all 't' character in rows will change to 'tt' to output file.
However, if use this output file to do LOAD DATA, and use same FIELDS and LINES split characters, the 'tt' will insert as '\t' in MySQL.

How to repeat:
mysql> create table t2(c1 int, c2 varchar(20), c3 varchar(20));
Query OK, 0 rows affected (0,53 sec)

mysql> insert into t2 values (1, 'ata', 'a\ta');
Query OK, 1 row affected (0,08 sec)

mysql> select * from t2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | ata  | a	a  |
+------+------+------+
1 row in set (0,01 sec)

mysql> select * from t2 into outfile '/tmp/a' fields terminated by '-' escaped by 't' enclosed by '+';
Query OK, 1 row affected (0,00 sec)

mysql> system cat /tmp/a
+1+-+atta+-+a	a+
mysql> load data infile '/tmp/a' into table t2 fields terminated by '-' escaped by 't' enclosed by '+';
Query OK, 1 row affected (0,09 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | ata  | a	a  |
|    1 | a	a  | a	a  |
+------+------+------+
2 rows in set (0,00 sec)

[4 Jul 2017 7:33] MySQL Verification Team

Hello!

Thank you for the report.
Imho, as quoted in manual "The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably"  https://dev.mysql.com/doc/refman/5.7/en/select-into.html 

-- 5.7.18

root@localhost [test]> drop table if exists t2;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> create table t2(c1 int, c2 varchar(20), c3 varchar(20));
system cat /tmp/a
load data infile '/tmp/a' into table t2 fields terminated by '-' escaped by '\t' enclosed by '+';
select * from t2;
Query OK, 0 rows affected (0.01 sec)

root@localhost [test]> insert into t2 values (1, 'ata', 'a\ta');
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> select * from t2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | ata  | a       a  |
+------+------+------+
1 row in set (0.00 sec)

root@localhost [test]> system rm -rf /tmp/a
root@localhost [test]> select * from t2 into outfile '/tmp/a' fields terminated by '-' escaped by '\t' enclosed by '+';
Query OK, 1 row affected (0.00 sec)

root@localhost [test]> system cat /tmp/a
+1+-+ata+-+a            a+
root@localhost [test]> load data infile '/tmp/a' into table t2 fields terminated by '-' escaped by '\t' enclosed by '+';
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

root@localhost [test]> select * from t2;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 | ata  | a       a  |
|    1 | ata  | a       a  |
+------+------+------+
2 rows in set (0.00 sec)

Thanks,
Umesh