UPDATE ... RETURNING ignores CLIENT_FOUND_ROWS: matched rows whose values do not change are not returned or reported in rowcount

When a client connects with CLIENT_FOUND_ROWS, a plain UPDATE reports
the number of rows matched by the WHERE clause, including rows whose new
values equal their old values. UPDATE ... RETURNING (MDEV-5092) does not
honor this flag: rows that match but are not changed are left out of the
result set. The matched-row count is also not sent to the client, as the
result set is terminated with an EOF/OK packet carrying 0 affected rows.

As a result, a client that relies on CLIENT_FOUND_ROWS cannot get either
the complete set of matched rows or the matched-row count from
UPDATE ... RETURNING; the client-side row count reflects only the rows
that were returned.

How to repeat

CLIENT_FOUND_ROWS can't be set from the mariadb command line client,
so this uses MariaDB Connector/Python 1.1.14:

import mariadb

from mariadb.constants import CLIENT

conn = mariadb.connect(

    host="localhost",

    user="root",

    database="test",

    client_flag=CLIENT.FOUND_ROWS,

    autocommit=True,

)

cur = conn.cursor()

print("server version:", conn.get_server_version())

def setup():

    cur.execute(

        "CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, data VARCHAR(10))"

    )

    cur.execute("INSERT INTO t1 VALUES (1, 'a'), (2, 'a'), (3, 'b')")

# without RETURNING, CLIENT_FOUND_ROWS is honored: matched rows are reported

setup()

cur.execute("UPDATE t1 SET data = 'b' WHERE id IN (2, 3)")

print("UPDATE, 2 matched, 1 changed:           rowcount", cur.rowcount)

cur.execute("UPDATE t1 SET data = 'b' WHERE id IN (2, 3)")

print("UPDATE, 2 matched, 0 changed:           rowcount", cur.rowcount)

# with RETURNING, only rows whose values changed are returned and counted

setup()

cur.execute("UPDATE t1 SET data = 'b' WHERE id IN (2, 3) RETURNING id")

rows = cur.fetchall()

print(

    "UPDATE RETURNING, 2 matched, 1 changed: rowcount",

    cur.rowcount,

    "rows",

    rows,

)

cur.execute("UPDATE t1 SET data = 'b' WHERE id IN (2, 3) RETURNING id")

rows = cur.fetchall()

print(

    "UPDATE RETURNING, 2 matched, 0 changed: rowcount",

    cur.rowcount,

    "rows",

    rows,

)

conn.close()

Output against 13.0.1:

server version: (13, 0, 1)

UPDATE, 2 matched, 1 changed:           rowcount 2

UPDATE, 2 matched, 0 changed:           rowcount 2

UPDATE RETURNING, 2 matched, 1 changed: rowcount 1 rows [(2,)]

UPDATE RETURNING, 2 matched, 0 changed: rowcount 0 rows []

Expected, given CLIENT_FOUND_ROWS:

UPDATE RETURNING, 2 matched, 1 changed: rowcount 2 rows [(2,), (3,)]

UPDATE RETURNING, 2 matched, 0 changed: rowcount 2 rows [(2,), (3,)]

The same behavior is seen with mysqlclient and PyMySQL, so it isn't specific
to the connector. At the protocol level, with CLIENT_DEPRECATE_EOF
negotiated, a plain UPDATE that matches 2 rows and changes none ends with an
OK packet containing affected_rows=2 and the info string
Rows matched: 2 Changed: 0 Warnings: 0. The same statement with
RETURNING sends 0 rows, followed by an OK packet with affected_rows=0
and no info string.

Cause

(note this section is LLM model output; I did not independently confirm the C code details)

In Sql_cmd_update::update_single_table() (sql/sql_update.cc, line
numbers from main at 6f18dc2d11), the row is counted in found before the
old and new records are compared, but it is only sent to the RETURNING result
inside the need_update branch, after ha_update_row() succeeds:

      found++;                                              // line 1022

      record_was_same= false;

      need_update= !can_compare_record || compare_record(table);

      if (need_update)                                      // line 1027

      {

        ...

          error= table->file->ha_update_row(table->record[1],

                                            table->record[0]);

        if (likely(!error) && thd->lex->has_returning() &&  // line 1087

            returning_result->send_data(thd->lex->returning()->returning_list) < 0)

A row is therefore left out of the result set when compare_record() finds
no difference, and also when the engine returns HA_ERR_RECORD_IS_THE_SAME,
because error is non-zero at the point of the check.

At the end of the statement, the non-RETURNING path reports
CLIENT_FOUND_ROWS ? found : updated via my_ok(), while the RETURNING
path calls returning_result->send_eof() (line 1363), so the matched count
never reaches the client.

The same code is present on the 13.0 and 13.1 branches.

Suggested behavior

When CLIENT_FOUND_ROWS is set, UPDATE ... RETURNING should return
every row matched by the WHERE clause, including rows whose values are
unchanged, so that the result set is consistent with the affected-rows count
a plain UPDATE reports under the same flag. The "rowcount" value should also mirror that of a plain UPDATE.

Impact

SQLAlchemy normalizes the behavior of UPDATE ... RETURNING across all
the databases it supports. On other databases such as PostgreSQL and SQLite,
UPDATE ... RETURNING always returns every row matched by the WHERE clause,
whether or not its values changed. SQLAlchemy always connects to MySQL and
MariaDB with CLIENT_FOUND_ROWS so that the affected-rows count of a plain
UPDATE likewise reflects matched rows. It uses this count to confirm that
an UPDATE located the rows it targeted, and reports it to applications as
rowcount.

With the current behavior, UPDATE ... RETURNING on MariaDB can't be made
consistent with other databases: an UPDATE that sets a column to its current
value returns no rows, which is indistinguishable from the row not existing.