This message was deleted.

Do you get any error, Could you share screen shot for both the output?

You are right:

select count(*), count(channel) from (select channel, count(*) numItems from wikipedia group by channel) where channel = 'x'

returns no records ... whereas this:

select count(*), count(channel) from (select channel from wikipedia) where channel = 'x'

Correctly returns zero counts. I will log an engineering ticket for this. Thanks. John

I think this happens because Druid returns no buckets if don’t match anything for

GROUP BY

. This is consistent with the behaviour of SQL, if we explicitly mention a group by, i.e.

select count(*) from wikipedia where channel = 'x' group by 'dummy_key'

returns no records. However if the

GROUP BY

clause is not present then we return the single record with count as 0 (which is where Druid’s behaviour is diverging from the expected SQL behaviour) Can you raise a bug in Druid’s issues so that we can track it. Thanks! (https://github.com/apache/druid/issues/new?assignees=&labels=&template=other.md&title=)

if you remove the

group by

clause then because you are using the count aggregator you still get an implicit

group by ()

clause and it returns a single row. This is consistent with how other dbs behave. Here is MySQL for example http://sqlfiddle.com/#!9/a6c585/292295

I have experimented with this a bit. The problem as I see it happens under some conditions when your subquery is a group by, the native SQL also types the outer query as a "groupBy", which results in "select count(*) with group by" behavior, i.e. no rows returned. The outer query here is not a group by, regardless of what the type of subquery is. I ran some more variations to see what would happen:

select count(*) from (select channel from wikipedia where channel = 'x') where channel = 'y'

Returns one record with zero (0) count.

select count(*) from (select channel, count(*) from wikipedia group by channel) where channel = 'y'

select count(*) from (select channel, count(*) from wikipedia where channel = 'x' group by channel)

Each of the above statements returns no records. In this case the native outer query type is "groupBy". Interestingly:

select count(*) from (select channel, count(*) from wikipedia where channel = 'x' group by channel) where channel = 'y'

Also correctly returns one record with zero (0) count. The native outer query type is "scan" . So even though the subquery is a group by, the addition of the filter condition in the subquery avoids the translation issue for the outer query.

i found a work around for this count(*) not getting displayed i just added a limit at the end i'm getting the result. This is not a correct solution but a workaround i found all of a sudden.

Hmmm ... I just tried these:

select count(*) from (select channel, count(*) from wikipedia group by channel) where channel = 'y' LIMIT 10

select count(*) from (select channel, count(*) from wikipedia where channel = 'x' group by channel) LIMIT 10

with no luck. Please do an Explain Plan on your query that is working and let us know what the top level "queryType" is. Thanks. John