Dolt returns wrong number of affected rows for UPDATE ... JOIN with clientFoundRows=true

[arvidfm]

When the clientFoundRows=true option is set, the database should return the number of found rows during an UPDATE, regardless of whether the rows were actually changed or not. However, with an UPDATE ... JOIN like the following, Dolt seems to always return the number of changed lines instead:

-- sets entity_test.value to 10 for id=1 and 20 for id=2
UPDATE entity_test
    JOIN (VALUES ROW(1, 10), ROW(2,20)) joined (id, value)
    ON joined.id = entity_test.id
SET entity_test.value = joined.value;

We rely on clientFoundRows=true as an optimisation to avoid having to run an extra query to check whether an ID exists when updating items - it allows us to just run an UPDATE, and if the number of found rows differs from the number of items that the user tried to update, we do a rollback and return a 404.

Here is a complete example that prints 0 affected rows with Dolt, but 2 with Percona Server 8.3:

package main

import (
"database/sql"
	"fmt"

	"github.com/go-sql-driver/mysql"
)

const (
	dbAddr = "127.0.0.1:3306"
	dbUser = "root"
	dbPass = "toor"
	dbName = "dolt"
)

func main() {
	cfg := mysql.NewConfig()
	cfg.Net = "tcp"
	cfg.Addr = dbAddr
	cfg.User = dbUser
	cfg.Passwd = dbPass
	cfg.DBName = dbName
	cfg.ParseTime = true
	cfg.MultiStatements = true
	cfg.ClientFoundRows = true

	conn := must(sql.Open("mysql", cfg.FormatDSN()))
	must(conn.Exec(`
DROP TABLE IF EXISTS entity_test;
CREATE TABLE entity_test(
    id INT PRIMARY KEY,
    value INT
);
`))

	must(conn.Exec(`
INSERT INTO entity_test (id, value)
VALUES (?,?), (?,?), (?,?);
`, 1, 10, 2, 20, 3, 30))

	res := must(conn.Exec(`
UPDATE entity_test
    JOIN (VALUES ROW(1, 10), ROW(2,20)) joined (id, value)
    ON joined.id = entity_test.id
SET entity_test.value = joined.value;
`))
	fmt.Println(res.RowsAffected())
}

func must[T any](t T, err error) T {
	if err != nil {
		panic(err)
	}
	return t
}

[timsehn]

@max-hoffman is gonna grab this one.

[fulghum]

This fix has been released in Dolt 1.39.4. Thanks for reporting this one! 🙏