spring-data-relational icon indicating copy to clipboard operation
spring-data-relational copied to clipboard
verified publisher icon spring-projects

Query with bind parameter of type List<String[]> does no longer work with versions 2.4.1 & 2.4.2

Open fmisir opened this issue 3 years ago • 6 comments

The following query doesn't work any more with spring-data-jdbc versions 2.4.1 & 2.4.2. It works in version 2.4.0.

@Query(
        "select distinct s.id_external from shell s where s.id in (" +
                "select si.fk_shell_id from shell_identifier si " +
                "join (values :keyValueCombinations ) as t (input_key,input_value) " +
                "ON si.namespace = input_key " +
                    "AND si.identifier = input_value " +
                "group by si.fk_shell_id " +
                ")"
)
List<String> findExternalShellIdsByIdentifiers(@Param("keyValueCombinations") List<String[]> keyValueCombinations);

The invocation of the method is like this:

List<String[]> keyValueCombinations = List.of(new String[]{"key1", "value1"}, new String[]{"key2", "value2"});
findExternalShellIdsByIdentifiers(keyValueCombinations)

I tested it with PostgreSQL and H2.

PostgreSQL Version: postgres:13.6-alpine Driver-Version: 42.5.0 Error:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select s.id_external from shell s where s.id in (select si.fk_shell_id from shell_identifier si join (values ?, ?, ? ) as t (input_key,input_value) ON si.namespace = input_key AND si.identifier = input_value group by si.fk_shell_id having  )]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY

H2 Version: 2.1.214 Error:

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column count does not match; SQL statement:
select s.id_external from shell s where s.id in (select si.fk_shell_id from shell_identifier si join (values ?, ?, ? ) as t (input_key,input_value) ON si.namespace = input_key AND si.identifier = input_value group by si.fk_shell_id ) [21002-214]

The issue seams to be here: https://github.com/spring-projects/spring-data-relational/compare/2.4.0...2.4.2#diff-b8453546f8ae7cc224005f536bb3be8a0c341186f2cf38b2b34d958cc769ac4cR171

The if condition should evaluate to false for Iterables containing array objects.

fmisir avatar Sep 05 '22 13:09 fmisir

Please provide a Minimimal Reproducable Example, preferable as a Github repository. Make sure to include the database, either as an in memory database or if that is not possible using Testcontainers.

schauder avatar Sep 07 '22 11:09 schauder

If you would like us to look at this issue, please provide the requested information. If the information is not provided within the next 7 days this issue will be closed.

spring-projects-issues avatar Sep 14 '22 11:09 spring-projects-issues

@schauder here is the Minimal Reproducable Example: https://github.com/bci-oss/spring-data-jdbc-1323 Thanks!

fmisir avatar Sep 15 '22 10:09 fmisir

Any update on this issue?

It's still not possible to use spring-data-jdbc query with a "IN" containing tuple (spring-data-jdbc version 2.4.8)

The exemple provided can be simplified with simple IN criteria @Query(""" select p.lastname from person p WHERE (lastname, firstname) IN (:combinations) """ ) List<String> findLastnameWithTupleIn(@Param("combinations") List<String[]> combinations);

Error with postgresql: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select p.lastname from person p WHERE (lastname, firstname) IN (?, ?) ]; nested exception is org.postgresql.util.PSQLException: Cannot cast an instance of java.util.ArrayList to type Types.ARRAY

Errro with H2: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select p.lastname from person p WHERE (lastname, firstname) IN (?, ?) ]; Serialization failed, cause: "java.io.NotSerializableException: org.h2.jdbc.JdbcArray" [90026-214]; nested exception is org.h2.jdbc.JdbcSQLDataException: Serialization failed, cause: "java.io.NotSerializableException: org.h2.jdbc.JdbcArray" [90026-214]

The workaround is to use directly NamedParameterJdbcTemplate : namedParameterJdbcTemplate.query("select p.lastname from person p WHERE (lastname, firstname) IN (:combinations)", Map.of("combinations", combinations), myRowMapper)) But we need to implement a custom rowMapper and we can't use the auto mapping providing by spring data jdbc. That is painful for complex aggregate.

jipipi avatar Mar 22 '23 13:03 jipipi

@schauder Is there an update to this issue? The problem is still there.

Thanks for your feedback

tunacicek avatar Sep 05 '23 10:09 tunacicek

This issue is still present in Spring Data JDBC 2.4.18.

