ClickHouse / ClickHouse/clickhouse-java

Regression on format JSONEachRow with JDBC v2

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

Description

## Description

(1) `SELECT * FROM table FORMAT JSONEachRow;`

gives

`SQL-Exception [HY000]: Only RowBinaryWithNameAndTypes is supported for output format. Please check your query.`

after Transition to JDBC driver v2 (tested with driver version 0.9.7).

### Moreover

When returning to v1 using driver Property` clickhouse.jdbc.v1=true` the select works as expected.
Also, the desired behaviour is produced (under v2) by the alternative select

(2) `SELECT formatRowNoNewline( 'JSONEachRow', * ) AS result FROM table;`

but none if these is documented in either [(https://clickhouse.com/docs/integrations/language-clients/java/jdbc#migration-guide](https://clickhouse.com/docs/integrations/language-clients/java/jdbc#migration-guide) or, more generally, here [https://clickhouse.com/docs/integrations/language-clients/java/jdbc](https://clickhouse.com/docs/integrations/language-clients/java/jdbc)

(Thus, as a - potentially cheap - alternative to fixing this regression, please document supported FORMAT specifiers and supported variations/workarounds)

See Expected Behaviour section below for an argument, why (2) cannot currently serve as a replacement for (1) on large tables.

### Steps to reproduce

Call
`SELECT * FROM table FORMAT JSONEachRow;`
on any (non empty) table.

### Error Log or Exception StackTrace

```
SQL-Exception [HY000]: Only RowBinaryWithNameAndTypes is supported for output format. Please check your query.
```

### Expected Behaviour

Return the row as Json Object: '{"' colname1 '"="' colvalue1 '", ' ....

Also

`SELECT formatRowNoNewline( 'JSONEachRow', * ) AS result FROM table;`

returns the desired result BUT processes each row to JSON _first_ and is thus not streaming compatible, whereas

`SELECT * FROM table FORMAT JSONEachRow;`

applies JSON formating _on the fly_, while returning results. Big difference on big tables.

### Code Example

```java
public static void main(String[] args) {

try(Connection conn = new ClickHouseDataSource(dburl, props).getConnection(user, pass)) {

Statement statement = conn.createStatement();

final String selectStatement = "SELECT * FROM test_table FORMAT JSONEachRow;";

ResultSet resultSet = statement.executeQuery(selectStatement);
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
}

} catch (Exception x) {
x.printStackTrace();
}
}
```

### Configuration

#### Environment
* Client version: clickhouse-jdbc-0.9.7-all.jar
* Language version: Java 17
* OS: tried on Windows and Linux

#### ClickHouse Server
* ClickHouse Server version: 25.6.4.12
* ClickHouse Server non-default settings, if any: %
* `CREATE TABLE` statements for tables involved: Use any.
* Sample data for all these tables, use [clickhouse-obfuscator](https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary

P.S.: This was first reported in the ClickHouse Server tracker as [99852](https://github.com/ClickHouse/ClickHouse/issues/99852)

Contributor guide

Open the contributing guide

Research direction

Reproduce the failure with the JDBC v2 driver using `SELECT * FROM table FORMAT JSONEachRow;`, then compare its handling with JDBC v1 and the `formatRowNoNewline` workaround. Trace the JDBC v2 output-format handling from `ClickHouseDataSource` and the query execution path; done means streamed JSONEachRow results work without the exception, or the supported formats and workaround are documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.