NotNullViolation in boolean field of view

[budiljak]

NotNullViolation in boolean field of view

[aidanharan]

Hi @budiljak, if you could create a script to reproduce the bug it would make it a lot easier to investigate. Thanks

[budiljak]

Hi @aidanharan,
thanks for your interest. Here's the script!
Cheers,
Ben

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "tiny_tds"

  gem "activerecord", "=7.0.4.2"
  gem "activerecord-sqlserver-adapter", "=7.0.4"
  #gem "activerecord-sqlserver-adapter", "7.0.0"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "test_database",
  username: "SA",
  password: "StrongPassword!",
  host:     "localhost",
  port:     1433,
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table :bug_tests_table rescue nil

  create_table :bug_tests_table, force: true do |t|
    t.boolean :bool_field, null: false, default: false
  end
  drop_view = "DROP VIEW IF EXISTS bug_tests;"
  create_view = "CREATE VIEW bug_tests AS SELECT id AS id, bool_field AS b FROM bug_tests_table;"
  ActiveRecord::Base.connection.execute(drop_view)
  ActiveRecord::Base.connection.execute(create_view)
end

class BugTest < ActiveRecord::Base
end

class Order < ActiveRecord::Base
end

class TestBugTest < Minitest::Test
  def setup
    # IMPORTANT: partial_inserts is false by default since Rails 7.0
    # without that ActiveRecord will not try to infer default values
    # before creating the record and hence there's no error
    ActiveRecord::Base.partial_inserts = false
    @bug_test = BugTest.new
  end

  def test_default_value
    pp "@bug_test.b: #{@bug_test.b.inspect}"
    @bug_test.save!
    pp "BugTest.first.b: #{BugTest.first.b}"
    assert_equal false, @bug_test.b
    assert_equal 1, BugTest.count
  end
end

[aidanharan]

Using your script I was able to recreate the issue for v7.0.4.0 of the "activerecord-sqlserver-adapter" gem. However, the same issue happens when I use versions 7.0.1.0, 7.0.2.0 and 7.0.3.0 of the gem in your script too.

Could you confirm that you are definitely only seeing this issue when you just upgrade the "activerecord-sqlserver-adapter" gem from v7.0.3.0 to v7.0.4.0?

[budiljak]

With the script I also have the same issue with the previous versions. BUT within the rails application it's definitely another behavior. With v7.0.3.0 the boolean field is "false" even for a new record. And saving the record without explicitly assigning a value to the boolean is successful. With v7.0.4.0 the value of the untouched field is "nil" and saving leads to the exception "NotNullViolation".
Maybe it's some ActiveRecord configuration setting that's causing these differences?!

Cheers!

[aidanharan]

@budiljak Could you try to provide a VERY basic Rails application that demonstrates the issue then? It would require just a migration and the model. You should be able to recreate the issue in the Rails console. By switching the SQL Server adapter gem between v7.0.4.0/v7.0.3.0 the issue should appear/disappear.

Without being able to recreate the issue I cannot fix it. The issue could be caused by a monkey-patch within your Rails application. There were only 3 changes from v7.0.3.0 to v7.0.4.0 (https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/v7.0.4.0/CHANGELOG.md) perhaps you could look into these and see if your issue is related to one of those.

[budiljak]

It's a very strange bug and I think it's probably related to some case sensitivity when creating the view. I worked on it many hours today and I think tomorrow I can finally reproduce it...

The error from the first test script has probably nothing to do with it. The problem with the script was the semicolon at the end of "CREATE VIEW ...;". To MS SQL Server this seems to make a big difference!

*sigh*

[budiljak]

Hi @aidanharan.

Ok, it's really all about case sensitivity. Obviously we created our views and used lower case for all column names of the tables. This leads to the described errors, but only since v7.0.4. The script below will fail with v7.0.4, but succeeds with v7.0.3 of the "activerecord-sqlserver-adapter".

For my case the solution will be to recreate all the views with correct cases of field names. I don't know if you consider the behaviour of the sqladapter a bug at all. The problem is maybe rather that everything else works just fine, and only the request of the default values seems to fail if field names aren't in correct case.

Here's the script to reproduce this:

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "tiny_tds"

  gem "activerecord", "=7.0.4.2"
  gem "activerecord-sqlserver-adapter", "=7.0.4"
  #gem "activerecord-sqlserver-adapter", "7.0.3"
end

require "active_record"
require "minitest/autorun"
require "logger"

ActiveRecord::Base.establish_connection(
  adapter:  "sqlserver",
  timeout:  5000,
  pool:     100,
  encoding: "utf8",
  database: "test_database",
  username: "SA",
  password: "StrongPassword!",
  host:     "localhost",
  port:     1433,
)
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  drop_table :bug_tests_table rescue nil

  create_table :bug_tests_table, force: true do |t|
    t.boolean :Bool_field, null: false, default: false
  end
  drop_view = "DROP VIEW IF EXISTS bug_tests;"
  create_view = "CREATE VIEW bug_tests AS SELECT id AS id, bool_field AS b FROM bug_tests_table"
  ActiveRecord::Base.connection.execute(drop_view)
  ActiveRecord::Base.connection.execute(create_view)
end

class BugTest < ActiveRecord::Base
end

class Order < ActiveRecord::Base
end

class TestBugTest < Minitest::Test
  def setup
    # IMPORTANT: partial_inserts is false by default since Rails 7.0
    # without that ActiveRecord will not try to infer default values
    # before creating the record and hence there's no error
    ActiveRecord::Base.partial_inserts = false
    @bug_test = BugTest.new
  end

  def test_default_value
    pp "@bug_test.b: #{@bug_test.b.inspect}"
    @bug_test.save!
    pp "BugTest.first.b: #{BugTest.first.b}"
    assert_equal false, @bug_test.b
    assert_equal 1, BugTest.count
  end
end

Cheers!
Ben