In #44466 we found that an upsert statement modifying the same row twice could lead to inconsistencies, due to the upsert operator not reading its own writes. This was fixed in #45372 by checking that the input to the upsert is distinct on PKs.
But now @florence-crl and @erikgrinaker have discovered another way to upsert the same row multiple times in a single statement: using CTEs.

For example:

CREATE TABLE a (i INT PRIMARY KEY, j INT, INDEX (j));
INSERT INTO a VALUES (0, 0);
WITH x AS (UPSERT INTO a VALUES (0, 1) RETURNING j), y AS (UPSERT INTO a VALUES (0, 2) RETURNING j) SELECT * FROM x;

Now, another execution of the last statement fails with a dupe key error (confusingly on a non-unique secondary index, but this is because the PK values are also the same, so it is a duplicate key from KV's perspective):

root@127.0.0.1:26257/defaultdb> WITH x AS (UPSERT INTO a VALUES (0, 1) RETURNING j), y AS (UPSERT INTO a VALUES (0, 2) RETURNING j) SELECT * FROM x;
ERROR: duplicate key value violates unique constraint "a_j_idx"
SQLSTATE: 23505
DETAIL: Key (j)=(1) already exists.
CONSTRAINT: a_j_idx

And furthermore we can see the inconsistency directly:

root@127.0.0.1:26257/defaultdb> SELECT i, j FROM a@primary;
  i | j
----+----
  0 | 2
(1 row)


Time: 2ms total (execution 2ms / network 0ms)

root@127.0.0.1:26257/defaultdb> SELECT i, j FROM a@a_j_idx;
  i | j
----+----
  0 | 1
  0 | 2
(2 rows)


Time: 2ms total (execution 1ms / network 0ms)

Jira issue: CRDB-10192