Wrong result: CTE with RIGHT JOIN and ST_IsValid returns NULL despite WHERE alias filter

I found a wrong-result issue involving a CTE, RIGHT JOIN, ST_IsValid(), and a WHERE filter on a SELECT-list alias.

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

The VIEW and TEMPORARY TABLE versions return 1, while the equivalent CTE version returns NULL.

This looks incorrect because the outer query has WHERE h. A row where h is NULL should not pass the WHERE h predicate. The CTE result should therefore be consistent with the VIEW and TEMPORARY TABLE results.

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 c;

CREATE TABLE c (

  f POINT,

  g INT

);

INSERT INTO c VALUES (Point(0,0), 1);

INSERT INTO c VALUES ();

CREATE OR REPLACE VIEW v0 AS

SELECT ST_IsValid(a.f) AS h

FROM c AS a RIGHT JOIN c AS b ON b.g;

SELECT h FROM v0 WHERE h;

WITH CTE AS (

  SELECT ST_IsValid(a.f) AS h

  FROM c AS a RIGHT JOIN c AS b ON b.g

)

SELECT h FROM CTE WHERE h;

CREATE TEMPORARY TABLE temp_t AS

SELECT ST_IsValid(a.f) AS h

FROM c AS a RIGHT JOIN c AS b ON b.g;

SELECT h FROM temp_t WHERE h;

Actual result:

The VIEW query returns:

+------+

| h    |

+------+

|    1 |

+------+

The CTE query returns:

+------+

| h    |

+------+

| NULL |

+------+

The TEMPORARY TABLE query returns:

+------+

| h    |

+------+

|    1 |

+------+

Expected result:

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

+------+

| h    |

+------+

|    1 |

+------+

Reason:

The CTE defines h as:

ST_IsValid(a.f)

The outer query applies:

WHERE h

A NULL value for h should not satisfy WHERE h. Therefore, the CTE query should not return a row where h is NULL.

Since the VIEW and TEMPORARY TABLE versions both return only h = 1, the CTE version appears to produce a wrong result.

Observed inconsistency:

The following three forms should be semantically consistent with respect to the result of the outer WHERE h predicate:

1. SELECT h FROM v0 WHERE h
2. WITH CTE AS (...) SELECT h FROM CTE WHERE h
3. CREATE TEMPORARY TABLE temp_t AS ...; SELECT h FROM temp_t WHERE h

However, only the CTE version returns NULL.

This suggests a wrong-result bug in the CTE optimization/execution path, possibly involving predicate pushdown, RIGHT JOIN null-complemented rows, SELECT-list alias resolution, or evaluation of ST_IsValid() on nullable geometry values.