Composite primary keys are not assigned on create

[dhockett-dmp]

When inserting a record into a table that uses composite primary keys, the instance of the model is not properly updated with the returned keys.

Assume the following contents of test.rb:

require 'bundler/inline'

gemfile(true) do
  source 'https://rubygems.org'
  gem 'dotenv' # Allows ENV to be populated from .env file
  gem 'byebug'
  gem 'activerecord', '7.2.2.1'
  gem 'activerecord-sqlserver-adapter', '7.2.6'
  gem 'pg'
end

require 'dotenv/load'

adapter = ENV['adapter']

MSSQL = 'sqlserver'
POSTGRES = 'postgresql'

raise "Invalid adapter #{adapter}" unless [MSSQL, POSTGRES].include?(adapter)

require 'active_record'
require 'minitest/autorun'
require 'logger'

# Specify database name, username, password, and host in .env
ActiveRecord::Base.establish_connection(
  adapter:,
  timeout:  5000,
  pool:     100,
  encoding: 'utf8',
  database: ENV['db_name'],
  username: ENV['username'],
  password: ENV['password'],
  host:     ENV['host'],
  port:     ENV['port']
)

ActiveRecord::Base.logger = Logger.new($stdout)

require_relative './patch' if ENV['PATCH']

ActiveRecord::Schema.define do
  drop_table :test_single_pk_tables rescue nil
  drop_table :test_composite_pk_tables rescue nil

  create_table :test_single_pk_tables

  case adapter
  when MSSQL
    execute <<-SQL
      CREATE TABLE [test_composite_pk_tables] (
        [id]   [bigint]           IDENTITY(1,1)     NOT NULL,
        [guid] [uniqueidentifier] DEFAULT (NEWID()) NOT NULL,
        CONSTRAINT [pk_test_tables] PRIMARY KEY CLUSTERED
        (
        	[id]   ASC,
        	[guid] ASC
        )
      )
    SQL
  when POSTGRES
    execute <<-SQL
      CREATE TABLE test_composite_pk_tables (
        id   BIGINT GENERATED ALWAYS AS IDENTITY,
        guid UUID   DEFAULT gen_random_uuid(),
        PRIMARY KEY (id, guid)
      )
    SQL
  end
end

class TestSinglePkTable < ActiveRecord::Base; end

class TestCompositePkTable < ActiveRecord::Base
  self.primary_key = %w[id guid]
end

class CreateWithSingleKeyAutoGeneratesIdTest < Minitest::Test
  def setup
    TestSinglePkTable.create!
    @object = TestSinglePkTable.last
  end

  def test_create_with_single_key_auto_generates_id
    refute_nil @object.id
  end
end

class CreateWithCompositeKeyAutoGeneratesIdTest < Minitest::Test
  def setup
    TestCompositePkTable.create!
    @object = TestCompositePkTable.last
  end

  def test_create_with_composite_key_auto_generates_id
    refute_nil @object.id
  end
end

class CreateWithCompositeKeyAutoGeneratesGuidTest < Minitest::Test
  def setup
    TestCompositePkTable.create!
    @object = TestCompositePkTable.last
  end

  def test_create_with_composite_key_auto_generates_guid
    refute_nil @object.guid
  end
end

class CreateWithSingleKeyAssignsIdToCreatedObjectTest < Minitest::Test
  def setup
    @object = TestSinglePkTable.create!
  end

  def test_create_with_single_key_assigns_id_to_created_object
    assert_equal @object.id, TestSinglePkTable.last.id
  end
end

class CreateWithCompositeKeyAssignsIdToCreatedObjectTest < Minitest::Test
  def setup
    @object = TestCompositePkTable.create!
  end

  def test_create_with_composite_key_assigns_id_to_created_object
    assert_equal @object.id, TestCompositePkTable.last.id
  end
end

class CreateWithCompositeKeyAssignsGuidToCreatedObjectTest < Minitest::Test
  def setup
    @object = TestCompositePkTable.create!
  end

  def test_create_with_composite_key_assigns_guid_to_created_object
    assert_equal @object.guid, TestCompositePkTable.last.guid
  end
end

And the following contents of patch.rb:

module ActiveRecord
  module ConnectionAdapters
    module SQLServer
      module DatabaseStatements
        def returning_column_values(result)
          result.rows.first
        end
      end
    end
  end
end

Note the patch is basically copy-pasted from the PostgreSQL implementation; see https://github.com/rails/rails/blob/v7.2.2.1/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb#L186-L188.

SQL Server

To test against the SQL Server adapter, ensure you have a running SQL server instance containing a database named "composite_key_test", then populate .env with something like this:

username=your_db_user
password=your_db_password
db_name=composite_key_test
adapter=sqlserver
host=localhost
port=1433

ruby test.rb will show two test failures:

  1) Failure:
CreateWithCompositeKeyAssignsGuidToCreatedObjectTest#test_create_with_composite_key_assigns_guid_to_created_object [test.rb:179]:
--- expected
+++ actual
@@ -1 +1 @@
-nil
+"4679ED83-27EB-418D-BCB5-2B3BDFF7A18D"


  2) Failure:
CreateWithCompositeKeyAssignsIdToCreatedObjectTest#test_create_with_composite_key_assigns_id_to_created_object [test.rb:169]:
--- expected
+++ actual
@@ -1 +1 @@
-[2, nil]
+[2, "EA785113-8AFE-4B8C-85F0-5D2FC58368C0"]

PATCH=1 ruby test.rb will show no test failures. The behavior is the same on gem 'activerecord-sqlserver-adapter', '8.0.7' with gem 'activerecord', '~> 8.0'.

PostgreSQL

As a comparison, you may also test against PostgreSQL via the docker image:

docker run --name some-postgres \
           -e POSTGRES_USER=your_db_user \
           -e POSTGRES_PASSWORD=your_db_password \
           -e POSTGRES_DB=composite_key_test \
           -p 5432:5432 \
           -d postgres

Then populate .env with something like this::

username=your_db_user
password=your_db_password
db_name=composite_key_test
adapter=postgresql
host=localhost
port=5432

ruby test.rb will show no test failures.