feat(table/pivot-table): correct non-additive totals/subtotals via DB rollup [SIP-216] - #41184

✅ Deploy Preview for superset-docs-preview ready!

Name	Link
🔨 Latest commit	e417294
🔍 Latest deploy log	https://app.netlify.com/projects/superset-docs-preview/deploys/6a600dba6706d9000894fc0c
😎 Deploy Preview	https://deploy-preview-41184--superset-docs-preview.netlify.app
📱 Preview on mobile	Toggle QR Code... Use your smartphone camera to open QR code link.
🤖 Make changes	Run an agent on this branch

To edit notification comments on pull requests, go to your Netlify project configuration.

Codecov Report

❌ Patch coverage is 93.14079% with 19 lines in your changes missing coverage. Please review.
✅ Project coverage is 65.19%. Comparing base (9c08240) to head (e417294).
⚠️ Report is 1 commits behind head on master.

@@            Coverage Diff             @@
##           master   #41184      +/-   ##
==========================================
+ Coverage   65.13%   65.19%   +0.06%     
==========================================
  Files        2789     2791       +2     
  Lines      157538   157761     +223     
  Branches    35864    35931      +67     
==========================================
+ Hits       102611   102858     +247     
+ Misses      52956    52931      -25     
- Partials     1971     1972       +1     

☔ View full report in Codecov by Harness.
📢 Have feedback on the report? Share it here.

The SIP is up for a vote on the dev@ list... please VOTE if you wanna see this happen

Following up on the earlier review: CI is currently red on this head (9a30e5003e88) in a way that looks like a real regression, not a flake.

sharded-jest-tests (6) fails 4 assertions in plugin-chart-pivot-table/test/TableRenderer.test.tsx (all about the pvtRowLabelLast class that marks the last data row before a totals row): dataRows[dataRows.length - 1] comes back undefined, and the "spanning" row-label header cell isn't found where expected.

That test file isn't part of this PR's own diff — it's an existing test that predates the rebase this branch picked up — and it was passing at the SHA this review was originally done against, so something in this PR's rewrite of the row/rollup logic is the likely cause rather than any of the small follow-up commits since (those only touch a control description, an unrelated table-chart test import, and a docstring).

My guess, not yet confirmed: processRecord()'s rewrite of how rows/subtotals get keyed and slotted (react-pivottable/utilities.ts) changed rowKeys/visibleRowCount in a way that breaks the last-row detection in TableRenderers.tsx's pvtRowLabelLast logic. Worth a look before merge — this is separate from the total-value correctness reviewed earlier, which still looks solid.

Thanks for catching this, @aminghadersohi. Turned out splitGroupingSetsResult was dropping every row when the frame had no GROUPING() marker columns (any query that never hit the grouping-sets extra), so it now falls back to attributing them to the leaf level. sharded-jest-tests (6) is green on the current head.

Thanks for digging in, @aminghadersohi. Good lead... though it turned out not to be the last-row logic at all. splitGroupingSetsResult attributes each row to a rollup level via the GROUPING() marker columns, and the old test fixtures feed rows with no markers, so every row got filtered out and the table rendered empty. Fixed by treating marker-less results as leaf-level data (blank totals cells beat a blank chart), with a unit test to pin it down, and merged current master while I was in there. CI's green again.

Been trying to get reviews on this for over a month by nagging people on slack, but I'll take the approval (thanks!) and lack of bot or human complaints as good enough... merging during the breaking change window to get it into 7.0, and will happily make touchups/fixes as needed when people do more testing than I already have.

SUMMARY

Proof-of-concept for [SIP-216] / #41463 — fixing one of Superset's longest-standing correctness gaps: totals and subtotals are wrong for non-additive metrics (ratios, COUNT_DISTINCT, AVG, percentiles, and "Percentage metrics") in both the Pivot Table and the regular Table chart.

See #41463 for the full proposal, motivation, and rejected alternatives. This PR is the working implementation referenced throughout that SIP.

Core principle: totals are computed by the database at the grouping granularity, never by re-aggregating already-aggregated cells. A SQL-aggregate metric is correct at any grouping level if the same expression is evaluated grouped at that level, so no formula language is needed.

What's here (consolidates #25747, #32260, #38674, #36165, #37627, #34350/#34425/#34426):

• Pivot Table — database-computed rollup levels via a single GROUPING SETS query (native where the engine supports it, per-level fallback otherwise); the react-pivottable aggregator becomes a passthrough that places DB-computed values. Removes the now-meaningless "Aggregation function" control.
• Table chart — retains post-processing on the summary query so percent/contribution columns are recomputed instead of dropped (Percentage metrics column display only zero value #37627/Page table calculation error #34350).
• Performance — additive fast-path (single query + client-side synthesis), supports_grouping_sets engine capability, query-level pruning to only displayed totals.

Builds on prior art in #34592 (salvages its rollup-combination generator). Out of scope: #39223.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

A rows=[gender, state] pivot with a ratio metric SUM(CASE WHEN state='CA' …)/SUM(num):

• Before: grand total / subtotal show the sum of per-row percentages (e.g. 100%+).
• After: grand total and the per-gender subtotal both show the DB-computed 11%, returned by a single GROUPING SETS query (verified in-app on Postgres; 25 rows = 22 leaf + 2 subtotals + 1 grand total).

TESTING INSTRUCTIONS

1. Pivot a non-additive metric (e.g. a ratio SUM(a)/SUM(b) or COUNT_DISTINCT) with row totals/subtotals enabled; confirm the totals reflect the metric recomputed at each level, not the sum of cells.
2. Table chart with a "Percentage metrics" column + "Show summary"; confirm the percent column appears in the summary row.
3. Verify additive metrics (SUM/COUNT) are unchanged.

Unit + integration tests cover the rollup synthesis, GROUPING SETS clause/marker/split helpers (native + SQLite fallback), additivity detection, and the table summary path.

ADDITIONAL INFORMATION

• Has associated issue: [SIP-216] Correct totals and subtotals for non-additive metrics in Table and Pivot Table charts #41463 (SIP), TOTALS incorrect for non-additive metric in pivot table #25747 (canonical bug); closes Rename "Subtotal" to "Subvalue" in Pivot Tables #35089
• Required feature flags:
• Changes UI
• Includes DB Migration
• Introduces new feature or API
• Removes existing feature or API

The SIP (#41463) passed its vote on the dev@ list.