MySQL Bugs: #114638: ROLLUP misses a summary NULL

Bug #114638	ROLLUP misses a summary NULL
Submitted:	12 Apr 2024 16:33	Modified:	5 Aug 2024 21:54
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
also verified on 8.0.

create table t(a int);
select a,count(*) from t group by a with rollup;
Empty set (0.00 sec)

Because some other DBMS (PostgreSQL) rather returns a one-row result, containing a NULL value, I checked the standard, and I believe they are right and MySQL is wrong.

 select a,count(*) from t group by a with rollup;
is equivalent to
 select a,count(*) from t group by rollup (a);
which is equivalent to:
 select a,count(*) from t group by a
 union all
 select null,count(*) from t group by ();

The first part of the UNION should be empty, but the second part should have one row. Indeed, GROUP BY () produces, according to the standard, one group (not zero group), containing all rows of the table, here it's an empty group but it's still one group, and from that it follows that the SELECT should return one row per group, so COUNT(*) should be calculated and should return 0. So the result should be:
  0, NULL.

I also think that this desired result would be more in line with the Docs at
https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html :
"Finally, following all other rows, an extra super-aggregate summary row appears".

How to repeat:
see above

Salut Guilhem,

Thank you for your bug report.

We have checked with the SQL standard and you are correct.

This is a verified for 8.0 and above.

Thanks.

Documented fix as follows in the MySQL 9.1.0 changelog:

    A ROLLUP query did not take account a summary NULL. Consider the
    following statements:

  CREATE TABLE t(a INT);

  SELECT a, COUNT(*) FROM t GROUP BY a WITH ROLLUP;

    According to the SQL standard, the SELECT statement should be
    equivalent to a UNION query which produces the result (0, NULL)
    because, even with an empty result set, ROLLUP should give us a
    single grouping row with NULL entries for all GROUP BY
    expressions as well as a grand total for any aggregates, in this
    0 for COUNT(*), but MySQL returned an empty set instead.

    Now we return the grouping row in such cases.

Closed.

(Bonjour Guilhem et merci beaucoup pour l'explication très claire du problème!)

Thank you, Jon.