NotNullViolation in boolean field of view

[budiljak]

Issue

After upgrading from 7.0.3 to 7.0.4 the adapter seems to have problems reading the default value for boolean columns of a view. When I initialize the model of that view the boolean field has the value "nil" instead of "false". When I try to save that record I get a NotNullViolation error.

Expected behavior

The initial value of the boolean field should be "false".

Actual behavior

It's "nil".

How to reproduce

Try to save a new instance of a model based on a view with boolean fields.

Details

• Rails version: 7.0.4.2
• SQL Server adapter version: 7.0.4
• TinyTDS version: 2.1.5

• FreeTDS details: Not installed

  run `tsql -C` and paste here the output.

[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