Vova Vysotskyi created DRILL-7421: ------------------------------------- Summary: FILTER clause for aggregate functions is ignored Key: DRILL-7421 URL: https://issues.apache.org/jira/browse/DRILL-7421 Project: Apache Drill Issue Type: Bug Affects Versions: 1.16.0 Reporter: Vova Vysotskyi Fix For: Future
Drill ignores FILTER clause for aggregate functions. The following query returns the wrong result: {code:sql} select count(*), count(employee_id) filter(where employee_id < 5) from {code} {noformat} cp.`employee.json`; +--------+--------+ | EXPR$0 | EXPR$1 | +--------+--------+ | 1155 | 1155 | +--------+--------+ {noformat} Calcite already supports this feature (CALCITE-704) and as it is mentioned in the Jira ticket, such syntax is allowed by SQL standard. As a short solution, we should throw an exception for such queries that this functionality is not supported. As was mentioned in Calcite's Jira, some queries may be rewritten using switch case: {code:sql} select count(*), count(case when employee_id < 5 then employee_id else null end) from cp.`employee.json`; {code} It is possible to add functionality into Drill to rewrite filtered aggregate calls in such a way, but some aggregate functions still would not be supported, for example, {{count(*)}}. -- This message was sent by Atlassian Jira (v8.3.4#803005)