# Unexpected Physical Plan for Multiple COUNT(DISTINCT CASE WHEN) Aggregations

**URL:** <https://community.dremio.com/t/unexpected-physical-plan-for-multiple-count-distinct-case-when-aggregations/13396>\
**Category:** Uncategorized\
**Created:** [September 30, 2026, 4:36pm UTC](https://community.dremio.com/t/unexpected-physical-plan-for-multiple-count-distinct-case-when-aggregations/13396 "2026-09-30T16:36:37Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![AjayBabuM](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/ajaybabum/32/6736_2.png) [@AjayBabuM](https://community.dremio.com/u/AjayBabuM)\
**Post date:** [September 30, 2026, 4:36pm UTC](https://community.dremio.com/t/unexpected-physical-plan-for-multiple-count-distinct-case-when-aggregations/13396/1 "2026-09-30T16:36:37Z")

</div>

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](https://community.dremio.com/uploads/short-url/1BQrz0q2UbJdQGpPCXqEohkDcPo.zip) (15.4 KB)

---

<div class="post-metadata">

**Author:** ![Rafay](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/rafay/32/3377_2.png) [@Rafay](https://community.dremio.com/u/Rafay)\
**Post date:** [October 1, 2026, 5:43pm UTC](https://community.dremio.com/t/unexpected-physical-plan-for-multiple-count-distinct-case-when-aggregations/13396/2 "2026-10-01T17:43:27Z")

</div>

Yes, this is expected. When a query has multiple `COUNT(DISTINCT ...)` over different expressions, Dremio rewrites it into one aggregation branch per distinct expression and joins them back on the grouping columns. Your two `CASE WHEN` expressions count different arguments, so they can’t share a single aggregation. There’s no setting to turn this off.

In your profile the whole rewrite is pushed down to MySQL as a single query. The join happens inside MySQL, not in Dremio. The profile you sent finished in about 0.5 seconds on a small table, so it doesn’t show a performance problem. On a large table, MySQL would scan the table twice and join on 8 columns using null-safe equality (`<=>`), which can be slow in 5.7.

Here are some ways to improve it:

1. **Pre-deduplicate, then pivot with `SUM`.** This avoids the multiple distincts, so there’s no join back:

2. **Index and filter the MySQL table.** Drop `UPPER(TRIM(...))` if the data is already clean, since it blocks index use. Add an index covering the filter and group-by columns.

3. **Consider a reflection** if this query runs repeatedly. An aggregation reflection with the group columns as dimensions and a distinct count on `field6` would avoid hitting MySQL on each run.

If the real query is slow, please send a profile from the full-size table and the MySQL `EXPLAIN` for the pushed-down SQL (it’s in the `Jdbc(sql=...)` node of the plan).