ClickHouse / ClickHouse/clickhouse-java

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

Open
#2,982 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
1.6k
Forks
636
Avg merge
2d 23h
Merged PRs (30d)
29

Description

## 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`

```java
public TableSchema getTableSchemaFromQuery(String sql, Map 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/`:

```java
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: https://github.com/ClickHouse/clickhouse-connect/issues/907
Central tracking issue: https://github.com/ClickHouse/integrations-ai-playground/issues/325

Contributor guide

Open the contributing guide

Research direction

Start in client-v2/src/main/java/com/clickhouse/client/api/Client.java at getTableSchemaFromQuery and review jdbc-v2/src/main/java/com/clickhouse/jdbc/PreparedStatementImpl.java around getMetaData(). Run the provided TrailingCommentMetaTest with the Maven command against ClickHouse. Done means all six SQL variants return one column named a through both the JDBC metadata path and the direct client API, while quoted markers remain preserved.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
74/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.