Skip to content

Group by duplicate GROUPING SETS returns wrong result #5672

Open

Description

@mingmwang

Describe the bug

To Reproduce

let sql = "SELECT  c1, avg(c12) FROM aggregate_test_100 GROUP BY GROUPING SETS((c1),(c1),())";
let actual = execute_to_batches(&ctx, sql).await;
    let expected = vec![
        "+----+-----------------------------+",
        "| c1 | AVG(aggregate_test_100.c12) |",
        "+----+-----------------------------+",
        "|    | 0.5089725099127211          |",
        "| a  | 0.48754517466109415         |",
        "| b  | 0.41040709263815384         |",
        "| c  | 0.6600456536439784          |",
        "| d  | 0.48855379387549824         |",
        "| e  | 0.48600669271341534         |",
        "| a  | 0.48754517466109415         |",
        "| b  | 0.41040709263815384         |",
        "| c  | 0.6600456536439784          |",
        "| d  | 0.48855379387549824         |",
        "| e  | 0.48600669271341534         |",
        "+----+-----------------------------+",
    ];

the actual returned is:

        "+----+-----------------------------+",
        "| c1 | AVG(aggregate_test_100.c12) |",
        "+----+-----------------------------+",
        "|    | 0.5089725099127211          |",
        "| a  | 0.48754517466109415         |",
        "| b  | 0.41040709263815384         |",
        "| c  | 0.6600456536439784          |",
        "| d  | 0.48855379387549824         |",
        "| e  | 0.48600669271341534         |",
        "+----+-----------------------------+",

Expected behavior

Additional context

Activity

  1. mingmwang commented on Mar 21, 2023

    @mingmwang
    ContributorAuthor

    I'm working on it now.

  2. Phoenix500526 commented on Nov 6, 2026

    @Phoenix500526
    Contributor

    This appears to be fixed on main by #21058, merged as 4aed81a. That PR preserves duplicate grouping sets and adds regression coverage, but it linked #21316 rather than this issue.

    I checked this reduced version of the reported query with a local v57.0.0-dev CLI:

    SELECT category, AVG(value) AS avg_value
    FROM (VALUES ('a', 10), ('b', 20)) AS t(category, value)
    GROUP BY GROUPING SETS ((category), (category), ())
    ORDER BY category NULLS LAST;

    It correctly returns five rows, preserving both occurrences of each category:

    category | avg_value
    a        | 10.0
    a        | 10.0
    b        | 20.0
    b        | 20.0
    NULL     | 15.0
    

    Could we close this issue as resolved?

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

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions