[client-v2/jdbc-v2] getTableSchemaFromQuery wraps SQL verbatim in DESC (...) - trailing comment or semicolon breaks column metadata (code 62)

[claude]

Description

Client.getTableSchemaFromQuery(String sql, Map params) builds its introspection query by wrapping the user SQL verbatim:

client-v2/src/main/java/com/clickhouse/client/api/Client.java:2117-2119

public TableSchema getTableSchemaFromQuery(String sql, Map<String, Object> params) {
    final String describeQuery = "DESC (" + sql + ") FORMAT " + ClickHouseFormat.TSKV.name();
    ...
}

Nothing strips a trailing statement terminator or a trailing SQL comment, so:

• trailing single-line comment (-- ... or # ...) — the comment swallows the wrapper's closing ) (and FORMAT TSKV), producing Unmatched parentheses;
• trailing ; (bare, or followed by a comment) — the ; ends up inside the subquery parentheses, which the server rejects.

Both raise ServerException: Code: 62 ... (SYNTAX_ERROR).

This surfaces in jdbc-v2 through PreparedStatementImpl.getMetaData() (jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java:406-421), which calls getTableSchemaFromQuery before execution. The exception is caught and only logged at WARN, so the driver silently falls back to a placeholder metadata object: getMetaData().getColumnCount() returns 0 instead of the real column list. A tool that inspects PreparedStatement.getMetaData() before executing (a common ORM / BI pattern) gets no column metadata at all for any query whose text ends with a comment or a semicolon — including the very common SELECT ... ; form.

For direct client-v2 API users the failure is loud: getTableSchemaFromQuery throws ServerException code 62.

ClickHouse server version

26.7.1.1315 (local server at http://localhost:8123), against main (0.10.0-rc1-SNAPSHOT).

Reproduction

TestNG test in jdbc-v2/src/test/java/com/clickhouse/jdbc/:

package com.clickhouse.jdbc;

import com.clickhouse.client.api.Client;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.util.Properties;

import static org.testng.Assert.assertEquals;

public class TrailingCommentMetaTest {

    @DataProvider(name = "sqls")
    static Object[][] sqls() {
        return new Object[][]{
                {"SELECT 13 AS a WHERE 0"},                    // baseline, works
                {"SELECT 13 AS a WHERE 0 -- trailing comment"},
                {"SELECT 13 AS a WHERE 0 # trailing comment"},
                {"SELECT 13 AS a WHERE 0;"},
                {"SELECT 13 AS a WHERE 0; -- trailing comment"},
                {"SELECT 13 AS a WHERE 0;\n-- trailing comment"},
        };
    }

    @Test(groups = {"integration"}, dataProvider = "sqls")
    public void testMetadataBeforeExecution(String sql) throws Exception {
        Properties p = new Properties();
        p.setProperty("user", "default");
        p.setProperty("password", "");
        try (Connection conn = new ConnectionImpl("jdbc:ch:http://localhost:8123/default", p);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSetMetaData md = stmt.getMetaData();
            assertEquals(md.getColumnCount(), 1);
            assertEquals(md.getColumnName(1), "a");
        }
    }

    @Test(groups = {"integration"}, dataProvider = "sqls")
    public void testClientGetTableSchemaFromQuery(String sql) throws Exception {
        try (Client client = new Client.Builder().addEndpoint("http://localhost:8123")
                .setUsername("default").setPassword("").setDefaultDatabase("default")
                .compressServerResponse(false).build()) {
            assertEquals(client.getTableSchemaFromQuery(sql).getColumns().size(), 1);
        }
    }
}

Run:

mvn -pl jdbc-v2 -Dj8 -DskipUTs -DclickhouseServer=localhost -Dit.test=TrailingCommentMetaTest verify

Expected

All six variants report one column a (UInt8); a trailing comment or statement terminator does not change the query's semantics.

Actual

Tests run: 12, Failures: 10 — only the two baseline cases pass.

PreparedStatement.getMetaData() (column count before execution):

[SELECT 13 AS a WHERE 0]                        cols=1 name=a type=UInt8   <- OK
[SELECT 13 AS a WHERE 0 -- trailing comment]    cols=0
[SELECT 13 AS a WHERE 0 # trailing comment]     cols=0
[SELECT 13 AS a WHERE 0;]                       cols=0
[SELECT 13 AS a WHERE 0; -- trailing comment]   cols=0
[SELECT 13 AS a WHERE 0;\n-- trailing comment]  cols=0

Client.getTableSchemaFromQuery():

[SELECT 13 AS a WHERE 0]                       -> [a UInt8]
[SELECT 13 AS a WHERE 0 -- trailing comment]   -> ServerException Code: 62 ... failed at position 6 ((): (SELECT 13 AS a WHERE 0 -- trailing comment) FORMAT TSKV. Unmatched parentheses: (. (SYNTAX_ERROR)
[SELECT 13 AS a WHERE 0 # trailing comment]    -> ServerException Code: 62 ... Unmatched parentheses: (. (SYNTAX_ERROR)
[SELECT 13 AS a WHERE 0;]                      -> ServerException Code: 62 ... failed at position 29 (end of query): ;. (SYNTAX_ERROR)
[SELECT 13 AS a WHERE 0; -- trailing comment]  -> ServerException Code: 62 ... (SELECT 13 AS a WHERE 0;. Unmatched parentheses: (. (SYNTAX_ERROR)
[SELECT 13 AS a WHERE 0;\n-- trailing comment] -> ServerException Code: 62 ... Unmatched parentheses: (. (SYNTAX_ERROR)

A constant-false WHERE is only used to keep the example short — the wrap is broken for any query text with a trailing comment or ;, regardless of whether it returns rows.

Suggested fix

In client-v2/src/main/java/com/clickhouse/client/api/Client.java:2117-2119, normalize the SQL before wrapping it: strip trailing whitespace, trailing single-line (--, #) and block (/* */) comments, and trailing ; terminators, repeating until stable. Comment markers and semicolons inside string literals (') and inside backtick / double-quote quoted identifiers must be preserved. Putting the closing paren on its own line ("DESC (\n" + sql + "\n) FORMAT TSKV") fixes the comment cases but not the trailing-; cases, so the strip is still needed.

Additionally, PreparedStatementImpl.getMetaData() (jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java:414-421) swallowing the failure into a zero-column ResultSetMetaData makes this hard to diagnose — worth reconsidering as part of the fix.

Link

Analogous issue in clickhouse-connect: ClickHouse/clickhouse-connect#907
Central tracking issue: https://github.com/ClickHouse/integrations-ai-playground/issues/325