Using: Ruby 1.9.2, Rails 3.0.9, SQLite3
I am seeing some odd behavior when saving an integer field in
activerecord. I have setup a test scenario in the Rails console using
the following migration and corresponding model:
class CreateMyobjs < ActiveRecord::Migration
def self.up
create_table :myobjs do |t|
t.integer :int, :default => 0, :null => false
t.timestamps
end
end
def self.down
drop_table :myobjs
end
end
In the Rails console (line numbers added by me):
1 ruby-1.9.2-p290 :001 > o = Myobj.new
2 => #<Myobj id: nil, int: 0, created_at: nil, updated_at: nil>
3 ruby-1.9.2-p290 :002 > o.save
4 => true
5 ruby-1.9.2-p290 :003 > o
6 => #<Myobj id: 1, int: 0, created_at: “2011-10-12 19:59:17”,
updated_at: “2011-10-12 19:59:17”>
7 ruby-1.9.2-p290 :004 > o.int = ‘’
8 => “”
9 ruby-1.9.2-p290 :005 > o
10 => #<Myobj id: 1, int: nil, created_at: “2011-10-12 19:59:17”,
updated_at: “2011-10-12 19:59:17”>
11 ruby-1.9.2-p290 :006 > o.save
12 => true
13 ruby-1.9.2-p290 :007 > o
14 => #<Myobj id: 1, int: nil, created_at: “2011-10-12 19:59:17”,
updated_at: “2011-10-12 19:59:17”>
15 ruby-1.9.2-p290 :008 > o2 = Myobj.find(1)
16 => #<Myobj id: 1, int: 0, created_at: “2011-10-12 19:59:17”,
updated_at: “2011-10-12 19:59:17”>
In lines 1-6 I create a new Myobj, save it and verify its attributes,
at this point o.int = 0, the default value from the database.
In lines 7-10 I set the value of o.int to ‘’ (blank string), which
activerecord translates to nil, since it is an integer field.
Lines 11-12 successfully saves o with o.int set to nil, this save
should raise an InvalidStatement exception from the database, but it
does not!
Lines 13-14 verify’s that the apparently saved o object still thinks
the int field is nil.
Line 15-16 lookups up the record from the database and shows that the
int field is not actually nil, but rather is still 0. It is apparent
that the original o object did not save the int attribute properly to
the database.
Trying to do the same thing when o.int starts ut as non-zero results
in the following:
17 ruby-1.9.2-p290 :009 > o.int = 3
18 => 3
19 ruby-1.9.2-p290 :010 > o.save
20 => true
21 ruby-1.9.2-p290 :011 > o
22 => #<Myobj id: 1, int: 3, created_at: “2011-10-12 19:59:17”,
updated_at: “2011-10-12 20:08:00”>
23 ruby-1.9.2-p290 :012 > o.int = nil
24 => nil
25 ruby-1.9.2-p290 :013 > o.save
26 ActiveRecord::StatementInvalid: SQLite3::ConstraintException:
myobjs.int may not be NULL: UPDATE “myobjs” SET “int” = NULL,
“updated_at” = ‘2011-10-12 20:08:13.550661’ WHERE “myobjs”.“id” =
1 …
I wont give a step-by-step description of this one, but as you can see
the expected database exception is raised.
From these tests it appears that activerecord’s save method is not
updating integer fields when they change from 0 to nil. I think it is
likely that this is because it is internally coercing the value of the
integer field using to_i, and of course nil.to_i == 0.
Can anyone else confirm this behavior or think of a good reason why it
would be like this?
Thanks,
Chris