🐞 Bug Report: Unexpected behavior with RETURNING (Postgres) clause when using R2dbcEntityTemplate with conditional UPDATE

[OTATA]

Summary

When performing a conditional UPDATE ... RETURNING using R2dbcEntityTemplate.getDatabaseClient(), the RETURNING clause returns a row even when no update is actually performed, due to the conditional WHERE clause not being satisfied. This leads to incorrect assumptions in the application logic, such as assuming an update happened when it didn't.

This issue does not occur when using a Connection obtained directly from ConnectionPool.

Version Info

Java 21
Spring Boot: 3.5.3
spring-boot-starter-data-r2dbc 3.5.3

Database: PostgreSQL 16+

Driver: io.r2dbc:r2dbc-postgresql

Connection factory: ConnectionPool (from io.r2dbc.pool)

Reproduction Steps

Here is a minimal example that demonstrates the issue:

return r2dbcEntityTemplate
    .getDatabaseClient()
    .sql("""
        UPDATE wallet
        SET balance = balance + :amount
        WHERE external_id = :externalId
          AND (balance + :amount) >= 0
        RETURNING balance
    """)
    .bind("amount", -1000L)
    .bind("externalId", someUUID)
    .map(row -> row.get("balance", Long.class))
    .one();

Problem:

If the wallet's balance is less than 1000, the WHERE condition is not satisfied.

Expected: no row should be returned (Mono.empty()).

Actual: a row is returned with the original balance, even though the row was not updated.

Working Example Using ConnectionPool

When using a raw connection from the ConnectionPool, the behavior is correct:

return connectionPool.create()
    .flatMap(conn -> conn
        .createStatement("""
            UPDATE wallet
            SET balance = balance + $1
            WHERE external_id = $2
              AND (balance + $1) >= 0
            RETURNING balance
        """)
        .bind("$1", -1000L)
        .bind("$2", someUUID)
        .execute()
        .flatMap(result -> Mono.from(result.map((row, meta) ->
            row.get("balance", Long.class)
        )))
    );

Here, if the condition is not met, no row is returned, as expected.

Additional Notes

The database behavior is correct (verified directly via psql).

The issue seems specific to the way Spring's DatabaseClient handles RETURNING rows in conjunction with conditional updates.

Expected Behavior

When using R2dbcEntityTemplate.getDatabaseClient() with a conditional UPDATE ... RETURNING, it should not return a row if the WHERE clause was not satisfied.