Upsert not returning all model attributes on conflict

[adsa95]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v4.15.0

What is your database and version (eg. Postgresql 10)

Postgresql 12.5

If this happened at runtime what code produced the issue? (if not applicable leave blank)

u := &model.User{
	ID:        "newClientGeneratedId", // generated in go, not using database generated id
	Email:     "[email protected]",
	DeletedAt: null.Time{},
}

err = u.Upsert(
	db,
	true,
	[]string{model.UserColumns.Email},
	boil.Whitelist(model.UserColumns.DeletedAt),
	boil.Infer(),
)
if err != nil {
	panic(err)
}

log.Println(u.ID)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE USERS (
	id varchar not null primary key, 
	email varchar not null unique, 
	deleted_at timestamp with time zone
);

Further information. What did you do, what did you expect?

I'm trying to use upsert to undelete (set deleted_at=null) or add a new row, but the model I'm using to upsert the row is not refreshed with all details from the existing row in case of a conflict, the main issue being that the primary key (ID) is not updated.

With the following data in the database:

id	email	deleted_at
abc123	[email protected]	2023-08-23 11:18:00

I expected the upsert to update the existing database row, which it did, and then update the u struct with all existing data so that the log call at the end would print abc123. However, the ID of the struct is not updated and the log call instead prints newClientGeneratedId. Since the ID is not updated and the u struct still has an ID that does not exist in the database, using this ID in following code would probably cause some major issues.

I've looked through some of the sqlboiler code and think this is because the return values in the generated query is the same for an insert as an upsert, which is columns-with-default minus some columns depending on the type of Columns used. Note that we're generating the IDs in go before upserting and does not use any database generated IDs, hence the ID field is not in columns-with-defaults. However I would argue that with an upsert you'd expect the whole model to get updated with values from the database if they exist, so maybe all columns should be returned in this case? When I manually added the ID column to the return set it was processed and set as I expected.

[stephenafamo]

Can you turn on boil.DebugMode and share the queries that were generated and ran?

[adsa95]

Yes, this is the executed query!

INSERT INTO "users" ("id", "email") VALUES ($1,$2) ON CONFLICT ("email") DO UPDATE SET "deleted_at" = EXCLUDED."deleted_at" RETURNING "deleted_at"

[stephenafamo]

I don't have the bandwidth to fix it soon, I have labelled the issue.

If you can send in a PR, I would appreciate it and be happy to review it.

[winterjung]

@stephenafamo I think pr #1306 has resolved the issue. Could we close it?