Override query limit in R2dbcEntityTemplate.selectOne only if the query has no limit

[RobertHeim]

Using selectOne on a query with multiple results obviously throws a "non unique result" exception. When the query is limited to 1, it still throws:

return template.selectOne(
  Query.query(
    Criteria.where("notificationId").isEqual(notificationId)
  )
    .sort(Sort.by("serverTimestamp").ascending())
    .limit(1), // <----
  MyEntity::class.java
)
  .awaitSingleOrNull()

In the query log I see that the limit is set to 2 instead of 1.

org.springframework.dao.IncorrectResultSizeDataAccessException: Query [SELECT my_entity.* FROM my_entity WHERE my_entity.notification_id = $1 ORDER BY server_timestamp ASC LIMIT 2] returned non unique result.

This makes me think it's a bug because the limit seems to be ignored?

[mp911de]

selectOne(…) overrides unconditionally the limit of your query to 2. We guarantee with that method to catch also cases where the query would yield more than one result, see the documentation:

@throws org.springframework.dao.IncorrectResultSizeDataAccessException if more than one match found

We could make the limit override conditional and set the limit only if it wasn't already set.

[RobertHeim]

Yes, that would be the expected behavior.

[mp911de]

Do you want to submit a pull request including test cases?

[RobertHeim]

Please have a look at the PR spring-projects/spring-data-relational#1233

I implemented it for the R2dbcEntityTemplate as well as for ReactiveSelectOperationSupport.

[mp911de]

Thanks a lot. The change in ReactiveSelectOperationSupport doesn't make sense since the API exposes first and one methods. first silently takes the first result while one provides stronger guarantees in the sense to fail if a result yields more than one result.

Happy to merge the Template API part though.

[RobertHeim]

I understand the argument that the developers my use first instead of limiting the query. but I would still argue that ReactiveSelectOperationSupport should employ the same change. 1) It should be semantically identical to template's selectOne and 2) given a query with a limit 1 it is strange that it would fail:

entityTemplate.select(Person.class) //
				.matching(query(where("name").is("Walter")).limit(1)) //
				.one() //

[mp911de]

We strive to have strict API guarantees. Reacting to the query limit as in the pull request is already widening what the methods are capable of.

On the other side, the R2DBC part is more strict than e.g. the MongoDB Template API which already returns the first result without checking whether a query would yield more results.

[RobertHeim]

ok. I removed the changes to ReactiveSelectOperationSupportTest and its test and updated the PR. Thanks for the immediate feedback.