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) Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.13 OS:Any
Assigned to: 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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.