Incorrect RowsAffected() result after updated

[knullism]

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