Incorrect result in query evaluation due to type coercion/evaluation divergence between dynamic expression and materialized column in CTAS (ST_MLineFromWKB)

There is an inconsistency in query evaluation results when comparing a MEDIUMTEXT/TEXT column with ST_MLineFromWKB(c0) directly in a WHERE clause versus comparing it against a materialized column in a derived table created via CREATE TABLE ... AS SELECT (CTAS).

When calling ST_MLineFromWKB(c0) dynamically on a POLYGON geometry, the function fails to parse it as a MultiLineString, emitting a warning and returning NULL. As a result, the comparison c1 > ST_MLineFromWKB(c0) evaluates to NULL (falsy) and filters out the row.

However, when executing CREATE TABLE t1 AS SELECT ST_MLineFromWKB(c0) AS c0, c1 FROM t0;, the CTAS materializes the geometry value into t1.c0 without converting it to NULL. Subsequent comparison c1 > c0 in t1 evaluates to TRUE (comparing string against the binary geometry representation).

How to repeat：
– Step 1: Prepare database and tables
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE t0 (c0 POLYGON, c1 MEDIUMTEXT);

INSERT INTO t0 (c0, c1) VALUES (
PolygonFromText('POLYGON((-6.929684 12.732735, 171.527101 -22.38418, 121.288535 -1.670355, -49.202572 -78.561137, 65.066672 -35.348599, -6.929684 12.732735))'),
'2wP6cMkT1EEZKcZQP1wq'
);

– Step 2: Create materialized table via CTAS
CREATE TABLE t1 AS SELECT ST_MLineFromWKB(c0) AS c0, c1 AS c1 FROM t0;

– Query 1: Dynamic evaluation on base table t0
SELECT (ST_MLineFromWKB(c0)) FROM t0 WHERE c1 > (ST_MLineFromWKB(c0));
– Returns: Empty set (1 warning)

– Query 2: Evaluation on materialized table t1
SELECT (c0) FROM t1 WHERE c1 > c0;
– Returns: 1 row

Actual Result:

Query 1 (t0): Returns Empty set (0 rows).

Query 2 (t1): Returns 1 row.