GROUP BY ... WITH ROLLUP: duplicate reference to grouping column not nullified in super-aggregate row

Summary

In a GROUP BY ... WITH ROLLUP query, the grouping column must be NULL in the super-aggregate (ROLLUP) row. MariaDB nullifies only the first select-list reference to the grouping column (the one bound to the GROUP BY item). Any additional bare reference to the same column — a duplicated mention, an alias, or a column produced by table. expansion — retains the *last processed group's value instead of NULL.

This is not specific to table.* wildcard expansion. A plain duplicated column or an alias reproduces it identically. MySQL returns NULL for all such references in the super-aggregate row.

Type / priority / component (suggested)

Minimal reproducer

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (10), (20), (30);

SELECT a, a AS c2

FROM t1

GROUP BY a WITH ROLLUP;

Actual result (MariaDB 12.3.1-MariaDB-asan-log)

+------+------+

| a    | c2   |

+------+------+

|   10 |   10 |

|   20 |   20 |

|   30 |   30 |

| NULL |   30 |   <-- super-aggregate row: c2 should be NULL

+------+------+

Expected result (SQL-standard super-aggregate; MySQL 9.6.0 control)

| NULL | NULL |

Scope — what is and isn't affected

All of the following return NULL, 30 in the super-aggregate row (the second reference leaks the last group's value). The first select-list reference is always correctly NULL:

SELECT a, a AS c2       FROM t1 GROUP BY a WITH ROLLUP;   -- NULL, 30

SELECT a AS c1, a AS c2 FROM t1 GROUP BY a WITH ROLLUP;   -- NULL, 30

SELECT a, t1.*          FROM t1 GROUP BY a WITH ROLLUP;   -- NULL, 30

SELECT t1.*, a          FROM t1 GROUP BY a WITH ROLLUP;   -- NULL, 30  (first ref NULL, second leaks)

The effect is positional, not syntactic: in SELECT t1., a, the t1.-expanded column is first and is correctly NULL, while the explicit second a leaks 30.

Key diagnostic — an expression over the duplicate reference returns the correct NULL

SELECT a, a+0 AS c2     FROM t1 GROUP BY a WITH ROLLUP;   -- NULL, NULL

Since NULL + 0 = NULL, the underlying group value is NULL. Only a bare duplicate column slot displays the stale value. This points at the duplicate Item_field being read from the row buffer rather than from the nullified group value.

Root cause (hypothesis)

ROLLUP nullification appears to mark a single item per grouping column (the GROUP BY item / first positional reference). Additional bare Item_field references to the same column read directly from the current row buffer instead of the nullified group value, so they show the last processed group. Wrapping the reference in any expression forces re-evaluation over the nullified input, which is why a+0 correctly yields NULL.

Comparison with MySQL

MySQL 9.6.0 returns NULL for every reference in the super-aggregate row — bare, duplicated, aliased, or t1.*-expanded — matching SQL-standard super-aggregate semantics. The same reproducer on MySQL yields NULL, NULL.

Environment

• MariaDB tested: 12.3.1-MariaDB-asan-log (reproduced on this tree)
• MySQL reference: 9.6.0 (NULL, NULL in super-aggregate row)
• Also reported on 10.11.14 / MySQL 8.0.45 with the same symptom (reporter to attach if filing from another distro build)