---
title: UNKNOWN_FUNCTION ClickHouse error
meta:
  description: Learn how to fix the UNKNOWN_FUNCTION error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# UNKNOWN_FUNCTION ClickHouse error

{% callout %}
This error usually means you're calling a function that doesn't exist in ClickHouse. Check the function name spelling and verify it's available in your ClickHouse version.
{% /callout %}

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

```sql {% title="Fails: non-existent function" %}
SELECT magic_function(user_id) FROM events
```

```sql {% title="Fails: non-existent function" %}
SELECT nonexistent_function(user_id) FROM events
```

```sql {% title="Fails: non-existent function" %}
SELECT magic_function(user_id) FROM events
```

```sql {% title="Fails: non-existent function" %}
SELECT invalid_function(user_id) FROM events
```

## How to fix it

### Check function spelling

Verify the correct function name:

```sql {% title="Fix: correct spelling" %}
SELECT substring('hello world', 1, 5)
```

### Use ClickHouse equivalents

Find the ClickHouse equivalent for functions from other databases:

```sql {% title="Fix: use ClickHouse concat" %}
SELECT concat('a', ',', 'b', ',', 'c')
```

### Check function availability

Verify the function exists in your ClickHouse version:

```sql {% title="Check available functions" %}
SELECT * FROM system.functions WHERE name LIKE '%string%'
```

### Use proper case

ClickHouse functions are case-sensitive:

```sql {% title="Fix: correct case" %}
SELECT substring('hello world', 1, 5)
```

{% callout type="caution" %}
Functions like `substring()` are case-sensitive. Always use lowercase function names in ClickHouse.
{% /callout %}

## Common patterns and solutions

### String functions

Common string function equivalents:

```sql {% title="String function examples" %}
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:

```sql {% title="Date function examples" %}
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:

```sql {% title="Math function examples" %}
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:

```sql {% title="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

| Other DB | ClickHouse | Description |
|----------|------------|-------------|
| `SUBSTR` | `substring` | Extract substring |
| `CONCAT_WS` | `concat` | Concatenate with separator |
| `TRIM` | `trim` | Remove whitespace |
| `UPPER` | `upper` | Convert to uppercase |
| `LOWER` | `lower` | Convert to lowercase |

### Date and time

| Other DB | ClickHouse | Description |
|----------|------------|-------------|
| `DATE_FORMAT` | `formatDateTime` | Format datetime (use `%Y-%m-%d` format) |
| `DATEDIFF` | `dateDiff` | Date difference |
| `DATE_ADD` | `addDays` | Add days to date |
| `NOW()` | `now()` | Current timestamp |

### Mathematical

| Other DB | ClickHouse | Description |
|----------|------------|-------------|
| `ABS` | `abs` | Absolute value |
| `ROUND` | `round` | Round number |
| `POWER` | `power` | Raise to power |
| `SQRT` | `sqrt` | Square root |

## Advanced solutions

### Using function aliases

Some functions have multiple names:

```sql {% title="Function aliases" %}
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:

```sql {% title="Custom function example" %}
-- This would require creating a custom function
-- SELECT custom_function(column) FROM table
```

### Function composition

Combine multiple functions for complex operations:

```sql {% title="Function composition" %}
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:

```sql {% title="List all functions" %}
SELECT name FROM system.functions ORDER BY name
```

```sql {% title="Search for specific functions" %}
SELECT name FROM system.functions WHERE name LIKE '%string%'
```

{% callout type="tip" %}
New functions may require enabling `allow_experimental_*` settings.
{% /callout %}

## 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](/sql-reference/functions)
* [String Functions](/sql-reference/functions/string-functions)
* [Date and Time Functions](/sql-reference/functions/date-time-functions)
* [Common function errors](/sql-reference/clickhouse-errors/NUMBER_OF_ARGUMENTS_DOESNT_MATCH)