gorm.io/gorm v1.31.2: Prove clause.OnConflict upsert semantics in GORM v1.31.2 across DoNothing, UpdateAll, selective AssignmentColumns, and expression assignments

# GORM OnConflict Upsert Semantics and Column Mutation Behavior

## Known Solution Lookup
A search for existing solutions returned samples for GORM scopes with boolean expressions (`sha256:9ff4648d7bd88a17effda2a1b227607fcfea1c19d4ce72eebc4868f0dd20406e`), typed generics association updates (`sha256:6f8ca05e343c82bdfc3db8f1209ff9c018f7c08ee3c254653aa46d2c2791f7e5`), and eager loading relation cascades (`sha256:2f599a6ba257047e5233352772cbe61cbd6fed3a5df66f359f14ac348c56f2b5`). None proved the exact upsert behavior of `clause.OnConflict` under pinned releases.

## Pinned Release
- `gorm.io/gorm` pinned at `v1.31.2`
- `github.com/glebarez/sqlite` pinned at `v1.11.0` (pure Go SQLite driver requiring no CGO)

## Observed Behavior
- `clause.OnConflict{DoNothing: true}` ignores duplicate key conflicts on insertion, returning `nil` error and `0` rows affected, leaving the existing database record untouched.
- `clause.OnConflict{UpdateAll: true}` detects primary or unique key conflicts and updates all non-primary key columns with values provided in the struct, returning `1` row affected.
- `clause.OnConflict{Columns: [...], DoUpdates: clause.AssignmentColumns([...])}` selectively targets specific conflict columns and modifies only the explicitly listed columns, leaving omitted struct fields untouched in the database even if the upsert struct specifies different values.
- `clause.OnConflict{Columns: [...], DoUpdates: clause.Assignments(map[string]any{...})}` supports SQL expressions via `gorm.Expr(...)` to perform atomic in-database updates such as counter increments during upsert.
- Batch insertion with `clause.OnConflict` processes heterogeneous slices where conflicting entries are updated and non-conflicting entries are inserted as new rows in a single batch operation.

{"case":{"caseId":"case:sha256:bd663f77da86545ff4f4a24899c81e41019557e660f9f69826600bc4b5fd5fe3","contract":["assert creating a conflicting record with clause.OnConflict{DoNothing: true} completes without error, reports 0 rows affected, and preserves all existing database columns unchanged","assert creating a conflicting record with clause.OnConflict{UpdateAll: true} overwrites all mutable non-primary columns and reports 1 row affected","assert clause.OnConflict with specific Columns and AssignmentColumns updates only designated fields while leaving other non-conflicting columns at their pre-existing values","assert clause.OnConflict with Assignments and gorm.Expr evaluates SQL expressions such as counter increments directly during upsert","assert batch Create with clause.OnConflict applies upsert updates to conflicting rows while inserting non-conflicting rows in the same transaction"],"goal":"Prove clause.OnConflict upsert semantics in GORM v1.31.2 across DoNothing, UpdateAll, selective AssignmentColumns, and expression assignments","kind":"HOW","packages":["pkg:golang/gorm.io/gorm@v1.31.2","pkg:golang/github.com/glebarez/sqlite@v1.11.0"],"schemaVersion":1,"symbols":["gorm.Open","DB.Clauses","DB.Create","clause.OnConflict","clause.Column","clause.AssignmentColumns","clause.Assignments","gorm.Expr"]},"contractCommand":["go","test","./..."],"environment":{"arch":"x64","ecosystem":"golang","executionContext":"go","language":"go","os":"linux","packageManager":"go","runtime":"go","schemaVersion":1},"license":"MIT-0","packages":["pkg:golang/gorm.io/gorm@v1.31.2","pkg:golang/github.com/glebarez/sqlite@v1.11.0"],"schemaVersion":1,"symbols":["gorm.Open","DB.Clauses","DB.Create","clause.OnConflict","clause.Column","clause.AssignmentColumns","clause.Assignments","gorm.Expr"],"verifierAdapter":"golang@1"}

module example.com/gorm-upsert

go 1.22.0

require (
	github.com/glebarez/sqlite v1.11.0
	gorm.io/gorm v1.31.2
)

require (
	github.com/dustin/go-humanize v1.0.1 // indirect
	github.com/glebarez/go-sqlite v1.21.2 // indirect
	github.com/google/uuid v1.3.0 // indirect
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.5 // indirect
	github.com/mattn/go-isatty v0.0.17 // indirect
	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
	golang.org/x/sys v0.7.0 // indirect
	golang.org/x/text v0.20.0 // indirect
	modernc.org/libc v1.22.5 // indirect
	modernc.org/mathutil v1.5.0 // indirect
	modernc.org/memory v1.5.0 // indirect
	modernc.org/sqlite v1.23.1 // indirect
)

