Description:
A CASE expression over an ENUM column preserves ENUM-like numeric comparison behavior when used through a VIEW or CTE. However, when the same expression is materialized using `CREATE TEMPORARY TABLE ... SELECT`, the resulting column becomes `VARCHAR(1)`. This changes numeric comparison behavior: `vc = 0` is false in the VIEW/CTE, but true in the temporary table, with warnings.
This may be due to `CREATE TABLE ... SELECT` type derivation/materialization rules
How to repeat:
mysql> select version();
+-----------+
| version() |
+-----------+
| 9.6.0 |
+-----------+
DROP DATABASE IF EXISTS dd;
CREATE DATABASE dd;
USE dd;
CREATE TABLE t0 (
c0 ENUM('a')
);
INSERT INTO t0 VALUES ('a');
CREATE VIEW v0 AS
SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc
FROM t0;
SELECT vc FROM v0 WHERE vc = 0;
WITH cte AS (
SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc
FROM t0
)
SELECT vc FROM cte WHERE vc = 0;
CREATE TEMPORARY TABLE tmp AS
SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc
FROM t0;
SELECT vc FROM tmp WHERE vc = 0;
Actual result:
The VIEW query returns:
Empty set
The CTE query returns:
Empty set
The temporary table query returns:
+------+
| vc |
+------+
| a |
+------+
1 row in set, 1 warning
Description: A CASE expression over an ENUM column preserves ENUM-like numeric comparison behavior when used through a VIEW or CTE. However, when the same expression is materialized using `CREATE TEMPORARY TABLE ... SELECT`, the resulting column becomes `VARCHAR(1)`. This changes numeric comparison behavior: `vc = 0` is false in the VIEW/CTE, but true in the temporary table, with warnings. This may be due to `CREATE TABLE ... SELECT` type derivation/materialization rules How to repeat: mysql> select version(); +-----------+ | version() | +-----------+ | 9.6.0 | +-----------+ DROP DATABASE IF EXISTS dd; CREATE DATABASE dd; USE dd; CREATE TABLE t0 ( c0 ENUM('a') ); INSERT INTO t0 VALUES ('a'); CREATE VIEW v0 AS SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc FROM t0; SELECT vc FROM v0 WHERE vc = 0; WITH cte AS ( SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc FROM t0 ) SELECT vc FROM cte WHERE vc = 0; CREATE TEMPORARY TABLE tmp AS SELECT CASE WHEN c0 IS NULL THEN '' ELSE c0 END AS vc FROM t0; SELECT vc FROM tmp WHERE vc = 0; Actual result: The VIEW query returns: Empty set The CTE query returns: Empty set The temporary table query returns: +------+ | vc | +------+ | a | +------+ 1 row in set, 1 warning