[jira] [Commented] (DRILL-6949) Query fails with "UNSUPPORTED_OPERATION ERROR: Hash-Join can not partition the inner data any further" when Semi join is enabled

["Boaz Ben-Zvi (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-6949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16967156#comment-16967156 ] 

Boaz Ben-Zvi commented on DRILL-6949:
-------------------------------------

The two profiles clearly show that this is a fundamental issue: The are only 561 distinct values of "l_discount" out of 600M rows. The new "efficient semi-join" was designed based on the assumption that the build side has a relatively small number of duplicates.  The new implementation saves the cost of the hash-aggregation, but then keeps *ALL* the build side rows . In a case like this query, the build side of the hash-join balloons to a huge size, which causes a huge spilling, which leads to secondary/tertiary/... spillings that try to subdivide the partitions by keys, but those keys are duplicates hence the subdivision does not help (as the error message explains).

Such an extreme situation is not expected in real-world queries. The only workaround for such unusual query is (as [~agirish] suggested) is to disable the new semi-join (hence the Hash-Aggregate would eliminate the duplicates).

Another far future solution is to use statistics in the planar to detect such a case, and then the planner would disable the new semi-join automatically.

 

> Query fails with "UNSUPPORTED_OPERATION ERROR: Hash-Join can not partition the inner data any further" when Semi join is enabled
> --------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DRILL-6949
>                 URL: https://issues.apache.org/jira/browse/DRILL-6949
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Query Planning & Optimization
>    Affects Versions: 1.15.0
>            Reporter: Abhishek Ravi
>            Assignee: Boaz Ben-Zvi
>            Priority: Major
>         Attachments: 23cc1240-74ff-a0c0-8cd5-938fc136e4e2.sys.drill, 23cc1369-0812-63ce-1861-872636571437.sys.drill
>
>
> Following query fails when with *Error: UNSUPPORTED_OPERATION ERROR: Hash-Join can not partition the inner data any further (probably due to too many join-key duplicates)* on TPC-H SF100 data.
> {code:sql}
> set `exec.hashjoin.enable.runtime_filter` = true;
> set `exec.hashjoin.runtime_filter.max.waiting.time` = 10000;
> set `planner.enable_broadcast_join` = false;
> select
>  count(*)
> from
>  lineitem l1
> where
>  l1.l_discount IN (
>  select
>  distinct(cast(l2.l_discount as double))
>  from
>  lineitem l2);
> reset `exec.hashjoin.enable.runtime_filter`;
> reset `exec.hashjoin.runtime_filter.max.waiting.time`;
> reset `planner.enable_broadcast_join`;
> {code}
> The subquery contains *distinct* keyword and hence there should not be duplicate values. 
> I suspect that the failure is caused by semijoin because the query succeeds when semijoin is disabled explicitly.
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)