ClickHouse / ClickHouse/clickhouse-java

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

Open
#2,824 10 comments 4 reactions 0 assignees View on GitHub
area:docs bug client-api-v2 jdbc-v2
Dominant language
Java
Stars
1.6k
Forks
636
Avg merge
2d 23h
Merged PRs (30d)
29

Description

## 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
1.
2.
3.
### 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

```java

```

### Configuration

#### Client Configuration
```java

```

#### 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](https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary

Contributor guide

Open the contributing guide

Research direction

The report names the JDBC driver, DBeaver, Trino, and the default.nm_advert_stats_daily table, but no repository files or tests. Start by reproducing the Trino JDBC query and comparing it with the native ClickHouse query using the stated server and driver versions; done means the JDBC path handles the avgIf aggregate function without the reported error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
api, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.