ClickHouse DB::Exception: Empty list of columns queried

The "DB::Exception: Empty list of columns queried" error occurs when ClickHouse determines that a query would read zero columns from a table. This can happen when every column in the SELECT list is optimized away, when a subquery produces no columns, or when a table has no columns defined at all (which is unusual but possible with some engine types).

Impact

The query fails without returning any results. This error is relatively rare in normal usage but can surface in programmatically generated queries, views, or edge cases involving query optimization.

Common Causes

1. SELECT from a table with no columns — certain special engines or incorrectly created tables may have zero columns.
2. Programmatically generated queries that produce an empty column list — dynamic SQL builders that conditionally add columns may end up with nothing selected.
3. Subqueries that resolve to zero columns — an inner query that inadvertently produces no output columns.
4. Using EXCEPT or REPLACE that eliminates all columns — SELECT * EXCEPT(col1, col2) when col1 and col2 are the only columns in the table.
5. Materialized view with an empty SELECT — a misconfigured MV whose query resolves to nothing.

Troubleshooting and Resolution Steps

1. Check the table structure. Verify the table actually has columns:

   DESCRIBE TABLE your_database.your_table;
   

2. Review the SELECT list. Make sure at least one column or expression is being selected. If using EXCEPT, verify that it doesn't remove all columns.

3. Test subqueries independently. If your query includes subqueries, run them separately to confirm they return columns:

   -- Test the inner query alone
   SELECT ... FROM (your_subquery) LIMIT 1;
   

4. Add a dummy column if needed. For queries where you only care about row count (e.g., SELECT count() FROM table), ClickHouse usually handles this fine. But if you truly need a no-column query for some reason, selecting a constant works:

   SELECT 1 FROM your_table;
   

5. Fix dynamic query generation. If your application builds SQL dynamically, add a check to ensure the column list is never empty before executing the query.

6. Check views and MV definitions. If the error comes from a view, inspect its definition:

   SHOW CREATE TABLE your_view;
   

Best Practices

• Always validate dynamically generated SQL before execution, particularly the column list.
• When using SELECT * EXCEPT(...), verify that the EXCEPT clause does not remove every column.
• Test materialized views and regular views after creation to confirm they return data.
• Use SELECT 1 FROM table LIMIT 1 as a lightweight existence check rather than attempting a zero-column query.

Frequently Asked Questions

Q: Can I run SELECT count() FROM table if the table has columns?
A: Yes. SELECT count() FROM table works fine — ClickHouse internally reads the smallest column to count rows. The EMPTY_LIST_OF_COLUMNS_QUERIED error only fires when truly zero columns are resolved.

Q: Why would a table have zero columns?
A: This is extremely rare. It can happen with some external engine types or if a table was created incorrectly. Standard MergeTree tables always require at least one column.

Q: I'm using SELECT * EXCEPT and getting this error. What should I do?
A: Your EXCEPT clause is removing every column in the table. Either keep at least one column in the result or change the query to select specific columns instead of using EXCEPT.

Q: Can this error occur in a UNION ALL?
A: It could if one of the UNION branches resolves to zero columns. Each branch must produce at least one column.