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

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Search Discussions

  • Philip Hallstrom at Oct 12, 2011 at 9:03 pm

    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.
    If this is true, why wouldn't your second example also succeed? Since in both you are setting o.int to nil which should then get coerced to zero. Actually you're not doing exactly the same thing.

    In the former you are setting o.int to "" and in the latter setting it to nil. So it could be that Rails is calling to_i on "", but not on nil... hence the error...

    And "".to_i => 0

    I haven't actually checked the source though, but seems like skipping type coercion on nil fields is a logical thing to do.

    Can anyone else confirm this behavior or think of a good reason why it
    would be like this?

    Thanks,
    Chris

    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
  • Peter Vandenabeele at Oct 16, 2011 at 6:48 pm

    On Wed, Oct 12, 2011 at 10:18 PM, Chris N wrote:

    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?
    I can confirm it (in Rails 3.1.1.rc1).

    I believe the cause of the difference is that "o.changed" does not see the
    difference between the nil in memory and the 0 in the database in the case
    where a NULL is not allowed for that column.

    Maybe 'changed' should see that difference (between nil and 0) to have
    consistent behavior. I have the impression it is not correct that the trying
    to save an object with an not allowed nil value for an attribute will behave
    differently, dependent on the current state of the object in the database?

    <code>
    class AddAgeToUsers < ActiveRecord::Migration
    def change
    add_column :users, :age, :int, :default => 0, :null => false
    end
    end
    </code>

    <code>
    049:0> u.age = 0
    => 0
    050:0> u.save!
    (0.4ms) BEGIN
    (0.5ms) UPDATE "users" SET "age" = 0, "updated_at" = '2011-10-16
    18:33:23.687350' WHERE "users"."id" = 2
    (1.3ms) COMMIT
    => true
    051:0> u.age = ''
    => ""
    052:0> u.changed
    => []
    053:0> u.changes
    => {}
    054:0> u.save # not actually saving, so not hitting the database NOT NULL
    restriction
    (0.4ms) BEGIN
    (0.2ms) COMMIT
    => true
    055:0> ActiveRecord::Base.partial_updates = false # force the save
    => false
    056:0> u
    => #<User id: 2, name: "Peter", created_at: "2011-10-11 10:56:14",
    updated_at: "2011-10-16 18:33:23", age: nil>
    057:0> u.reload
    User Load (0.7ms) SELECT "users".* FROM "users" WHERE "users"."id" = $1
    LIMIT 1 [["id", 2]]
    => #<User id: 2, name: "Peter", created_at: "2011-10-11 10:56:14",
    updated_at: "2011-10-16 18:33:23", age: 0>
    058:0> u.age = ''
    => ""
    059:0> u.changes
    => {}
    060:0> u.save
    (0.4ms) BEGIN
    (1.0ms) UPDATE "users" SET "name" = 'Peter', "created_at" = '2011-10-11
    10:56:14.824780', "updated_at" = '2011-10-16 18:35:53.711462', "age" = NULL
    WHERE "users"."id" = 2
    PGError: ERROR: null value in column "age" violates not-null constraint
    : UPDATE "users" SET "name" = 'Peter', "created_at" = '2011-10-11
    10:56:14.824780', "updated_at" = '2011-10-16 18:35:53.711462', "age" = NULL
    WHERE "users"."id" = 2
    (0.3ms) ROLLBACK
    ActiveRecord::StatementInvalid: PGError: ERROR: null value in column "age"
    violates not-null constraint
    : UPDATE "users" SET "name" = 'Peter', "created_at" = '2011-10-11
    10:56:14.824780', "updated_at" = '2011-10-16 18:35:53.711462', "age" = NULL
    WHERE "users"."id" = 2
    ...
    </code>

    HTH,

    Peter

    --
    You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
    To post to this group, send email to rubyonrails-talk@googlegroups.com.
    To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com.
    For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouprubyonrails-talk @
categoriesrubyonrails
postedOct 12, '11 at 8:18p
activeOct 16, '11 at 6:48p
posts3
users3
websiterubyonrails.org
irc#RubyOnRails

People

Translate

site design / logo © 2022 Grokbase