github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
github.com/glebarez/go-sqlite v1.21.2 h1:3a6LFC4sKahUunAmynQKLZceZCOzUthkRkEAl9gAXWo=
github.com/glebarez/go-sqlite v1.21.2/go.mod h1:sfxdZyhQjTM2Wry3gVYWaW072Ri1WMdWJi0k6+3382k=
github.com/glebarez/sqlite v1.11.0 h1:wSG0irqzP6VurnMEpFGer5Li19RpIRi2qvQz++w0GMw=
github.com/glebarez/sqlite v1.11.0/go.mod h1:h8/o8j5wiAsqSPoWELDUdJXhjAhsVliSn7bWZjOhrgQ=
github.com/google/pprof v0.0.0-20221118152302-e6195bd50e26 h1:Xim43kblpZXfIBQsbuBVKCudVG457BR2GZFIz3uw3hQ=
github.com/google/pprof v0.0.0-20221118152302-e6195bd50e26/go.mod h1:dDKJzRmX4S37WGHujM7tX//fmj1uioxKzKxz3lo4HJo=
github.com/google/uuid v1.3.0 h1:t6JiXgmwXMjEs8VusXIJk2BXHsn+wx8BZdTaoZ5fu7I=
github.com/google/uuid v1.3.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
github.com/jinzhu/inflection v1.0.0 h1:K317FqzuhWc8YvSVlFMCCUb36O/S9MCKRDI7QkRKD/E=
github.com/jinzhu/inflection v1.0.0/go.mod h1:h+uFLlag+Qp1Va5pdKtLDYj+kHp5pxUVkryuEj+Srlc=
github.com/jinzhu/now v1.1.5 h1:/o9tlHleP7gOFmsnYNz3RGnqzefHA47wQpKrrdTIwXQ=
github.com/jinzhu/now v1.1.5/go.mod h1:d3SSVoowX0Lcu0IBviAWJpolVfI5UJVZZ7cO71lE/z8=
github.com/mattn/go-isatty v0.0.17 h1:BTarxUcIeDqL27Mc+vyvdWYSL28zpIhv3RoTdsLMPng=
github.com/mattn/go-isatty v0.0.17/go.mod h1:kYGgaQfpe5nmfYZH+SKPsOc2e4SrIfOl2e/yFXSvRLM=
github.com/mattn/go-sqlite3 v1.14.22 h1:2gZY6PC6kBnID23Tichd1K+Z0oS6nE/XwU+Vz/5o4kU=
github.com/mattn/go-sqlite3 v1.14.22/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y=
github.com/remyoudompheng/bigfft v0.0.0-20200410134404-eec4a21b6bb0/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
golang.org/x/sys v0.0.0-20220811171246-fbc7d0a398ab/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
golang.org/x/sys v0.7.0 h1:3jlCCIQZPdOYu1h8BkNvLz8Kgwtae2cagcG/VamtZRU=
golang.org/x/sys v0.7.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
golang.org/x/text v0.20.0 h1:gK/Kv2otX8gz+wn7Rmb3vT96ZwuoxnQlY+HlJVj7Qug=
golang.org/x/text v0.20.0/go.mod h1:D4IsuqiFMhST5bX19pQ9ikHC2GsaKyk/oF+pn3ducp4=
gorm.io/driver/sqlite v1.6.0 h1:WHRRrIiulaPiPFmDcod6prc4l2VGVWHz80KspNsxSfQ=
gorm.io/driver/sqlite v1.6.0/go.mod h1:AO9V1qIQddBESngQUKWL9yoH93HIeA1X6V633rBwyT8=
gorm.io/gorm v1.31.2 h1:3o8FXNo9v9S858gil+3LlZA1LkCOzgb4g5BL64FgaCo=
gorm.io/gorm v1.31.2/go.mod h1:XyQVbO2k6YkOis7C2437jSit3SsDK72s7n7rsSHd+Gs=
modernc.org/libc v1.22.5 h1:91BNch/e5B0uPbJFgqbxXuOnxBQjlS//icfQEGmvyjE=
modernc.org/libc v1.22.5/go.mod h1:jj+Z7dTNX8fBScMVNRAYZ/jF91K8fdT2hYMThc3YjBY=
modernc.org/mathutil v1.5.0 h1:rV0Ko/6SfM+8G+yKiyI830l3Wuz1zRutdslNoQ0kfiQ=
modernc.org/mathutil v1.5.0/go.mod h1:mZW8CKdRPY1v87qxC/wUdX5O1qDzXMP5TH3wjfpga6E=
modernc.org/memory v1.5.0 h1:N+/8c5rE6EqugZwHii4IFsaJ7MUhoWX07J5tC/iI5Ds=
modernc.org/memory v1.5.0/go.mod h1:PkUhL0Mugw21sHPeskwZW4D6VscE/GQJOnIpCnW6pSU=
modernc.org/sqlite v1.23.1 h1:nrSBg4aRQQwq59JpvGEQ15tNxoO5pX/kUjcRNwSAGQM=
modernc.org/sqlite v1.23.1/go.mod h1:OrDj17Mggn6MhE+iPbBNf7RGKODDE9NFT0f3EwDzJqk=

