How to get multiple result sets from single query?

[dynaxis]

ClickHouse sometimes returns multiple tables from single query. For instance, you may specify GROUP BY WITH ROLLUP and get multiple tables. So how is it possible to retrieve such results w/ JDBC or new clickhouse-client interface? Looking into the JDBC code, it seems the statement implementation doesn't support getMoreResults() in such a way.

[zhicwu]

Yes, JDBC driver does not support multiple result sets at this point.

What's your query look like? From what I saw, ClickHouse will return additional rows instead of multiple tables.

[dynaxis]

https://clickhouse.com/docs/en/sql-reference/statements/select/group-by/#with-rollup-modifier

I've not yet tested it with clickhouse-http-client. But I'll try if those are returned as a table with such rows instead of just returning the first table.

[zhicwu]

Yes, it will append a few more aggregated rows to the same result set. It's probably better to return multiple result sets instead.

SELECT year, month, day, count(*)
FROM (
	select 2019 as year, 1 as month, 5 as day
	union all
	select 2019 as year, 1 as month, 15 as day
	union all
	select 2020 as year, 10 as month, 5 as day
) t 
GROUP BY year, month, day WITH ROLLUP

[dynaxis]

BTW, if you are to implement support for multiple result sets, will the current behavior change? It's ok to adapt my code to the future change when it's available, but hope to know what change is expected.

[zhicwu]

BTW, if you are to implement support for multiple result sets, will the current behavior change?

No worries, it won't be changed. If we do need to break it down to multiple result sets in the future, we can add a JDBC-specific option to turn it on/off.

[den-crane]

@dynaxis CH does not support multiple results and never will, because it's impossible to return with HTTP API. All queries always return a single resultset. Only one query is possible per request.

They are not multiple results, they are columnar blocks.

set max_block_size=2;

select * from numbers(10);
┌─number─┐
│      0 │
│      1 │
└────────┘
┌─number─┐
│      2 │
│      3 │
└────────┘
┌─number─┐
│      4 │
│      5 │
└────────┘
┌─number─┐
│      6 │
│      7 │
└────────┘
┌─number─┐
│      8 │
│      9 │
└────────┘


 select * from numbers(10) format PrettyCompactMonoBlock;
┌─number─┐
│      0 │
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

[den-crane]

There is zero guarantee that WITH ROLLUP returns in multiple blocks in Native format. Basically it just the current behavior, it can be changed tomorrow.