Regression on format JSONEachRow with JDBC v2

[Olivier-Petri]

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 TABLE statements 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