Error default NullZero

[dlarin]

when inserting or updating, the value is equal to the default value, then it is replaced with null

tag.option "default" set NullZero = true. Because of this, we get that value null
https://github.com/uptrace/bun/blob/master/schema/table.go#L380

https://github.com/uptrace/bun/blob/master/schema/field.go#L96

[dlarin]

example

type Item struct {
	bun.BaseModel    `bun:"item"`
	ID               int       `bun:"id,pk,autoincrement"`
	NomenclatureGUID uuid.UUID `bun:"nomenclature_guid,type:binary(36)"`
	Count            int       `bun:"count,type:int,notnull,default:0"`
	Url              string    `bun:"url,type:varchar(100),notnull,default:''"`
}

item := error2.Item{
	Count: 5,
	Url:   "www.test.ru",
}
_, err = db.NewInsert().Model(&item).Exec(context.Background())

item.Url = ""
_, err = db.NewUpdate().Model(&item).WherePK().Exec(context.Background())

waiting
UPDATE `item` SET `nomenclature_guid` = '00000000-0000-0000-0000-000000000000', `count` = 5, `url` = '' WHERE (`item`.`id` = 1)
actual
UPDATE `item` SET `nomenclature_guid` = '00000000-0000-0000-0000-000000000000', `count` = 5, `url` = NULL WHERE (`item`.`id` = 1)

item.Count = 0
_, err = db.NewUpdate().Model(&item).WherePK().Exec(context.Background())

waiting
UPDATE `item` SET `nomenclature_guid` = '00000000-0000-0000-0000-000000000000', `count` = 0, `url` = '' WHERE (`item`.`id` = 1)
actual
UPDATE `item` SET `nomenclature_guid` = '00000000-0000-0000-0000-000000000000', `count` = NULL, `url` = NULL WHERE (`item`.`id` = 1)

The problem appeared after fixing issue 512
It was enough to pass the uuid=nil to be executed uuid_generate_v4()

https://github.com/uptrace/bun/pull/515/files#diff-abecd8d935ca8952a3554e15fa6989972dd0cea6de3410b6541931883b0d6d84R377

[maximerety]

I just noticed I had the same problem when migrating an application from go-pg/pg to bun.

Thanks @dlarin for reporting this!

To clarify the issue: it is currently impossible to update a field to the zero value if it is declared with a default value (should we update the title of the issue to clarify?).

Note that this is not an issue when inserting an item, since a zero value would be translated either to DEFAULT or to the default value set via the default tag (see query_insert.go#L322-L330).

Elaborating on your example, the following:

item := Item{
	Count: 5,
	Url:   "",
}
_, err = db.NewInsert().Model(&item).Exec(ctx)

would generate:

-- Using PostgreSQL, where DEFAULT placeholder is supported:
INSERT INTO "item" ("nomenclature_guid", "count", "url") VALUES (NULL, 5, DEFAULT);

-- Using a database where DEFAULT placeholder is not supported, e.q. sqlite:
INSERT INTO "item" ("nomenclature_guid", "count", "url") VALUES (NULL, 5 , '');

In both cases, the url column is set to the empty string (either by the database itself, applying the column's default, or by the query explicitly), as expected.

Then, as @dlarin showed previously, an update to the zero value is impossible as it is translated to NULL (see query_update.go#L313 which calls schema/field.go#L96-L98), since the default tag implies nullzero (see schema/table.go#L378-L381):

item.Url = ""
_, err = db.NewUpdate().Model(&item).Column("url").WherePK().Exec(context.Background())

UPDATE "item" SET "url" = NULL WHERE ("item"."id" = 1)

This is unexpected and seems like a bug.

A short-term workaround would be to use Set() or SetColumn() (see Update API) instead, but this can be tedious to implement.

EDIT (2024-11-14): the workaround is no longer needed with all Bun versions >= 1.1.17 (see comment)

[liut]

same to me.

[kimlundgren]

Also having this issue. Had to remove all default values, which is obviously highly problematic. Is it specific to Postgres?

[casoetan]

Any update on this?

[stansado]

same here, had to remove "default" value from the model field

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.

[maximerety]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.

Let's keep it open, unless someone can confirm it was fixed.

[j2gg0s]

In the latest version(v1.2.5), the default tag will not implies nullzero.
So this problem will no longer occur.

[maximerety]

In the latest version(v1.2.5), the default tag will not implies nullzero. So this problem will no longer occur.

@j2gg0s Thanks for checking this 🙏

Indeed, Bun version 1.1.17 shipped with a fix from #937 (here) ensuring that the default tag no longer implies nullzero.

I've tested this change in one of my applications that would be affected by this issue, and I can confirm it is fixed if using Bun 1.1.17+. This issue can be closed as resolved!