Gorm postgresql : UPDATE query formed incorrectly, update ... set ... FROM ... where

GORM Playground Link

https://github.com/go-gorm/playground/pull/746 provided by @ajhodges

Description

I have an update executed within a database transaction and that is leading to the following faulty query

UPDATE users 
SET col1 = value,
col2  = value,
...
col 20  = value
FROM users -- WRONG! FROM must not be present in the update query 
WHERE user_id = value 
AND user_role.is_del = 0

The error obtained from postgres is

ERROR: table name "uaa_role" specified more than once (SQLSTATE 42712)

I have another location where this query works fine, the only difference I see is that at SELECT has been performed on the same table before this update.

Is this a case of the stale FROM clause from the SELECT lying in the parse tree when the UPDATE is query is being formed? If so what is a workaround for this?

this is using

    gorm.io/driver/postgres v1.5.7
    gorm.io/gorm v1.25.10

Here is a sequence of queries that ran before getting to this faulty update query. The sequence is edited by hand for brevity


[rows:1] SELECT count(*) FROM users WHERE user_id = value AND users.is_del = 0

[rows:26] SELECT * FROM users WHERE ("tenant_id","user_id") IN (('Value','Value'))

[rows:1] SELECT * FROM users WHERE user_id = 'value' AND users.is_del = 0 ORDER BY user_id 

[rows:1] SELECT count(*) FROM users WHERE users.is_del = 0


followed by the faulty query

Are there any changes that happened which could lead to the previous SELECT query's parse tree is still lying around and polluting the parser input for the update?

If so is there a workaround for this?

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: marek-veber

I have the same issue (with gorm.io/driver/postgres v1.5.7& gorm.io/gorm v1.25.10). It's based on: db.Model(...).Find(....).Update(...)

Comment From: github-actions[bot]

Comment From: ajhodges

@alkuma I added a playground test case for this here, I'm facing the same issue: https://github.com/go-gorm/playground/pull/746

Comment From: ajhodges

FWIW it looks like this is a regression that started in gorm.io/driver/postgres v1.5.3 (v1.5.2 does not have this issue)

Comment From: github-actions[bot]

Comment From: alkuma

@ajhodges https://github.com/go-gorm/playground/pull/746 is a much more concise test case than mine. Let me add this to the original issue report thank you

Comment From: gabizou

Just got bit by this issue as well, it's even more apparent when using soft deletion "updates".

Comment From: a631807682

use Session api https://gorm.io/docs/method_chaining.html#Reusability-and-Safety

Comment From: ajhodges

@a631807682 I updated the example here with a session: https://github.com/go-gorm/playground/pull/746

It has no effect. I still get this error:

2024/06/29 16:28:21 /Users/adamhodges/src/gorm-playground/main_test.go:20 ERROR: table name "users" specified more than once (SQLSTATE 42712)
[0.635ms] [rows:1] UPDATE "users" SET "name"='jinzhu 2',"updated_at"='2024-06-29 16:28:21.723' FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL AND "id" = 1
--- FAIL: TestGORM (0.00s)
    main_test.go:21: Failed, got error: ERROR: table name "users" specified more than once (SQLSTATE 42712)
FAIL
exit status 1
FAIL    gorm.io/playground  0.580s

Again, this seems to be a regression in gorm.io/driver/postgres v1.5.3+

Comment From: a631807682

A critical aspect of GORM is understanding when a gorm.DB instance is safe to reuse. Following a Chain Method or Finisher Method, GORM returns an initialized gorm.DB instance. This instance is not safe for reuse as it may carry over conditions from previous operations, potentially leading to contaminated SQL queries.

The documentation explains why sessions are needed, but it does not mean that you can use sessions everywhere as you expect. https://github.com/go-gorm/playground/pull/746/files#diff-79ce3229c13921b79b1175dcb211336aaf84dfd8edcf19c07698934d4fe70e5eR20

Comment From: ajhodges

@a631807682 I get this error even when I use a fresh DB connection (no previous queries) https://github.com/go-gorm/playground/pull/746/files#diff-79ce3229c13921b79b1175dcb211336aaf84dfd8edcf19c07698934d4fe70e5eR24-R29

❯ GORM_DIALECT=postgres go test
2024/07/08 10:00:42 testing postgres...

2024/07/08 10:00:42 ./gorm-playground/main_test.go:18
[1.046ms] [rows:1] INSERT INTO "users" ("created_at","updated_at","deleted_at","name","age","birthday","company_id","manager_id","active") VALUES ('2024-07-08 10:00:42.987','2024-07-08 10:00:42.987',NULL,'jinzhu',0,NULL,NULL,NULL,false) RETURNING "id"
2024/07/08 10:00:42 testing postgres...

2024/07/08 10:00:42 ./gorm-playground/main_test.go:29 ERROR: table name "users" specified more than once (SQLSTATE 42712)
[0.552ms] [rows:1] UPDATE "users" SET "name"='jinzhu 2',"updated_at"='2024-07-08 10:00:42.993' FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL AND "id" = 1
--- FAIL: TestGORM (0.01s)
    main_test.go:30: Failed, got error: ERROR: table name "users" specified more than once (SQLSTATE 42712)
FAIL
exit status 1
FAIL    gorm.io/playground  0.518s

Comment From: a631807682

@a631807682 I get this error even when I use a fresh DB connection (no previous queries) go-gorm/playground#746 (files)

``` ❯ GORM_DIALECT=postgres go test 2024/07/08 10:00:42 testing postgres...

2024/07/08 10:00:42 ./gorm-playground/main_test.go:18 [1.046ms] [rows:1] INSERT INTO "users" ("created_at","updated_at","deleted_at","name","age","birthday","company_id","manager_id","active") VALUES ('2024-07-08 10:00:42.987','2024-07-08 10:00:42.987',NULL,'jinzhu',0,NULL,NULL,NULL,false) RETURNING "id" 2024/07/08 10:00:42 testing postgres...

2024/07/08 10:00:42 ./gorm-playground/main_test.go:29 ERROR: table name "users" specified more than once (SQLSTATE 42712) [0.552ms] [rows:1] UPDATE "users" SET "name"='jinzhu 2',"updated_at"='2024-07-08 10:00:42.993' FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL AND "id" = 1 --- FAIL: TestGORM (0.01s) main_test.go:30: Failed, got error: ERROR: table name "users" specified more than once (SQLSTATE 42712) FAIL exit status 1 FAIL gorm.io/playground 0.518s ```

All finished apis will change the statement, which means you should not use DB.First(...).Update... you should use DB.First and DB.Update instead

Comment From: leokassio

same for me, any success in solving this guys?

Comment From: LordPax

i have the same issue, is someone solved that ?