MULTIPLE_EXPRESSIONS_FOR_ALIAS ClickHouse error¶

The MULTIPLE_EXPRESSIONS_FOR_ALIAS error in ClickHouse (and Tinybird) happens when you try to use the same alias name for multiple expressions in a SELECT clause. Each column alias must be unique within a query to avoid ambiguity.

What causes this error¶

You'll typically see it when:

• Using the same alias for multiple columns in SELECT
• Having duplicate alias names in subqueries
• Using the same alias in UNION queries
• Having conflicting aliases in JOIN queries
• Using reserved words as aliases
• Collisions between columns and functions (e.g., col AS value and count(*) AS value)

Example errors¶

SELECT user_id as id, event_id as id FROM events

SELECT user_id as id FROM events
UNION ALL
SELECT user_id as id FROM events

SELECT u.id as id, e.event_type as id FROM users u JOIN events e ON u.id = e.user_id

SELECT * FROM (
    SELECT user_id as id, event_id as id FROM events
) t

How to fix it¶

Use unique alias names¶

Give each column a unique alias:

SELECT user_id as user_id, event_id as event_id FROM events

Use descriptive aliases¶

Make aliases more descriptive to avoid conflicts:

SELECT user_id as user_identifier, event_id as event_identifier FROM events

Use table prefixes¶

Prefix aliases with table names:

SELECT u.id as user_id, e.id as event_id 
FROM users u JOIN events e ON u.id = e.user_id

Remove unnecessary aliases¶

If aliases aren't needed, remove them:

SELECT user_id, event_id FROM events

Common patterns and solutions¶

Simple column selection¶

When selecting columns with aliases:

SELECT 
    user_id as user_identifier,
    event_type as event_category,
    timestamp as event_time
FROM events

Aggregated columns¶

When using aggregate functions:

SELECT 
    user_id,
    COUNT(*) as event_count,
    SUM(value) as total_value,
    AVG(value) as average_value
FROM events
GROUP BY user_id

JOIN queries¶

When joining tables with similar column names:

SELECT 
    u.id as user_id,
    u.name as user_name,
    e.id as event_id,
    e.event_type as event_category
FROM users u
JOIN events e ON u.id = e.user_id

UNION queries¶

When combining results from multiple queries:

SELECT user_id as user_identifier, 'user' as source FROM users
UNION ALL
SELECT event_id as user_identifier, 'event' as source FROM events

Advanced solutions¶

Using CTEs with aliases¶

When using Common Table Expressions:

WITH user_stats AS (
    SELECT 
        user_id as user_identifier,
        COUNT(*) as event_count
    FROM events
    GROUP BY user_id
),
event_stats AS (
    SELECT 
        event_type as event_category,
        COUNT(*) as type_count
    FROM events
    GROUP BY event_type
)
SELECT 
    us.user_identifier,
    us.event_count,
    es.event_category,
    es.type_count
FROM user_stats us
CROSS JOIN event_stats es

Subqueries with aliases¶

When using subqueries:

SELECT 
    u.id as user_id,
    u.name as user_name,
    (SELECT COUNT(*) FROM events e WHERE e.user_id = u.id) as user_event_count
FROM users u

Window functions with aliases¶

When using window functions:

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

Best practices¶

Naming conventions¶

• Use descriptive, unique alias names
• Avoid generic names like id, name, value
• Use table prefixes when joining multiple tables
• Be consistent with naming patterns

Query structure¶

• Plan your aliases before writing complex queries
• Use meaningful names that describe the data
• Avoid reserved words as aliases
• Test queries with simple aliases first

Documentation¶

• Document your alias naming conventions
• Use consistent patterns across your codebase
• Comment complex queries with alias explanations
• Maintain a glossary of common aliases

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Building Pipes with conflicting column names
• Using Materialized Views with duplicate aliases
• Working with nested Pipes that have similar schemas
• Creating API endpoints with conflicting response fields

To debug in Tinybird:

1. Check your Pipe node outputs for duplicate column names
2. Verify Materialized View schemas for conflicts
3. Use descriptive column names in your Pipes
4. Test Pipe outputs with simple queries first

See also¶

• ClickHouse SELECT Statement
• Working with Pipes
• Common function errors