GORM Playground Link

https://github.com/go-gorm/playground/pull/793

Description

Execute an update Exec against a supported dbms and check the RowsAffected is the expected number. Execute a slightly different (or the same) update Exec against the same table and check the RowsAffected is the expected number.

If there are 3 records that will, and are, updated, then RowsAffected should be populated with 3 each time you Exec the update.

dbms status
sqlite working
postgres working
mysql fails
mssql unable to test

Source data for this example is 3 user records with the age set to 25, and active set to true. Example updates:

    condition := gorm.Expr("users.age = ? AND active = ?", 25, true)
    res := DB.Exec(`UPDATE users SET birthday = '2025-03-07' WHERE ?`, condition)
    t.Logf("res.RowsAffected - Expected = 3, Actual = %d", res.RowsAffected)
    if res.RowsAffected != 3 {
        t.Fail()
    }
    condition2 := gorm.Expr("users.age = ? AND active = ? AND company_id IS NULL", 25, true)
    res2 := DB.Exec(`UPDATE users SET birthday = '2025-03-07' WHERE ?`, condition2)
    t.Logf("res.RowsAffected - Expected = 3, Actual = %d", res2.RowsAffected)
    if res2.RowsAffected != 3 {
        t.Fail()
    }

Comment From: ivila

Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795

Comment From: keif888

Yes the test is updating the rows to the same values. I didn’t find the closed issue when searching prior to raising this one.

If you run the SQL statements generated via MySQL workbench it reports that both statements affect 3 rows. Having done some more reading I suspect that the Workbench is using the CLIENT_FOUND_ROWS setting, and that Gorm is not. https://dev.mysql.com/doc/c-api/8.4/en/mysql-affected-rows.html

Given the different results between Gorm and MySQL Workbench, it would be helpful if the documentation about RowsAffected and MySQL mentioned the setting CLIENT_FOUND_ROWS.

On 11 Mar 2025, at 4:19 pm, ZC @.***> wrote:



Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795https://github.com/go-gorm/gorm/issues/6795

— Reply to this email directly, view it on GitHubhttps://github.com/go-gorm/gorm/issues/7384#issuecomment-2712800992, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI. You are receiving this because you authored the thread.Message ID: @.***>

[ivila]ivila left a comment (go-gorm/gorm#7384)https://github.com/go-gorm/gorm/issues/7384#issuecomment-2712800992

Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795https://github.com/go-gorm/gorm/issues/6795

— Reply to this email directly, view it on GitHubhttps://github.com/go-gorm/gorm/issues/7384#issuecomment-2712800992, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI. You are receiving this because you authored the thread.Message ID: @.***>

Comment From: ivila

Yes the test is updating the rows to the same values. I didn’t find the closed issue when searching prior to raising this one.

If you run the SQL statements generated via MySQL workbench it reports that both statements affect 3 rows. Having done some more reading I suspect that the Workbench is using the CLIENT_FOUND_ROWS setting, and that Gorm is not. https://dev.mysql.com/doc/c-api/8.4/en/mysql-affected-rows.html

Given the different results between Gorm and MySQL Workbench, it would be helpful if the documentation about RowsAffected and MySQL mentioned the setting CLIENT_FOUND_ROWS.

On 11 Mar 2025, at 4:19 pm, ZC @.***> wrote:



Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795<#6795>

— Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI. You are receiving this because you authored the thread.Message ID: @.***>

[ivila]ivila left a comment (go-gorm/gorm#7384)<#7384 (comment)>

Are you trying to update the birthday field with the same value it already has?

I think you should expect 3 rows found, not 3 rows affected.

The MySQL Server (and I believe newer versions of PostgreSQL as well) might omit your second update if it determines that no actual changes are needed.

You might just check: #6795<#6795>

— Reply to this email directly, view it on GitHub<#7384 (comment)>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADD24XF34WSMRFUIRJJQPZ32TZ56TAVCNFSM6AAAAABYQ5JX7CVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDOMJSHAYDAOJZGI. You are receiving this because you authored the thread.Message ID: @.***>

@jinzhu @a631807682 What do you think of the suggestion? Just add a comment section to inform developers about this and some workarounds. I've seen people ask similar questions repeatedly.

For reference: CLIENT_FOUND_ROWS

Comment From: a631807682

RowsAffected in gorm is the same as RowsAffected in database/sql. What comments do we need to add? should we add the code comments of database/sql to gorm?

https://pkg.go.dev/database/sql#Result

Comment From: ivila

RowsAffected in gorm is the same as RowsAffected in database/sql. What comments do we need to add? should we add the code comments of database/sql to gorm?

https://pkg.go.dev/database/sql#Result

No, I think we could add it to FAQ (as developers keep asking this repeatedly), how about this.

Q: Why do I get unexpected results from RowsAffected? A: Some developers may notice discrepancies between RowsAffected in Golang and the results displayed in MySQL Workbench. This is because, in Golang, RowsAffected refers to the number of rows that were actually modified, whereas MySQL Workbench displays the number of rows found, not necessarily changed. For example, if you update two rows but set their values to what they already are, MySQL will report: "Rows matched: 2 Changed: 0 Warnings: 0", In MySQL Workbench, this may be displayed as "2 rows changed" (referring to matched rows), but in Golang, RowsAffected will correctly return 0, as no actual modifications were made. For more details, you may refer to #6795, #7384 and CLIENT_FOUND_ROWS.

Sorry, my bad, I forgot to mention the comment section is in documentation, not in the code comments.

Comment From: a631807682

In my opinion, it does not belong to the question that needs to be explained in the document. I think it is quickest to search through the issue and look at the comments. You can also move the question to https://github.com/go-gorm/gorm/discussions/categories/q-a