Bug #92917 LOAD DATA INFILE does not prefix line or field terminator with escape character

• Submitted: 24 Oct 2018 3:43
• Modified: 20 Nov 2018 15:37
• Reporter: Kun Zhou
• Status: Closed
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0.12
• OS: MacOS
• CPU Architecture: Any

[24 Oct 2018 3:43] Kun Zhou

Description:
Docs at https://dev.mysql.com/doc/refman/8.0/en/load-data.html states the FIELDS ESCAPED BY character prefixes "The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values". However, it only in fact does so when FIELDS ENCLOSED BY is unspecified.

How to repeat:
First, create a sample DB
```sql
create database testdb;
use testdb;
CREATE TABLE main (str VARCHAR(20));
INSERT INTO main VALUES ('foo,bar"');
```
Then in command line, do
```sh
mysqldump -u root -p \
                    -T'~'  \
                    --column-statistics=0 \
                    --fields-optionally-enclosed-by=\" \
                    --fields-terminated-by=',' \
                    --fields-escaped-by=\" \
                    testdb
```
We see that the outputted file `main.txt` contains
```plaintext
"foo,bar"""
```
Note that `"`, the FIELDS ESCAPED BY character, does NOT prefix `,`, the FIELDS TERMINATED BY value, also the first character of the FIELDs TERMINATED BY value.

However, if we omit the line `--fields-optionally-enclosed-by=\" \`, i.e., run
```sh
mysqldump -u root -p \
                    -T'~'  \
                    --column-statistics=0 \
                    --fields-terminated-by=',' \
                    --fields-escaped-by=\" \
                    testdb
```,
the content of `main.txt` is
```plaintext
foo",bar""
```
Note that `"`, the FIELDS ESCAPED BY character, does prefix `,`, the FIELDS TERMINATED BY value, also the first character of the FIELDs TERMINATED BY value.

Suggested fix:
Either change the doc or change the behavior of the code. But practically, I think it is a mis communication by the documentation at https://dev.mysql.com/doc/refman/8.0/en/load-data.html.

[2 Nov 2018 16:52] MySQL Verification Team

Hi,

Thank you for your report. 

This is truly insufficiently described in our documentation.

Verified as a documentation bug.

[20 Nov 2018 15:37] Paul DuBois

Posted by developer:
 
Amended the statement in question to:

The first character of the FIELDS TERMINATED BY and LINES TERMINATED
BY values, if the ENCLOSED BY character is empty or unspecified.