ClickHouse DB::Exception: Incompatible type of JOIN

The "DB::Exception: Incompatible type of JOIN" error in ClickHouse occurs when the specified JOIN type cannot be used with the target table engine, join algorithm, or data layout. Not every JOIN variant is supported in every context, and ClickHouse raises the INCOMPATIBLE_TYPE_OF_JOIN error to indicate that the combination you requested is invalid.

Impact

The query is rejected during the planning stage, so no data is read or returned. If used in a materialized view or an INSERT ... SELECT pipeline, the entire operation will fail.

Common Causes

1. Using a JOIN type not supported by the Join table engine -- when a table is created with ENGINE = Join(type, strictness, keys), only the join type and strictness declared at creation time can be used in queries against that table.
2. FULL JOIN not supported by the selected algorithm -- the default hash join does not support FULL OUTER JOIN. You need to use a compatible algorithm.
3. ASOF JOIN used with an incompatible engine or algorithm -- ASOF JOIN has specific requirements and is not universally supported across all join algorithms.
4. RIGHT JOIN not available with certain algorithms -- some join implementations only support LEFT and INNER joins.
5. Distributed table join restrictions -- joining distributed tables with certain join types may not be supported depending on the cluster configuration.

Troubleshooting and Resolution Steps

1. Check the Join table engine declaration. If you are querying a Join engine table, the join type must match:

   -- Table created for ANY LEFT join
   CREATE TABLE join_table (id UInt32, name String)
   ENGINE = Join(ANY, LEFT, id);
   
   -- This works
   SELECT * FROM main ANY LEFT JOIN join_table USING (id);
   
   -- This fails: incompatible join type
   SELECT * FROM main INNER JOIN join_table USING (id);
   

2. Switch the join algorithm for FULL JOIN support:

   SET join_algorithm = 'hash';
   -- hash supports FULL JOIN in recent ClickHouse versions
   SELECT * FROM a FULL JOIN b ON a.id = b.id;
   
   -- alternatively
   SET join_algorithm = 'full_sorting_merge';
   SELECT * FROM a FULL JOIN b ON a.id = b.id;
   

3. Verify your ClickHouse version supports the join type. Some join type + algorithm combinations were added in later versions:

   SELECT version();
   

4. Rewrite the query to use a supported join type. For example, replace a FULL JOIN with a combination of LEFT JOIN and anti-join using UNION ALL:

   SELECT a.*, b.value FROM a LEFT JOIN b ON a.id = b.id
   UNION ALL
   SELECT a.*, b.value FROM b LEFT JOIN a ON b.id = a.id WHERE a.id IS NULL;
   

5. For ASOF JOIN, ensure you use the correct algorithm:

   SET join_algorithm = 'hash';
   SELECT * FROM trades ASOF LEFT JOIN quotes
   ON trades.symbol = quotes.symbol AND trades.ts >= quotes.ts;
   

Best Practices

• When creating Join engine tables, plan ahead for how the table will be queried and set the type and strictness accordingly.
• Test join algorithm compatibility in a development environment before deploying complex join queries to production.
• Prefer LEFT JOIN or INNER JOIN for maximum compatibility across join algorithms and engines.
• Keep ClickHouse up to date to benefit from expanded join type support in newer versions.

Frequently Asked Questions

Q: What join types does the Join table engine support?
A: The Join engine supports the join type and strictness specified at creation time (e.g., ANY LEFT, ALL INNER). You cannot use a different type or strictness when querying the table.

Q: Can I use FULL OUTER JOIN in ClickHouse?
A: Yes, FULL OUTER JOIN is supported but not by all join algorithms. The hash algorithm supports it in recent versions. If you encounter this error, try setting join_algorithm = 'hash' or 'full_sorting_merge'.

Q: Why does my RIGHT JOIN fail with partial_merge?
A: The partial_merge join algorithm only supports LEFT and INNER joins. Switch to hash or full_sorting_merge for RIGHT JOIN support.

Q: Is CROSS JOIN affected by this error?
A: Generally no. CROSS JOIN produces a Cartesian product and does not depend on join strictness or algorithm in the same way. However, it is almost never used with the Join table engine.