[ActiveRecord] Upsert ignore on_duplicate if unique_by is specified

[javierav]

Steps to reproduce

require 'bundler/inline'

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

  gem 'rails', '7.1.1'
  gem 'sqlite3'
  gem 'byebug'
end

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

# This connection will do for database-independent bug reports.
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")
ActiveRecord::Base.logger = Logger.new(STDOUT)

ActiveRecord::Schema.define do
  create_table :sequences, force: true do |t|
    t.integer :year, null: false
    t.integer :sequence, null: false, default: 1
    t.index :year, unique: true
  end
end

class Sequence < ActiveRecord::Base
end

class BugTest < Minitest::Test
  def setup
    Sequence.create(year: 2023)
  end

  def test_upsert
    Sequence.upsert({year: 2023}, unique_by: :year, on_duplicate: Arel.sql("sequence = sequence + 1"))
  end
end

Expected behavior

Sequence.upsert({year: 2023}, unique_by: :year, on_duplicate: Arel.sql("sequence = sequence + 1"))

Should produce following SQL code:

INSERT INTO "sequences" ("year") VALUES (2023) ON CONFLICT ("year") DO UPDATE SET sequence = sequence + 1 RETURNING "id"

Actual behavior

ActiveRecord::RecordNotUnique: SQLite3::ConstraintException: UNIQUE constraint failed: sequences.year

Due to produced SQL code:

INSERT INTO "sequences" ("year") VALUES (2023) ON CONFLICT ("year") DO NOTHING RETURNING "id"

If I remove unique_by the SQL code not is valid:

INSERT INTO "sequences" ("year") VALUES (2023) ON CONFLICT ("id") DO UPDATE SET sequence = sequence + 1 RETURNING "id"

System configuration

Rails version: 7.1.1
Ruby version: ruby 3.2.2 (2023-03-30 revision e51014f9c0) [arm64-darwin22]

[fatkodima]

Confirmed. Going to open a PR.