Wrong result with VARIANCE, GROUP BY and HAVING through VIEW/CTE: NULL row is lost

I found a wrong-result issue involving VARIANCE(), GROUP BY, HAVING, VIEW/CTE expansion, and an outer WHERE h IS NULL predicate.

The same query returns different results depending on whether the grouped aggregate result is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

The VIEW and CTE versions return an empty set, while the equivalent TEMPORARY TABLE version returns one NULL row.

This looks incorrect because one group contains only a NULL value for the aggregate argument. VARIANCE(f) for that group should return NULL. Therefore, the outer predicate h IS NULL should preserve that row.

This may be related to other aggregate-function wrong-result issues involving STDDEV/STD/VAR_POP, but this test case is different because the expected aggregate result is NULL and the query also uses GROUP BY and HAVING.

Environment:

MariaDB version:
12.2.2-MariaDB-ubu2404

How to repeat:

DROP VIEW IF EXISTS v0;

DROP TEMPORARY TABLE IF EXISTS temp_t;

DROP TABLE IF EXISTS a;

CREATE TABLE a (

  d INT,

  f DOUBLE

);

INSERT INTO a VALUES

  (1, 1),

  (2, NULL);

CREATE VIEW v0 AS

SELECT d AS g, VARIANCE(f) AS h

FROM a

GROUP BY 1

HAVING COUNT(DISTINCT 4);

SELECT h

FROM v0

WHERE h IS NULL;

WITH CTE AS (

  SELECT d AS g, VARIANCE(f) AS h

  FROM a

  GROUP BY 1

  HAVING COUNT(DISTINCT 4)

)

SELECT h

FROM CTE

WHERE h IS NULL;

CREATE TEMPORARY TABLE temp_t AS

SELECT d AS g, VARIANCE(f) AS h

FROM a

GROUP BY 1

HAVING COUNT(DISTINCT 4);

SELECT h

FROM temp_t

WHERE h IS NULL;

Actual result:

The VIEW query returns:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

+------+

| h    |

+------+

| NULL |

+------+

Expected result:

The VIEW and CTE queries should return the same result as the TEMPORARY TABLE query:

+------+

| h    |

+------+

| NULL |

+------+

Reason:

The table contains two rows:

(1, 1)

(2, NULL)

The query groups by d:

GROUP BY 1

Therefore there are two groups:

g = 1, f = 1

g = 2, f = NULL

For the group where f = NULL, VARIANCE(f) should return NULL because there are no non-NULL input values for the aggregate.

The HAVING clause is:

HAVING COUNT(DISTINCT 4)

For each non-empty group, COUNT(DISTINCT 4) evaluates to 1, so both groups should pass the HAVING condition.

Therefore, the grouped result should include one row where h is NULL.

The outer predicate is:

WHERE h IS NULL

This predicate should return the NULL aggregate row.

The TEMPORARY TABLE version confirms the expected result. However, the VIEW and CTE versions return an empty set.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT h FROM v0 WHERE h IS NULL

2. CTE:
WITH CTE AS (...) SELECT h FROM CTE WHERE h IS NULL

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT h FROM temp_t WHERE h IS NULL

However, the VIEW and CTE versions return an empty set, while the TEMPORARY TABLE version returns the expected NULL row.

This suggests a wrong-result bug involving VARIANCE(), GROUP BY, HAVING, aggregate nullability, VIEW/CTE expansion, or predicate pushdown of IS NULL conditions.