Wrong result with STDDEV aggregate through VIEW/CTE: NULL returned instead of 0.0000

I found a wrong-result issue involving STDDEV(), VIEW/CTE expansion, and an outer WHERE predicate.

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

The VIEW and CTE versions return an empty set when filtered with c IS NOT NULL, while the equivalent TEMPORARY TABLE version returns 0.0000.

This looks incorrect because STDDEV(4) over one input row should return 0.0000, not NULL. Therefore, the outer predicate c IS NOT NULL should keep the row.

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 (

  b TINYINT

);

INSERT INTO a VALUES ();

CREATE VIEW v0 AS

SELECT STDDEV(4) AS c

FROM a;

SELECT c

FROM v0

WHERE c IS NOT NULL;

WITH CTE AS (

  SELECT STDDEV(4) AS c

  FROM a

)

SELECT c

FROM CTE

WHERE c IS NOT NULL;

CREATE TEMPORARY TABLE temp_t AS

SELECT STDDEV(4) AS c

FROM a;

SELECT c

FROM temp_t

WHERE c IS NOT NULL;

Actual result:

The VIEW query returns:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

+--------+

| c      |

+--------+

| 0.0000 |

+--------+

Expected result:

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

+--------+

| c      |

+--------+

| 0.0000 |

+--------+

Reason:

The table contains one row.

The aggregate expression is:

STDDEV(4)

Since all input values are the constant value 4 and there is one input row, the standard deviation should be 0.0000.

Therefore, the outer predicate:

WHERE c IS NOT NULL

should preserve this row.

The TEMPORARY TABLE version confirms the expected result and returns 0.0000. However, the VIEW and CTE versions return an empty set, suggesting that STDDEV(4) may be incorrectly evaluated as NULL or that the outer IS NOT NULL predicate is incorrectly optimized when the aggregate is expanded through a VIEW or CTE.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT c FROM v0 WHERE c IS NOT NULL

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

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

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

This suggests a wrong-result bug involving aggregate function evaluation, VIEW/CTE expansion, derived condition pushdown, or NULLability inference for STDDEV().