Query methods with tuple bind parameter `List<String[]>` does no longer work

[fmisir]

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:
2.4.0...2.4.2#diff-b8453546f8ae7cc224005f536bb3be8a0c341186f2cf38b2b34d958cc769ac4cR171

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

[schauder]

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.

[spring-projects-issues]

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.

[fmisir]

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

[jipipi]

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.