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

Uncategorized
1

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)

2

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:

    SELECT <group cols>,
       SUM(CASE WHEN grp = 'TEST_GROUP'    THEN 1 ELSE 0 END) AS TG_SUBMITTED,
       SUM(CASE WHEN grp = 'CONTROL_GROUP' THEN 1 ELSE 0 END) AS CG_SUBMITTED
FROM (
  SELECT DISTINCT <group cols>, field7 AS grp, field6
  FROM mysql_82.DREMIO_TEST_DB_82.rule_cdr_08
  WHERE UPPER(TRIM(field14)) = 'PROMOTION'
    AND UPPER(TRIM(field43)) = 'SUBMIT_SUCCESS'
    AND field9 = 1
    AND field7 IN ('TEST_GROUP','CONTROL_GROUP')
) t
GROUP BY <group cols>

    Please validate the results against your original query first. This assumes field6 is non-null.

  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).