Bug #121213 UNIQUE const-table plan evaluates `GROUPING()=1` before ROLLUP and drops the super-aggregate row

• Submitted: 2 Sep 15:24
• Reporter: 策 吕
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.7.2
• OS: Linux
• CPU Architecture: Any

[2 Sep 15:24] 策 吕

Description:
## Version

MySQL **9.7.2** (`mysql:9.7.2` Docker, `SELECT VERSION()` = `9.7.2`). Reproduced 3/3 on the live campaign instance.

## What's Wrong?

`GROUP BY col WITH ROLLUP` must emit a super-aggregate row where `GROUPING(col) = 1`. Filtering that row with `HAVING GROUPING(col) = 1` is the documented way to keep only the grand total.

When the same predicate is computed in the SELECT list of a derived table and filtered with outer `WHERE ref1`, **and** `WHERE col = <const>` can be satisfied by a UNIQUE/PRIMARY lookup, the optimizer treats the table as a const table, evaluates `GROUPING(col)=1` on the **pre-ROLLUP** const row (where GROUPING is 0), and concludes the outer WHERE is impossible. The super-aggregate row that would have made the predicate TRUE is never produced.

| Form | `WHERE c0=0 … GROUP BY c0 WITH ROLLUP` and `GROUPING(c0)=1` | Verdict |
|---|---|---|
| `HAVING GROUPING(c0)=1` | one NULL super-aggregate row | correct |
| Derived `(GROUPING(c0)=1) AS ref1 … WHERE ref1` | empty; EXPLAIN: Impossible WHERE after const tables | **wrong** |
| Inner derived SELECT **without** outer WHERE | both the `0` row and the NULL/`GROUPING=1` row | inner is correct |
| Same pair with `IGNORE INDEX` / non-unique key / `WHERE c0 IN (0,1)` | both forms one row | const lookup is load-bearing |

DQR rule R8 relocates HAVING into a derived-table boolean, so the pair diverges. Paper NoREC cannot write `GROUPING()` at all.

How to repeat:
## How to Reproduce?

```sql
CREATE TABLE t(c0 INT UNIQUE);
INSERT INTO t VALUES (0), (1);

-- Q1 HAVING: the ROLLUP super-aggregate row
SELECT c0 FROM t
WHERE c0 = 0
GROUP BY c0 WITH ROLLUP
HAVING GROUPING(c0) = 1;
-- NULL

-- Q2 derived-table boolean (semantically equivalent): empty
SELECT ref0 FROM (
  SELECT c0 AS ref0,
         (GROUPING(c0) = 1) AS ref1
  FROM t
  WHERE c0 = 0
  GROUP BY c0 WITH ROLLUP
) AS s WHERE ref1;
-- (empty)

-- Inner query without the outer WHERE still produces the GROUPING=1 row
SELECT c0, GROUPING(c0) AS g
FROM t WHERE c0 = 0
GROUP BY c0 WITH ROLLUP;
-- 0    0
-- NULL 1

-- Controls (both sides agree, one super-aggregate row)
SELECT c0 FROM t IGNORE INDEX (c0)
WHERE c0 = 0 GROUP BY c0 WITH ROLLUP HAVING GROUPING(c0) = 1;
SELECT ref0 FROM (
  SELECT c0 AS ref0, (GROUPING(c0) = 1) AS ref1
  FROM t IGNORE INDEX (c0)
  WHERE c0 = 0 GROUP BY c0 WITH ROLLUP
) AS s WHERE ref1;

SELECT c0 FROM t WHERE c0 IN (0, 1) GROUP BY c0 WITH ROLLUP HAVING GROUPING(c0) = 1;
SELECT ref0 FROM (
  SELECT c0 AS ref0, (GROUPING(c0) = 1) AS ref1
  FROM t WHERE c0 IN (0, 1) GROUP BY c0 WITH ROLLUP
) AS s WHERE ref1;
```

`EXPLAIN FORMAT=TREE` of Q2 (abridged):

```
-> Zero rows (Impossible WHERE noticed after reading const tables)
```

`EXPLAIN` of Q1 still builds a Group over the const row and filters `grouping(...) = 1` **after** ROLLUP, so the super-aggregate survives.

`GROUPING()` in WHERE is rejected (`ERROR 1111 Invalid use of group function`), so a NoREC-style `SUM((GROUPING(c0)=1) IS TRUE)` pair is not even parseable.