spring-data-jpa icon indicating copy to clipboard operation
spring-data-jpa copied to clipboard

Published 20 hours ago • verified publisher icon spring-projects

Passing null as stored procedure argument is broken in v2.7.0 (works in v2.6.8).

Open denis111 opened this issue 2 years ago • 7 comments

With spring.jpa.properties.hibernate.proc.param_null_passing=true we can pass null as stored procedure as parameter without any problem with Spring Boot 2.6.8 but upgrading to v2.7.0 throws exception with the same Hibernate version. I've tried only with PostgreSQL and UUID as procedure parameter but I suppose it could be for any type of DB and parameters. The exception is: Caused by: org.springframework.dao.InvalidDataAccessApiUsageException: Bind value [org.hibernate.jpa.TypedParameterValue@5b1420f9] was not of specified type [class java.util.UUID; nested exception is java.lang.IllegalArgumentException: Bind value [org.hibernate.jpa.TypedParameterValue@5b1420f9] was not of specified type [class java.util.UUID

Example:

public interface TestModelRepository extends JpaRepository<TestModel, Long> {
  @Procedure("countByUuid")
  void countUuid(UUID one);
}

then just call testModelRepository.countUuid(null). I've prepared a sample project to reproduce the problem, just change Spring Boot version to 2.6.8 and it works fine: https://github.com/denis111/spring-data-jpa270test

denis111 avatar May 23 '22 16:05 denis111

I ran your code against Spring Boot 3.0.0-M3 and it failed the same. I also ran it against Spring Boot 3.0.0-SNAPSHOT and it worked. I'd like to pinpoint the patch so I can assess whether it's a candidate for backporting.

Can also confirm the issue still resides in Spring Boot 2.7.2-SNAPSHOT, so a patch that wasn't backported.

gregturn avatar Jul 14 '22 19:07 gregturn

This might explain our current issue using an @Procedure annotation with a number of parameters where one is nullable, and we suddenly get a Bind value issue. Using V2.7.2

SchlauFuchs avatar Jul 29 '22 01:07 SchlauFuchs

The same problem occurs with Oracle database. Calling a procedure with a nullable parameter leads to the following error:

