MySQL Bugs: #31663: FIELDS TERMINATED BY special character

Bug #31663	FIELDS TERMINATED BY special character
Submitted:	17 Oct 2007 13:32	Modified:	3 Dec 2007 17:35
Reporter:	Gleb Shchepa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
This bug is related to the bug #29294 (FIELDS ENCLOSED BY special character).

This bug leads to corrupted data during the LOAD DATA INFILE statement with the same FIELDS ENCLOSED BY clause.

It is not a quite regression, because before that bugfix data was also corrupted by the same pair of statements (but in a different way).

How to repeat:
mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 5.0.52-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (c1 VARCHAR(256), c2 VARCHAR(256));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('-r-', '=r=');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * INTO OUTFILE 'x.txt' FIELDS TERMINATED BY 'r' FROM t1;
Query OK, 1 row affected (0.00 sec)

mysql> TRUNCATE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD DATA INFILE 'x.txt' INTO TABLE t1 FIELDS TERMINATED BY 'r';
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 1

mysql> SELECT * FROM t1;
+------+------+
| c1   | c2   |
+------+------+
| -    |      | 
+------+------+
1 row in set (0.00 sec)

mysql> Bye

$ cat x.txt
-rr-r=rr=

Suggested fix:
The LOAD DATA INFILE statement is able to decode doubled ENCLOSED BY character as an escaped character (see commentaries for bug #24249).
It should be able to decode doubled character of the FIELDS TERMINATED BY string in a same way.

Thank you for the bug report.

test case

Attachment: 31663.test (application/octet-stream, text), 629 bytes.

Typo in the original bugreport, second phrase should be:

This bug leads to corrupted data during the LOAD DATA INFILE statement with the same FIELDS *TERMINATED* BY clause.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/35913

ChangeSet@1.2544, 2007-10-19 17:12:10+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #31663: if the FIELDS TERMINATED BY string
  in the SELECT INTO OUTFILE clause starts with a special
  character (one of n, t, r, b, 0, Z or N) and ENCLOSED BY
  is empty, every occurrence of this character within a
  field value is duplicated.
  
  Duplication has been avoided.
  New warning message has been added: "First character of
  the FIELDS TERMINATED string is ambiguous; please use
  FIELDS ENCLOSED BY".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36013

ChangeSet@1.2544, 2007-10-22 14:54:20+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #31663: if the FIELDS TERMINATED BY string
  in the SELECT INTO OUTFILE clause starts with a special
  character (one of n, t, r, b, 0, Z or N) and ENCLOSED BY
  is empty, every occurrence of this character within a
  field value is duplicated.
  
  Duplication has been avoided.
  New warning message has been added: "First character of
  the FIELDS TERMINATED string is ambiguous; please use
  FIELDS ENCLOSED BY".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36083

ChangeSet@1.2544, 2007-10-23 00:51:21+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #31663: if the FIELDS TERMINATED BY string
  in the SELECT INTO OUTFILE clause starts with a special
  character (one of n, t, r, b, 0, Z or N) and ENCLOSED BY
  is empty, every occurrence of this character within a
  field value is duplicated.
  
  Duplication has been avoided.
  New warning message has been added: "First character of
  the FIELDS TERMINATED string is ambiguous; please use
  FIELDS ENCLOSED BY".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36117

ChangeSet@1.2544, 2007-10-23 16:16:59+05:00, gshchepa@gleb.loc +5 -0
  Fixed bug #31663: if the FIELDS TERMINATED BY string
  in the SELECT INTO OUTFILE clause starts with a special
  character (one of n, t, r, b, 0, Z or N) and ENCLOSED BY
  is empty, every occurrence of this character within a
  field value is duplicated.
  
  Duplication has been avoided.
  New warning message has been added: "First character of
  the FIELDS TERMINATED string is ambiguous; please use
  non-optional and non-empty FIELDS ENCLOSED BY".

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.