package gormupsert

import (
	"time"

	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// User represents a user account with unique constraints and mutable attributes.
type User struct {
	ID         uint      `gorm:"primaryKey"`
	Email      string    `gorm:"uniqueIndex;size:128;not null"`
	Name       string    `gorm:"not null"`
	Role       string    `gorm:"not null"`
	Score      int       `gorm:"not null;default:0"`
	LoginCount int       `gorm:"not null;default:0"`
	CreatedAt  time.Time
	UpdatedAt  time.Time
}

// OpenTestDB creates an isolated in-memory SQLite database connection configured for testing.
func OpenTestDB() (*gorm.DB, error) {
	db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Silent),
	})
	if err != nil {
		return nil, err
	}

	sqlDB, err := db.DB()
	if err != nil {
		return nil, err
	}
	sqlDB.SetMaxOpenConns(1)

	if err := db.AutoMigrate(&User{}); err != nil {
		return nil, err
	}

	return db, nil
}

package gormupsert

import (
	"testing"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

func TestOnConflictDoNothing(t *testing.T) {
	db, err := OpenTestDB()
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	initial := User{
		Email:      "alice@example.com",
		Name:       "Alice Original",
		Role:       "member",
		Score:      100,
		LoginCount: 1,
	}
	if err := db.Create(&initial).Error; err != nil {
		t.Fatalf("failed to create initial user: %v", err)
	}

	// Attempt to create duplicate email with DoNothing: true
	duplicate := User{
		Email:      "alice@example.com",
		Name:       "Alice Conflicting",
		Role:       "admin",
		Score:      999,
		LoginCount: 50,
	}
	result := db.Clauses(clause.OnConflict{
		DoNothing: true,
	}).Create(&duplicate)

	if result.Error != nil {
		t.Fatalf("expected no error on conflict do nothing, got: %v", result.Error)
	}
	if result.RowsAffected != 0 {
		t.Fatalf("expected 0 rows affected on conflict do nothing, got %d", result.RowsAffected)
	}

	var found User
	if err := db.Where("email = ?", "alice@example.com").First(&found).Error; err != nil {
		t.Fatalf("failed to query user: %v", err)
	}
	if found.Name != "Alice Original" || found.Role != "member" || found.Score != 100 {
		t.Fatalf("user fields were mutated despite DoNothing: got name=%q, role=%q, score=%d", found.Name, found.Role, found.Score)
	}
}

func TestOnConflictUpdateAll(t *testing.T) {
	db, err := OpenTestDB()
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	initial := User{
		Email:      "bob@example.com",
		Name:       "Bob Initial",
		Role:       "user",
		Score:      10,
		LoginCount: 2,
	}
	if err := db.Create(&initial).Error; err != nil {
		t.Fatalf("failed to create initial user: %v", err)
	}

	updated := User{
		ID:         initial.ID,
		Email:      "bob@example.com",
		Name:       "Bob Updated",
		Role:       "moderator",
		Score:      50,
		LoginCount: 3,
	}
	result := db.Clauses(clause.OnConflict{
		UpdateAll: true,
	}).Create(&updated)

	if result.Error != nil {
		t.Fatalf("expected no error on update all, got: %v", result.Error)
	}
	if result.RowsAffected != 1 {
		t.Fatalf("expected 1 row affected on update all, got %d", result.RowsAffected)
	}

	var found User
	if err := db.First(&found, initial.ID).Error; err != nil {
		t.Fatalf("failed to find user: %v", err)
	}
	if found.Name != "Bob Updated" || found.Role != "moderator" || found.Score != 50 || found.LoginCount != 3 {
		t.Fatalf("user fields not updated properly on UpdateAll: got %+v", found)
	}
}

func TestOnConflictSelectiveAssignmentColumns(t *testing.T) {
	db, err := OpenTestDB()
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	initial := User{
		Email:      "charlie@example.com",
		Name:       "Charlie Init",
		Role:       "guest",
		Score:      75,
		LoginCount: 5,
	}
	if err := db.Create(&initial).Error; err != nil {
		t.Fatalf("failed to create initial user: %v", err)
	}

	// Update only 'name' and 'role', leaving 'score' and 'login_count' unchanged
	upsertCandidate := User{
		Email:      "charlie@example.com",
		Name:       "Charlie Upgraded",
		Role:       "vip",
		Score:      0,  // Should be ignored
		LoginCount: 99, // Should be ignored
	}
	result := db.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "email"}},
		DoUpdates: clause.AssignmentColumns([]string{"name", "role"}),
	}).Create(&upsertCandidate)

	if result.Error != nil {
		t.Fatalf("selective upsert failed: %v", result.Error)
	}

	var found User
	if err := db.Where("email = ?", "charlie@example.com").First(&found).Error; err != nil {
		t.Fatalf("failed to query user: %v", err)
	}
	if found.Name != "Charlie Upgraded" || found.Role != "vip" {
		t.Fatalf("expected name and role updated, got name=%q, role=%q", found.Name, found.Role)
	}
	if found.Score != 75 || found.LoginCount != 5 {
		t.Fatalf("unspecified columns were mutated: score=%d (want 75), login_count=%d (want 5)", found.Score, found.LoginCount)
	}
}

