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
Comment From: 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?
Comment From: 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
Comment From: sbrannen
Potentially related to #26071 and #21935.
Comment From: fprochazka
The problem was introduced here https://github.com/spring-projects/spring-framework/commit/66292cd7a1697a8d99b3dd3eaff4706e4beab558
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.
Comment From: sbrannen
Thanks for the feedback.
This may be a duplicate of #26467.
Comment From: 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.