Possible coalesce problem

[arthurgregorio]

Hello guys, I'm facing a really odd issue when using coalesce.

I have an application running with spring boot 3.5.0 and kotlin 2 and there I have the following query:

@Query("select coalesce(sum(mc.budget), 0.0) from MovementClass mc where mc.costCenter = :costCenter")
fun findBudgetAllocatedByCostCenter(costCenter: CostCenter): BudgetAllocated

This is generating this query:

`select coalesce(sum(mc1_0.budget),0.0) from registration.movement_classes mc1_0 where mc1_0.id_cost_center=?``

And I'm using a projection defined like this:

interface BudgetAllocated {
    val total: BigDecimal
}

And when I run the method it gives a null pointer exception telling that:

java.lang.NullPointerException: Return value is null but must not be null
	at org.springframework.data.util.NullnessMethodInvocationValidator.returnValueIsNull(NullnessMethodInvocationValidator.java:124) ~[spring-data-commons-3.5.0.jar:3.5.0]
	at org.springframework.data.util.NullnessMethodInvocationValidator.invoke(NullnessMethodInvocationValidator.java:97) ~[spring-data-commons-3.5.0.jar:3.5.0]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.7.jar:6.2.7]
	at org.springframework.data.projection.ProxyProjectionFactory$TargetAwareMethodInterceptor.invoke(ProxyProjectionFactory.java:253) ~[spring-data-commons-3.5.0.jar:3.5.0]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.2.7.jar:6.2.7]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223) ~[spring-aop-6.2.7.jar:6.2.7]
	at jdk.proxy3/jdk.proxy3.$Proxy224.getTotal(Unknown Source) ~[na:na]
	at br.com.webbudget.domain.validators.registration.ExpenseBudgetLimitValidator.validateSaved(ExpenseBudgetLimitValidator.kt:41) ~[main/:na]

My question is: if I get the generated query and run it directly in the database the returning value will be 0 and not null because of the coalesce, why this happens when I run it from spring data?

I'm really not sure if this is a question for spring team or Hibernate team since if I go back to spring boot 3.4.2 for example (the version I was using before) the method run's without NPE but the value is set to null in my result projection 🤔

Any clues on what could be happening?

[mp911de]

I've tried to reproduce your queries and I get with both, Boot 3.4.2 and Hibernate 6.6.5 and Boot 3.5.0 and Hibernate 6.6.15 the same behavior (using Java and H2).

Have you noticed that your query doesn't define an alias for the result (coalesce(sum(mc.budget), 0.0) vs coalesce(sum(mc.budget), 0.0) as total)?

Regardless of versions, if I don't specify a column alias, the projection result is across all versions null for BudgetAllocated.total.

[arthurgregorio]

omg, completely forgot the fact that I need to name de field, my bad!

Thanks for the answer @mp911de! I will close the issue.