prestodb/presto · error · PrestoException

CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION

CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION

Error message

Unsupported aggregation node 

What it means

ClickHousePushdownUtils.computeAggregationNodes throws CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION when an AggregationNode contains an aggregation with a FILTER clause, DISTINCT aggregation, or ORDER BY (within-group ordering). None of these can be represented in the simple ClickHouse aggregation pushdown, so the whole node is rejected.

Source

Thrown at presto-clickhouse/src/main/java/com/facebook/presto/plugin/clickhouse/optimization/ClickHousePushdownUtils.java:70

import static java.lang.String.format;
import static java.util.concurrent.TimeUnit.MILLISECONDS;

public class ClickHousePushdownUtils
{
    private ClickHousePushdownUtils() {}

    public static List<ClickHouseAggregationColumnNode> computeAggregationNodes(AggregationNode aggregationNode)
    {
        int groupByKeyIndex = 0;
        ImmutableList.Builder<ClickHouseAggregationColumnNode> nodeBuilder = ImmutableList.builder();
        for (VariableReferenceExpression outputColumn : aggregationNode.getOutputVariables()) {
            AggregationNode.Aggregation aggregation = aggregationNode.getAggregations().get(outputColumn);

            if (aggregation != null) {
                if (aggregation.getFilter().isPresent()
                        || aggregation.isDistinct()
                        || aggregation.getOrderBy().isPresent()) {
                    throw new PrestoException(CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION, "Unsupported aggregation node " + aggregationNode);
                }
                nodeBuilder.add(new AggregationFunctionColumnNode(outputColumn, aggregation.getCall()));
            }
            else {
                VariableReferenceExpression inputColumn = aggregationNode.getGroupingKeys().get(groupByKeyIndex);
                nodeBuilder.add(new GroupByColumnNode(inputColumn, outputColumn));
                groupByKeyIndex++;
            }
        }
        return nodeBuilder.build();
    }

    private static Set<String> getGroupKeys(List<VariableReferenceExpression> groupingKeys)
    {
        Set<String> groupKeySet = new HashSet<>();
        groupingKeys.forEach(groupingKey -> groupKeySet.add(groupingKey.getName()));
        return groupKeySet;
    }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Rewrite DISTINCT aggregations, e.g. count(DISTINCT x) as count over a de-duplicated subquery, or use approx_distinct if acceptable
2. Rewrite FILTER aggregates as CASE WHEN inside the aggregate argument: sum(CASE WHEN cond THEN x END)
3. Remove/avoid WITHIN GROUP ORDER BY or push only the plain aggregations and apply distinct/filter/ordering Presto-side
4. Disable aggregation pushdown so the whole aggregation runs in Presto

Example fix

// before
SELECT count(DISTINCT user_id) FILTER (WHERE active) FROM ch_t;
// after
SELECT count(DISTINCT user_id) FROM (SELECT user_id FROM ch_t WHERE active) t;

// Java-side check before relying on aggregation pushdown
boolean isPushableAggregation(AggregationNode.Aggregation agg) {
    return !agg.getFilter().isPresent()
        && !agg.isDistinct()
        && !agg.getOrderBy().isPresent();
}

boolean hasDistinct(AggregationNode.Aggregation agg) { return agg.isDistinct(); }

try {
    nodes = ClickHousePushdownUtils.computeAggregationNodes(aggregationNode, mapping);
} catch (PrestoException e) {
    if (e.getErrorCode().getCode() == CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION.toErrorCode().getCode()) {
        return Optional.empty(); // aggregation runs in Presto
    }
    throw e;
}

When it happens

Trigger: Pushing an aggregation to ClickHouse where any aggregation function uses: FILTER (WHERE ...), DISTINCT inside the aggregate (count(DISTINCT x), avg(DISTINCT y)), or ORDER BY ... WITHIN GROUP / array-ordered aggregation.

Common situations: count(DISTINCT ...) on ClickHouse tables, filtered aggregates like sum(x) FILTER (WHERE flag), and ordered aggregations; very common in analytics queries that mix these features.

Related errors

• CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION
• CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION
• CLICKHOUSE_QUERY_GENERATOR_FAILURE
• DRUID_PUSHDOWN_UNSUPPORTED_EXPRESSION
• DRUID_PUSHDOWN_UNSUPPORTED_EXPRESSION

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/5cd0f3da7c5bb7ee. Report an issue: GitHub.