ClickHouse DB::Exception: Function cannot have parameters

The "DB::Exception: Function cannot have parameters" error in ClickHouse occurs when you use the parametric function syntax -- func(params)(args) -- on a function that does not support it. In ClickHouse, only certain aggregate functions accept parameters (the values in the first set of parentheses). Attempting to pass parameters to a regular function or a non-parametric aggregate triggers the FUNCTION_CANNOT_HAVE_PARAMETERS error.

Impact

The query is rejected during analysis. No data processing occurs. This is strictly a query syntax issue that needs to be corrected before the query can run.

Common Causes

1. Applying parametric syntax to a scalar function -- writing something like toString(10)('hello') when you meant toString('hello').
2. Using parametric syntax on a non-parametric aggregate -- for example, count(1)(column) instead of just count(column).
3. Confusing function call syntax -- accidentally double-wrapping parentheses, creating what looks like a parametric call.
4. Migrating from another database -- other SQL dialects do not have parametric aggregate syntax, so mixing conventions can produce this error.
5. Incorrect combinator usage -- attempting to parameterize an aggregate that accepts combinators but not parameters.

Troubleshooting and Resolution Steps

1. Understand parametric vs. regular syntax. In ClickHouse, parametric aggregate functions use this pattern:

   -- Parametric: parameters in first parens, arguments in second
   SELECT quantile(0.95)(response_time) FROM requests;
   
   -- Non-parametric: just arguments
   SELECT sum(amount) FROM orders;
   

2. Identify which functions are parametric. Common parametric aggregates include quantile, quantiles, quantileExact, quantileTDigest, topK, topKWeighted, sequenceMatch, sequenceCount, windowFunnel, and retention. Most other aggregates are not parametric.

3. Remove the extra parentheses. If you accidentally used parametric syntax:

   -- Wrong
   SELECT sum(1)(amount) FROM orders;
   
   -- Correct
   SELECT sum(amount) FROM orders;
   

4. Check for double parentheses from copy-paste errors:

   -- Wrong: accidental double parens
   SELECT count()(user_id) FROM users;
   
   -- Correct
   SELECT count(user_id) FROM users;
   -- Or simply
   SELECT count() FROM users;
   

5. Verify the function type. Query system.functions to check whether a function is an aggregate:

   SELECT name, is_aggregate FROM system.functions WHERE name = 'yourFunction';
   

Best Practices

• Learn which ClickHouse aggregate functions are parametric -- they are a relatively small, well-defined set.
• Be careful with parentheses when writing complex expressions. An extra pair can inadvertently create parametric syntax.
• Use a SQL formatter that understands ClickHouse syntax to make parenthesis grouping visually clear.
• When in doubt, check the function's documentation page for its exact calling convention.

Frequently Asked Questions

Q: Which aggregate functions in ClickHouse support parameters?
A: The main parametric aggregates are the quantile family (quantile, quantiles, quantileExact, quantileTDigest, quantileTiming, etc.), topK, topKWeighted, sequenceMatch, sequenceCount, windowFunnel, retention, histogram, and groupBitmapAnd/Or/Xor. Check the docs for a complete list.

Q: Can scalar (non-aggregate) functions ever have parameters in ClickHouse?
A: No. The parametric syntax func(params)(args) is exclusive to certain aggregate functions. Scalar functions always use the standard func(args) syntax.

Q: I want to pass a configuration value to an aggregate function. How do I do that?
A: If the aggregate function supports parameters, use the parametric syntax: quantile(0.99)(latency). If it does not support parameters, you cannot configure it this way -- look for alternative functions or use WHERE/HAVING to shape the input data.

Q: What if I need quantile but I am getting this error on a different aggregate?
A: You might be calling the wrong function. For example, avg(0.5)(col) is invalid because avg is not parametric. If you want a weighted or conditional average, use avgIf or avgWeighted instead.