MariaDB CTAS changes SET comparison semantics for CHAR(0)

Summary

On MariaDB 12.2.2, materializing a SET comparison with CREATE TABLE ... AS SELECT changes which rows satisfy the same predicate. The dynamic expression applies SET/numeric comparison rules, while CTAS infers the expression column as a binary string. MariaDB 10.6 does not show the mismatch.

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 set_src, set_mat;

CREATE TABLE set_src (e SET('a','b') NULL);

INSERT INTO set_src VALUES ('0'), ('a'), ('a,b'), (NULL);

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

CREATE TABLE set_mat AS

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

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

SELECT e FROM (

  SELECT CHAR(0) AS x, e FROM set_src

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

DROP TABLE set_mat, set_src;

Actual result

MariaDB 12.2.2:

Dynamic query:       a , a,b

Materialized query:  '' , a , a,b

Derived query:       a , a,b

SHOW CREATE TABLE set_mat reports the CTAS expression column x as a binary string. The materialized predicate therefore treats the empty SET value differently from the dynamic SET comparison. An explicit character comparison provides a stable control:

SELECT e FROM set_mat

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

ORDER BY e+0;

Expected result

Materializing the expression should not silently change SET comparison semantics or add the empty SET value to the result. The dynamic query, derived-table query, and CTAS query should agree when no explicit type conversion is requested.

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.

I am guessing it it related to MDEV-40514.