Repository navigation
Regression on format JSONEachRow with JDBC v2 #2799
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 or, more generally, here 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
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 TABLEstatements for tables involved: Use any.- Sample data for all these tables, use clickhouse-obfuscator if necessary
P.S.: This was first reported in the ClickHouse Server tracker as 99852
Activity
chernser commentedon Mar 30, 2026
Good day, @Olivier-Petri !
Thank you for reporting the issue!
Agree we should document this "change".
As for "why?":
JDBC is kind of standard. V1 had many extensions that contradict JDBC spec like this one. If we allow reading other formats we should completely parse each row instead of dumping everything to a string or so.
However reading alternative formats might be needed for you use-case and I will appreciate if you can share it.
Thanks!
Olivier-Petri commentedon Mar 31, 2026
Hello @chernser ,
Thank you for accepting this issue!
I very much appreciate bringing the JDBC driver implementation closer to the standard!
Formatting an entire row of a given query result into valid JSON, is a strong feature of Clickhouse, and imo far beyond simple string dumping. In contrast to what I stated above we DO now use (2) as a replacement for (1). Measuring performance revealed that (2) is just as well suited (if not better!) for streamed bulk processing as (1)! My statement above "(2) cannot currently serve as a replacement for (1)" was based on rather superficial code analysis and I must withdraw (sorry for the noise).
Nonetheless, since (1) is a prominent Clickhouse feature, users will expect it to work (with the same outcome as (2), which does work). If this cannot be achieved for whatever reason, it should be mentioned in the Clickhouse-JDBC documentation, giving (2) as a replacement (I found this solution by pure chance, and it would be kind to spare others from the need to figure it out on their own).
Best regards,
Olivier
chernser commentedon Jun 24, 2026
Good day, @Olivier-Petri !
Would you please review the PR #2871
This is related to the problem but has a new interface. Please let me know what is missing and what would need improvements?
Thank you!
Olivier-Petri commentedon Jun 24, 2026
Hi @chernser,
Thank you very much for taking the pain to look into this!
Now it seems indeed to be related to this Bugreport, yet a totaly new feature. When I looked into the code/diff, I got a bit confused about the Json parser that make their appearance there, since Json parsing was never an issue for us. I also build the HEAD as maven SNAPSHOT artifacts and found the original error of my use case
beeing replaced with the new
I understood, that I should specify the 'jdbc_json_parser_factory' property, but to cut a long story short: to me this seems to be a misunderstanding.
Given a table
and a row
"Toast" | 12 | ["alpha", "omega"] | {"attribute" : "value" }
then
SELECT formatRowNoNewline( 'JSONEachRow', * ) AS result FROM test_table;returns the (valid!) Json String
{"name" : "Toast", "number" : 12, "list" : ["alpha", "omega"], "the_blob" : {"attribute" : "value" } }and
SELECT * FROM test_table FORMAT JSONEachRow;used to return the same (and still returns it with V1 driver setting). This is obtained by calling
resultSet.getString(1)and the ResultSet contains only one "column" anyway, "the" result.
Also this is all that we need, and all what we requested with the present bug report: obtaining each row of the query result as a properly formated JSON string.
Now the purpose of this new development seems to be to use JSON as a transport format between server and client, then parse it on the client side, so the row can be queried (through the ResultSet object) as if the regular binary transport had been used. This is certainly an interesting feature, but it is a breaking change to the old V1 behaviour just as well, and it is pretty far from what we thought of.
I admit, I am a bit buffled as to where this misunderstaning introduced itself, but I'm afraid this is how it looks from my side.
Best regards, and thank you very much again for all your efforts!
Olivier