UNKNOWN_FUNCTION ClickHouse error¶

The UNKNOWN_FUNCTION error in ClickHouse (and Tinybird) happens when you try to use a function that doesn't exist in the ClickHouse function library. This can occur due to typos, using functions from other databases, or calling functions that aren't available in your ClickHouse version.

What causes this error¶

You'll typically see it when:

• Misspelling function names
• Using functions from other database systems (MySQL, PostgreSQL, etc.)
• Calling functions that don't exist in ClickHouse
• Using functions available in newer ClickHouse versions
• Using custom functions that haven't been created

Example errors¶

SELECT magic_function(user_id) FROM events

SELECT nonexistent_function(user_id) FROM events

SELECT magic_function(user_id) FROM events

SELECT invalid_function(user_id) FROM events

How to fix it¶

Check function spelling¶

Verify the correct function name:

SELECT substring('hello world', 1, 5)

Use ClickHouse equivalents¶

Find the ClickHouse equivalent for functions from other databases:

SELECT concat('a', ',', 'b', ',', 'c')

Check function availability¶

Verify the function exists in your ClickHouse version:

SELECT * FROM system.functions WHERE name LIKE '%string%'

Use proper case¶

ClickHouse functions are case-sensitive:

SELECT substring('hello world', 1, 5)

Common patterns and solutions¶

String functions¶

Common string function equivalents:

SELECT substring('hello world', 1, 5)  -- Extract substring
SELECT replace('hello world', 'world', 'clickhouse')  -- Replace text
SELECT concat('hello', ' ', 'world')  -- Concatenate strings
SELECT trim('  hello  ')  -- Remove whitespace

Date and time functions¶

Date function equivalents:

SELECT toDate('2023-01-01')  -- Convert to date
SELECT toDateTime('2023-01-01 12:00:00')  -- Convert to datetime
SELECT now()  -- Current timestamp
SELECT formatDateTime(now(), '%Y-%m-%d')  -- Format datetime

Mathematical functions¶

Math function equivalents:

SELECT abs(-10)  -- Absolute value
SELECT round(3.14159, 2)  -- Round number
SELECT power(2, 3)  -- Raise to power
SELECT sqrt(16)  -- Square root

Aggregate functions¶

Aggregate function examples:

SELECT count(*) FROM events  -- Count rows
SELECT sum(value) FROM events  -- Sum values
SELECT avg(value) FROM events  -- Average values
SELECT max(timestamp) FROM events  -- Maximum value

Function categories and equivalents¶

String manipulation¶

Date and time¶

Mathematical¶

Advanced solutions¶

Using function aliases¶

Some functions have multiple names:

SELECT substring('hello', 1, 3)  -- Same as substr
SELECT concat('a', 'b')  -- Same as concat_ws with empty separator

Custom functions¶

Create custom functions if needed:

-- This would require creating a custom function
-- SELECT custom_function(column) FROM table

Function composition¶

Combine multiple functions for complex operations:

SELECT upper(trim(concat('  hello  ', ' world'))) as result

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Using functions not available in Tinybird's ClickHouse version
• Calling custom functions that haven't been implemented
• Using functions from other database systems
• Working with Materialized Views that use unavailable functions

To debug in Tinybird:

1. Check the ClickHouse function documentation
2. Test functions in the Query Builder first
3. Verify function availability in your Tinybird environment
4. Use alternative functions when needed

Function discovery¶

Use these commands to discover available functions:

SELECT name FROM system.functions ORDER BY name

SELECT name FROM system.functions WHERE name LIKE '%string%'

Best practices¶

Function verification¶

• Test functions with simple examples first
• Check function documentation before use
• Verify function availability in your environment
• Use function autocomplete in your IDE

Error handling¶

• Implement proper error handling for function calls
• Use try() functions for potentially problematic operations
• Log function errors for debugging
• Have fallback functions ready

Performance considerations¶

• Use built-in functions when possible
• Avoid complex function compositions
• Test function performance on large datasets
• Use appropriate function types for your data

See also¶

• ClickHouse Functions
• String Functions
• Date and Time Functions
• Common function errors