Why is the query result mapped with a null value for COUNT(*)?

Question

interface TaskReviewRepository : CoroutineCrudRepository<TaskReview, Long> {
  @Query(
    """
    SELECT task_id, AVG(rating) as avgRating, COUNT(*) as reviewsCount
    FROM task_review
    WHERE task_id IN (:taskIds)
    GROUP BY task_id
  """
  )
  fun ratingSummaryWhereTaskIdIn(taskIds: Set<Long>): Flow<TaskReviewService.RatingSummaryWithTaskId>
}

data class RatingSummaryWithTaskId(
  val taskId: Long,
  val avgRating: Double?,
  val reviewsCount: Long,
)

When I run this query in the db by hand

SELECT task_id, AVG(rating) as avgRating, COUNT(*) as reviewsCount
FROM task_review
WHERE task_id IN (1)
GROUP BY task_id

the result is:

task_id  | avgRating  | reviewsCount
      1  |   5.00000  |            1

But when using the repository from a service I get:

2021-06-30 09:22:33.240 DEBUG 7529 --- [     parallel-3] io.r2dbc.postgresql.PARAM                : Bind parameter [0] to: 1
2021-06-30 09:22:33.246 DEBUG 7529 --- [     parallel-3] io.r2dbc.postgresql.QUERY                : Executing query: 
    SELECT task_id, AVG(rating) as avgRating, COUNT(*) as reviewsCount
    FROM task_review
    WHERE task_id IN ($1)
    GROUP BY task_id
// ...
org.springframework.data.mapping.model.MappingInstantiationException: Failed to instantiate com.example.tasks.TaskReviewService$RatingSummaryWithTaskId using constructor fun <init>(kotlin.Long, kotlin.Double?, kotlin.Long): com.example.tasks.TaskReviewService.RatingSummaryWithTaskId with arguments 1,null,null
    at org.springframework.data.mapping.model.ClassGeneratingEntityInstantiator$EntityInstantiatorAdapter.createInstance(ClassGeneratingEntityInstantiator.java:246) ~[spring-data-commons-2.5.1.jar:2.5.1]
    Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
// ...
Caused by: java.lang.IllegalArgumentException: Parameter reviewsCount must not be null!

Why is is the mapper using null as third argument? (com.example.tasks.TaskReviewService.RatingSummaryWithTaskId with arguments 1,null,null)

My guess was that the repository itself uses TaskReview and not RatingSummaryWithTaskId as a generic, but from what I understand it is possible to define arbitrary return types for the mapping and it works in other repositories.

---

I am using spring-boot 2.5.1 with spring-boot-starter-data-r2dbc and io.r2dbc:r2dbc-postgresql. Other repositories work as expected.

Answer 1

Turns out using snake case instead of camel case works fine:

SELECT task_id, AVG(rating) as avg_rating, COUNT(*) as reviews_count
FROM task_review
WHERE task_id IN (:taskIds)
GROUP BY task_id

Because it is was unexpected that using camel case in aliases for selected columns is not supported, I raised an issue here to follow up with the nice spring people: https://github.com/spring-projects/spring-data-r2dbc/issues/616