CTE in INSERT INTO SELECT query triggers unexpected "Missing parameters" exception

[Dnnd]

I'm using clickhouse-jdbc v0.3.2-patch1 with Clickhouse Server v21.6.6. The code snippet below:

var ds = new ClickHouseDataSource(chURL, properties);
try (var conn = ds.getConnection()) {
      conn.prepareStatement("CREATE TABLE IF NOT EXISTS  target(value String) ENGINE Log").execute();
      conn.prepareStatement("INSERT INTO target(value) WITH t as ( SELECT 'testValue') SELECT * FROM  t").execute();
}

triggers an SQLException with the following exception message:

Exception in thread "main" java.sql.SQLException: Missing parameter(s): [1]
    at com.clickhouse.jdbc.SqlExceptionUtils.clientError(SqlExceptionUtils.java:42)
    at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.ensureParams(InputBasedPreparedStatement.java:87)
    at com.clickhouse.jdbc.internal.InputBasedPreparedStatement.execute(InputBasedPreparedStatement.java:215)

If I remove CTE from the query, it executes without errors:

var ds = new ClickHouseDataSource(chURL, properties);
try (var conn = ds.getConnection()) {
      conn.prepareStatement("CREATE TABLE IF NOT EXISTS  target(value String) ENGINE Log").execute();
      conn.prepareStatement("INSERT INTO target(value) SELECT 'testValue'").execute();
}

If I have at least one parameter, no exception is thrown as well:

try (var conn = ds.getConnection()) {
      conn.prepareStatement("CREATE TABLE IF NOT EXISTS  target(value String) ENGINE Log").execute();
      var ps = conn.prepareStatement("INSERT INTO target(value) WITH t as ( SELECT 'testValue' as value)  SELECT * FROM t WHERE value != ?");
      ps.setString(1, "test");
      ps.execute();
}

This wasn't the case in the v0.3.1 and it seems like a bug.

[zhicwu]

Thanks for reporting the issue. Will fix it later today and release patch3 tomorrow or so.

[zy8las]

Has it been released

[zhicwu]

Has it been released

Yes. If your CTE is complex, for instance it contains CASE WHEN, you'd better use parensises to group sub expressions.

[zhicwu]

Has it been released

Yes. If your CTE is complex, for instance it contains CASE WHEN, you'd better use parensises to group sub expressions.