ClickHouse DB::Exception: Parameters to aggregate functions must be literals

The "DB::Exception: Parameters to aggregate functions must be literals" error in ClickHouse is raised when you pass a non-constant value (such as a column reference or a runtime expression) as a parameter to a parametric aggregate function. Parameters in ClickHouse aggregate functions -- the values in the first set of parentheses -- must be compile-time constants. The error code is PARAMETERS_TO_AGGREGATE_FUNCTIONS_MUST_BE_LITERALS.

Impact

The query fails at analysis time. ClickHouse needs to know aggregate function parameters before execution begins because they affect how the aggregate state is initialized and processed. Dynamic parameters would make this impossible, so the query is rejected.

Common Causes

1. Passing a column as a parameter -- writing quantile(percentile_column)(value) instead of quantile(0.95)(value).
2. Using a subquery result as a parameter -- trying to compute the parameter dynamically from data.
3. Passing a setting or variable -- ClickHouse does not support variable substitution in aggregate parameters.
4. Confusing parameters with arguments -- mixing up which values go in the first parentheses (parameters) vs. the second (arguments).

Troubleshooting and Resolution Steps

1. Replace dynamic values with literals. Aggregate function parameters must be hard-coded constants:

   -- Wrong: column reference as parameter
   SELECT quantile(pct_column)(latency) FROM requests;
   
   -- Correct: literal value
   SELECT quantile(0.95)(latency) FROM requests;
   

2. Compute multiple quantiles at once. If you need different percentiles, use quantiles():

   SELECT quantiles(0.5, 0.9, 0.95, 0.99)(latency) FROM requests;
   

3. Use a different approach for dynamic parameters. If you genuinely need a data-driven parameter, restructure the query. For example, compute a filtered aggregation instead:

   -- Instead of a dynamic topK parameter, use a fixed value and filter afterwards
   SELECT topK(100)(url) FROM requests;
   -- Then filter to the desired count in application code
   

4. For dynamic percentiles, use quantileExact with post-processing:

   -- Compute multiple fixed percentiles and select the one you need
   SELECT
       quantiles(0.5, 0.75, 0.9, 0.95, 0.99)(latency) AS pcts
   FROM requests;
   

5. Consider using arrayElement for dynamic selection from pre-computed quantiles:

   SELECT arrayElement(
       quantiles(0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9)(latency),
       5  -- picks the 50th percentile
   ) FROM requests;
   

Best Practices

• Accept that aggregate parameters in ClickHouse are compile-time constants by design. Structure your queries accordingly.
• If you need flexibility in percentiles or topK values, compute a superset of values and filter in your application layer.
• Use quantiles() (plural) to compute multiple percentiles in a single pass rather than multiple quantile() calls.
• Document the parameter values used in important queries so team members understand why specific constants were chosen.

Frequently Asked Questions

Q: Why can't ClickHouse accept dynamic parameters for aggregate functions?
A: Aggregate function parameters affect the internal state structure and initialization. ClickHouse compiles the query plan before reading data, and it needs to know the exact parameters at compile time. Dynamic parameters would require fundamentally different execution logic.

Q: Can I use a macro or constant expression as a parameter?
A: You can use constant expressions that ClickHouse can evaluate at compile time, such as arithmetic on literals: quantile(1.0 / 2)(value). But column references, subqueries, or settings are not allowed.

Q: Is this limitation specific to quantile functions?
A: No. All parametric aggregate functions have this requirement, including topK, topKWeighted, sequenceMatch, sequenceCount, windowFunnel, retention, and histogram. The parameters for all of them must be literals.

Q: How do I handle this in a BI tool that generates dynamic percentiles?
A: Most BI tools that support ClickHouse allow you to inject literal values into the SQL template. Configure the percentile as a template variable that gets substituted into the SQL string before it is sent to ClickHouse, rather than as a query parameter or column reference.