NewUpdate SET ignores nullzero

[sergolius]

nullzero behaves differently at NewInsert, NewUpdate.
OmitZero() has no impact on NewUpdate that uses Set method.

NewInsert will produce SQL where nullzero values replaced with DEFAULT, but NewUpdate will replace with default value of type.

type User struct {
	ID          uuid.UUID `bun:"type:uuid,default:uuid_generate_v4()"`
	FirstName   string    `bun:"first_name,nullzero"`
	ProfileID   uuid.UUID `bun:"profile_id,nullzero,type:uuid"`
	DeletedAt   time.Time `bun:"deleted_at,nullzero"`
	DocumentIDs []string  `bun:"document_ids,nullzero"`
}

user := new(User)
_, err := db.NewInsert().Model(user).
	Returning("null").
	Exec(ctx)

INSERT INTO "users" ("id", "first_name", "profile_id", "deleted_at", "document_ids") VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)

Set ignores nullzero:

user = new(User)
_, err = db.NewUpdate().
	Model(user).
	Set("first_name = ?first_name").
	Set("profile_id = ?profile_id").
	Set("deleted_at = ?deleted_at").
	Set("document_ids = ?document_ids").
	Where("true").
	Exec(ctx)

UPDATE "users" AS "user" SET first_name = '', profile_id = '00000000-0000-0000-0000-000000000000', deleted_at = '0001-01-01 00:00:00+00:00', document_ids = 'null' WHERE (true)

Column applies nullzero:

user = new(User)
_, err = db.NewUpdate().
	Model(user).
	Column("first_name").
	Column("profile_id").
	Column("deleted_at").
	Column("document_ids").
	Where("true").
	Exec(ctx)

UPDATE "users" AS "user" SET "first_name" = NULL, "profile_id" = NULL, "deleted_at" = NULL, "document_ids" = NULL WHERE (true)

[sergolius]

It requires explicit pointer using

*string
*uuid.UUID
*time.Time

[vmihailenco]

OmitZero() has no impact on NewUpdate that uses Set method.

This is working as expected. OmitZero only works with updates and only when using Column method.

[sergolius]

@vmihailenco
Ok OmitZero is for usecase of Column.
But nullzero?
NewUpdate, Set ignores nullzero. https://bun.uptrace.dev/guide/pg-migration.html#go-zero-values-and-null
It will produce SQL with nil value of type instead of DEFAULT or NULL.
I updated issue with clarified examples.
Copy of SQL sample:

Result SQL with Set:

UPDATE "users" AS "user" SET first_name = '', profile_id = '00000000-0000-0000-0000-000000000000', deleted_at = '0001-01-01 00:00:00+00:00', document_ids = 'null' WHERE (true)

Result SQL with Column:

UPDATE "users" AS "user" SET "first_name" = NULL, "profile_id" = NULL, "deleted_at" = NULL, "document_ids" = NULL WHERE (true)

p.s. It's a challenge to migrate from go-pg. Not because of change of huge codebase, but because of such pitfalls.

[vmihailenco]

That is a bug / overlook and it should be fixed by #339. Please try master branch.

[sergolius]

@vmihailenco thanks a lot