In my case in conjunction with MariaDB:

org.springframework.dao.InvalidDataAccessApiUsageException: ConnectionCallback; (conn=48692) Array type is not supported; nested exception is java.sql.SQLFeatureNotSupportedException: (conn=48692) Array type is not supported
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:96) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1577) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:344) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at org.springframework.data.jdbc.core.convert.DefaultJdbcTypeFactory.createArray(DefaultJdbcTypeFactory.java:75) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.data.jdbc.core.convert.BasicJdbcConverter.writeJdbcValue(BasicJdbcConverter.java:313) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.convertAndAddParameter(StringBasedJdbcQuery.java:181) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.bindParameters(StringBasedJdbcQuery.java:155) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.data.jdbc.repository.query.StringBasedJdbcQuery.execute(StringBasedJdbcQuery.java:136) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137) ~[spring-data-commons-2.7.18.jar:2.7.18]
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121) ~[spring-data-commons-2.7.18.jar:2.7.18]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:160) ~[spring-data-commons-2.7.18.jar:2.7.18]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:139) ~[spring-data-commons-2.7.18.jar:2.7.18]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.31.jar:5.3.31]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.31.jar:5.3.31]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.31.jar:5.3.31]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137) ~[spring-tx-5.3.31.jar:5.3.31]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.31.jar:5.3.31]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.31.jar:5.3.31]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) ~[spring-aop-5.3.31.jar:5.3.31]
	at jdk.proxy4/jdk.proxy4.$Proxy86.findAllLeikaleistungVerfahren(Unknown Source) ~[na:na]
	at de.chamaeleon.didaexporter.service.VerfahrenExportService.exportVerfahren(VerfahrenExportService.java:73) ~[classes/:na]
	at de.chamaeleon.didaexporter.service.DidaExportService.export(DidaExportService.java:67) ~[classes/:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.context.event.ApplicationListenerMethodAdapter.doInvoke(ApplicationListenerMethodAdapter.java:344) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.event.ApplicationListenerMethodAdapter.processEvent(ApplicationListenerMethodAdapter.java:229) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.event.ApplicationListenerMethodAdapter.onApplicationEvent(ApplicationListenerMethodAdapter.java:166) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:178) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:171) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:145) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:429) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:386) ~[spring-context-5.3.31.jar:5.3.31]
	at org.springframework.boot.context.event.EventPublishingRunListener.ready(EventPublishingRunListener.java:114) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplicationRunListeners.lambda$ready$6(SpringApplicationRunListeners.java:82) ~[spring-boot-2.7.18.jar:2.7.18]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
	at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:120) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:114) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplicationRunListeners.ready(SpringApplicationRunListeners.java:82) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:323) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) ~[spring-boot-2.7.18.jar:2.7.18]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1289) ~[spring-boot-2.7.18.jar:2.7.18]
	at de.chamaeleon.didaexporter.DidaExporterApplication.main(DidaExporterApplication.java:10) ~[classes/:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:50) ~[spring-boot-devtools-2.7.18.jar:2.7.18]
Caused by: java.sql.SQLFeatureNotSupportedException: (conn=48692) Array type is not supported
	at org.mariadb.jdbc.export.ExceptionFactory.createException(ExceptionFactory.java:281) ~[mariadb-java-client-3.4.0.jar:na]
	at org.mariadb.jdbc.export.ExceptionFactory.notSupported(ExceptionFactory.java:333) ~[mariadb-java-client-3.4.0.jar:na]
	at org.mariadb.jdbc.Connection.createArrayOf(Connection.java:743) ~[mariadb-java-client-3.4.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyConnection.createArrayOf(HikariProxyConnection.java) ~[HikariCP-4.0.3.jar:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.jdbc.core.JdbcTemplate$CloseSuppressingInvocationHandler.invoke(JdbcTemplate.java:1650) ~[spring-jdbc-5.3.31.jar:5.3.31]
	at jdk.proxy2/jdk.proxy2.$Proxy52.createArrayOf(Unknown Source) ~[na:na]
	at org.springframework.data.jdbc.core.convert.DefaultJdbcTypeFactory.lambda$createArray$1(DefaultJdbcTypeFactory.java:75) ~[spring-data-jdbc-2.4.18.jar:2.4.18]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:336) ~[spring-jdbc-5.3.31.jar:5.3.31]
	... 49 common frames omitted

chamgda avatar Jun 10 '24 10:06 chamgda