Description:
A materialized CTE containing the predicate (NULL AND d), where d is a
DATE column containing only valid, nonzero dates, unexpectedly fails
with ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'.
The same predicate evaluated directly returns an empty result set.
The error also disappears when derived condition pushdown is disabled.
Expected result:
The CTE query should return an empty result set, as does the direct
query. For each row, d is a valid nonzero DATE and (NULL AND d)
evaluates to NULL, so the WHERE clause should filter out every row.
Actual result:
The CTE query fails with:
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
Reproduced on MySQL Community Server 8.0.46 and 8.4.7.
The issue was also reproduced on Percona Server 8.4.11-11.
How to repeat:
SET SESSION sql_mode = 'NO_ZERO_DATE';
CREATE TABLE t_cte_dcp_date (
id INT NOT NULL PRIMARY KEY,
d DATE NOT NULL
);
INSERT INTO t_cte_dcp_date VALUES
(1, '1000-01-01'),
(2, '2020-02-29'),
(3, '9999-12-31');
-- Baseline: returns an empty result set.
SELECT id
FROM t_cte_dcp_date
WHERE NULL AND d
ORDER BY id;
-- Control: disabling derived condition pushdown returns an empty set.
WITH q AS (
SELECT id, (NULL AND d) AS p
FROM t_cte_dcp_date
)
SELECT /*+ NO_MERGE(q) NO_DERIVED_CONDITION_PUSHDOWN(q) */ id
FROM q
WHERE p
ORDER BY id;
-- Reproducer: returns ERROR 1525 on the affected versions.
WITH q AS (
SELECT id, (NULL AND d) AS p
FROM t_cte_dcp_date
)
SELECT /*+ NO_MERGE(q) */ id
FROM q
WHERE p
ORDER BY id;
