Wrong result with ST_Centroid through VIEW/CTE and outer ST_IsValid predicate

I found a wrong-result issue involving ST_Centroid(), VIEW/CTE expansion, ST_IsValid(), and ST_GeometryType().

The same query returns different results depending on whether ST_Centroid(c2) 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 POINT.

This looks incorrect because the base table contains one valid POLYGON row. The inner query computes ST_Centroid(c2), which should return a valid POINT geometry. The outer predicate ST_IsValid(vc_0) = 1 should keep this row, and ST_GeometryType(vc_0) should return POINT.

Environment:

MariaDB version:
12.2.2-MariaDB-ubu2404

Related issues:

This may be related to MDEV-40247, but this test case is not identical. It uses ST_Centroid() in the inner query and then applies ST_IsValid() / ST_GeometryType() to the computed geometry in the outer query.

How to repeat:

DROP VIEW IF EXISTS v0;

DROP TEMPORARY TABLE IF EXISTS temp_t;

DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (

  c0 DATE,

  c2 POLYGON

);

INSERT INTO t0 (c0, c2) VALUES

  (NULL, ST_GeomFromText(NULL));

INSERT INTO t0 (c0, c2) VALUES

  ('9101-04-09',

   ST_GeomFromText('POLYGON((-140.5788 72.5516, -140.0788 72.5516, -140.0788 73.0516, -140.5788 73.0516, -140.5788 72.5516))'));

CREATE VIEW v0 AS

SELECT ST_Centroid(c2) AS vc_0

FROM t0

WHERE c0;

SELECT ST_GeometryType(vc_0) AS d_0

FROM v0

WHERE ST_IsValid(vc_0) = 1;

WITH CTE AS (

  SELECT ST_Centroid(c2) AS vc_0

  FROM t0

  WHERE c0

)

SELECT ST_GeometryType(vc_0) AS d_0

FROM CTE

WHERE ST_IsValid(vc_0) = 1;

CREATE TEMPORARY TABLE temp_t AS

SELECT ST_Centroid(c2) AS vc_0

FROM t0

WHERE c0;

SELECT ST_GeometryType(vc_0) AS d_0

FROM temp_t

WHERE ST_IsValid(vc_0) = 1;

Actual result:

The VIEW query returns:

Empty set

The CTE query returns:

Empty set

The TEMPORARY TABLE query returns:

+-------+

| d_0   |

+-------+

| POINT |

+-------+

Expected result:

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

+-------+

| d_0   |

+-------+

| POINT |

+-------+

Reason:

The table contains two rows:

(NULL, NULL)

('9101-04-09', valid POLYGON)

The inner predicate is:

WHERE c0

This should keep the row with the non-NULL date value and filter out the NULL date row.

For the remaining POLYGON row:

ST_Centroid(c2)

should return a POINT geometry.

The outer predicate is:

WHERE ST_IsValid(vc_0) = 1

The centroid POINT should be valid, so this row should be preserved.

Finally:

ST_GeometryType(vc_0)

should return POINT.

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 ST_GeometryType(vc_0) FROM v0 WHERE ST_IsValid(vc_0) = 1

2. CTE:
WITH CTE AS (...) SELECT ST_GeometryType(vc_0) FROM CTE WHERE ST_IsValid(vc_0) = 1

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS ...;
SELECT ST_GeometryType(vc_0) FROM temp_t WHERE ST_IsValid(vc_0) = 1

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

This suggests a wrong-result bug involving GIS expression evaluation, ST_Centroid(), VIEW/CTE expansion, predicate pushdown, or ST_IsValid() evaluation on computed geometry values.