[ https://issues.apache.org/jira/browse/DRILL-7421?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Vova Vysotskyi updated DRILL-7421: ---------------------------------- Description: 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(\*)}}. was: 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(*)}}. > 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 > Priority: Major > 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)