CLICKHOUSE -- Double-distributed IN/JOIN subqueries is denied
发布于 6 个月前 作者 jingxin 1128 次浏览 来自 问答

在测试这个场景的时候貌似CLICKHOUSE无法支持这种join,大神们有啥好建议吗? Q7:select t1.status as f1,CASE WHEN t3.f4>1000 THEN ‘MORE’ ELSE ‘LESS’ END as f2,SUM(t1.payment) as f5,MAX(t1.payment) as f7,AVG(t1.payment) as f6 from shop_sub_order_detail_test t1 left join ( select t2.status as f3,(COUNT(DISTINCT t2.oid )) as f4 from shop_sub_order_detail_test t2 group by f3) t3 on t1.status = t3.f3 group by f1,f2 having not ((AVG(t1.payment) is null and MAX(t1.payment) is null and SUM(t1.payment) is null )) limit 1000

Received exception from server (version 1.1.54385): Code: 288. DB::Exception: Received from localhost:9000, ::1. DB::Exception: Double-distributed IN/JOIN subqueries is denied (distributed_product_mode = ‘deny’). You may rewrite query to use local tables in subqueries, or use GLOBAL keyword, or set distributed_product_mode to suitable value.

2 回复

A left join B on … 不是ClickHouse SQL的语法。 在ClickHouse中做left join,要用 A [GLOBAL] LEFT JOIN B USING columns_list 参见:https://clickhouse.yandex/docs/en/query_language/queries/#join-clause

这不告诉你了么? use GLOBAL keyword, or set distributed_product_mode to suitable value.

回到顶部