ClickHouse DB::Exception: Incorrect index definition

The "DB::Exception: Incorrect index definition" error in ClickHouse is triggered when a data skipping index definition is structurally invalid. Unlike ILLEGAL_INDEX which deals with type compatibility, the INCORRECT_INDEX error typically points to missing parameters, syntax issues, or malformed index specifications in the CREATE TABLE or ALTER TABLE statement.

Impact

The statement containing the malformed index definition is rejected. If it is part of a CREATE TABLE, the table is not created. If it is an ALTER TABLE ADD INDEX, the existing table remains unchanged. No data is lost or affected.

Common Causes

1. Missing required index parameters -- some index types require specific parameters (e.g., set requires a max rows argument).
2. Incorrect parameter count or types -- providing the wrong number of parameters to an index type constructor.
3. Missing GRANULARITY clause -- while GRANULARITY has a default, explicit specification is recommended and some configurations may require it.
4. Duplicate index names -- creating an index with a name that already exists on the table.
5. Invalid expression in the index definition -- using a function or expression that is not supported in index expressions.
6. Syntax errors in the index clause -- missing commas, parentheses, or keywords.

Troubleshooting and Resolution Steps

1. Review the index syntax carefully. The general form is:

   INDEX index_name expression TYPE index_type(params) GRANULARITY n
   

2. Check required parameters for each index type:

   -- minmax: no parameters needed
   INDEX idx col TYPE minmax GRANULARITY 4
   
   -- set: max_rows parameter
   INDEX idx col TYPE set(100) GRANULARITY 4
   
   -- bloom_filter: false positive rate
   INDEX idx col TYPE bloom_filter(0.01) GRANULARITY 4
   
   -- ngrambf_v1: 4 parameters (ngram_size, filter_size, hash_count, seed)
   INDEX idx col TYPE ngrambf_v1(3, 256, 2, 0) GRANULARITY 4
   
   -- tokenbf_v1: 3 parameters (filter_size, hash_count, seed)
   INDEX idx col TYPE tokenbf_v1(256, 2, 0) GRANULARITY 4
   

3. Check for duplicate index names on the table:

   SELECT name, type_full, expr, granularity
   FROM system.data_skipping_indices
   WHERE database = 'default' AND table = 'your_table';
   

4. Fix parameter type issues. Ensure numeric parameters are integers where required:

   -- Wrong: float where integer expected
   INDEX idx col TYPE set(100.5) GRANULARITY 4
   
   -- Correct
   INDEX idx col TYPE set(100) GRANULARITY 4
   

5. Verify the expression is valid in an index context:

   -- Test the expression
   SELECT lower(name) FROM your_table LIMIT 1;
   
   -- Then use in index
   INDEX idx_lower_name lower(name) TYPE tokenbf_v1(256, 2, 0) GRANULARITY 4
   

Best Practices

• Refer to the ClickHouse documentation for the exact parameter specifications of each index type.
• Use descriptive index names that indicate the column and type (e.g., idx_status_bloom, idx_timestamp_minmax).
• Validate index definitions in development before applying to production tables.
• Keep track of existing indexes on each table to avoid name collisions.
• When in doubt, start with simpler index types like minmax and add more specialized indexes as query patterns emerge.

Frequently Asked Questions

Q: What is the default GRANULARITY if I omit it?
A: The default granularity depends on the ClickHouse version but is typically 1. It is best practice to always specify it explicitly.

Q: Can I have multiple indexes on the same column?
A: Yes, you can define multiple indexes on the same column with different types and names. For example, a minmax index for range queries and a bloom_filter for equality checks.

Q: How do I fix a malformed index on an existing table?
A: Drop the incorrect index with ALTER TABLE ... DROP INDEX index_name and then add it again with the correct definition using ALTER TABLE ... ADD INDEX.

Q: Does the order of indexes in a CREATE TABLE matter?
A: No, the order of index definitions does not affect functionality. ClickHouse evaluates all applicable indexes during query planning regardless of their definition order.