Description:
Description:
A query result inconsistency occurs when evaluating an ENUM column wrapped in MAX() within a boolean context dynamically versus evaluating the materialized column produced via CREATE TABLE ... AS SELECT (CTAS).
Specifically:
In base table t0, column c is defined as ENUM('x') (internal index value = 1). When executing HAVING MAX(c) OR 'N', MAX(c) converts the enum to its string representation ('x'). In boolean context, 'x' is converted to 0 (FALSE), causing the predicate to evaluate to FALSE (returning Empty set).
However, CTAS creates table t1 with column c retaining the ENUM('x') data type. When evaluating WHERE c OR 'N' on t1, column c is evaluated using its ENUM numerical index (1 = TRUE), causing the predicate to evaluate to TRUE (returning 1 row).
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
-- Step 1: Create base table t0 with ENUM column
CREATE TABLE t0 (c ENUM('x'));
INSERT INTO t0 VALUES ('x');
-- Step 2: Materialize MAX(c) into table t1 via CTAS
CREATE TABLE t1 AS SELECT MAX(c) AS c FROM t0;
-- Query 1: Dynamic evaluation with MAX(ENUM) on base table t0
-- MAX(c) evaluates as string 'x' -> 0 (FALSE)
SELECT 1 FROM t0 HAVING MAX(c) OR 'N';
-- Returns: Empty set (with 1 warning)
-- Query 2: Evaluation on materialized ENUM column in t1
-- Column c evaluates using internal index 1 -> 1 (TRUE)
SELECT 1 FROM t1 WHERE c OR 'N';
-- Returns: 1 row
Description: Description: A query result inconsistency occurs when evaluating an ENUM column wrapped in MAX() within a boolean context dynamically versus evaluating the materialized column produced via CREATE TABLE ... AS SELECT (CTAS). Specifically: In base table t0, column c is defined as ENUM('x') (internal index value = 1). When executing HAVING MAX(c) OR 'N', MAX(c) converts the enum to its string representation ('x'). In boolean context, 'x' is converted to 0 (FALSE), causing the predicate to evaluate to FALSE (returning Empty set). However, CTAS creates table t1 with column c retaining the ENUM('x') data type. When evaluating WHERE c OR 'N' on t1, column c is evaluated using its ENUM numerical index (1 = TRUE), causing the predicate to evaluate to TRUE (returning 1 row). How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; -- Step 1: Create base table t0 with ENUM column CREATE TABLE t0 (c ENUM('x')); INSERT INTO t0 VALUES ('x'); -- Step 2: Materialize MAX(c) into table t1 via CTAS CREATE TABLE t1 AS SELECT MAX(c) AS c FROM t0; -- Query 1: Dynamic evaluation with MAX(ENUM) on base table t0 -- MAX(c) evaluates as string 'x' -> 0 (FALSE) SELECT 1 FROM t0 HAVING MAX(c) OR 'N'; -- Returns: Empty set (with 1 warning) -- Query 2: Evaluation on materialized ENUM column in t1 -- Column c evaluates using internal index 1 -> 1 (TRUE) SELECT 1 FROM t1 WHERE c OR 'N'; -- Returns: 1 row