Wrong result with ST_IsValid through VIEW/CTE and derived table ORDER BY LIMIT

I found a wrong-result issue involving ST_IsValid(), VIEW/CTE, a derived table with ORDER BY and LIMIT, and an outer WHERE predicate.

The query returns different results depending on whether ST_IsValid(d) is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.

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

This looks incorrect because the base table contains one valid geometry and one NULL geometry. ST_IsValid() should produce a true value for the valid geometry row. Since the derived table uses LIMIT 2 and there are only 2 rows, LIMIT should not remove the valid row. The outer WHERE g predicate should keep the row where g is true.

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 GEOMETRY

);

INSERT INTO a VALUES (ST_GeomFromText('POINT(0 0)'));

INSERT INTO a VALUES (NULL);

CREATE VIEW v0 AS

SELECT ST_IsValid(d) AS f

FROM a;

SELECT 0

FROM (

  SELECT f AS g

  FROM v0

  ORDER BY 1

  LIMIT 2

) AS x

WHERE g;

WITH CTE AS (

  SELECT ST_IsValid(d) AS f

  FROM a

)

SELECT 0

FROM (

  SELECT f AS g

  FROM CTE

  ORDER BY 1

  LIMIT 2

) AS x

WHERE g;

CREATE TEMPORARY TABLE temp_t AS

SELECT ST_IsValid(d) AS f

FROM a;

SELECT 0

FROM (

  SELECT f AS g

  FROM temp_t

  ORDER BY 1

  LIMIT 2

) AS x

WHERE g;

Actual result:

The VIEW query returns:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

+---+

| 0 |

+---+

| 0 |

+---+

Expected result:

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

+---+

| 0 |

+---+

| 0 |

+---+

Reason:

The table contains two rows:

ST_GeomFromText('POINT(0 0)')

NULL

For the valid geometry row, ST_IsValid(d) should evaluate to a true value.

The derived table is:

SELECT f AS g

FROM ...

ORDER BY 1

LIMIT 2

Since the source has only 2 rows, LIMIT 2 should not remove any row. The outer predicate:

WHERE g

should keep the row where g is true.

The TEMPORARY TABLE version confirms this behavior and returns 1 row. However, the VIEW and CTE versions return an empty result set, which suggests that the optimizer/execution path for VIEW/CTE is incorrectly evaluating or pushing down the outer WHERE predicate, possibly in combination with ST_IsValid(), ORDER BY, and LIMIT.

Observed inconsistency:

The following forms should be semantically consistent with respect to the final row count:

1. VIEW:
SELECT 0 FROM (SELECT f AS g FROM v0 ORDER BY 1 LIMIT 2) AS x WHERE g

2. CTE:
WITH CTE AS (...) SELECT 0 FROM (SELECT f AS g FROM CTE ORDER BY 1 LIMIT 2) AS x WHERE g

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT 0 FROM (SELECT f AS g FROM temp_t ORDER BY 1 LIMIT 2) AS x WHERE g

However, only the TEMPORARY TABLE version returns the expected row.