Wrong result: CREATE TEMPORARY TABLE AS SELECT with ST_IsValid join condition creates 0 rows

I found a wrong-result issue involving CREATE TEMPORARY TABLE AS SELECT, ST_IsValid(), a GEOMETRY column, and a JOIN condition.

The same query returns 2 rows when evaluated through a VIEW or CTE, but CREATE TEMPORARY TABLE AS SELECT materializes 0 rows.

This looks incorrect because the base table contains one valid POINT geometry. The JOIN condition depends only on b.d:

ST_IsValid(b.d) = 1

For the valid POINT row, this condition should be true. Since the left table a has 2 rows, the join should produce 2 rows containing the valid POINT value from b.d.

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 (

  c INT,

  d GEOMETRY

);

INSERT INTO a VALUES (NULL, NULL);

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

CREATE VIEW v0 AS

SELECT b.d AS e

FROM a

JOIN a AS b

ON ST_IsValid(b.d) = 1;

SELECT *

FROM v0

WHERE e = e;

WITH CTE AS (

  SELECT b.d AS e

  FROM a

  JOIN a AS b

  ON ST_IsValid(b.d) = 1

)

SELECT *

FROM CTE

WHERE e = e;

CREATE TEMPORARY TABLE temp_t AS

SELECT b.d AS e

FROM a

JOIN a AS b

ON ST_IsValid(b.d) = 1;

SELECT *

FROM temp_t

WHERE e = e;

Actual result:

The VIEW query returns 2 rows:

+------------------------------------------------------+

| e                                                    |

+------------------------------------------------------+

| 0x00000000010100000000000000000000000000000000000000 |

| 0x00000000010100000000000000000000000000000000000000 |

+------------------------------------------------------+

The CTE query returns 2 rows:

+------------------------------------------------------+

| e                                                    |

+------------------------------------------------------+

| 0x00000000010100000000000000000000000000000000000000 |

| 0x00000000010100000000000000000000000000000000000000 |

+------------------------------------------------------+

CREATE TEMPORARY TABLE reports 0 rows:

Query OK, 0 rows affected

Records: 0  Duplicates: 0  Warnings: 0

The TEMPORARY TABLE query returns:

Empty set

Expected result:

The TEMPORARY TABLE should contain the same 2 rows as the VIEW and CTE results:

+------------------------------------------------------+

| e                                                    |

+------------------------------------------------------+

| 0x00000000010100000000000000000000000000000000000000 |

| 0x00000000010100000000000000000000000000000000000000 |

+------------------------------------------------------+

Reason:

The table contains two rows:

(NULL, NULL)

(NULL, POINT(0 0))

The join condition is:

ST_IsValid(b.d) = 1

This condition depends only on b.d.

For b.d = POINT(0 0), ST_IsValid(b.d) should evaluate to 1.

For b.d = NULL, the condition should not match.

Since the left table a has 2 rows, and there is one matching b row with a valid POINT geometry, the join should produce 2 rows.

The VIEW and CTE versions confirm this behavior and return 2 rows. However, CREATE TEMPORARY TABLE AS SELECT materializes 0 rows, which appears to be a wrong result.

Observed inconsistency:

The following forms should be semantically consistent:

1. VIEW:
SELECT * FROM v0 WHERE e = e

2. CTE:
WITH CTE AS (...) SELECT * FROM CTE WHERE e = e

3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS SELECT ...;
SELECT * FROM temp_t WHERE e = e

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

This suggests a wrong-result bug involving CREATE TEMPORARY TABLE AS SELECT, GIS function evaluation, and JOIN conditions using ST_IsValid().