fix(pivot-table): key GROUPING SETS lookup by logical label, not engine-mutated alias

BigQuery mangles SQL aliases containing non-word characters (e.g. a Custom
SQL row label with a space, "Test Row" -> "Test_Row_<hash>"). The legacy
Pivot Table's non-additive-metric GROUPING SETS query sends rollup levels
keyed by each column's original, unmutated label, but get_sqla_query looked
up those levels in a dict keyed by the mutated SQL alias. The lookup missed
on BigQuery, silently dropping the column from every rollup level and
producing a GROUP BY clause that no longer covered a selected,
non-aggregated column -- which BigQuery rejects at query time.

Key the GROUPING SETS lookup and its GROUPING() marker labels by the
column's `.key` (the logical label) instead, matching what the frontend
sends and what every other selected column already does via
make_sqla_column_compatible.

superset/models/helpers.py

@@ -80,7 +80,6 @@ from superset import db, is_feature_enabled
 from superset.advanced_data_type.types import AdvancedDataTypeResponse
 from superset.common.db_query_status import QueryStatus
 from superset.common.grouping_sets import (
-    grouping_id_column,
     grouping_marker_label,
     grouping_sets_clause,
 )
@@ -4690,10 +4689,31 @@ class ExploreMixin:  # pylint: disable=too-many-public-methods
             and groupby_all_columns
             and db_engine_spec.supports_grouping_sets
         )
+        # Both the GROUPING() marker labels and the `grouping_sets` level
+        # definitions sent by the frontend (see buildQuery.ts) are expressed in
+        # terms of the column's logical/requested label (``.key``), not the
+        # engine-mutated SQL alias (``.name``). BigQuery, for example, mangles
+        # labels containing spaces (e.g. a Custom SQL column named "Test Row")
+        # into something like "Test_Row_a1b2c3" for `.name`, while `.key` keeps
+        # the original "Test Row". Keying by `.name` here would silently drop
+        # such columns from every rollup level (the `col in ...` guard below),
+        # producing an invalid ``GROUP BY GROUPING SETS`` clause that omits a
+        # selected, non-aggregated column.
+        groupby_columns_by_label = {
+            gby_expr.key: gby_expr for gby_expr in groupby_all_columns.values()
+        }
         if use_grouping_sets:
+            # Route the marker through `make_sqla_column_compatible` like every
+            # other selected column: the SQL-level alias is engine-mutated if
+            # required (e.g. BigQuery rejects aliases with spaces), while
+            # `.key` keeps the unmutated marker label so it lines up with the
+            # `groupby_columns_by_label` keys above and with what the frontend
+            # looks for when splitting the combined result back per level.
             select_exprs = select_exprs + [
-                grouping_id_column(gby_expr, grouping_marker_label(name))
-                for name, gby_expr in groupby_all_columns.items()
+                self.make_sqla_column_compatible(
+                    sa.func.grouping(gby_expr), grouping_marker_label(label)
+                )
+                for label, gby_expr in groupby_columns_by_label.items()
             ]
 
         # Expected output columns
@@ -4710,9 +4730,9 @@ class ExploreMixin:  # pylint: disable=too-many-public-methods
             if use_grouping_sets:
                 gs_levels = [
                     [
-                        groupby_all_columns[col]
+                        groupby_columns_by_label[col]
                         for col in level
-                        if col in groupby_all_columns
+                        if col in groupby_columns_by_label
                     ]
                     for level in grouping_sets or []
                 ]

tests/unit_tests/models/helpers_test.py

@@ -4309,6 +4309,80 @@ def test_get_sqla_query_dotted_struct_column_bigquery(
     assert "`forecasts.original`" not in sql
 
 
+def test_get_sqla_query_grouping_sets_preserves_bigquery_mutated_label(
+    mocker: MockerFixture,
+    session: Session,
+) -> None:
+    """
+    A pivot table with a non-additive metric issues a `grouping_sets` query
+    (see plugin-chart-pivot-table/src/plugin/buildQuery.ts), whose rollup
+    levels are expressed in terms of each column's original, unmutated label
+    (e.g. a Custom SQL row labelled "Test Row"). BigQuery mutates SQL aliases
+    that contain non-word characters, so the same column's `Label.name` ends
+    up as something like "Test_Row_<hash>". Looking up each level's columns by
+    that mutated name instead of the original label silently drops the column
+    from every rollup level, producing a `GROUP BY GROUPING SETS` clause that
+    doesn't cover a selected, non-aggregated column.
+    """
+    bigquery = pytest.importorskip("sqlalchemy_bigquery")
+
+    from superset.connectors.sqla.models import SqlaTable, TableColumn
+    from superset.models.core import Database
+
+    SqlaTable.metadata.create_all(session.get_bind())
+
+    dialect = bigquery.BigQueryDialect()
+
+    @contextmanager
+    def fake_engine(*args, **kwargs):
+        engine = MagicMock()
+        engine.dialect = dialect
+        yield engine
+
+    database = Database(database_name="bq", sqlalchemy_uri="bigquery://project")
+    mocker.patch.object(database, "get_sqla_engine", new=fake_engine)
+
+    table = SqlaTable(
+        database=database,
+        schema=None,
+        table_name="orders",
+        columns=[TableColumn(column_name="amount", type="FLOAT")],
+    )
+
+    row_col: AdhocColumn = {
+        "sqlExpression": "some_column",
+        "label": "Test Row",
+    }
+    metric: AdhocMetric = {
+        "expressionType": "SIMPLE",
+        "column": {"column_name": "amount"},
+        "aggregate": "AVG",
+        "label": "avg_amount",
+    }
+
+    sqlaq = table.get_sqla_query(
+        columns=[row_col],
+        metrics=[metric],
+        extras={},
+        filter=[],
+        granularity=None,
+        is_timeseries=False,
+        grouping_sets=[["Test Row"], []],
+    )
+    sql = str(
+        sqlaq.sqla_query.compile(
+            dialect=dialect, compile_kwargs={"literal_binds": True}
+        )
+    )
+
+    # Before the fix, looking up each level's columns by the BigQuery-mutated
+    # alias (e.g. "Test_Row_<hash>") instead of the original "Test Row" label
+    # sent by the frontend silently dropped the column from every level,
+    # producing `GROUPING SETS ((), ())` -- a GROUP BY that doesn't cover the
+    # selected, non-aggregated "some_column" expression.
+    assert "GROUPING SETS((some_column), ())" in sql
+
+
 def test_temporal_epoch_string_filter_is_coerced_for_bigquery() -> None:
     """
     Drill-to-detail can send JavaScript timestamp strings for temporal values.