Explicit Query with Returning clause throwing "A result was returned when none was expected" error in Kotlin

[scprek]

First off, it appears that after a CrudRepository call the fields annotated with @AutoPopulated, @dateUpdate, etc of the entity class are modified in-place so you can access them after the DB operations. This issue is simply using Returning id as an example of something that once worked and no longer does. If this is working as expected, that is fine, just want to confirm.

Expected Behavior

Upsert

Have a repo I'm updating from Micronaut 2.4.4 to 2.5.11 and the following was working

upsertTestReturning below should return the @Autopopulated ID but required @Transactional. It worked for both inserts and updates (new and existing paths of the upsert)

Without @Tansactional you'd get this, which is documented in previous issues

"Internal Server Error: Error executing SQL Query: ERROR: cannot execute INSERT in a read-only transaction"

java: JDK 11 Micronaut: v2.4.4 Micronaut-data: 2.3.1 jdbc-hikari: 3.4.0

Update

As for explicit updates with returning, that did not use to work either and not sure if it should or not?

Actual Behaviour

Upsert

It appears that the upsert is ALWAYS being seen as an Update now because it throws the update error

 ERROR i.m.http.server.RouteExecutor - Unexpected error occurred: Error executing SQL UPDATE: A result was returned when none was expected.

Update

ERROR i.m.http.server.RouteExecutor - Unexpected error occurred: Error executing SQL UPDATE: A result was returned when none was expected

Steps To Reproduce

@Repository("locations-db")
@JdbcRepository(dialect = Dialect.POSTGRES)
abstract class LocationRepository: CrudRepository<LocationModel, UUID> {
    @Query(
        """
        INSERT INTO locations (location_id, external_location_id, partner)
        VALUES( :locationId, :externalLocationId, :partner)
        ON CONFLICT (location_id) WHERE deleted_at IS NULL
        DO
        UPDATE SET external_location_id = EXCLUDED.external_location_id, partner = EXCLUDED.partner
        RETURNING id
    """)
    abstract fun upsertTestReturning(locationId: Long, externalLocationId: String, partner: PartnerType): UUID
    
    
    @Query(
        """
        UPDATE fleet_locations
        SET external_location_id = :externalLocationId, partner = :partner
        WHERE location_id = :locationId
        RETURNING id
    """)
    abstract fun updateTestReturning(locationId: Long, externalLocationId: String, partner: PartnerType): UUID
}

Environment Information

java: JDK 11 kotlin: 1.6 Micronaut: v3.3.2-SNAPSHOT Micronaut-data: 3.2.2 jdbc-hikari: 4.1.1

Example Application

https://github.com/scprek/micronaut-data-return-clause

Version

v3.3.2-SNAPSHOT

[dstepanov]

Not sure what do you mean by the first sentence. It looks like Returning id is not supported yet, execute update method always returns the number of rows updated, we would introduce something that can be used together with the support for executing functions.

You can probably pass the entity and map the parameters from the https://github.com/micronaut-projects/micronaut-data/blob/e502bf1302bee80e0d9f493590dbe48d800b0a25/doc-examples/example-java/src/main/java/example/BookRepository.java#L78

[scprek]

Ok I think returning in general is not supported in insert or update. My main point is return on insert statements or at least the upsert query I used to have worked.

My first sentence was if you switch to using '@Autopopulate' from '@GeneratedValue' the need for returning is not needed. This is because the id gets filled into the object(entity) passed into the repository call and you can reference it after.

So I wasn't sure if not supporting returning was intentionally or if we should leave this issue open