Description:
A query result divergence occurs when evaluating LEAST(TIME, VARCHAR) in a boolean predicate context dynamically versus evaluating a materialized column generated via CREATE TABLE ... AS SELECT (CTAS).
Specifically:
In base table t0, c0 is TIME ('16:28:55') and c1 is VARCHAR ('cXtrY6O'). Dynamic evaluation of WHERE (LEAST(c0, c1)) evaluates to FALSE (returning Empty set), because the comparison coercion handles 'cXtrY6O' as 0 in boolean evaluation.
In table t1 (created via CREATE TABLE t1 AS SELECT LEAST(c0, c1) AS c0, c2 FROM t0), CTAS materializes c0 with the string/time value '16:28:55'. When evaluating WHERE (c0) on t1, the value '16:28:55' evaluates to TRUE in boolean context, returning 1 row.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
-- Step 1: Create base table t0 with TIME and VARCHAR columns
CREATE TABLE t0 (c0 TIME, c1 VARCHAR(7), c2 DECIMAL);
-- Step 2: Insert sample row
INSERT INTO t0 (c0, c1, c2) VALUES ('16:28:55', 'cXtrY6O', -1389339889.422922);
-- Step 3: Materialize expression into table t1 via CTAS
CREATE TABLE t1 AS SELECT (LEAST(c0,c1)) AS c0, c2 AS c2 FROM t0;
-- Query 1: Dynamic evaluation on base table t0
SELECT (LEAST(c0,c1)), c2 FROM t0 WHERE (LEAST(c0,c1));
-- Returns: Empty set
-- Query 2: Evaluation on materialized table t1
SELECT (c0), c2 FROM t1 WHERE (c0);
-- Returns: 1 row ('16:28:55', -1389339889)
Description: A query result divergence occurs when evaluating LEAST(TIME, VARCHAR) in a boolean predicate context dynamically versus evaluating a materialized column generated via CREATE TABLE ... AS SELECT (CTAS). Specifically: In base table t0, c0 is TIME ('16:28:55') and c1 is VARCHAR ('cXtrY6O'). Dynamic evaluation of WHERE (LEAST(c0, c1)) evaluates to FALSE (returning Empty set), because the comparison coercion handles 'cXtrY6O' as 0 in boolean evaluation. In table t1 (created via CREATE TABLE t1 AS SELECT LEAST(c0, c1) AS c0, c2 FROM t0), CTAS materializes c0 with the string/time value '16:28:55'. When evaluating WHERE (c0) on t1, the value '16:28:55' evaluates to TRUE in boolean context, returning 1 row. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; -- Step 1: Create base table t0 with TIME and VARCHAR columns CREATE TABLE t0 (c0 TIME, c1 VARCHAR(7), c2 DECIMAL); -- Step 2: Insert sample row INSERT INTO t0 (c0, c1, c2) VALUES ('16:28:55', 'cXtrY6O', -1389339889.422922); -- Step 3: Materialize expression into table t1 via CTAS CREATE TABLE t1 AS SELECT (LEAST(c0,c1)) AS c0, c2 AS c2 FROM t0; -- Query 1: Dynamic evaluation on base table t0 SELECT (LEAST(c0,c1)), c2 FROM t0 WHERE (LEAST(c0,c1)); -- Returns: Empty set -- Query 2: Evaluation on materialized table t1 SELECT (c0), c2 FROM t1 WHERE (c0); -- Returns: 1 row ('16:28:55', -1389339889)