FROM INPUT inserts in spring JDBC fail to parameterize [worked in V1]

[troyjcurt]

Ive been investigating upgrading our JDBC clickhouse client from 0.7.1-patch1 to 0.8.5 (moving from clickhouse java client v1 to v2 underneath), and our usual insertion query now fails to parameterize.

Relevant Versions

Clickhouse jdbc: 0.8.5 Spring boot: 3.4.5 hikari cp: 6.3.0

Outputs

here is a simplified version of our prepared statement:

INSERT INTO app_database.table SELECT IdCol, NameCol, MapCol, TimeCol, Value from input ('idCol UInt64, nameCol String, mapCol Map(LowCardinality(String), String), timeCol DateTime, Value Float64')
SETTINGS async_insert=0, wait_for_async_insert=0;

and previously, we could set parameters like this:

try (
					Connection connection = dataSource.getConnection();
					PreparedStatement preparedStatement = connection.prepareStatement(getSql())) {
				    data.forEach(d -> {

						preparedStatement.setString(1, d.getId());
						preparedStatement.setString(2, d.getName());
						preparedStatement.setObject(3, d.getMap());
						preparedStatement.setLong(4, DateTimeUtil.convertToSeconds(d.getTime()));
						preparedStatement.setDouble(5, d.getValue());
						preparedStatement.addBatch();
				    });
                    preparedStatement.executeLargeBatch()
} catch (SQLException e) {
                    //
}

but now, we see errors that look like these when parameterizing this query:

java.lang.ArrayIndexOutOfBoundsException: Index 0 out of bounds for length 0
        at com.clickhouse.jdbc.PreparedStatementImpl.setString(PreparedStatementImpl.java:172)                                                                                              
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setString(HikariProxyPreparedStatement.java)                                                                                 
        at com.example.MetricRetryWriter.lambda$writeToDb$0(MetricRetryWriter.java:118)                                                                 
        at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
        at com.example.MetricRetryWriter.writeToDb(MetricRetryWriter.java:115)                                                                          
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)                                                                                 
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)                                                                         
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)                                                                                       
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)                                                                
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)                                                                        
        at org.springframework.retry.interceptor.RetryOperationsInterceptor$1.doWithRetry(RetryOperationsInterceptor.java:114)                                                              
        at org.springframework.retry.support.RetryTemplate.doExecute(RetryTemplate.java:357)
        at org.springframework.retry.support.RetryTemplate.execute(RetryTemplate.java:246)
        at org.springframework.retry.interceptor.RetryOperationsInterceptor.invoke(RetryOperationsInterceptor.java:135)                                                                     
        at org.springframework.retry.annotation.AnnotationAwareRetryOperationsInterceptor.invoke(AnnotationAwareRetryOperationsInterceptor.java:162)                                        
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)                                                                        
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)                                                                      
        at com.example.MetricRetryWriter$$SpringCGLIB$$0.writeToDb(<generated>)                                                                         
        at com.example.MetricThreadWriter.checkAndWrite(MetricThreadWriter.java:98)                                                                     
        at com.example.MetricThreadWriter.lambda$startBatchProcessor$0(MetricThreadWriter.java:75)                                                      
        at java.base/java.lang.Thread.run(Thread.java:833)

Was support for this style of insert using FROM INPUT intentionally removed, or a side effect of client v2?

[chernser]

Good day, @troyjcurt! In the 0.8.0 we made switch to a JDBC-v2 that is built more close to spec. This particular form of SQL is not part of JDBC spec and we do not support it in the new version.

However I've heard your voice and we may be implement it later. There is a workaround to use v1, but it will not work for long-term. If possible I'd recommend using Client directly (you may get access to the client by unwrapping Connection and calling com.clickhouse.jdbc.ConnectionImpl#getClient.