{"record":{"id":"5cd0f3da7c5bb7ee","repo":"prestodb/presto","slug":"clickhouse-pushdown-unsupported-expression-5cd0f3","errorCode":"CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION","errorMessage":"Unsupported aggregation node ","messagePattern":"Unsupported aggregation node ","errorType":"error_code","errorClass":"PrestoException","httpStatus":null,"severity":"error","filePath":"presto-clickhouse/src/main/java/com/facebook/presto/plugin/clickhouse/optimization/ClickHousePushdownUtils.java","lineNumber":70,"sourceCode":"import static java.lang.String.format;\nimport static java.util.concurrent.TimeUnit.MILLISECONDS;\n\npublic class ClickHousePushdownUtils\n{\n    private ClickHousePushdownUtils() {}\n\n    public static List<ClickHouseAggregationColumnNode> computeAggregationNodes(AggregationNode aggregationNode)\n    {\n        int groupByKeyIndex = 0;\n        ImmutableList.Builder<ClickHouseAggregationColumnNode> nodeBuilder = ImmutableList.builder();\n        for (VariableReferenceExpression outputColumn : aggregationNode.getOutputVariables()) {\n            AggregationNode.Aggregation aggregation = aggregationNode.getAggregations().get(outputColumn);\n\n            if (aggregation != null) {\n                if (aggregation.getFilter().isPresent()\n                        || aggregation.isDistinct()\n                        || aggregation.getOrderBy().isPresent()) {\n                    throw new PrestoException(CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION, \"Unsupported aggregation node \" + aggregationNode);\n                }\n                nodeBuilder.add(new AggregationFunctionColumnNode(outputColumn, aggregation.getCall()));\n            }\n            else {\n                VariableReferenceExpression inputColumn = aggregationNode.getGroupingKeys().get(groupByKeyIndex);\n                nodeBuilder.add(new GroupByColumnNode(inputColumn, outputColumn));\n                groupByKeyIndex++;\n            }\n        }\n        return nodeBuilder.build();\n    }\n\n    private static Set<String> getGroupKeys(List<VariableReferenceExpression> groupingKeys)\n    {\n        Set<String> groupKeySet = new HashSet<>();\n        groupingKeys.forEach(groupingKey -> groupKeySet.add(groupingKey.getName()));\n        return groupKeySet;\n    }","sourceCodeStart":52,"sourceCodeEnd":88,"githubUrl":"https://github.com/prestodb/presto/blob/55bb57d202de3b926896fa966c2c4a44c779634e/presto-clickhouse/src/main/java/com/facebook/presto/plugin/clickhouse/optimization/ClickHousePushdownUtils.java#L52-L88","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Rewrite DISTINCT aggregations, e.g. count(DISTINCT x) as count over a de-duplicated subquery, or use approx_distinct if acceptable","Rewrite FILTER aggregates as CASE WHEN inside the aggregate argument: sum(CASE WHEN cond THEN x END)","Remove/avoid WITHIN GROUP ORDER BY or push only the plain aggregations and apply distinct/filter/ordering Presto-side","Disable aggregation pushdown so the whole aggregation runs in Presto"],"exampleFix":"// before\nSELECT count(DISTINCT user_id) FILTER (WHERE active) FROM ch_t;\n// after\nSELECT count(DISTINCT user_id) FROM (SELECT user_id FROM ch_t WHERE active) t;","handlingStrategy":"validation","validationCode":"// Java-side check before relying on aggregation pushdown\nboolean isPushableAggregation(AggregationNode.Aggregation agg) {\n    return !agg.getFilter().isPresent()\n        && !agg.isDistinct()\n        && !agg.getOrderBy().isPresent();\n}","typeGuard":"boolean hasDistinct(AggregationNode.Aggregation agg) { return agg.isDistinct(); }","tryCatchPattern":"try {\n    nodes = ClickHousePushdownUtils.computeAggregationNodes(aggregationNode, mapping);\n} catch (PrestoException e) {\n    if (e.getErrorCode().getCode() == CLICKHOUSE_PUSHDOWN_UNSUPPORTED_EXPRESSION.toErrorCode().getCode()) {\n        return Optional.empty(); // aggregation runs in Presto\n    }\n    throw e;\n}","preventionTips":["Avoid count(DISTINCT ...) on ClickHouse tables; use subqueries or approx_distinct","Rewrite FILTER aggregates as CASE WHEN inside the aggregate argument","Avoid WITHIN GROUP ORDER BY in pushed aggregations","Simplify pushed aggregations to plain column-argument functions"],"tags":["clickhouse","pushdown","aggregation","distinct","filter-clause"],"backgroundTag":"connector-pushdown-unsupported-expression","analyzedSha":"55bb57d202de3b926896fa966c2c4a44c779634e","analyzedAt":"2026-09-04T12:50:26.162Z","contentChangedAt":"2026-09-04T12:50:26.162Z","schemaVersion":2},"datasetVersion":"2026-09-11T21:17:09.523Z"}