ClickHouse / ClickHouse/clickhouse-java
[client-v2/jdbc-v2] getTableSchemaFromQuery wraps SQL verbatim in DESC (...) - trailing comment or semicolon breaks column metadata (code 62)
- 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
Assessment
This issue has not been assessed yet.