Incorrect RowsAffected() result after updated

[5ylar]

Describe the bug RowsAffected() returned incorrect value.

To Reproduce

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"

    _ "github.com/microsoft/go-mssqldb"
)

func main() {
    server := "???"
    user := "???"
    password := "???"
    port := 1433

    connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d", server, user, password, port)

    conn, err := sql.Open("mssql", connString)

    if err != nil {
        panic(err)
    }

    defer conn.Close()

    ctx := context.Background()

    countrow := conn.QueryRow("select count(id) from objects where id = 1;")

    if err := countrow.Err(); err != nil {
        panic(err)
    }

    var count int64

    err = countrow.Scan(&count)

    if err != nil {
        panic(err)
    }

    log.Println("count", count) // output: 1

    result, err := conn.ExecContext(ctx, "update objects set value = 'test' where id = 1;")

    if err != nil {
        panic(err)
    }

    rowsAffected, _ := result.RowsAffected()

    log.Println("rowsAffected", rowsAffected) // output: 3
}

Output

2024/07/09 13:28:15 count 1
2024/07/09 13:28:15 rowsAffected 3

Expected behavior Following the reproduce code, it should be 1 that returned from result.RowsAffected().

Further technical details

SQL Server version: SQL Server 2017 go-mssqldb: v1.7.2

[shueybubbles]

thx for opening an issue! What was the last version of the driver you used where it returned the correct value?

[shueybubbles]

Can you add a logger to your repro and add the traces here? You should see traces like this:

e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.5018429 -0500 CDT m=+1.289748201 [token.go:1049]: got DONE or DONEPROC status=16
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.5018429 -0500 CDT m=+1.289748201 [token.go:1061]: (Rows affected: 1)
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.5018429 -0500 CDT m=+1.289748201 [mssql.go:528]: update #foo set val = N'test' where bar = 1
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.6575046 -0500 CDT m=+1.445412201 [token.go:993]: got token tokenDone
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.658402 -0500 CDT m=+1.446309601 [token.go:1049]: got DONE or DONEPROC status=16
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.658402 -0500 CDT m=+1.446309601 [token.go:1061]: (Rows affected: 1)
    e:\git\go-mssqldb\tds_test.go:387: 2024-07-09 17:22:09.7354126 -0500 CDT m=+1.523321301 [token.go:993]: got token tokenEnvChange

I can't reproduce the issue when I modify TestAffectedRows. This version of the test passes:

func TestAffectedRows(t *testing.T) {
    conn, logger := open(t)
    defer conn.Close()
    defer logger.StopLogging()

    tx, err := conn.Begin()
    if err != nil {
        t.Fatal("Begin tran failed", err)
    }
    defer tx.Rollback()

    res, err := tx.Exec("create table #foo (bar int, val nvarchar(10) null)")
    if err != nil {
        t.Fatal("create table failed")
    }
    n, err := res.RowsAffected()
    if err != nil {
        t.Fatal("rows affected failed")
    }
    if n != 0 {
        t.Error("Expected 0 rows affected, got ", n)
    }

    res, err = tx.Exec("insert into #foo (bar) values (1)")
    if err != nil {
        t.Fatal("insert failed")
    }
    n, err = res.RowsAffected()
    if err != nil {
        t.Fatal("rows affected failed")
    }
    if n != 1 {
        t.Error("Expected 1 row affected, got ", n)
    }

    res, err = tx.Exec("insert into #foo (bar) values (@p1)", 2)
    if err != nil {
        t.Fatal("insert failed", err)
    }
    n, err = res.RowsAffected()
    if err != nil {
        t.Fatal("rows affected failed")
    }
    if n != 1 {
        t.Error("Expected 1 row affected, got ", n)
    }
    // Regression test for https://github.com/microsoft/go-mssqldb/issues/204
    countrow := tx.QueryRow("select count(bar) from #foo where bar = 1;")
    if err := countrow.Err(); err != nil {
        t.Fatal("QueryRow failed ", err)
    }
    var count int64
    err = countrow.Scan(&count)
    if err != nil || count != 1 {
        t.Fatal("Unexpected error or count from QueryRow:", err, count)
    }
    res, err = tx.Exec("update #foo set val = N'test' where bar = 1")
    if err != nil {
        t.Fatal("update failed", err)
    }
    n, err = res.RowsAffected()
    if err != nil {
        t.Fatal("rows affected failed after update")
    }
    if n != 1 {
        t.Error("Expected 1 row affected for update, got ", n)
    }

}

[brijesh-amin]

Not able to re produce

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Test1234@" -p 1433:1433 mcr.microsoft.com/mssql/server:2017-latest