MariaDB CTAS changes ENUM comparison semantics for CHAR(0)

Summary

On MariaDB 12.2.2, a comparison against an ENUM column can return different rows after the expression is materialized with CREATE TABLE ... AS SELECT. The dynamic expression treats the empty ENUM value as numeric zero, while the materialized column is inferred as VARBINARY and uses string comparison. The same statements are consistent on MariaDB 10.6.

Environment

• MariaDB 12.2.2, InnoDB, default SQL mode
• MariaDB 10.6 control
• No replication or external files required

Steps to reproduce

DROP TABLE IF EXISTS enum_src, enum_mat;

CREATE TABLE enum_src (e ENUM('a') NULL);

INSERT INTO enum_src VALUES ('0'), ('a'), (NULL);

SELECT e FROM enum_src WHERE e <> CHAR(0) ORDER BY e+0;

CREATE TABLE enum_mat AS

  SELECT CHAR(0) AS x, e FROM enum_src;

SELECT e FROM enum_mat WHERE e <> x ORDER BY e+0;

SELECT e FROM (

  SELECT CHAR(0) AS x, e FROM enum_src

) AS derived WHERE e <> x ORDER BY e+0;

DROP TABLE enum_mat, enum_src;

Actual result

MariaDB 12.2.2:

Dynamic query:       a

Materialized query:  '' , a

Derived query:       a

The empty ENUM value is equal to numeric zero in the dynamic comparison. After CTAS, SHOW CREATE TABLE reports x as VARBINARY, and the materialized comparison uses binary/string semantics, making the empty value qualify unexpectedly. An explicit character comparison is stable:

SELECT e FROM enum_mat

WHERE CAST(e AS CHAR) <> CAST(x AS CHAR)

ORDER BY e+0;

This explicit-cast control returns the same row set as the materialized query on both tested versions.

Expected result

Materializing an expression and then evaluating the same comparison should preserve the comparison semantics and result set, unless the materialization explicitly requests a different type. CREATE TABLE ... AS SELECT should not silently change an ENUM comparison from numeric to binary semantics.

Verification

The minimal case reproduced in 5/5 repetitions on MariaDB 12.2.2 and 0/5 on MariaDB 10.6. The explicit-cast control was clean in 10/10 runs. The derived-table form preserved the dynamic result.