Model initialized w/ "NULL" if migration string column is default null

I cannot reproduce this. Can you provide a test case?

Note: generating [test_column] varchar(255) DEFAULT NULL is correct. If you do an insert from SQL Server Analyzer you'll see that a NULL is inserted. Question is what exactly causes the string "NULL" to be inserted?

Here is a test case, which produces the same results for gem rails 1.2.3 and edge rails 7416. BTW, this bug is not specific to MS SQL Server.

$ rails project ; cd project
$ cat >| config/database.yml
   development:
     adapter: sqlite3
     database: db/project.development
^D
$ mkdir db/migrate ; cat > db/migrate/001_initial_schema.rb
   class InitialSchema < ActiveRecord::Migration
      def self.up
         create_table :items do |t|
            t.column :name, :string
            # I also tried:
            # t.column :name, :string, :null => true, :default => nil
         end
      end
   end
^D
$ cat > app/models/item.rb
   class Item < ActiveRecord::Base
   end
^D
$ rake db:migrate

$ grep name db/schema.rb  # show schema.rb is using the literal string "NULL"
    t.column "name", :string, :default => "NULL"

$ script/console          # show that Item.name defaults to "NULL"
Loading development environment.
>> x = Item.create
=> #<Item id: 1, name: "NULL">
>> x.name.length
=> 4
^D

$ # show that rails is inserting the literal string "NULL",
$ # which is different from the database's null value
$ sqlite3 db/project.development
sqlite> .schema items
CREATE TABLE items ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" varchar(255) DEFAULT NULL);
sqlite> insert into items (id) values (69);
sqlite> select * from items;
1|NULL
69|
sqlite> select * from items where name is null;
69|
sqlite> select * from items where name = 'NULL';
1|NULL

I was having the identical issue with an sqlite3 database. Eventually, looked at the sqlite3-ruby gem itself, and found out I wasn't using the latest version (sqlite3-ruby-1.1.0 vs sqlite3-ruby-1.2.1). Upgrading to the latest sqlite3-ruby gem resolved the problem.

I confirmed this using Ubuntu 7.04, Rails 1.2.5, Sqlite3 3.3.13, and sqlite-ruby 1.2.1

me@my_server:/data/my_server/current$ sqlite3 /data/my_server/shared/db/prod.sqlite3 
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> .schema products
CREATE TABLE products ("id" INTEGER PRIMARY KEY NOT NULL, "title" varchar(255) DEFAULT NULL, "description" text DEFAULT NULL, "price" decimal(8,2) DEFAULT 0.0, "weight" decimal(8,2) DEFAULT 0.0, "active" boolean DEFAULT 't', "sold" integer DEFAULT 0, "shipping" decimal(8,2) DEFAULT 0.0, "retail_price" decimal(8,2) DEFAULT 0.0);
sqlite> .quit

me@my_server:/data/my_server/current$ RAILS_ENV=production script/console
Loading production environment.
>> Product.new
=> #<Product:0xb6dd6f10 @new_record=true, @attributes={"weight"=>#<BigDecimal:b6d25594,'0.0',4(8)>, "price"=>#<BigDecimal:b6d25710,'0.0',4(8)>, "title"=>"NULL", "retail_price"=>#<BigDecimal:b6d250a8,'0.0',4(8)>, "description"=>"NULL", "shipping"=>#<BigDecimal:b6d25224,'0.0',4(8)>, "sold"=>0, "active"=>false}>

Notice that the "title" and "description" attributes of the product object contain the string "NULL"

Here's a temporary monkey patch for those (like me) desperately needing a fix. Throw it in lib/null_in_attrs_fix.rb and then require 'null_in_attrs_fix' in your environment.rb:

ActiveRecord::Base.class_eval do
	def attributes_from_column_definition_with_null_fix
		returning attributes = attributes_from_column_definition_without_null_fix do
			attributes.each_value { |value| value.replace('') if value == 'NULL' }
		end
	end
	
	alias_method_chain :attributes_from_column_definition, :null_fix
end

Also, I changed the Priority to high and the severity to major. I would say that all of the form fields on my clients' web sites showing up with 'NULL' in them is a major problem ;)

Trying to replicate meekish's test.

Environment: Ubuntu 7.10; Rails 1.2.5; sqlite3 3.4.2-1build1; libsqlite3-dev 3.4.2-1build1; sqlite3-ruby 1.2.1

