ClickHouse DB::Exception: Column queried more than once

The "DB::Exception: Column queried more than once" error appears when a query references the same column more than once in a context where ClickHouse requires unique column references. This is a stricter check than DUPLICATE_COLUMN — it fires during analysis when the same column is listed twice in a position that must be deduplicated, such as the column list of a DESCRIBE or similar metadata operation.

Impact

The query is rejected at parse or analysis time. It doesn't cause data corruption or lasting issues — you simply need to fix the query to remove the duplicate reference.

Common Causes

1. Listing the same column twice in a SELECT without aliases — SELECT id, id FROM table produces two columns with the same name.
2. Duplicate columns in an INSERT column list — INSERT INTO table (col1, col1) VALUES (...).
3. Repeated column references in GROUP BY or ORDER BY — while often harmless, some contexts reject duplicates.
4. Programmatically generated queries — query builders that accidentally add the same column twice.
5. Copy-paste errors in complex queries — duplicating a column expression during query editing.

Troubleshooting and Resolution Steps

1. Read the error message to identify the duplicated column name.

2. Remove the duplicate from the SELECT list or alias one of the occurrences:

   -- Instead of:
   SELECT id, id FROM your_table;
   
   -- Use:
   SELECT id FROM your_table;
   -- Or alias if you need two references:
   SELECT id, id AS id_copy FROM your_table;
   

3. Fix INSERT column lists. Each column should appear only once:

   -- Wrong
   INSERT INTO your_table (col1, col1) VALUES (1, 2);
   
   -- Correct
   INSERT INTO your_table (col1) VALUES (1);
   

4. Review dynamically generated SQL. Add deduplication logic in your query builder to ensure no column appears twice.

5. Check for implicit duplicates. When using SELECT *, extra_col and extra_col is already in the table, you get a duplicate. Use SELECT * EXCEPT(extra_col), modified_expression AS extra_col instead.

Best Practices

• Avoid SELECT * combined with additional named columns that may already exist in the table.
• Use explicit column lists in both SELECT and INSERT statements.
• When generating SQL programmatically, deduplicate the column list before building the query string.
• Use SELECT * EXCEPT(col), new_expr AS col when you want to override a single column from a wildcard select.

Frequently Asked Questions

Q: Is referencing the same column twice in WHERE clauses a problem?
A: No. You can reference a column multiple times in WHERE, HAVING, and expressions freely. The error only applies to contexts where the output column list must be unique, like SELECT output or INSERT target columns.

Q: Does this error apply to GROUP BY?
A: Listing the same column twice in GROUP BY is typically harmless (ClickHouse deduplicates it). The error is specific to contexts that require unique names in the result set.

Q: How do I select the same column twice with different transformations?
A: Use aliases to give each occurrence a unique name:

SELECT
    toDate(timestamp) AS date,
    toHour(timestamp) AS hour
FROM your_table;

Q: My query builder keeps producing this error. What's the best fix?
A: Add a deduplication step that tracks column names and either removes duplicates or auto-aliases them before emitting the final SQL string.