Problem: Null values in a CSV format fixture show up a 0 in

Hi all,

I’m running into a strange problem. I’m trying to get null values to
load from a fixture in CSV form into my test database, but they show
up as 0 (zero).

I have a table people (simplified version below)

create table people (
id int not null auto_increment,
target int null default null,
name varchar(50) not null,
primary key(id)

that has a column target which can be null. In my development
database many people have null values for target.

In my unit tests, I want to be able to use all the people (a couple
of hundred), so I exported people from MySQL 4.1.12-standard in CSV
format using the following command:

select * into outfile '/tmp/people.csv' fields terminated by ','

optionally enclosed by ‘"’ lines terminated by ‘\n’ from people;

That gives me a CSV file with null values represented as \N

According to the fixtures documentation, to get a null value inserted
into your test database from a CSV, you should have nothing between
the commas.

"nulls can be achived by just placing a comma, for example,

(1,sclaus,false,) minus the parenthesis of course."
Peak Obsession

So I went through and replaced \N with nothing … as a result rows
that previously looked like:


now look like


The rails unit test libraries load the fixture without any complaints.


wherever I had a null for target in my development database, I now
have a 0 my test database.

This is a problem because 0 is an illegal value for a target…
targets must be positive or non-existant. As a result, the
validation on my model (person.rb::validate) is failing and my unit
tests are throwing errors.

As a work around, I commented out my validate, but I’d like to turn
it back on.

I’m running the following:
Mac OSX 10.4.5
Ruby 1.8.2 (2004-12-25) [powerpc-darwin8.0]
Rails 1.0.0

Thanks for any help.