MySQL Bugs: #62325: LOAD DATA not loading NULL values properly

Bug #62325	LOAD DATA not loading NULL values properly
Submitted:	1 Sep 2011 17:45	Modified:	14 Nov 2011 10:37
Reporter:	Michael Butler	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.8 MySQL Community Server (GPL)	OS:	Windows (Windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	infile, LOAD DATA, null

Description:
According to the LOAD DATA INFILE page on dev.mysql.com (http://dev.mysql.com/doc/refman/5.1/en/load-data.html), 

For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field value of \N is read as NULL for input (assuming that the ESCAPED BY character is “\”).

I take that to mean that using the most basic syntax of 

LOAD DATA INFILE 'filepath'
INTO TABLE table (field_1, field_2, field_3);

placing \N in a field that should be NULL will cause MySQL to read it in as NULL and put a NULL in that column for that row. 

Unfortunately, it doesn't do this.

Nor does it work with the ASCII NUL (\0). Instead, in both cases, it reads it literally regardless of whether the escape character is set to \.

How to repeat:
Place the values.txt files on your D: drive or where ever you want (just change the paths in the source.sql) and run the commands in the source file. According to the documentation, SELECT p_key FROM child WHERE c_key=107 should yield a NULL. Instead, it is a 0.

--------------------------------------------------
source.sql:

drop database if exists bugtest;
create database if not exists bugtest;
use bugtest;

CREATE TABLE values(
  c_key int primary key,
  c_data char(6),
  p_key int
);

LOAD DATA INFILE 'D:\values.txt'
INTO TABLE values(c_key, c_data, p_key);

--------------------------------------------------
values.txt:

101	foobar	1
102	barfoo	2
103	raboof	3
104	oofrab	4
105	boofar	5
106	farboo	6
107	oobraf	\N
108	rafoob	8
109	piggyp	9
110	pyggip	10

Suggested fix:
There is a workaround where you could set all of the NULLs in a table to a certain value and change it after. However, unless you're working with a small dataset, that won't work in the situation that I discovered it: where p_key is a checked foreign key.

What exact server version, x.y.z, do you use?

5.5.8 MySQL Community Server (GPL)

I did some putzing and discovered the (IMHO) oddly specific nature of this bug.

I was focused on a case where it wasn't working where my NULL was the last value in the row. Deleting the tab between where that NULL is and the second-to-last value gives me my NULL. Also, putting \N into a spot in the middle of the row gives me a NULL.

This provides a simple workaround, but I still think this counts as a bug because the \N should work no matter where it's placed, even if it's not needed.

Our manual, http://dev.mysql.com/doc/refman/5.5/en/load-data.html, clearly says:

"If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''"

Extra tab matters, it is interpreted as field separator, so your \N goes to the "next" filed comparing to expected.