ClickHouse DB::Exception: Illegal projection

The "DB::Exception: Illegal projection" error in ClickHouse occurs when a projection definition in a MergeTree table is invalid. Projections are pre-computed aggregations or alternative sort orders stored alongside the main data, and they must follow specific rules regarding their SELECT query structure. The error code is ILLEGAL_PROJECTION.

Impact

The CREATE TABLE or ALTER TABLE statement containing the invalid projection is rejected. If the projection is part of a CREATE TABLE, the table is not created. Existing tables and their data are unaffected by this error.

Common Causes

1. Using unsupported SQL features in the projection query -- projections support a limited subset of SQL. Features like JOINs, subqueries, window functions, and UNION are not allowed.
2. Projection SELECT without GROUP BY when using aggregate functions -- if the projection uses aggregates, it must include a GROUP BY clause.
3. Referencing columns not present in the parent table -- the projection query can only reference columns that exist in the table.
4. Using non-deterministic functions -- functions like now(), rand(), or currentDatabase() are not allowed in projections.
5. HAVING or ORDER BY in aggregate projections -- these clauses are not supported within projection definitions.
6. Projection name conflicts -- using a name already taken by another projection on the same table.

Troubleshooting and Resolution Steps

1. Simplify the projection to a supported form. Projections support two patterns:

   -- Pattern 1: Alternative sort order (normal projection)
   PROJECTION proj_by_date (
       SELECT * ORDER BY event_date
   )
   
   -- Pattern 2: Pre-aggregated projection
   PROJECTION proj_agg (
       SELECT
           user_id,
           count() AS cnt,
           sum(amount) AS total
       GROUP BY user_id
   )
   

2. Remove unsupported clauses:

   -- Wrong: HAVING is not supported
   PROJECTION proj_bad (
       SELECT user_id, count() AS cnt
       GROUP BY user_id
       HAVING cnt > 10
   )
   
   -- Correct: filter in the query that uses the projection
   PROJECTION proj_good (
       SELECT user_id, count() AS cnt
       GROUP BY user_id
   )
   

3. Ensure all referenced columns exist in the table:

   -- Check table columns
   DESCRIBE TABLE your_table;
   

4. Remove JOINs and subqueries from the projection query:

   -- Wrong: JOIN not allowed
   PROJECTION proj_bad (
       SELECT a.id, b.name FROM a JOIN b ON a.id = b.id
   )
   
   -- Correct: projections only reference the parent table
   PROJECTION proj_good (
       SELECT id, name ORDER BY name
   )
   

5. Replace non-deterministic functions with deterministic alternatives:

   -- Wrong: non-deterministic
   PROJECTION proj_bad (
       SELECT *, now() AS created_at ORDER BY created_at
   )
   
   -- Correct: use an existing column
   PROJECTION proj_good (
       SELECT * ORDER BY event_time
   )
   

Best Practices

• Keep projections simple -- use them for alternative sort orders or straightforward aggregations.
• Test projection definitions separately before including them in production table definitions.
• Limit the number of projections per table since each projection adds storage and write overhead.
• Use SELECT * for normal (non-aggregate) projections to ensure all columns are available.
• Monitor projection storage overhead using system.parts and system.projection_parts.

Frequently Asked Questions

Q: Can projections include WHERE clauses?
A: No. Projections do not support WHERE clauses in their definition. The projection stores data for all rows, and filtering is applied at query time when ClickHouse decides whether to use the projection.

Q: How do projections differ from materialized views?
A: Projections are stored within the same table and are automatically maintained during inserts. Materialized views are separate tables with their own storage. Projections are simpler but more limited; materialized views support the full range of SQL features.

Q: Can I use DISTINCT in a projection?
A: No. DISTINCT is not supported in projection definitions. Use GROUP BY to achieve deduplication semantics.

Q: Will ClickHouse automatically use my projection?
A: Yes, if the query optimizer determines that the projection can satisfy the query more efficiently than the base table. You can check whether a projection was used by examining the query plan with EXPLAIN.