MySQL: Inserts does not work for tables only having a composite unique index

[jacobmolby]

In v0.30 (and probably also v0.29, but I haven't tested that) inserts does not work for MySQL tables that relies on having a composite unique index.

When you're doing an insert this error is returned: cannot retrieve inserted row (ErrCannotRetrieveRow) and the model is nil.

Minimum reproducer

func TestBob(t *testing.T) {
	f := factory.New()
	f.NewUniqueOnlyTable(
		factory.UniqueOnlyTableMods.A(1),
		factory.UniqueOnlyTableMods.B(1),
	).CreateOrFail(context.Background(), t, db)
}

Setup

# bobgen.yaml
constraints:
  unique_only_table:
    primary:
      name: "unique_only_table_unique"
      columns: ["a", "b"]

unique_only_table.bob

// Code generated by BobGen mysql v0.30.0. DO NOT EDIT.
// This file is meant to be re-generated in place and/or deleted at any time.

package models

import (
	"context"
	"io"

	"github.com/aarondl/opt/omit"
	"github.com/stephenafamo/bob"
	"github.com/stephenafamo/bob/dialect/mysql"
	"github.com/stephenafamo/bob/dialect/mysql/dialect"
	"github.com/stephenafamo/bob/dialect/mysql/dm"
	"github.com/stephenafamo/bob/dialect/mysql/sm"
	"github.com/stephenafamo/bob/dialect/mysql/um"
	"github.com/stephenafamo/bob/expr"
)

// UniqueOnlyTable is an object representing the database table.
type UniqueOnlyTable struct {
	A int32 `db:"a,pk" json:"a"`
	B int32 `db:"b,pk" json:"b"`
}

// UniqueOnlyTableSlice is an alias for a slice of pointers to UniqueOnlyTable.
// This should almost always be used instead of []*UniqueOnlyTable.
type UniqueOnlyTableSlice []*UniqueOnlyTable

// UniqueOnlyTables contains methods to work with the unique_only_table table
var UniqueOnlyTables = mysql.NewTablex[*UniqueOnlyTable, UniqueOnlyTableSlice, *UniqueOnlyTableSetter]("unique_only_table", []string{"a", "b"})

// UniqueOnlyTablesQuery is a query on the unique_only_table table
type UniqueOnlyTablesQuery = *mysql.ViewQuery[*UniqueOnlyTable, UniqueOnlyTableSlice]

type uniqueOnlyTableColumnNames struct {
	A string
	B string
}

var UniqueOnlyTableColumns = buildUniqueOnlyTableColumns("unique_only_table")

type uniqueOnlyTableColumns struct {
	tableAlias string
	A          mysql.Expression
	B          mysql.Expression
}

func (c uniqueOnlyTableColumns) Alias() string {
	return c.tableAlias
}

func (uniqueOnlyTableColumns) AliasedAs(alias string) uniqueOnlyTableColumns {
	return buildUniqueOnlyTableColumns(alias)
}

func buildUniqueOnlyTableColumns(alias string) uniqueOnlyTableColumns {
	return uniqueOnlyTableColumns{
		tableAlias: alias,
		A:          mysql.Quote(alias, "a"),
		B:          mysql.Quote(alias, "b"),
	}
}

type uniqueOnlyTableWhere[Q mysql.Filterable] struct {
	A mysql.WhereMod[Q, int32]
	B mysql.WhereMod[Q, int32]
}

func (uniqueOnlyTableWhere[Q]) AliasedAs(alias string) uniqueOnlyTableWhere[Q] {
	return buildUniqueOnlyTableWhere[Q](buildUniqueOnlyTableColumns(alias))
}

func buildUniqueOnlyTableWhere[Q mysql.Filterable](cols uniqueOnlyTableColumns) uniqueOnlyTableWhere[Q] {
	return uniqueOnlyTableWhere[Q]{
		A: mysql.Where[Q, int32](cols.A),
		B: mysql.Where[Q, int32](cols.B),
	}
}

var UniqueOnlyTableErrors = &uniqueOnlyTableErrors{
	ErrUniqueAAndB: &UniqueConstraintError{s: "unique_index"},
}

type uniqueOnlyTableErrors struct {
	ErrUniqueAAndB *UniqueConstraintError
}

// UniqueOnlyTableSetter is used for insert/upsert/update operations
// All values are optional, and do not have to be set
// Generated columns are not included
type UniqueOnlyTableSetter struct {
	A omit.Val[int32] `db:"a,pk" json:"a"`
	B omit.Val[int32] `db:"b,pk" json:"b"`
}

func (s UniqueOnlyTableSetter) SetColumns() []string {
	vals := make([]string, 0, 2)
	if !s.A.IsUnset() {
		vals = append(vals, "a")
	}

	if !s.B.IsUnset() {
		vals = append(vals, "b")
	}

	return vals
}

func (s UniqueOnlyTableSetter) Overwrite(t *UniqueOnlyTable) {
	if !s.A.IsUnset() {
		t.A, _ = s.A.Get()
	}
	if !s.B.IsUnset() {
		t.B, _ = s.B.Get()
	}
}

func (s *UniqueOnlyTableSetter) Apply(q *dialect.InsertQuery) {
	q.AppendHooks(func(ctx context.Context, exec bob.Executor) (context.Context, error) {
		return UniqueOnlyTables.BeforeInsertHooks.RunHooks(ctx, exec, s)
	})

	q.AppendValues(bob.ExpressionFunc(func(ctx context.Context, w io.Writer, d bob.Dialect, start int) ([]any, error) {
		vals := make([]bob.Expression, 2)
		if s.A.IsUnset() {
			vals[0] = mysql.Raw("DEFAULT")
		} else {
			vals[0] = mysql.Arg(s.A)
		}

		if s.B.IsUnset() {
			vals[1] = mysql.Raw("DEFAULT")
		} else {
			vals[1] = mysql.Arg(s.B)
		}

		return bob.ExpressSlice(ctx, w, d, start, vals, "", ", ", "")
	}))
}

func (s UniqueOnlyTableSetter) UpdateMod() bob.Mod[*dialect.UpdateQuery] {
	return um.Set(s.Expressions("unique_only_table")...)
}

func (s UniqueOnlyTableSetter) Expressions(prefix ...string) []bob.Expression {
	exprs := make([]bob.Expression, 0, 2)

	if !s.A.IsUnset() {
		exprs = append(exprs, expr.Join{Sep: " = ", Exprs: []bob.Expression{
			mysql.Quote(append(prefix, "a")...),
			mysql.Arg(s.A),
		}})
	}

	if !s.B.IsUnset() {
		exprs = append(exprs, expr.Join{Sep: " = ", Exprs: []bob.Expression{
			mysql.Quote(append(prefix, "b")...),
			mysql.Arg(s.B),
		}})
	}

	return exprs
}

// FindUniqueOnlyTable retrieves a single record by primary key
// If cols is empty Find will return all columns.
func FindUniqueOnlyTable(ctx context.Context, exec bob.Executor, APK int32, BPK int32, cols ...string) (*UniqueOnlyTable, error) {
	if len(cols) == 0 {
		return UniqueOnlyTables.Query(
			SelectWhere.UniqueOnlyTables.A.EQ(APK),
			SelectWhere.UniqueOnlyTables.B.EQ(BPK),
		).One(ctx, exec)
	}

	return UniqueOnlyTables.Query(
		SelectWhere.UniqueOnlyTables.A.EQ(APK),
		SelectWhere.UniqueOnlyTables.B.EQ(BPK),
		sm.Columns(UniqueOnlyTables.Columns().Only(cols...)),
	).One(ctx, exec)
}

// UniqueOnlyTableExists checks the presence of a single record by primary key
func UniqueOnlyTableExists(ctx context.Context, exec bob.Executor, APK int32, BPK int32) (bool, error) {
	return UniqueOnlyTables.Query(
		SelectWhere.UniqueOnlyTables.A.EQ(APK),
		SelectWhere.UniqueOnlyTables.B.EQ(BPK),
	).Exists(ctx, exec)
}

// AfterQueryHook is called after UniqueOnlyTable is retrieved from the database
func (o *UniqueOnlyTable) AfterQueryHook(ctx context.Context, exec bob.Executor, queryType bob.QueryType) error {
	var err error

	switch queryType {
	case bob.QueryTypeSelect:
		ctx, err = UniqueOnlyTables.AfterSelectHooks.RunHooks(ctx, exec, UniqueOnlyTableSlice{o})
	case bob.QueryTypeInsert:
		ctx, err = UniqueOnlyTables.AfterInsertHooks.RunHooks(ctx, exec, UniqueOnlyTableSlice{o})
	case bob.QueryTypeUpdate:
		ctx, err = UniqueOnlyTables.AfterUpdateHooks.RunHooks(ctx, exec, UniqueOnlyTableSlice{o})
	case bob.QueryTypeDelete:
		ctx, err = UniqueOnlyTables.AfterDeleteHooks.RunHooks(ctx, exec, UniqueOnlyTableSlice{o})
	}

	return err
}

// PrimaryKeyVals returns the primary key values of the UniqueOnlyTable
func (o *UniqueOnlyTable) PrimaryKeyVals() bob.Expression {
	return mysql.ArgGroup(
		o.A,
		o.B,
	)
}

func (o *UniqueOnlyTable) pkEQ() dialect.Expression {
	return mysql.Group(mysql.Quote("unique_only_table", "a"), mysql.Quote("unique_only_table", "b")).EQ(bob.ExpressionFunc(func(ctx context.Context, w io.Writer, d bob.Dialect, start int) ([]any, error) {
		return o.PrimaryKeyVals().WriteSQL(ctx, w, d, start)
	}))
}

// Update uses an executor to update the UniqueOnlyTable
func (o *UniqueOnlyTable) Update(ctx context.Context, exec bob.Executor, s *UniqueOnlyTableSetter) error {
	_, err := UniqueOnlyTables.Update(s.UpdateMod(), um.Where(o.pkEQ())).Exec(ctx, exec)
	if err != nil {
		return err
	}

	s.Overwrite(o)

	return nil
}

// Delete deletes a single UniqueOnlyTable record with an executor
func (o *UniqueOnlyTable) Delete(ctx context.Context, exec bob.Executor) error {
	_, err := UniqueOnlyTables.Delete(dm.Where(o.pkEQ())).Exec(ctx, exec)
	return err
}

// Reload refreshes the UniqueOnlyTable using the executor
func (o *UniqueOnlyTable) Reload(ctx context.Context, exec bob.Executor) error {
	o2, err := UniqueOnlyTables.Query(
		SelectWhere.UniqueOnlyTables.A.EQ(o.A),
		SelectWhere.UniqueOnlyTables.B.EQ(o.B),
	).One(ctx, exec)
	if err != nil {
		return err
	}

	*o = *o2

	return nil
}

// AfterQueryHook is called after UniqueOnlyTableSlice is retrieved from the database
func (o UniqueOnlyTableSlice) AfterQueryHook(ctx context.Context, exec bob.Executor, queryType bob.QueryType) error {
	var err error

	switch queryType {
	case bob.QueryTypeSelect:
		ctx, err = UniqueOnlyTables.AfterSelectHooks.RunHooks(ctx, exec, o)
	case bob.QueryTypeInsert:
		ctx, err = UniqueOnlyTables.AfterInsertHooks.RunHooks(ctx, exec, o)
	case bob.QueryTypeUpdate:
		ctx, err = UniqueOnlyTables.AfterUpdateHooks.RunHooks(ctx, exec, o)
	case bob.QueryTypeDelete:
		ctx, err = UniqueOnlyTables.AfterDeleteHooks.RunHooks(ctx, exec, o)
	}

	return err
}

func (o UniqueOnlyTableSlice) pkIN() dialect.Expression {
	if len(o) == 0 {
		return mysql.Raw("NULL")
	}

	return mysql.Group(mysql.Quote("unique_only_table", "a"), mysql.Quote("unique_only_table", "b")).In(bob.ExpressionFunc(func(ctx context.Context, w io.Writer, d bob.Dialect, start int) ([]any, error) {
		pkPairs := make([]bob.Expression, len(o))
		for i, row := range o {
			pkPairs[i] = row.PrimaryKeyVals()
		}
		return bob.ExpressSlice(ctx, w, d, start, pkPairs, "", ", ", "")
	}))
}

// copyMatchingRows finds models in the given slice that have the same primary key
// then it first copies the existing relationships from the old model to the new model
// and then replaces the old model in the slice with the new model
func (o UniqueOnlyTableSlice) copyMatchingRows(from ...*UniqueOnlyTable) {
	for i, old := range o {
		for _, new := range from {
			if new.A != old.A {
				continue
			}
			if new.B != old.B {
				continue
			}

			o[i] = new
			break
		}
	}
}

// UpdateMod modifies an update query with "WHERE primary_key IN (o...)"
func (o UniqueOnlyTableSlice) UpdateMod() bob.Mod[*dialect.UpdateQuery] {
	return bob.ModFunc[*dialect.UpdateQuery](func(q *dialect.UpdateQuery) {
		q.AppendHooks(func(ctx context.Context, exec bob.Executor) (context.Context, error) {
			return UniqueOnlyTables.BeforeUpdateHooks.RunHooks(ctx, exec, o)
		})

		q.AppendLoader(bob.LoaderFunc(func(ctx context.Context, exec bob.Executor, retrieved any) error {
			var err error
			switch retrieved := retrieved.(type) {
			case *UniqueOnlyTable:
				o.copyMatchingRows(retrieved)
			case []*UniqueOnlyTable:
				o.copyMatchingRows(retrieved...)
			case UniqueOnlyTableSlice:
				o.copyMatchingRows(retrieved...)
			default:
				// If the retrieved value is not a UniqueOnlyTable or a slice of UniqueOnlyTable
				// then run the AfterUpdateHooks on the slice
				_, err = UniqueOnlyTables.AfterUpdateHooks.RunHooks(ctx, exec, o)
			}

			return err
		}))

		q.AppendWhere(o.pkIN())
	})
}

// DeleteMod modifies an delete query with "WHERE primary_key IN (o...)"
func (o UniqueOnlyTableSlice) DeleteMod() bob.Mod[*dialect.DeleteQuery] {
	return bob.ModFunc[*dialect.DeleteQuery](func(q *dialect.DeleteQuery) {
		q.AppendHooks(func(ctx context.Context, exec bob.Executor) (context.Context, error) {
			return UniqueOnlyTables.BeforeDeleteHooks.RunHooks(ctx, exec, o)
		})

		q.AppendLoader(bob.LoaderFunc(func(ctx context.Context, exec bob.Executor, retrieved any) error {
			var err error
			switch retrieved := retrieved.(type) {
			case *UniqueOnlyTable:
				o.copyMatchingRows(retrieved)
			case []*UniqueOnlyTable:
				o.copyMatchingRows(retrieved...)
			case UniqueOnlyTableSlice:
				o.copyMatchingRows(retrieved...)
			default:
				// If the retrieved value is not a UniqueOnlyTable or a slice of UniqueOnlyTable
				// then run the AfterDeleteHooks on the slice
				_, err = UniqueOnlyTables.AfterDeleteHooks.RunHooks(ctx, exec, o)
			}

			return err
		}))

		q.AppendWhere(o.pkIN())
	})
}

func (o UniqueOnlyTableSlice) UpdateAll(ctx context.Context, exec bob.Executor, vals UniqueOnlyTableSetter) error {
	_, err := UniqueOnlyTables.Update(vals.UpdateMod(), o.UpdateMod()).Exec(ctx, exec)

	for i := range o {
		vals.Overwrite(o[i])
	}

	return err
}

func (o UniqueOnlyTableSlice) DeleteAll(ctx context.Context, exec bob.Executor) error {
	if len(o) == 0 {
		return nil
	}

	_, err := UniqueOnlyTables.Delete(o.DeleteMod()).Exec(ctx, exec)
	return err
}

func (o UniqueOnlyTableSlice) ReloadAll(ctx context.Context, exec bob.Executor) error {
	if len(o) == 0 {
		return nil
	}

	o2, err := UniqueOnlyTables.Query(sm.Where(o.pkIN())).All(ctx, exec)
	if err != nil {
		return err
	}

	o.copyMatchingRows(o2...)

	return nil
}

Investigation

So it seems the issue arises in table.go as getInserted calls the function below.

func (t *Table[T, Tslice, Tset]) uniqueSet(w *bytes.Buffer, row []bob.Expression) (int, []bob.Expression) {
	if len(row) != len(t.nonGeneratedCols) {
		return -1, nil
	}

Outer:
	for i, u := range t.uniqueIdx {
		colVals := make([]bob.Expression, 0, len(u))

		for _, col := range u {
			field := row[col]

			if field == nil || isDefaultOrNull(w, field) {
				continue Outer
			}

			colVals = append(colVals, field)
		}

		if len(colVals) == len(u) {
			return i, colVals
		}
	}

	return -1, nil
}

It takes a []bob.Expression as argument and starts by comparing the length to t.nonGeneratedCols. However I think the values passed in here are wrong.

Running the debugger for the test above we see the following:

Which makes it fail already on the length check. Also looking at the structures, the rest of the function does not seem to make much sense either with this input.

Maybe it's a simple thing that needs to change, I have difficulties seeing what needs to change for it to work as expected