[HIVE-24976] CBO: count(distinct) in a window function fails CBO

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: CBO
• Labels: pull-request-available
• Assignee: Krisztian Kasa
• Reporter: Gopal Vijayaraghavan
• Created: 05/Apr/21 21:12
• Updated: 17/Nov/22 08:48
• Resolved: 08/Apr/21 04:45

Description

create temporary table tmp_tbl(
`rule_id` string,
`severity` string,
`alert_id` string,
`alert_type` string);

explain cbo
select `k`.`rule_id`,
count(distinct `k`.`alert_id`) over(partition by `k`.`rule_id`) `subj_cnt`
from tmp_tbl k
;


explain
select `k`.`rule_id`,
count(distinct `k`.`alert_id`) over(partition by `k`.`rule_id`) `subj_cnt`
from tmp_tbl k
;

Fails CBO, because the count(distinct) is not being recognized as belonging to a windowing operation.

So it throws the following exception

            throw new CalciteSemanticException("Distinct without an aggregation.",
                    UnsupportedFeature.Distinct_without_an_aggreggation);

https://github.com/apache/hive/blob/73c3770d858b063c69dea6c64a759f8fdacad460/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java#L4914

This prevents a query like this from using a materialized view which already exists in the system (the MV obviously does not contain this expression, but represents a complex transform from a JSON structure into a columnar layout).

Issue Links

• links to: GitHub Pull Request #2155