Bug #61857 Allow NULL fields in CSV engine
Submitted: 13 Jul 2011 19:33 Modified: 14 Jul 2011 10:50
Reporter: David Nuttall Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: CSV Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: null

[13 Jul 2011 19:33] David Nuttall
Description:
I would like to recommend that NULL fields in CSV tables be recognized properly.  I realize that there was a bug related to NULL fields in CSV files, but I would like to find a way to accommodate NULL fields again.  Also, once the 

How to repeat:
When trying to create a CSV table defining some values as NULL fields, I get an error message:
#1178 - The storage engine for the table doesn't support nullable columns. 

Suggested fix:
I would recommend using #NULL# as the place-holder value in the CSV table.  So the engine should generate/read lines such as these, if the 3rd and 4th fields are defined as nullable:

1,"Main test",300.2,19.85,"2011-08-13","2011-07-13 10:24:06"
2,"Another",60.9,33.2,#NULL#,"2011-07-13 14:03:55"
3,"Whiny values",55,#NULL#,"2012-05-01","2011-07-13 14:05:06"

Defined as:
+-----------------+---------------------+
| Field           | Type                |
+-----------------+---------------------+
| Document_Number | bigint(20) unsigned |
| Document_Name   | varchar(32)         |
| Quantity        | double              |
| Price           | float unsigned      |
| Activity_Date   | date                |
| Timestamp       | timestamp           |
+-----------------+---------------------+
[13 Jul 2011 19:35] David Nuttall
Also, once NULL fields are allowed again, NULL defaults would also need to be allowed.
[14 Jul 2011 10:51] David Nuttall
Additional thoughts:
While including #NULL# as a field is unambiguous as to what is intended, the NULL fields in a CSV file should also recognize empty fields.  e.g.
4,"More words",-16.5,3,,"2011-07-14 08:26:15"

There should be no spaces around the field separating commas, but the engine should be able to handle them if they do appear, such as when the table has been loaded with data from the OS.

At this point, I recommend implementing the empty field over #NULL# place-holder in the CSV tables.