ClickHouse / ClickHouse/clickhouse-java

Regression on format JSONEachRow with JDBC v2

Ouverte
#2,799 4 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
bug jdbc-v2
Langage dominant
Java
Étoiles
1.6k
Forks
636
Merge moyen
2 j 23 h
PR mergées (30 j)
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)

Guide de contribution

Ouvrir le guide de contribution

Piste de recherche

Reproduisez l’échec avec le pilote JDBC v2 en utilisant `SELECT * FROM table FORMAT JSONEachRow;`, puis comparez son comportement avec JDBC v1 et le contournement `formatRowNoNewline`. Suivez le traitement du format de sortie de JDBC v2 depuis `ClickHouseDataSource` et le chemin d’exécution de la requête ; le travail est terminé lorsque les résultats JSONEachRow diffusés fonctionnent sans l’exception, ou lorsque les formats pris en charge et le contournement sont documentés.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
java, sql
Domaine
database
Type d'issue
Bug
Difficulté
4/5
Temps estimé
3-5 jours
Activité
Calme
Clarté
Plutôt claire
Accessibilité débutants
52/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.