Hi Team,
We are observing an unexpected physical query plan in Dremio for a simple SQL query using multiple COUNT(DISTINCT CASE WHEN ...) expressions.
The input query is executed against a MySQL source:
MySQL Version: 5.7.28-log
Dremio Version: dremio-community-26.0.5-202509091642240013-f5051a07
The input query contains two COUNT(DISTINCT CASE WHEN ...) aggregations. However, Dremio generates separate aggregation branches for each expression and then performs an INNER JOIN on all the grouping columns.
We expected both aggregations to be handled within a single aggregation rather than generating separate aggregation subqueries and joining them back.
Could you please confirm if this is expected behavior in Dremio? Also, is there any recommended approach to avoid this query plan or improve the query performance?
I have attached the job profile for reference.
Thanks,
Ajay Babu Maguluri.
f36c05dc-71f2-41a2-91fc-1743d281cb31.zip (15.4 KB)