java.lang.IllegalArgumentException: Bind value [org.hibernate.jpa.TypedParameterValue@42dae24d] was not of specified type [class java.lang.String

It works with all versions < 2.6.10.

Is a correction planned or are nullable parameters no longer supported?

stylepatrick avatar Aug 16 '22 12:08 stylepatrick

is this still broken with 2.7.3? we downgraded to 2.6.8, waiting for the fix.

SchlauFuchs avatar Sep 13 '22 23:09 SchlauFuchs

is this still broken with 2.7.3? we downgraded to 2.6.8, waiting for the fix.

It should fixed in 2.7.3, but it's not released yet. @gregturn

quaff avatar Sep 14 '22 01:09 quaff

we are using 2.7.3 and this is still an issue. did you mean it will be fixed in 2.7.4?

apatel0708 avatar Sep 14 '22 15:09 apatel0708

I've just tried 2.7.4 and the issue is still there, I have updated my test project.

denis111 avatar Sep 23 '22 06:09 denis111

v2.7.5, the issue is still there, updated my test project.

denis111 avatar Oct 20 '22 15:10 denis111

@denis111 It should be fixed at hibernate side, I have created https://github.com/hibernate/hibernate-orm/pull/5438

quaff avatar Oct 21 '22 09:10 quaff

Any idea when will the issue be fixed?

kashann avatar Nov 25 '22 08:11 kashann

The issue is still present un v2.7.6 but it's solved in v3.0.0. I've updated my test project and created the branch spring-boot-3.

denis111 avatar Nov 25 '22 08:11 denis111

The issue was clearly mended when Spring Data JPA moved to Hibernate 6 in Spring Boot 3.0.0-M4 (Spring Data 2022.0.0-M5).

You can see ALL the changes that happened in Spring Data JPA between 3.0.0-M4 and 3.0.0-M5, and nothing stands out except upgrading to Hibernate 6 and properly handling nulls for LIKEs. The former will never be backported to 2.7.x and the latter already has been in 2.7.6.

gregturn avatar Dec 15 '22 17:12 gregturn

Thank you for that analysis, but can you do one for what happened between 2.6.8 and 2.7.0? It was working and then not anymore.

On Fri, 16 Dec 2022 at 06:18, Greg L. Turnquist @.***> wrote:

The issue was clearly mended when Spring Data JPA moved to Hibernate 6 in Spring Boot 3.0.0-M4 (Spring Data 2022.0.0-M5).

You can see ALL the changes that happened in Spring Data JPA https://github.com/spring-projects/spring-data-jpa/compare/3.0.0-M4...3.0.0-M5 between 3.0.0-M4 and 3.0.0-M5, and nothing stands out except upgrading to Hibernate 6 and properly handling nulls for LIKEs. The former will never be backported to 2.7.x and the latter already has been in 2.7.6.

— Reply to this email directly, view it on GitHub https://github.com/spring-projects/spring-data-jpa/issues/2544#issuecomment-1353432200, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAHK5HNKSI5OAWG6ADOHJHTWNNHGBANCNFSM5WWOS37Q . You are receiving this because you are subscribed to this thread.Message ID: @.***>

SchlauFuchs avatar Dec 16 '22 00:12 SchlauFuchs

I have created a test case inside Spring Data JPA 2.7.x that replicates the error message first reported at the top. I want to isolate if this is specific to UUID or is something else.

gregturn avatar Dec 19 '22 16:12 gregturn

Okay, changing the reproducer's type to uuid to varchar/String results in a slightly different error. But it has the same pattern.

gregturn avatar Dec 19 '22 16:12 gregturn

Looks like our TypedParameterValue solution to support Hibernate is the culprit here. If I look for that type when setting stored procedure parameters, and dereference it, it works. I believe that was introduced in 2.7, which would explain why it still works in 2.6.

gregturn avatar Dec 19 '22 17:12 gregturn

Thanks @quaff for submitting the PR (https://github.com/spring-projects/spring-data-jpa/issues/2544#issuecomment-1286689469). Until they accept that PR, I can conditionally de-reference the TypedParameterValue on our end for stored procedure calls.

gregturn avatar Dec 19 '22 20:12 gregturn

Issue resolved. Check it out in 2.7.7-SNAPSHOT.

gregturn avatar Dec 19 '22 20:12 gregturn

Merged forward to 3.0.x and main.

While TypedParameterValue is handled properly in Hibernate 6.1 for general parameter types, it still requires dereferencing for temporal types.

CC @quaff

gregturn avatar Dec 19 '22 21:12 gregturn

@gregturn it looks like it didn't get to Spring Boot 2.7.7 (I've updated my test project), should we wait for 2.7.8 or it will never be included in 2.7.x branch?

denis111 avatar Dec 23 '22 09:12 denis111

@gregturn We still have this issue. Here a stack trace:

org.springframework.dao.InvalidDataAccessApiUsageException: Bind value [org.hibernate.jpa.TypedParameterValue@2cff35e7] was not of specified type [class java.lang.String; nested exception is java.lang.IllegalArgumentException: Bind value [org.hibernate.jpa.TypedParameterValue@2cff35e7] was not of specified type [class java.lang.String
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:374)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:235)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:99)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)
	at jdk.proxy3/jdk.proxy3.$Proxy279.assignJob(Unknown Source)
...
     Caused by: java.lang.IllegalArgumentException: Bind value [org.hibernate.jpa.TypedParameterValue@2cff35e7] was not of specified type [class java.lang.String
     	at org.hibernate.procedure.internal.ParameterBindImpl.internalSetValue(ParameterBindImpl.java:94)
     	at org.hibernate.procedure.internal.ParameterBindImpl.setBindValue(ParameterBindImpl.java:64)
     	at org.hibernate.procedure.internal.ProcedureCallImpl.setParameter(ProcedureCallImpl.java:817)
     	at org.hibernate.procedure.internal.ProcedureCallImpl.setParameter(ProcedureCallImpl.java:70)
     	at org.springframework.data.jpa.repository.query.QueryParameterSetter$BindableQuery.setParameter(QueryParameterSetter.java:326)
     	at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$4(QueryParameterSetter.java:117)
     	at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$1.execute(QueryParameterSetter.java:140)
     	at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:117)
     	at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:82)
     	at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:74)
     	at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.doCreateQuery(StoredProcedureJpaQuery.java:102)
     	at org.springframework.data.jpa.repository.query.StoredProcedureJpaQuery.createQuery(StoredProcedureJpaQuery.java:89)
     	at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:318)
     	at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
     	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:156)
     	at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:144)
     	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
     	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
     	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:160)
     	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139)
     	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     	at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:81)
     	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
     	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
     	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
     	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)

SchlauFuchs avatar Jan 20 '23 01:01 SchlauFuchs

Seems like there is a dependency mismatch. Using springboot-starter-data-jpa 2.7.8 it works, and it has the transient dependency to spring-data-jpa 2.7.7.

SchlauFuchs avatar Jan 20 '23 03:01 SchlauFuchs