jdbc-v2: values-list positions from the JavaCC parser are offsets into the rebuilt SQL, but PreparedStatementImpl slices the original SQL

[polyglotAI-bot]

Describe the bug

In jdbc-v2 the default JAVACC SQL parser records the INSERT ... VALUES list start/end positions as offsets into the SQL it rebuilds from the token stream, but PreparedStatementImpl applies those offsets to the SQL string the caller passed in.

ClickHouseSqlParser.jj builds a second copy of the statement in its token manager (CommonTokenAction, builder), and addCustomKeywordPosition(...) records positions with builder.lastIndexOf(...). That rebuilt SQL is not always character-identical to the input: JDBC escape sequences ({d '...'}, {ts '...'}, {t '...'}, {tt '...'}) are rewritten to ClickHouse expressions of a different length, and content the lexer considers an invalid escape is dropped entirely.

PreparedStatementImpl (jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java:105) then does originalSql.substring(valueListStartPos, valueListStopPos + 1) and derives the parameter offsets inside that slice from parameter positions that were scanned from the original SQL. When the rebuilt SQL has drifted, the slice is taken at the wrong offsets.

Depending on whether the rewrite grew or shrank the statement, this surfaces as:

• StringIndexOutOfBoundsException out of Connection#prepareStatement, or
• a silently truncated values-list template, which then throws StringIndexOutOfBoundsException out of PreparedStatement#addBatch because the parameter offset falls outside the template.

Both are unchecked exceptions escaping the JDBC API.

Note that a bare escape sequence in the values list is not parseable by the grammar, so the statement has to keep the values list parseable for the positions to be recorded at all — e.g. by nesting the escape in a function call. Any ClickHouse query parameter whose name starts with d or t (e.g. {d:Int32}, {ts:DateTime}) is also matched by the JDBC escape token and dropped from the rebuilt SQL.

The ANTLR4 and ANTLR4_PARAMS_PARSER backends record parse-tree indices into the original SQL and are not affected.

Steps to reproduce

try (Connection conn = DriverManager.getConnection(url, props)) {
    // (1) grows: "{ts '...'}" is rebuilt as "timestamp'...'" (4 characters longer)
    //     -> StringIndexOutOfBoundsException from prepareStatement
    conn.prepareStatement("INSERT INTO t (s, n) VALUES (toDateTime({ts '2024-01-01 00:00:00'}), ?)");

    // (2) shrinks: "{d:Int32}" is dropped from the rebuilt SQL
    //     -> values list template is truncated, StringIndexOutOfBoundsException from addBatch
    try (PreparedStatement ps = conn.prepareStatement("INSERT INTO t (s, n) VALUES (toInt32({d:Int32}), ?)")) {
        ps.setInt(1, 42);
        ps.addBatch();
    }
}

Observed (case 1):

java.lang.StringIndexOutOfBoundsException: begin 60, end 107, length 103
	at com.clickhouse.jdbc.PreparedStatementImpl.<init>(PreparedStatementImpl.java:105)

Expected behaviour

Connection#prepareStatement and PreparedStatement#addBatch should not throw an unchecked StringIndexOutOfBoundsException, and the values-list template must never be sliced at offsets that do not address the caller's SQL.

Configuration

• Environment: any
• ClickHouse server: any (the failure happens client-side, before any request is sent)
• clickhouse-java version: 0.11.0-rc1 / current main
• Driver: jdbc-v2, default jdbc_sql_parser=JAVACC