UNSUPPORTED_METHOD ClickHouse® error¶

The UNSUPPORTED_METHOD error in ClickHouse® (and Tinybird) happens when you try to use a method, operation, or feature that isn't supported by ClickHouse®. This commonly occurs when using SQL features from other database systems or trying to use ClickHouse® features that aren't available in your version.

What causes this error¶

You typically see it when:

• Using MySQL or PostgreSQL specific features in ClickHouse®
• Trying to use unsupported ClickHouse® features
• Using deprecated or removed feature
• Attempting operations not supported by your ClickHouse® version
• Using client-specific methods that aren't available

Example errors¶

SELECT * FROM events LIMIT 10 OFFSET 20

UPDATE events SET event_type = 'click' WHERE user_id = 123

SELECT * FROM events FOR UPDATE

UPDATE events SET event_type = 'click' WHERE user_id = 123

How to fix it¶

Use ClickHouse®-compatible syntax¶

Replace unsupported features with ClickHouse® equivalents:

SELECT * FROM events LIMIT 20 OFFSET 10

Use supported operations¶

Replace unsupported operations with supported alternatives:

INSERT INTO events (user_id, event_type) VALUES (123, 'click')

Check ClickHouse® documentation¶

Verify feature support in your ClickHouse® version:

SELECT version()

Use alternative approaches¶

Find alternative ways to achieve the same result:

INSERT INTO events (user_id, event_type) VALUES (123, 'click')

Common patterns and solutions¶

Pagination¶

ClickHouse® pagination syntax:

SELECT * FROM events ORDER BY timestamp DESC LIMIT 10 OFFSET 20

Data modification¶

ClickHouse® doesn't support UPDATE/DELETE in the same way as other databases:

-- Use INSERT with deduplication
INSERT INTO events_new SELECT * FROM events WHERE user_id != 123
INSERT INTO events_new (user_id, event_type) VALUES (123, 'click')

Joins¶

Supported join types in ClickHouse®:

SELECT * FROM users INNER JOIN events ON users.id = events.user_id
SELECT * FROM users LEFT JOIN events ON users.id = events.user_id
SELECT * FROM users RIGHT JOIN events ON users.id = events.user_id
SELECT * FROM users FULL OUTER JOIN events ON users.id = events.user_id  -- Available in recent versions

Transactions¶

ClickHouse® has limited transaction support:

-- Use atomic operations where possible
-- Consider using Materialized Views for complex updates

Advanced solutions¶

Using Materialized Views¶

For complex data modifications:

CREATE MATERIALIZED VIEW events_updated AS
SELECT
    user_id,
    CASE WHEN user_id = 123 THEN 'click' ELSE event_type END as event_type,
    timestamp
FROM events

Using INSERT with deduplication¶

For data replacement:

INSERT INTO events_new
SELECT
    user_id,
    CASE WHEN user_id = 123 THEN 'click' ELSE event_type END as event_type,
    timestamp
FROM events

Using window functions¶

For complex aggregations:

SELECT
    user_id,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) as row_num
FROM events

ClickHouse®-specific features¶

Using ReplacingMergeTree¶

For handling updates:

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

Using CollapsingMergeTree¶

For handling deletes:

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

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Using unsupported ClickHouse® features in Pipes
• Trying to use features not available in Tinybird's ClickHouse® version
• Using client-specific methods that aren't supported
• Working with Materialized Views that use unsupported operations

To debug in Tinybird:

1. Check Tinybird's ClickHouse® version and supported features
2. Use the Query Builder to test operations
3. Consult Tinybird documentation for supported features
4. Use alternative approaches for unsupported operations

Best practices¶

Feature compatibility¶

• Check ClickHouse® documentation before using new features
• Test operations in development environment first
• Use version-specific feature checks
• Keep up with ClickHouse® updates and changes

Alternative approaches¶

• Use Materialized Views for complex data modifications
• Implement logic in app layer when needed
• Use INSERT with deduplication for updates
• Consider using specialized table engines

Error handling¶

• Implement proper error handling for unsupported features
• Have fallback approaches ready
• Log unsupported method attempts
• Document workarounds for common issues

See also¶

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