Wrong result with TEMPORARY TABLE created from ROW_NUMBER() and outer VAR_POP/GROUP BY query

I found a wrong-result issue involving CREATE TEMPORARY TABLE AS SELECT, ROW_NUMBER(), GROUP BY, VAR_POP(), and an outer WHERE predicate.

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

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

This looks incorrect because the table contains one row. ROW_NUMBER() should produce d = 1, and wf_0 is the constant 1. Therefore d DIV 10 should be 0, and VAR_POP(wf_0) over the single-row group should be 0.0000. The outer predicate f IS NOT NULL AND g = 0 should preserve this 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 (

  c0 TEXT

);

INSERT INTO a VALUES ('');

CREATE VIEW v0 AS

SELECT *

FROM (

  SELECT

    1 AS wf_0,

    ROW_NUMBER() OVER () AS d

  FROM a

) AS e;

SELECT f

FROM (

  SELECT d DIV 10 AS g, VAR_POP(wf_0) AS f

  FROM v0

  GROUP BY g

) AS x

WHERE f IS NOT NULL AND g = 0;

WITH CTE AS (

  SELECT *

  FROM (

    SELECT

      1 AS wf_0,

      ROW_NUMBER() OVER () AS d

    FROM a

  ) AS e

)

SELECT f

FROM (

  SELECT d DIV 10 AS g, VAR_POP(wf_0) AS f

  FROM CTE

  GROUP BY g

) AS x

WHERE f IS NOT NULL AND g = 0;

CREATE TEMPORARY TABLE temp_t AS

SELECT *

FROM (

  SELECT

    1 AS wf_0,

    ROW_NUMBER() OVER () AS d

  FROM a

) AS e;

SELECT f

FROM (

  SELECT d DIV 10 AS g, VAR_POP(wf_0) AS f

  FROM temp_t

  GROUP BY g

) AS x

WHERE f IS NOT NULL AND g = 0;

Actual result:

The VIEW query returns:

+--------+

| f      |

+--------+

| 0.0000 |

+--------+

The CTE query returns:

+--------+

| f      |

+--------+

| 0.0000 |

+--------+

The TEMPORARY TABLE query returns:

Empty set

Expected result:

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

+--------+

| f      |

+--------+

| 0.0000 |

+--------+

Reason:

The base table contains one row.

The inner query is:

SELECT

  1 AS wf_0,

  ROW_NUMBER() OVER () AS d

FROM a

For one input row, this should produce:

wf_0 = 1

d    = 1

Then the grouped query computes:

d DIV 10 AS g

Since d = 1:

1 DIV 10 = 0

The grouped aggregate is:

VAR_POP(wf_0)

For a single value 1, VAR_POP(wf_0) should evaluate to 0.0000.

Therefore the derived table should contain:

g = 0

f = 0.0000

The outer predicate is:

WHERE f IS NOT NULL AND g = 0

This predicate should keep the row.

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

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT from v0, then GROUP BY d DIV 10 and compute VAR_POP(wf_0)

2. CTE:
WITH CTE AS (... ROW_NUMBER() ...) SELECT from CTE, then GROUP BY d DIV 10 and compute VAR_POP(wf_0)

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ... ROW_NUMBER() ...
SELECT from temp_t, then GROUP BY d DIV 10 and compute VAR_POP(wf_0)

However, only the TEMPORARY TABLE version returns an empty set.

This suggests a wrong-result bug involving CREATE TEMPORARY TABLE AS SELECT, ROW_NUMBER() materialization, expression evaluation of d DIV 10, GROUP BY, VAR_POP(), or derived-condition optimization.