NamedParameterJdbcTemplate.query() breaks handling of SqlParameterValue with a collection of UUID's

[fprochazka]

Affects: v5.2.12.RELEASE (Spring Boot v2.3.7.RELEASE)

I have a PostgreSQL query

SELECT * FROM items WHERE id IN (:ids)

executed as

var jdbcTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource));
RowMapper<ItemData> rowMapper = null; // not relevant

List<UUID> ids = List.of(
    UUID.fromString("57feb862-b8e4-4754-8b75-4e2621388fa0"),
    UUID.fromString("2225559e-97d3-4839-b7b4-a97f4f4f49a9")
);

var parameters = new LinkedHashMap<String, SqlParameterValue>();
parameters.put("ids", new SqlParameterValue(Types.OTHER, ids));

var query = "SELECT * FROM items WHERE id IN (:ids)";

var result = jdbcTemplate.query(query, parameters, rowMapper);

Previously, this happily worked, it expanded the collection to a list of parameters and executed it.

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT * FROM items WHERE id IN (?, ?)]; No value specified for parameter 2.; nested exception is org.postgresql.util.PSQLException: No value specified for parameter 2.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:216)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:223)
    ...

org.postgresql.util.PSQLException: No value specified for parameter 2.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:275)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:307)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:678)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:216)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:223)
    ...

I've tried to debug it and at some point, the parameters that it's trying to bind to the prepared statement contain string like this

String valueBeforeApplyingToPrepareStatement = "['57feb862-b8e4-4754-8b75-4e2621388fa0', '2225559e-97d3-4839-b7b4-a97f4f4f49a9']";

which suggest to me that the query is correctly expanded based on the number of values in the collection, but the parameter is then converted somehow to a single value, which breaks the parameter binding

[sbrannen]

Previously, this happily worked, it expanded the collection to a list of parameters and executed it.

Do you mean this worked in 5.2.11 and then stopped working in 5.2.12?

If not, can you tell us which version this previously worked with?

[fprochazka]

I did some more debugging and the problem is introduced in org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(), where the SqlParameterValue is wrapped into another SqlParameterValue

[sbrannen]

Potentially related to #26071 and #21935.

[fprochazka]

The problem was introduced here 66292cd

Previously, the value was un-wrapped and then re-wrapped into a new instance of SqlParameterValue, but now it's always wrapped into another instance.

[sbrannen]

Thanks for the feedback.

This may be a duplicate of #26467.

[fprochazka]

I wasn't sure at first, that's why I've created a new issue, but now I think you're correct and this is a duplicate.