Unknown aggregate function: avgIf` occurs when querying via JDBC. (Trino and Dbeaver)

[YuriyGavrilov]

Description

Problem Analysis:

Core issue: An error occurs when executing a query against the default.nm_advert_stats_daily table via the JDBC driver (DBeaver), whereas a direct query using the native ClickHouse client works correctly.

Key points:

1. Error: Unknown aggregate function: avgIf occurs when querying via JDBC.
2. Direct query in the ClickHouse console executes successfully.
3. The table uses aggregate functions in its columns:
   • position_avg_state AggregateFunction(avgIf, Float64, UInt8)
   • position_quantiles_state AggregateFunction(quantilesTDigestIf(0.5, 0.9, 0.95), Float64, UInt8)

Software versions:

• ClickHouse Server: 24.8.8
• JDBC Driver: Official ClickHouse driver (server version 21.3+)

The Problem: There is an incompatibility between the JDBC driver version and the ClickHouse server version. The driver does not recognize the syntax of the aggregate functions (avgIf) that are supported by the server.

Solutions suggested by the participants:

• Fix JDBC driver is up to date due to we can't use native client for Trino and Dbeaver

Steps to reproduce

Error Log or Exception StackTrace

Trino try to select 

select * 
from table(ads_ch_ch.system.query(
    query => 'select * from default.nm_advert_stats_daily limit 100'
    )); -- this is native clickhouse execution 

QL Error [65536]: Query failed (#20260407_141005_02502_hqy64): com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf

Expected Behaviour

Code Example

Configuration

Client Configuration

Environment

• Cloud
• Client version:
• Language version:
• OS:

ClickHouse Server

• ClickHouse Server version:
• ClickHouse Server non-default settings, if any:
• CREATE TABLE statements for tables involved:
• Sample data for all these tables, use clickhouse-obfuscator if necessary

[YuriyGavrilov]

Hi @den-crane may be you know somebody from java to tag here? thank you forward 🙏🏻

[den-crane]

1. It's a trino error.
2. It has no sense to do select *, it will return avgIf state (AggregateFunction), only ClickHouse server is able to read it.

select avgIfState(333.1, 1);
┌─avgIfState(333.1, 1)─┐
│ ������t@                   │
└──────────────────────┘

You should use avgIfMerge: select avgIfMerge(x) from nm_advert_stats_daily => 333.1
Or finalizeAggregation: select finalizeAggregation(x) from nm_advert_stats_daily => 333.1

3. Nobody should use AggregateFunction(avgIf it makes no sense. Instead of avgIfState one should use avgStateIf !!!!

[YuriyGavrilov]

Thx @den-crane I will refer this issue to trino. But strange think that direct connection from dbeaver got same. So only native works fine.

[den-crane]

Thx @den-crane I will refer this issue to trino. But strange think that direct connection from dbeaver got same. So only native works fine.

I see, then the issue is in ClickHouse jdbc. But it's a very minor issue. States are unusable anyway. You have to finalize them in ClickHouse server anyway.

create table test1 (x AggregateFunction(avg,Int64)) Engine=Memory;

insert into test1 select avgState(333::Int64);

select *, hex(x) from test1;
┌─x─┬─hex(x)─────────────┐
│ M │ 4D0100000000000001 │
└───┴────────────────────┘

select finalizeAggregation(x) from test1;
┌─finalizeAggregation(x)─┐
│                    333 │
└────────────────────────┘

[den-crane]

quantilesTDigestIf(0.5, 0.9, 0.95), Float64, UInt8)

BTW, all quantiles AggregateFunction states, including quantilesTDigest stores a state that can be evaluated to any quantile.

So quantilesTDigest(0.5), Float64 can be used with any quantilesTDigestMerge(0.01, 0.05, 0.5, 0.66661 ...)

[chernser]

@den-crane thank you for the detailed explanation. We need to add similar to our documentation.

@YuriyGavrilov thank you for reporting the issue. We plan to make partial support of AggregateFunction and SimpleAggregateFunction types in the future. However as @den-crane state should be resolved on server side.

I will keep the issue open because we have no clear documentation about usage of this type in JDBC/Client.

[YuriyGavrilov]

Just put it here for history and logs and thanks a lot for the details and explanations

This is Trino way to connect:

1. aggregate func.
2. WINDOW_UPDATE - minor but annoying ( some times it happens )

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [65536]: Query failed (#20260413_191430_15264_ewfjp): com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:136)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:714)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$executeSingleElement$1(SQLQueryJob.java:598)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:210)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleElement(SQLQueryJob.java:617)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:478)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:1102)
	at org.jkiss.dbeaver.ui.editors.sql.QueryResultsContainer.readData(QueryResultsContainer.java:275)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$run$0(ResultSetJobDataRead.java:129)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:210)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:127)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5368)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:110)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Query failed (#20260413_191430_15264_ewfjp): com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at io.trino.jdbc.ResultUtils.resultsException(ResultUtils.java:33)
	at io.trino.jdbc.TrinoResultSet.getColumns(TrinoResultSet.java:143)
	at io.trino.jdbc.TrinoResultSet.create(TrinoResultSet.java:49)
	at io.trino.jdbc.TrinoStatement.internalExecute(TrinoStatement.java:276)
	at io.trino.jdbc.TrinoStatement.execute(TrinoStatement.java:254)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:332)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
	... 13 more
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2086)
	at com.google.common.cache.LocalCache.get(LocalCache.java:4017)
	at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4898)
	at io.trino.cache.EvictableCache.get(EvictableCache.java:119)
	at io.trino.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:805)
	at io.trino.plugin.jdbc.CachingJdbcClient.getColumns(CachingJdbcClient.java:182)
	at io.trino.plugin.jdbc.DefaultJdbcMetadata.getTableSchema(DefaultJdbcMetadata.java:1041)
	at io.trino.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.getTableSchema(ClassLoaderSafeConnectorMetadata.java:265)
	at io.trino.tracing.TracingConnectorMetadata.getTableSchema(TracingConnectorMetadata.java:233)
	at io.trino.metadata.MetadataManager.getTableSchema(MetadataManager.java:471)
	at io.trino.tracing.TracingMetadata.getTableSchema(TracingMetadata.java:295)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:2298)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:522)
	at io.trino.sql.tree.Table.accept(Table.java:60)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:541)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:4920)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:3096)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:522)
	at io.trino.sql.tree.QuerySpecification.accept(QuerySpecification.java:155)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:541)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:549)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1564)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:522)
	at io.trino.sql.tree.Query.accept(Query.java:130)
	at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
	at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:541)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:501)
	at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:490)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:98)
	at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:87)
	at io.trino.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:289)
	at io.trino.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:222)
	at io.trino.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:892)
	at io.trino.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:158)
	at io.trino.$gen.Trino_474____20260413_081933_2.call(Unknown Source)
	at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
	at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:75)
	at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1095)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:619)
	at java.base/java.lang.Thread.run(Thread.java:1447)
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at io.trino.cache.EmptyCache.get(EmptyCache.java:101)
	at io.trino.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:805)
	at io.trino.plugin.jdbc.CachingJdbcClient.getColumns(CachingJdbcClient.java:182)
	at io.trino.plugin.jdbc.CachingJdbcClient.lambda$getColumns$3(CachingJdbcClient.java:182)
	at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4903)
	at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3574)
	at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2316)
	at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2190)
	at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2080)
	... 42 more
Caused by: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at com.clickhouse.data.ClickHouseAggregateFunction.of(ClickHouseAggregateFunction.java:187)
	at com.clickhouse.data.ClickHouseColumn.readColumn(ClickHouseColumn.java:332)
	at com.clickhouse.data.ClickHouseColumn.of(ClickHouseColumn.java:537)
	at io.trino.plugin.clickhouse.ClickHouseClient.toColumnMapping(ClickHouseClient.java:638)
	at io.trino.plugin.jdbc.BaseJdbcClient.getColumns(BaseJdbcClient.java:330)
	at io.trino.plugin.jdbc.ForwardingJdbcClient.getColumns(ForwardingJdbcClient.java:109)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getColumns$6(StatisticsAwareJdbcClient.java:130)
	at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:34)
	at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getColumns(StatisticsAwareJdbcClient.java:130)
	at io.trino.plugin.jdbc.RetryingJdbcClient.lambda$getColumns$6(RetryingJdbcClient.java:106)
	at dev.failsafe.Functions.lambda$toCtxSupplier$11(Functions.java:243)
	at dev.failsafe.Functions.lambda$get$0(Functions.java:46)
	at dev.failsafe.internal.RetryPolicyExecutor.lambda$apply$0(RetryPolicyExecutor.java:74)
	at dev.failsafe.SyncExecutionImpl.executeSync(SyncExecutionImpl.java:187)
	at dev.failsafe.FailsafeExecutor.call(FailsafeExecutor.java:376)
	at dev.failsafe.FailsafeExecutor.get(FailsafeExecutor.java:112)
	at io.trino.plugin.jdbc.RetryingModule.retry(RetryingModule.java:67)
	at io.trino.plugin.jdbc.RetryingJdbcClient.getColumns(RetryingJdbcClient.java:106)
	at io.trino.plugin.jdbc.CachingJdbcClient.lambda$getColumns$3(CachingJdbcClient.java:182)
	at io.trino.cache.EmptyCache.get(EmptyCache.java:95)
	... 50 more

Direct Dbeaver connection (second way)

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [HY000]: Unknown aggregate function: avgIf
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:136)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:714)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$executeSingleElement$1(SQLQueryJob.java:598)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:210)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleElement(SQLQueryJob.java:617)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:478)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:1102)
	at org.jkiss.dbeaver.ui.editors.sql.QueryResultsContainer.readData(QueryResultsContainer.java:275)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$run$0(ResultSetJobDataRead.java:129)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:210)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:127)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5368)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:110)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Unknown aggregate function: avgIf
	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:75)
	at com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:43)
	at com.clickhouse.jdbc.StatementImpl.executeQueryImpl(StatementImpl.java:218)
	at com.clickhouse.jdbc.StatementImpl.execute(StatementImpl.java:368)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:332)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:134)
	... 13 more
Caused by: java.lang.IllegalArgumentException: Unknown aggregate function: avgIf
	at com.clickhouse.data.ClickHouseAggregateFunction.of(ClickHouseAggregateFunction.java:188)
	at com.clickhouse.data.ClickHouseColumn.readColumn(ClickHouseColumn.java:370)
	at com.clickhouse.data.ClickHouseColumn.of(ClickHouseColumn.java:639)
	at com.clickhouse.client.api.data_formats.RowBinaryWithNamesAndTypesFormatReader.readSchema(RowBinaryWithNamesAndTypesFormatReader.java:52)
	at com.clickhouse.client.api.data_formats.RowBinaryWithNamesAndTypesFormatReader.<init>(RowBinaryWithNamesAndTypesFormatReader.java:26)
	at com.clickhouse.client.api.Client.newBinaryFormatReader(Client.java:2044)
	at com.clickhouse.client.api.Client.newBinaryFormatReader(Client.java:2060)
	at com.clickhouse.jdbc.StatementImpl.executeQueryImpl(StatementImpl.java:184)
	... 16 more

Anoter clickhouse server and other bi tool ( not dbeaver )

` t.conn.QueryContext: trino: query failed (200 OK): "INTERNAL_ERROR: Error listing table columns for catalog ads_platform_paas_ch: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.IllegalArgumentException: Unknown aggregate function: minIf"`

[den-crane]

@chernser about SimpleAggregateFunction, please discuss it with ClickHouse core team.

see ClickHouse/ClickHouse#27668

There is no need to return type SimpleAggregateFunction(min, UInt64), ClickHouse server can return UInt64 instead.