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 16h
Merged PRs (30d)
28

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

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.