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

denis111 · 2022-05-23T16:48:38Z

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

The text was updated successfully, but these errors were encountered:

Properly handle null values with like or contains. Null values are wrapped with a special handler when interacting with Hibernate. However, this becomes an issue for queries when LIKE or CONTAINS are applied. In this situation, the null needs to be condensed into an empty string and any wildcards can then be applied with expected results. Closes #2548, #2570. Supercedes: #2585. Related: #2461, #2544#

Null values are wrapped with a special handler when interacting with Hibernate. However, this becomes an issue for queries when LIKE or CONTAINS are applied. In this situation, the null needs to be condensed into an empty string and any wildcards can then be applied with expected results. Closes #2548, #2570. Supercedes: #2585. Related: #2461, #2544#

gregturn · 2022-07-14T19:31:07Z

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.

SchlauFuchs · 2022-07-29T01:33:28Z

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

stylepatrick · 2022-08-16T12:58:38Z

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?

SchlauFuchs · 2022-09-13T23:53:37Z

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

quaff · 2022-09-14T01:36:18Z

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

apatel0708 · 2022-09-14T15:31:15Z

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

denis111 · 2022-09-23T06:26:55Z

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

Null values are wrapped with a special handler when interacting with Hibernate. However, this becomes an issue for queries when LIKE or CONTAINS are applied. In this situation, the null needs to be condensed into an empty string and any wildcards can then be applied with expected results. Closes #2548, #2570. Supercedes: #2585. Related: #2461, #2544#

denis111 · 2022-10-20T15:28:02Z

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

quaff · 2022-10-21T09:17:59Z

@denis111 It should be fixed at hibernate side, I have created hibernate/hibernate-orm#5438

Resolves: #2653. Related: #2548, #2683, #2655, #2461, possibly #2544

kashann · 2022-11-25T08:07:30Z

Any idea when will the issue be fixed?

denis111 · 2022-11-25T08:49:09Z

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.

gregturn · 2022-12-15T17:18:14Z

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.

SchlauFuchs · 2022-12-16T00:57:44Z

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 <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 <#2544 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAHK5HNKSI5OAWG6ADOHJHTWNNHGBANCNFSM5WWOS37Q> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

gregturn · 2022-12-19T16:32:22Z

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 · 2022-12-19T16:37:08Z

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

gregturn · 2022-12-19T17:07:59Z

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 · 2022-12-19T20:09:27Z

Thanks @quaff for submitting the PR (#2544 (comment)). Until they accept that PR, I can conditionally de-reference the TypedParameterValue on our end for stored procedure calls.

Hibernate supports TypedParameterValue in queries, but not stored procedures. Until hibernate/hibernate-orm#5438 is adopted, we have to dereference such parameters on our end first. Closes #2544.

gregturn · 2022-12-19T20:51:07Z

Issue resolved. Check it out in 2.7.7-SNAPSHOT.

Hibernate 6.1 properly handles TypeParameterValue for general parameters, but not for temporal ones. So we must dereference in those scenarios. Related: #2544, hibernate/hibernate-orm#5438

gregturn · 2022-12-19T21:50:49Z

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

denis111 · 2022-12-23T09:32:16Z

@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?

SchlauFuchs · 2023-01-20T01:24:47Z

@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 · 2023-01-20T03:16:34Z

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.

spring-projects-issues added the status: waiting-for-triage An issue we've not yet triaged label May 23, 2022

jonhakr mentioned this issue May 25, 2022

Annotated query with wildcard-appended named parameter does not handle null in 2.7.0 #2548

Closed

pkernevez mentioned this issue Jun 16, 2022

Unable to do a query with a null paramater with 'contains' and 'is null' in 2.7.0 #2570

Closed

mp911de assigned gregturn Jul 25, 2022

quaff mentioned this issue Oct 21, 2022

HHH-15618 Accept TypedParameterValue for procedure hibernate/hibernate-orm#5438

Merged

gregturn added a commit that referenced this issue Nov 7, 2022

Properly handle null mixed with LIKE.

cdb32ba

Resolves: #2653. Related: #2548, #2683, #2655, #2461, possibly #2544

gregturn added a commit that referenced this issue Nov 9, 2022

Properly handle null mixed with LIKE.

34e16e3

Resolves: #2653. Related: #2548, #2683, #2655, #2461, possibly #2544

gregturn added a commit that referenced this issue Nov 9, 2022

Properly handle null mixed with LIKE.

e8c4c53

Resolves: #2653. Related: #2548, #2683, #2655, #2461, possibly #2544

gregturn added a commit that referenced this issue Dec 19, 2022

gh-2544 - Prepare branch

700a7dd

gregturn added type: regression A regression from a previous release and removed status: waiting-for-triage An issue we've not yet triaged labels Dec 19, 2022

gregturn added this to the 2.7.7 (2021.2.7) milestone Dec 19, 2022

gregturn closed this as completed Dec 19, 2022

haube mentioned this issue Mar 14, 2023

NoClassDefFoundError TypedParameterValue when using eclipselink #2857

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

denis111 commented May 23, 2022 •

edited by gregturn

Loading

gregturn commented Jul 14, 2022 •

edited

Loading

SchlauFuchs commented Jul 29, 2022 •

edited

Loading

stylepatrick commented Aug 16, 2022 •

edited

Loading

SchlauFuchs commented Sep 13, 2022

quaff commented Sep 14, 2022

apatel0708 commented Sep 14, 2022

denis111 commented Sep 23, 2022

denis111 commented Oct 20, 2022

quaff commented Oct 21, 2022

kashann commented Nov 25, 2022

denis111 commented Nov 25, 2022

gregturn commented Dec 15, 2022

SchlauFuchs commented Dec 16, 2022 via email

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022 •

edited

Loading

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

denis111 commented Dec 23, 2022

SchlauFuchs commented Jan 20, 2023 •

edited

Loading

SchlauFuchs commented Jan 20, 2023

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

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

Comments

denis111 commented May 23, 2022 • edited by gregturn Loading

gregturn commented Jul 14, 2022 • edited Loading

SchlauFuchs commented Jul 29, 2022 • edited Loading

stylepatrick commented Aug 16, 2022 • edited Loading

SchlauFuchs commented Sep 13, 2022

quaff commented Sep 14, 2022

apatel0708 commented Sep 14, 2022

denis111 commented Sep 23, 2022

denis111 commented Oct 20, 2022

quaff commented Oct 21, 2022

kashann commented Nov 25, 2022

denis111 commented Nov 25, 2022

gregturn commented Dec 15, 2022

SchlauFuchs commented Dec 16, 2022 via email

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022 • edited Loading

gregturn commented Dec 19, 2022

gregturn commented Dec 19, 2022

denis111 commented Dec 23, 2022

SchlauFuchs commented Jan 20, 2023 • edited Loading

SchlauFuchs commented Jan 20, 2023

denis111 commented May 23, 2022 •

edited by gregturn

Loading

gregturn commented Jul 14, 2022 •

edited

Loading

SchlauFuchs commented Jul 29, 2022 •

edited

Loading

stylepatrick commented Aug 16, 2022 •

edited

Loading

gregturn commented Dec 19, 2022 •

edited

Loading

SchlauFuchs commented Jan 20, 2023 •

edited

Loading