ClickHouse DB::Exception: Bad collation

The "DB::Exception: Bad collation" error in ClickHouse appears when you specify a collation that ClickHouse does not recognize or support. The BAD_COLLATION error code is raised in ORDER BY clauses or column definitions that reference a collation name not available in the ICU library bundled with your ClickHouse build.

Impact

The query fails without returning results. No data is modified. The error prevents you from using locale-specific sorting until the collation is corrected or supported.

Common Causes

1. A misspelled collation name in an ORDER BY clause (for example, COLLATE 'en_US' instead of COLLATE 'en')
2. Using a collation that is not compiled into the ClickHouse ICU library
3. Attempting to apply a collation to a non-String column type
4. Copying collation syntax from another database system that uses different collation names
5. The ClickHouse build was compiled without ICU support

Troubleshooting and Resolution Steps

1. Check the exact collation name you are using. ClickHouse collations follow ICU locale conventions:

   SELECT * FROM my_table ORDER BY name COLLATE 'en';
   

2. List available collations in your ClickHouse installation:

   SELECT * FROM system.collations;
   

   Use one of the names from this list.

3. If the collation you need is not listed, try a broader locale. For example, use 'en' instead of 'en_US_POSIX'.

4. Ensure the column you are collating is a String type. Collations cannot be applied to numeric or date columns:

   SELECT name, type FROM system.columns
   WHERE database = 'my_database' AND table = 'my_table';
   

5. If you need locale-specific sorting and ICU is not available, consider preprocessing your data with a sort key column that normalizes the values for correct ordering.

6. Check whether your ClickHouse was built with ICU support:

   SELECT * FROM system.build_options WHERE name LIKE '%ICU%';
   

Best Practices

• Consult system.collations before using a collation to confirm it is available in your build.
• Use simple locale codes (like 'en', 'de', 'zh') rather than full locale strings from other database systems.
• Apply collations only when locale-aware sorting is genuinely needed, as collation-based sorting is slower than binary sorting.
• Document which collations your application relies on so you can verify they are available after ClickHouse upgrades.

Frequently Asked Questions

Q: Does ClickHouse support case-insensitive collations?
A: ClickHouse collations provided by ICU handle locale-aware ordering, which can include case sensitivity rules depending on the locale. For simple case-insensitive sorting, consider using lower() or upper() in your ORDER BY clause.

Q: Can I define a custom collation?
A: No. ClickHouse uses the ICU library's built-in collations and does not support user-defined collation rules.

Q: Why is collation not supported for LowCardinality(String)?
A: Collation should work with LowCardinality(String) columns in most ClickHouse versions. If you encounter issues, try casting the column to String in your ORDER BY clause as a workaround.