Clarify that `@Modifying` should be omitted for `DELETE FROM ... RETURNING ...` @Query's

[nt-gt]

Hi,

I was creating a repository method a la:

    // PostgreSQL specific (due to "FOR UPDATE SKIP LOCKED")
    @Modifying  // <-- problematic line
    @Query("DELETE FROM unmapped_event_queue WHERE event_id = ("
            + "  SELECT event_id FROM unmapped_event_queue FOR UPDATE SKIP LOCKED LIMIT 1"
            + ") RETURNING event_id")
    Mono<UUID> pollUnmappedEventID();

This fails with a "class cast exception" stating that an Integer cannot be converted to a UUID. I originally added the @Modifying because it says in its javadoc that it "Indicates a query method should be considering a modifying query as it that changes the way it needs to be excuted". I thought that matched my case but in reality it works if I omit @Modifying in this case.

I am guessing that @Modifying should have its documentation updated to state that you should use it only for modifying queries that do not rely on RETURNING.

[mp911de]

The documentation actually says:

The result of these queries can be consumed as affected row count

@Modifying queries are executed/consumed through Result.getRowsUpdated() and not Result.map(…). Using RETURNING … in combination with Mono<UUID> doesn't fall into that category.

See also the reference documentation for further details: https://docs.spring.io/spring-data/r2dbc/docs/current/reference/html/#r2dbc.repositories.modifying

We can certainly improve the documentation to explain the impact of using this annotation.

[nt-gt]

Indeed that sentence is present in the annotation documentation as the last one. I see how it makes sense now in hindsight with your explanation. For me, I think the following alternative would have avoided the confusion:

Indicates a query method should be considered a modifying query that returns nothing or the number of rows affected by the query.

Queries that should be annotated with a @Modifying annotation include DDL statements as well as INSERT, UPDATE, and DELETE statements without the RETURNING keyword. The result of these queries can be consumed as affected row count.

When using INSERT or DELETE with RETURNING, you should omit the @Modifying annotation.

[nt-gt]

Thanks. :)