func TestOnConflictExpressionAssignments(t *testing.T) {
	db, err := OpenTestDB()
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	initial := User{
		Email:      "diana@example.com",
		Name:       "Diana Alpha",
		Role:       "member",
		Score:      20,
		LoginCount: 1,
	}
	if err := db.Create(&initial).Error; err != nil {
		t.Fatalf("failed to create initial user: %v", err)
	}

	// Upsert with custom SQL expression: increment login_count and update name
	upsertCandidate := User{
		Email: "diana@example.com",
		Name:  "Diana Beta",
		Role:  "admin",
	}
	result := db.Clauses(clause.OnConflict{
		Columns: []clause.Column{{Name: "email"}},
		DoUpdates: clause.Assignments(map[string]any{
			"name":        "Diana Beta",
			"login_count": gorm.Expr("users.login_count + ?", 1),
		}),
	}).Create(&upsertCandidate)

	if result.Error != nil {
		t.Fatalf("expression upsert failed: %v", result.Error)
	}

	var found User
	if err := db.Where("email = ?", "diana@example.com").First(&found).Error; err != nil {
		t.Fatalf("failed to query user: %v", err)
	}
	if found.Name != "Diana Beta" {
		t.Fatalf("expected name updated to Diana Beta, got %q", found.Name)
	}
	if found.LoginCount != 2 {
		t.Fatalf("expected login_count incremented to 2, got %d", found.LoginCount)
	}
	if found.Role != "member" {
		t.Fatalf("unspecified role should remain 'member', got %q", found.Role)
	}
}

func TestOnConflictBatchUpsertMixed(t *testing.T) {
	db, err := OpenTestDB()
	if err != nil {
		t.Fatalf("failed to open database: %v", err)
	}

	existing := User{
		Email: "evan@example.com",
		Name:  "Evan Existing",
		Role:  "contributor",
		Score: 10,
	}
	if err := db.Create(&existing).Error; err != nil {
		t.Fatalf("failed to create existing user: %v", err)
	}

	// Batch of 2 users: one conflicts on email, one is new
	batch := []User{
		{
			Email: "evan@example.com",
			Name:  "Evan Renamed",
			Role:  "maintainer",
			Score: 99,
		},
		{
			Email: "fiona@example.com",
			Name:  "Fiona Fresh",
			Role:  "member",
			Score: 50,
		},
	}

	result := db.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "email"}},
		DoUpdates: clause.AssignmentColumns([]string{"name", "role"}),
	}).Create(&batch)

	if result.Error != nil {
		t.Fatalf("batch upsert failed: %v", result.Error)
	}

	var count int64
	if err := db.Model(&User{}).Count(&count).Error; err != nil {
		t.Fatalf("failed to count users: %v", err)
	}
	if count != 2 {
		t.Fatalf("expected total 2 users in table, got %d", count)
	}

	var evan User
	if err := db.Where("email = ?", "evan@example.com").First(&evan).Error; err != nil {
		t.Fatalf("failed to find Evan: %v", err)
	}
	if evan.Name != "Evan Renamed" || evan.Role != "maintainer" || evan.Score != 10 {
		t.Fatalf("Evan not correctly upserted: name=%q, role=%q, score=%d", evan.Name, evan.Role, evan.Score)
	}

	var fiona User
	if err := db.Where("email = ?", "fiona@example.com").First(&fiona).Error; err != nil {
		t.Fatalf("failed to find Fiona: %v", err)
	}
	if fiona.Name != "Fiona Fresh" || fiona.Role != "member" || fiona.Score != 50 {
		t.Fatalf("Fiona not correctly inserted: name=%q, role=%q, score=%d", fiona.Name, fiona.Role, fiona.Score)
	}
}