ClickHouse DB::Exception: Unknown distributed product mode

The UNKNOWN_DISTRIBUTED_PRODUCT_MODE error is triggered when the distributed_product_mode setting is set to a value that ClickHouse does not recognize. The message reads DB::Exception: Unknown distributed product mode: 'invalid_value'. This setting controls how ClickHouse handles subqueries involving distributed tables in IN and JOIN clauses, and it only accepts a handful of specific values.

Impact

The query or session that sets the invalid value will fail immediately. If the value is set in a user profile, all queries that involve distributed subqueries for the affected users will be impacted. This can break analytical queries that join distributed tables or use subqueries with IN clauses -- a very common pattern in multi-shard ClickHouse deployments.

Common Causes

1. Typo in the setting value -- Writing 'globals' instead of 'global', or 'locals' instead of 'local'.
2. Using a value from another setting -- Confusing values from settings like load_balancing or overflow_mode.
3. Outdated documentation or examples -- Referencing a blog post or example that uses a non-existent value.
4. Case sensitivity -- The values are case-sensitive and must match exactly.

Troubleshooting and Resolution Steps

1. Review the valid distributed_product_mode values: ClickHouse supports the following modes:

   • 'deny' -- (Default) Prohibit the use of distributed tables in subqueries within IN and JOIN clauses. ClickHouse throws an error if it detects this pattern, forcing you to explicitly rewrite the query.
   • 'local' -- Replace the distributed table in the subquery with the local table on each shard. Each shard executes the subquery against its own local data. This is efficient but may produce incomplete results if the subquery data is not fully replicated.
   • 'global' -- Replace the subquery with a GLOBAL IN or GLOBAL JOIN. ClickHouse executes the subquery on the initiator node, materializes the result into a temporary table, and distributes it to all shards. This ensures correct results but can be expensive for large subquery results.
   • 'allow' -- Allow the subquery with a distributed table as-is, without any automatic rewriting. Use this only if you understand the implications and have structured your data to handle it correctly.

2. Set the correct value:

   -- Check the current value
   SELECT name, value FROM system.settings WHERE name = 'distributed_product_mode';
   
   -- Set a valid value
   SET distributed_product_mode = 'global';
   

3. Fix configuration files if needed:

   grep -rn 'distributed_product_mode' /etc/clickhouse-server/users.xml /etc/clickhouse-server/users.d/
   

   Update the value:

   <profiles>
       <default>
           <distributed_product_mode>deny</distributed_product_mode>
       </default>
   </profiles>
   

4. Understand the query pattern that triggered this: The distributed_product_mode setting is only relevant when you have a subquery on a distributed table inside an IN or JOIN:

   -- This pattern is what the setting controls
   SELECT * FROM distributed_table_a
   WHERE id IN (SELECT id FROM distributed_table_b);
   
   -- Or with JOIN
   SELECT * FROM distributed_table_a
   JOIN distributed_table_b ON a.id = b.id;
   

5. Choose the right mode for your use case:

   -- If you want correct results and the subquery is small:
   SET distributed_product_mode = 'global';
   
   -- If each shard has all the data it needs locally:
   SET distributed_product_mode = 'local';
   
   -- If you want to be explicit and rewrite queries manually:
   SET distributed_product_mode = 'deny';
   

Best Practices

• Start with 'deny' (the default) to force explicit handling of distributed subqueries. This prevents accidental Cartesian products or incorrect results.
• Use 'global' when correctness matters and the subquery result set is small enough to fit in memory on the initiator.
• Use 'local' only when the subquery table's data is fully replicated across all shards or when you are intentionally filtering against local data only.
• Prefer explicit GLOBAL IN and GLOBAL JOIN syntax in queries rather than relying on the distributed_product_mode setting, as it makes the query's behavior self-documenting.
• Monitor memory usage on the initiator node when using 'global' mode, since the entire subquery result is materialized there.

Frequently Asked Questions

Q: What happens if I use 'deny' and my query has a distributed subquery?
A: ClickHouse will throw an error telling you that using a distributed table in a subquery is not allowed. You need to either rewrite the query with GLOBAL IN/GLOBAL JOIN explicitly, or change the distributed_product_mode setting.

Q: When would I use 'allow'?
A: The 'allow' mode lets the subquery execute without rewriting. This can lead to each shard sending the subquery to all other shards, potentially causing a multiplicative increase in network traffic. Only use it if you have specifically designed your table sharding to handle this, or for small clusters where the overhead is acceptable.

Q: Is 'global' mode always safe to use?
A: It produces correct results, but it can be expensive. The subquery runs on the initiator node, and its entire result set is sent to every shard. For large subquery results (millions of rows), this can consume significant memory and network bandwidth. Consider filtering or limiting the subquery to keep its result set manageable.

Q: Can I set this per-query instead of per-session?
A: Yes. Use the SETTINGS clause on the query:

SELECT * FROM dist_a
WHERE id IN (SELECT id FROM dist_b)
SETTINGS distributed_product_mode = 'global';

This overrides the session-level setting for that single query.