Does Drill support FILTER WHERE?

Question

Consider following query:

SELECT
   COUNT(*) unfiltered,
   COUNT(*) FILTER (WHERE a."Id" <= 5) AS filtered
FROM db.example."Articles" a

This should return something like:

unfiltered  |  filtered
456         |  5

But Apache Drill returns:

unfiltered  |  filtered
456         |  456

Apparently FILTER WHERE does not work. Is it bug or feature? If it's not supported, why Drill does not throw any error when parsing this SQL?

Using Drill 1.16.0

Vova Vysotskyi Vova Vysotskyi · Accepted Answer · 2019-10-24T16:45:42

Thanks for pointing out this issue. Yes, we don't support this and we should at least throw an exception instead of returning the wrong result. I have created a Jira ticket for this issue: DRILL-7421

Does Drill support FILTER WHERE?

1 Answers