ILLEGAL_FINAL ClickHouse error¶

The ILLEGAL_FINAL error in ClickHouse (and Tinybird) happens when you try to use the FINAL keyword in a context where it's not supported. The FINAL keyword is used to get the latest version of rows in tables with certain engines like ReplacingMergeTree, but it has specific usage requirements.

What causes this error¶

You'll typically see it when:

• Using FINAL with table engines that don't support it
• Using FINAL in subqueries or CTEs
• Using FINAL with certain table engines
• Using FINAL in JOIN operations incorrectly
• Using FINAL in contexts where it's not allowed

Example errors¶

SELECT * FROM events FINAL WHERE user_id = 123

SELECT * FROM (SELECT * FROM events FINAL) t

SELECT * FROM users JOIN events FINAL ON users.id = events.user_id

SELECT FINAL * FROM events

How to fix it¶

Use FINAL with supported engines¶

Only use FINAL with engines that support it:

SELECT * FROM events_replacing FINAL WHERE user_id = 123

Remove FINAL from unsupported contexts¶

Don't use FINAL in subqueries or CTEs:

SELECT * FROM events WHERE user_id = 123

Use proper FINAL syntax¶

Use FINAL correctly in supported contexts:

SELECT * FROM events_replacing FINAL WHERE user_id = 123

Check table engine¶

Verify the table engine supports FINAL:

SELECT * FROM system.tables WHERE name = 'events'

Common patterns and solutions¶

Using FINAL with ReplacingMergeTree¶

FINAL works with ReplacingMergeTree:

SELECT * FROM events_replacing FINAL WHERE user_id = 123

Using FINAL with CollapsingMergeTree¶

FINAL works with CollapsingMergeTree:

SELECT * FROM events_collapsing FINAL WHERE user_id = 123

Using FINAL with VersionedCollapsingMergeTree¶

FINAL works with VersionedCollapsingMergeTree:

SELECT * FROM events_versioned FINAL WHERE user_id = 123

Using FINAL with GraphiteMergeTree¶

FINAL works with GraphiteMergeTree:

SELECT * FROM metrics_graphite FINAL WHERE timestamp >= now() - INTERVAL 1 HOUR

Advanced solutions¶

Using FINAL in complex queries¶

FINAL can be used in complex queries with supported engines:

SELECT 
    user_id,
    event_type,
    timestamp
FROM events_replacing FINAL
WHERE timestamp >= now() - INTERVAL 1 DAY
ORDER BY timestamp DESC

Using FINAL with aggregations¶

FINAL can be used with aggregate functions:

SELECT 
    user_id,
    COUNT(*) as event_count
FROM events_replacing FINAL
GROUP BY user_id

Using FINAL with window functions¶

FINAL can be used with window functions:

SELECT 
    user_id,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp DESC) as row_num
FROM events_replacing FINAL

Table engines that support FINAL¶

ReplacingMergeTree¶

For handling updates with version numbers:

CREATE TABLE events_replacing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    version UInt32
) ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, timestamp)

SELECT * FROM events_replacing FINAL

CollapsingMergeTree¶

For handling inserts and deletes:

CREATE TABLE events_collapsing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY (user_id, timestamp)

SELECT * FROM events_collapsing FINAL

VersionedCollapsingMergeTree¶

For handling versioned collapsing:

CREATE TABLE events_versioned (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    version UInt32,
    sign Int8
) ENGINE = VersionedCollapsingMergeTree(sign, version)
ORDER BY (user_id, timestamp)

SELECT * FROM events_versioned FINAL

Table engines that don't support FINAL¶

MergeTree¶

Standard MergeTree doesn't support FINAL:

SELECT * FROM events WHERE user_id = 123

SummingMergeTree¶

SummingMergeTree doesn't support FINAL:

SELECT * FROM events_summing WHERE user_id = 123

AggregatingMergeTree¶

AggregatingMergeTree doesn't support FINAL:

SELECT * FROM events_aggregating WHERE user_id = 123

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Using FINAL with Data Sources that don't support it
• Using FINAL in Pipes with unsupported table engines
• Using FINAL in Materialized Views incorrectly
• Using FINAL in API endpoints with wrong contexts

To debug in Tinybird:

1. Check your Data Source table engine for FINAL support
2. Verify Pipe operations don't use FINAL in unsupported contexts
3. Test FINAL usage in the Query Builder first
4. Use alternative approaches when FINAL isn't supported

Best practices¶

FINAL usage¶

• Only use FINAL with supported table engines
• Don't use FINAL in subqueries or CTEs
• Use FINAL when you need the latest version of rows
• Consider performance implications of using FINAL

Performance considerations¶

• FINAL can be expensive on large tables
• Consider using appropriate indexes
• Monitor query performance with FINAL
• Use FINAL only when necessary

Error prevention¶

• Check table engine before using FINAL
• Test FINAL usage with sample data
• Use alternative approaches when FINAL isn't supported
• Document FINAL usage requirements

See also¶

• ClickHouse Table Engines
• Working with Data Sources
• Common function errors