Return at least one row

[cosmin-mogos]

We have a simple druid sql query to return the count of some events.

select count(*) from events where type='the event'

The problem is that if no events match the query no rows are returned, since we use it for alerts we'd like the query above to return a row with 0 value in case no events match the where clause. How can we achieve this?

The closest I managed was with

select sum(c) from (
    select count(*) as c from events where type='the event'
    union all
    select 0 as c from events limit 1
)

which fails with Cannot build plan for query, but succeeds if the sum(c) is replaced with just c.

[HansFranzHaase]

I am a bit late, but had the same problem.
Currently it works also with only the inner part:

select count(*) as c from events where type='the event'
union all
select 0 as c from events limit 1

Thanks @cosmin-mogos for also providing the workaround!

[abhishekagarwal87]

what druid version are you on?

[HansFranzHaase]

That was on version 0.21.0.

While writing these lines I can also try on 2021.12 and the result for

select count(*) from events where type='the event'

is as expected now: 0

[github-actions]

This issue has been marked as stale due to 280 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If this issue is still
relevant, please simply write any comment. Even if closed, you can still revive the
issue at any time or discuss it on the dev@druid.apache.org list.
Thank you for your contributions.

[github-actions]

This issue has been closed due to lack of activity. If you think that
is incorrect, or the issue requires additional review, you can revive the issue at
any time.