ClickHouse / ClickHouse/clickhouse-java
Regression on format JSONEachRow with JDBC v2
- 主要语言
- Java
- 星标
- 1.6k
- 派生
- 636
- 平均合并
- 2 天 23 小时
- 30 天内合并 PR
- 29
描述
## 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)
贡献指南
调研方向
使用 JDBC v2 驱动程序和 `SELECT * FROM table FORMAT JSONEachRow;` 重现该故障,然后将其处理方式与 JDBC v1 以及 `formatRowNoNewline` 这一变通方案进行比较。从 `ClickHouseDataSource` 和查询执行路径开始跟踪 JDBC v2 的输出格式处理;当流式 JSONEachRow 结果能够在不抛出异常的情况下正常工作,或已记录受支持的格式和变通方案时,即视为完成。
由索引模型根据 Issue 内容生成。
评估
- 技术栈
- java, sql
- 领域
- database
- Issue 类型
- 缺陷
- 难度
- 4/5
- 预计耗时
- 3-5 天
- 活跃度
- 冷清
- 描述清晰度
- 基本清楚
- 新手友好度
- 52/100