ClickHouse DB::Exception: Ambiguous identifier

The "DB::Exception: Ambiguous identifier" error in ClickHouse arises when a column name used in a query could refer to columns from more than one table. This commonly happens in JOIN queries where both tables have a column with the same name and no table qualifier is provided. ClickHouse raises the AMBIGUOUS_IDENTIFIER error because it cannot determine which table's column you intend to reference.

Impact

The query fails during analysis, and no results are returned. The error message typically names the ambiguous column, making it straightforward to fix. However, in dynamically generated queries or ORMs, this can require changes to the query-building logic.

Common Causes

1. Same column name in both joined tables -- columns like id, name, status, or created_at often exist in multiple tables. Referencing them without a table alias in a join creates ambiguity.
2. SELECT * with overlapping column names -- using SELECT * in a join where both tables share column names.
3. Missing table alias in WHERE, GROUP BY, or ORDER BY -- even if the SELECT clause is unambiguous, other clauses may reference columns without qualification.
4. Self-joins without proper aliasing -- joining a table to itself requires distinct aliases, and every column reference must be qualified.
5. Subqueries exposing columns with generic names -- when subqueries produce columns like value or count that clash with columns from other tables.

Troubleshooting and Resolution Steps

1. Qualify the ambiguous column with its table alias:

   -- Ambiguous: 'id' exists in both tables
   SELECT id, name FROM users u JOIN orders o ON u.id = o.user_id;
   
   -- Fixed: qualified column references
   SELECT u.id, u.name FROM users u JOIN orders o ON u.id = o.user_id;
   

2. Use table aliases consistently throughout the entire query:

   SELECT u.id, u.name, o.amount
   FROM users AS u
   JOIN orders AS o ON u.id = o.user_id
   WHERE u.status = 'active'    -- qualify in WHERE
   ORDER BY o.created_at DESC;  -- qualify in ORDER BY
   

3. Avoid SELECT * in join queries. Explicitly list the columns you need:

   -- Problematic with joins
   SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
   
   -- Better: explicit columns
   SELECT u.id, u.name, o.order_id, o.amount
   FROM users u JOIN orders o ON u.id = o.user_id;
   

4. For self-joins, assign distinct aliases and qualify every column:

   SELECT e.name AS employee, m.name AS manager
   FROM employees AS e
   JOIN employees AS m ON e.manager_id = m.id;
   

5. Rename columns in subqueries to avoid collisions:

   SELECT a.id, s.total_orders
   FROM accounts AS a
   JOIN (
       SELECT user_id, count() AS total_orders FROM orders GROUP BY user_id
   ) AS s ON a.id = s.user_id;
   

Best Practices

• Always assign short, meaningful aliases to tables in join queries and qualify every column reference.
• Avoid SELECT * in production queries, especially those involving joins.
• Establish naming conventions that reduce column name collisions (e.g., prefixing with the entity name: user_id, order_id).
• When writing query builders or ORM code, automatically qualify column names with table aliases.

Frequently Asked Questions

Q: Does USING automatically resolve ambiguity for the join column?
A: Yes. When you use JOIN ... USING (column), ClickHouse treats the join column as a single column from both tables and does not report ambiguity for that specific column. However, other shared columns not in the USING clause can still be ambiguous.

Q: How is AMBIGUOUS_IDENTIFIER different from AMBIGUOUS_COLUMN_NAME?
A: Both errors relate to name resolution ambiguity, but AMBIGUOUS_IDENTIFIER is the more general error used in the newer query analyzer. AMBIGUOUS_COLUMN_NAME is a similar error from the legacy analyzer. The resolution is the same: qualify column names with table aliases.

Q: Can I use the database.table.column syntax instead of aliases?
A: Yes, you can use fully qualified names like mydb.users.id, but table aliases are shorter and more readable. Aliases also work with subqueries, which do not have database-level names.

Q: Why does this error appear when I use GROUP BY or ORDER BY?
A: Column references must be unambiguous everywhere in the query, not just in the SELECT clause. If you GROUP BY or ORDER BY a column that exists in multiple joined tables, you must qualify it with a table alias.