ClickHouse DB::Exception: Function is special

The "DB::Exception: Function is special" error in ClickHouse occurs when you try to call a special-syntax function as if it were a regular function. Certain operations in ClickHouse -- like IN, NOT IN, GLOBAL IN, BETWEEN, LIKE, CASE, and lambda expressions -- look like functions internally but have dedicated syntax in the SQL grammar. Attempting to use them with standard function-call syntax triggers the FUNCTION_IS_SPECIAL error.

Impact

The query fails during parsing or analysis. This is a syntax-level error that prevents the query from being compiled. No data is read or processed.

Common Causes

1. Calling in as a function -- writing in(column, (1, 2, 3)) instead of using the SQL IN operator.
2. Attempting to use lambda syntax as a function call -- treating lambda as a callable function rather than using the arrow syntax ->.
3. Programmatically generating SQL -- query builders that treat all operations as function calls may produce invalid syntax for these special operators.
4. Using CASE as a function -- writing case(expr, val1, res1, default) instead of the SQL CASE WHEN ... THEN ... END syntax.
5. Referencing special functions in higher-order function contexts -- trying to pass in or like as function arguments to arrayMap or similar.

Troubleshooting and Resolution Steps

1. Use the proper SQL syntax for the operator. Replace function-call syntax with the correct SQL form:

   -- Wrong: function-call syntax
   SELECT in(status, ('active', 'pending')) FROM users;
   
   -- Correct: SQL IN operator
   SELECT status IN ('active', 'pending') FROM users;
   

2. Use CASE with proper syntax:

   -- Wrong: function-call style
   SELECT case(type, 'a', 1, 'b', 2, 0) FROM my_table;
   
   -- Correct: SQL CASE expression
   SELECT CASE type WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 0 END FROM my_table;
   
   -- Or use multiIf as an alternative
   SELECT multiIf(type = 'a', 1, type = 'b', 2, 0) FROM my_table;
   

3. Use LIKE as an operator, not a function:

   -- Wrong
   SELECT like(name, '%test%') FROM users;
   
   -- Correct
   SELECT name LIKE '%test%' FROM users;
   

4. Write lambda expressions with arrow syntax:

   -- Lambdas use the arrow operator
   SELECT arrayMap(x -> x * 2, [1, 2, 3]);
   

5. For GLOBAL IN, use the SQL form:

   -- Correct: operator syntax
   SELECT * FROM distributed_table
   WHERE id GLOBAL IN (SELECT id FROM local_lookup);
   

6. Update your query builder. If application code generates these queries, fix the SQL generation layer to use operator syntax for special functions.

Best Practices

• Familiarize yourself with the set of special-syntax operations in ClickHouse: IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, LIKE, NOT LIKE, ILIKE, NOT ILIKE, BETWEEN, CASE, and lambda expressions.
• When building SQL programmatically, maintain a list of operators that require infix or special syntax rather than function-call syntax.
• Use multiIf() as a function-style alternative to CASE WHEN if you prefer function syntax in your code.
• For IN checks with small sets, consider hasAny() or has() array functions which use standard function syntax.

Frequently Asked Questions

Q: Why does ClickHouse treat these as special rather than regular functions?
A: These operations require special parsing rules in the SQL grammar. IN takes a set or subquery, CASE has multi-part syntax, and BETWEEN is a ternary operator. They cannot be represented cleanly as standard function calls.

Q: Can I use has() or hasAny() as alternatives to IN?
A: For checking membership in an array, yes. has([1,2,3], column) is equivalent to column IN (1,2,3) for small sets. But for subquery-based IN checks, you still need the IN operator.

Q: Is multiIf() a drop-in replacement for CASE?
A: For simple CASE WHEN expressions, multiIf() works well and uses regular function syntax: multiIf(cond1, result1, cond2, result2, default). It does not support the CASE expr WHEN val THEN form directly, but you can rewrite those as equality conditions.

Q: Does this error appear in the ClickHouse query log?
A: Yes, failed queries are logged in system.query_log with the exception message. You can search for FUNCTION_IS_SPECIAL there to find all occurrences.