[jdbc-v2] PreparedStatement argCount is 0 when using IN with a list of parameters

[hmerat]

Description

When using spring's NamedParameterJdbcTemplate, a IN with a named value is converted to a IN with a list of parameters.

For example, SELECT myColumn FROM myTable WHERE myColumn in (:values) is converted to SELECT myColumn FROM myTable WHERE myColumn in (?, ?, ?).

It leads to a PreparedStatement with 0 as argCount.

Steps to reproduce

1. Execute a query using NamedParameterJdbcTemplate with a list as a named parameter

Error Log or Exception StackTrace

java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
        at com.clickhouse.jdbc.PreparedStatementImpl.setString(PreparedStatementImpl.java:210)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:438)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:242)
        at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:158)
        at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:282)
        at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:245)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:656)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
        at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForRowSet(NamedParameterJdbcTemplate.java:324)

Expected Behaviour

We can set a list as parameter

Configuration

Environment

• Cloud
• Client version: 0.9.2
• Language version:
• OS:

ClickHouse Server

• ClickHouse Server version: 25.2.2

[chernser]

Good day, @hmerat !

Thank you for reporting the issue! We maybe can fix it.

However I think it is a Spring bug because:

• IN is 1 argument effectively
• having separate argument means that different types can be used what will cause error in some cases
• PreparedStatement is designed to be reused and Spring ignores this fact creating statement with static number of arguments in IN statement. For example, there are two arrays [1, 2, 3] and [1, 2, 3,4,5]. In this case IN (?, ?, ? will not work for second array, right?

[hmerat]

In spring, with NamedParameterJdbcTemplate we give the sql and the parameters. With that, it creates the PreparedStatement and run it. We don't have access to the PreparedStatement so we can't use it with other parameters.

Here's an example of how I use it:

var params = new MapSqlParameterSource();
params.addValue("values", List.of("value1", "value2", "value3"));
SqlRowSet resultSet = template.queryForRowSet("SELECT myColumn FROM myTable WHERE myColumn in (:values)", params)

[chernser]

@hmerat

Looks like I was wrong. They do array unfolding into multiple arguments to use IN canonical form that all DB should support.
I will fix it.