rbolkey@danu:~/workspaces/rails$ sqlite3 db/dev.sqlite3 
SQLite version 3.4.2
Enter ".help" for instructions
sqlite> .schema users
CREATE TABLE users ("id" INTEGER PRIMARY KEY NOT NULL, "login" varchar(255) DEFAULT NULL, "email" varchar(255) DEFAULT NULL, "crypted_password" varchar(40) DEFAULT NULL, "salt" varchar(40) DEFAULT NULL, "created_at" datetime DEFAULT NULL, "updated_at" datetime DEFAULT NULL, "remember_token" varchar(255) DEFAULT NULL, "remember_token_expires_at" datetime DEFAULT NULL, "role" varchar(20) DEFAULT NULL, "first_name" varchar(255), "last_name" varchar(255), "show_email" boolean DEFAULT 'f', "homepage" varchar(255));
CREATE INDEX "index_users_on_role" ON users ("role");
sqlite> .quit
rbolkey@danu:~/workspaces/rails$ script/console
Loading development environment.
>> User.new
=> #<User:0xb706d698 @new_record=true, @attributes={"salt"=>nil, "updated_at"=>nil, "crypted_password"=>nil, "role"=>nil, "remember_token_expires_at"=>nil, "homepage"=>nil, "remember_token"=>nil, "show_email"=>false, "first_name"=>nil, "last_name"=>nil, "login"=>nil, "created_at"=>nil, "email"=>nil}>
>> 

All string fields defaulting to nil.

Replying to rbolkey:

Trying to replicate meekish's test. Environment: Ubuntu 7.10; Rails 1.2.5; sqlite3 3.4.2-1build1; libsqlite3-dev 3.4.2-1build1; sqlite3-ruby 1.2.1

Is it possible to get these packages for Ubuntu 7.04?

Replying to meekish:

Replying to rbolkey:

Trying to replicate meekish's test. Environment: Ubuntu 7.10; Rails 1.2.5; sqlite3 3.4.2-1build1; libsqlite3-dev 3.4.2-1build1; sqlite3-ruby 1.2.1

Is it possible to get these packages for Ubuntu 7.04?

Probably only by building your own package or installing from source. Ubuntu's source package for sqlite3 is here: http://packages.ubuntu.com/gutsy/source/sqlite3. It may be worth testing your app with a Gutsy VMWare image before going down that track to see if it makes a difference.

Replying to lawrence:

I cannot reproduce this. Can you provide a test case? Note: generating [test_column] varchar(255) DEFAULT NULL is correct. If you do an insert from SQL Server Analyzer you'll see that a NULL is inserted. Question is what exactly causes the string "NULL" to be inserted?

On SQL Server, there is a setting ANSI_NULL_DFLT. This setting determines whether a column should allow nulls or not when it's not defined. From MS documentation. "This setting only affects the nullability of new columns when the nullability of the column is not specified in the CREATE TABLE and ALTER TABLE statements. When SET ANSI_NULL_DFLT_ON is ON, new columns created with the ALTER TABLE and CREATE TABLE statements allow null values if the nullability status of the column is not explicitly specified. SET ANSI_NULL_DFLT_ON has no effect on columns created with an explicit NULL or NOT NULL."

Depending on how the database is configured, the following statement: CREATE TABLE dbo.test(column_name nvarchar(10))

could go with either value... in order to support either configuration, it should explicitly define whether the column is null or not... such as

CREATE TABLE dbo.test(column_name nvarchar(10) NULL)

Replying to meekish:

Here's a temporary monkey patch for those (like me) desperately needing a fix. Throw it in lib/null_in_attrs_fix.rb and then require 'null_in_attrs_fix' in your environment.rb: {{{ ActiveRecord::Base.class_eval do def attributes_from_column_definition_with_null_fix returning attributes = attributes_from_column_definition_without_null_fix do attributes.each_value { |value| value.replace() if value == 'NULL' } end end alias_method_chain :attributes_from_column_definition, :null_fix end }}} Also, I changed the Priority to high and the severity to major. I would say that all of the form fields on my clients' web sites showing up with 'NULL' in them is a major problem ;)

Thanks...this worked for me and I'm using MS SQLServer 2005.

Note to others: this doesn't update the schema.rb file, but when you run "rake db:migrate" the NULLs will no longer appear in the forms on your websites.

Technically, the database is saving in zero length strings...not real nulls.

I will be looking for a real fix for this issue!

I was having a similar problem on Gentoo, Rails 1.2.6 with sqlite3-ruby-1.1.0. Removing a column from a table in a migration resulted in things like:

    t.column "title",   :string,  :default => "'NULL'"
    t.column "kind",    :string,  :default => "''''''basic_entry''''''", :null => false

in my schema.rb. The extra quotes appeared when the sqlite connection adapter called copy_table. Upgrading to sqlite3-ruby-1.2.1 appears to have fixed this.

Interestingly, I have an identical setup on an AMD64 machine that was working fine even on sqlite3-ruby-1.1.0.

Add me to the list of people that encountered this problem.

I applied the patch submitted as #8886 and this fixed it.