This message was deleted.

What's the query, and the full error?

@Krishna hi, in order to evaluate a HAVING clause, final aggregation must first be achieved and final aggregation is completed on the Broker. If you share you query perhaps we can recommend changes that would improve the overall performance and still achieve your objective.

sure @Sergio Ferragut @Ben Krug we are not seeing any errors but cluster will be busy and causing slowness to small queries also

Oh, well sample query could help. I guess not error, I was asking about this: "When we try to keep

forcelimitPushDown

to true it is failing saying this setting won’t applicable if we have having clause ." What's the full message?

sure

how about rewrite with:

SELECT * FROM
( 
SELECT sum(metrics) as sum_metrics FROM "druid"."datasource"
WHERE ...
LIMIT 10
)
WHERE sum_metrics > 0

This should run the subquery with the pushdown and then resolve the "having" with the external WHERE clause.

let me check

That is weird, I tested the query on Apache Druid 25.0 and it worked. what version are you on? Another point for this particular query is that the LIMIT won't do anything because it produces a single result per segment which then gets merged in the broker to a single result, so there is nothing to limit.

OK, looking at it, it makes sense to me now. We generally can't evaluate the HAVING at the historical, because we need the totals first. For this particular query, it's logically possible, but the planner's not detailed enough to see that.

Druid version is.21

It's not going to work like that with forcing limit pushdown to historicals. Druid can't properly evaluate a HAVING with partial results. For this particular query, I might have a workaround...

Ok

actually, this is just an example to show the error, right, not a real query? For a real query I probably won't have any workaround. Do you have a real one?

Will get one actual query

if you need precise control over exactly what is and isn't pushed down, you could also use the native query API instead of SQL

for testing we can use native API but our application use avatica sql to query druid

Is there a reason not to set forceLimitPushdown to false in the query context for these queries? I think you could do that, and leave it true for other queries.