Description:
An execution logic divergence occurs when comparing an integer constant (121) with a dynamic ADDDATE(d, 0) function expression against a DATETIME column, compared to evaluating the same predicate against a materialized column produced via CREATE TABLE ... AS SELECT (CTAS).
Specifically:
In base table t0, d is a DATETIME column ('1969-01-01'). The predicate WHERE 121 > ADDDATE(d, 0) dynamically coerces the datetime expression into a numeric timestamp representation (19690101000000). Since 121 > 19690101000000 is FALSE, it returns Empty set.
In table t1 (created via CREATE TABLE t1 AS SELECT ADDDATE(d,0) d FROM t0), column d is materialized. Executing WHERE 121 > d on t1 uses a different type coercion rule, evaluating the predicate to TRUE and returning 1 row.
How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
-- Step 1: Create base table t0 with DATETIME column
CREATE TABLE t0 (d DATETIME);
INSERT INTO t0 VALUES ('1969-01-01');
-- Step 2: Materialize ADDDATE(d,0) into table t1 via CTAS
CREATE TABLE t1 AS SELECT ADDDATE(d, 0) d FROM t0;
-- Query 1: Dynamic comparison on base table t0
SELECT 1 FROM t0 WHERE 121 > ADDDATE(d, 0);
-- Returns: Empty set
-- Query 2: Comparison on materialized column in t1
SELECT 1 FROM t1 WHERE 121 > d;
-- Returns: 1 row
Description: An execution logic divergence occurs when comparing an integer constant (121) with a dynamic ADDDATE(d, 0) function expression against a DATETIME column, compared to evaluating the same predicate against a materialized column produced via CREATE TABLE ... AS SELECT (CTAS). Specifically: In base table t0, d is a DATETIME column ('1969-01-01'). The predicate WHERE 121 > ADDDATE(d, 0) dynamically coerces the datetime expression into a numeric timestamp representation (19690101000000). Since 121 > 19690101000000 is FALSE, it returns Empty set. In table t1 (created via CREATE TABLE t1 AS SELECT ADDDATE(d,0) d FROM t0), column d is materialized. Executing WHERE 121 > d on t1 uses a different type coercion rule, evaluating the predicate to TRUE and returning 1 row. How to repeat: DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; -- Step 1: Create base table t0 with DATETIME column CREATE TABLE t0 (d DATETIME); INSERT INTO t0 VALUES ('1969-01-01'); -- Step 2: Materialize ADDDATE(d,0) into table t1 via CTAS CREATE TABLE t1 AS SELECT ADDDATE(d, 0) d FROM t0; -- Query 1: Dynamic comparison on base table t0 SELECT 1 FROM t0 WHERE 121 > ADDDATE(d, 0); -- Returns: Empty set -- Query 2: Comparison on materialized column in t1 SELECT 1 FROM t1 WHERE 121 > d; -- Returns: 1 row