Skip to content

GROUP BY ROLLUP returns 0 rows with empty input (should return grand total row) #21570

Open

Description

@jverhoeks

Describe the bug

When GROUP BY ROLLUP is used with a query that produces 0 input rows, DataFusion returns 0 output rows. The SQL standard requires ROLLUP to always produce the grand total row (with NULL grouping keys and aggregate results computed over the empty set).

To Reproduce

CREATE TABLE test (category VARCHAR, value INT) AS VALUES ('a', 1);

-- With data: works correctly (returns 2 rows: group + grand total)
SELECT category, count(*) FROM test GROUP BY ROLLUP(category);
-- Returns: [('a', 1), (NULL, 1)]  ✅ correct

-- With empty input: returns 0 rows instead of 1
SELECT category, count(*) FROM test WHERE value < 0 GROUP BY ROLLUP(category);
-- Returns: []  ❌ should return [(NULL, 0)]

-- Without GROUP BY, empty input correctly returns 1 row:
SELECT count(*) FROM test WHERE value < 0;
-- Returns: [(0)]  ✅ correct

Expected behavior

Per SQL standard (and PostgreSQL, Trino, Oracle behavior):

SELECT category, count(*) FROM test WHERE value < 0 GROUP BY ROLLUP(category);
-- Should return: [(NULL, 0)]

The grand total grouping set (the empty grouping set that ROLLUP always includes) should produce a row even when there are zero input rows, because aggregate functions like count(*) have well-defined behavior on empty sets (returns 0).

Additional context

  • Tested on DataFusion 52.3.0
  • Same behavior with CUBE and explicit GROUPING SETS((), (category))
  • Regular GROUP BY category correctly returns 0 rows with empty input (no grand total expected)
  • This affects 6 TPC-DS queries (q18, q27, q36, q67, q70, q86) at small scale factors where certain joins produce 0 matching rows

Root cause (likely)

DataFusion's grouped aggregation implementation skips producing output batches when the hash table is empty (no input rows). For regular GROUP BY, this is correct. For ROLLUP/CUBE/GROUPING SETS that include the empty grouping set (), a row should always be produced for that set.

The fix would need to check if any grouping set is the empty set, and if so, produce a row with NULL grouping keys and empty-set aggregate values (count=0, sum=NULL, etc.) even when the hash table has no entries.

Activity

  1. buraksenn commented on Apr 12, 2026

    @buraksenn
    Contributor

    take

  2. buraksenn commented on Apr 12, 2026

    @buraksenn
    Contributor

    This makes sense for me. I think we can emit empty batch in the grouped hash aggregate stream if we've aggregation and an empty group set. I'll open a PR using this approach for review

  3. xiedeyantu commented on Jun 1, 2026

    @xiedeyantu
    Member

    Perhaps it has been solved in #21058, please see it.

  4. buraksenn commented on Jul 3, 2026

    @buraksenn
    Contributor

    untake

  5. asolimando commented on Jul 31, 2026

    @asolimando
    Member

    This looks fixed by #22039, on current main all three repros return the grand-total row:

    CREATE TABLE test (category VARCHAR, value INT) AS VALUES ('a', 1);
    
    SELECT category, count(*) FROM test WHERE value < 0 GROUP BY ROLLUP(category);
    -- NULL 0
    SELECT category, count(*) FROM test WHERE value < 0 GROUP BY CUBE(category);
    -- NULL 0
    SELECT category, count(*) FROM test WHERE value < 0 GROUP BY GROUPING SETS((), (category));
    -- NULL 0

    I don't have enough rights to close the issue but it should be.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions