MySQL Bugs: #104394: SELECT's GROUPING() not taken in account in HAVING when JOIN used

Bug #104394	SELECT's GROUPING() not taken in account in HAVING when JOIN used
Submitted:	23 Jul 2021 10:18	Modified:	27 Aug 2021 16:41
Reporter:	Sébastien F.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	grouping, rollup

Description:
I can't filter a SELECT GROUPING() with HAVING when JOIN is used.
When JOIN is not used the HAVING filter is correctly taken in account.

How to repeat:
-- Bug reproduction => JOIN is present
WITH 
products (id, label) AS (
    VALUES
        ROW (1, 'Computer'),
        ROW (2, 'Phone')
),
sales (year, product, count) AS (
    VALUES
        ROW (2021, 1, 35),
        ROW (2021, 2, 72),
        ROW (2020, 2, 64)
)
SELECT ALL 
    year, label, 
    GROUPING(year) AS group_year,
    SUM(count) AS count
FROM sales INNER JOIN products ON product = id
GROUP BY year, label WITH ROLLUP
HAVING group_year = 0 -- OK with "GROUPING(year) = 0"
;

/*

Result given :

| year | label    | group_year | count |
+------+----------+------------+-------+
| 2020 | Phone    | 0          | 64    |
| 2020 | NULL     | 0          | 64    |
| 2021 | Computer | 0          | 35    |
| 2021 | Phone    | 0          | 72    |
| 2021 | NULL     | 0          | 107   |
| NULL | NULL     | 1          | 171   | => I don't want this row

Result awaited :

| year | label    | group_year | count |
+------+----------+------------+-------+
| 2020 | Phone    | 0          | 64    |
| 2020 | NULL     | 0          | 64    |
| 2021 | Computer | 0          | 35    |
| 2021 | Phone    | 0          | 72    |
| 2021 | NULL     | 0          | 107   |

*/

-- No bug => JOIN is absent

WITH sales (year, product, count) AS (
    VALUES
        ROW (2021, 'Computer', 35),
        ROW (2021, 'Phone', 72),
        ROW (2020, 'Phone', 64)
)
SELECT ALL 
    year, product, 
    GROUPING(year) AS group_year,
    SUM(count) AS count
FROM sales
GROUP BY year, product WITH ROLLUP
HAVING group_year = 0
;

Hi Mr. F.

Thank you for your bug report.

We have tested your report and we agree that this is not functioning.

Hence, we will accept it, but do keep in mind that it might be turned into a feature request.

Verified as reported.

This is also present in the latest 8.0 release .....

A simpler test case:

CREATE TABLE t1 (a INTEGER, b INTEGER);
INSERT INTO t1 VALUES (2020, 2);

CREATE TABLE t2 (b INTEGER);
INSERT INTO t2 VALUES (2);

SELECT a, GROUPING(a) AS ga
  FROM t1 JOIN t2 USING (b)
  GROUP BY a WITH ROLLUP
  HAVING ga = 0;

Note that this query is illegal; you cannot refer to aliases in HAVING (as projection happens after HAVING evaluation). MySQL seems to accept it, but I'm not sure whether we should (the semantics can often be unclear).

The query plan goes:

-> Filter: (ga = 0) (cost=2.50..2.50 rows=0)
    -> Table scan on <temporary>  (cost=2.50..2.50 rows=0)
        -> Temporary table  (cost=2.50..2.50 rows=0)
            -> Group (no aggregates)
                -> Sort: t1.a
                    -> Stream results through <temporary>  (cost=0.70 rows=1)
                        -> Inner hash join (t2.b = t1.b)  (cost=0.70 rows=1)
                            -> Table scan on t2  (cost=0.35 rows=1)
                            -> Hash
                                -> Table scan on t1  (cost=0.35 rows=1)

The immediate problem is the stream through <temporary> (which we only need due to legacy in the old join optimizer); it materializes the expression (ga = 0), which doesn't make sense at this point, since it's below the aggregation (ga will change as the aggregation produces ROLLUP rows). We'd need to take that out of the list of materialized expressions. The hypergraph optimizer gets it right, because it doesn't depend on slices and references the same way.

The immediate workaround is to write the query legally.

Thank you, Steinar !!!!

Posted by developer:
 
Fixed as of the upcoming MySQL 8.0.28 release, and here's the proposed changelog entry from the documentation team:

When setting up an Item_ref to a SELECT alias, its cached properties are
copied (including whether it is part of a ROLLUP expression or not).
However, these might not yet be correctly computed, so the computation
should to be done first or the values could be wrong. Having the wrong
value could cause certain expressions to be materialized in an
intermediate step when they should not (because they contain ROLLUP
expressions that are not ready for computation, but having the wrong
cached values is unknown at this point). The issue is fixed by forcing
cached values to be recomputed when an item is designated as a rollup
item.

Thank you for the bug report.

Thank you, Christine ......