Bug #121735 IS NULL on a UNION-derived DATE column incorrectly raises ERROR 1525
Description:
MySQL Community Server 9.7.1 rejects a valid SELECT with `ERROR 1525: Incorrect DATE value: '0000-00-00'` when an outer IS NULL predicate is applied to a UNION-derived DATE column. Both input columns are NOT NULL and contain only the valid date `'2023-01-01'`; no zero date is inserted or referenced by the query.
The expected result is an empty set. Evaluating the predicate directly on either input, using a simple derived table, or disabling derived condition pushdown returns that empty set without error. UNION DISTINCT, INTERSECT, EXCEPT and a DATETIME variant exhibit the same failure pattern. These controls associate the error with derived condition pushdown; the internal source-code cause has not been established.
## Expected result
An empty result set. UNION ALL contains two copies of a valid, non-null date. Neither row satisfies IS NULL, and no date conversion involving a zero-date literal is requested.
## Actual result
```text
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
```
How to repeat:
SET SESSION optimizer_switch=DEFAULT;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
CREATE TABLE da(d DATE NOT NULL);
CREATE TABLE db(d DATE NOT NULL);
INSERT INTO da VALUES('2023-01-01');
INSERT INTO db VALUES('2023-01-01');
SELECT *
FROM (
SELECT d FROM da
UNION ALL
SELECT d FROM db
) AS s
WHERE s.d IS NULL;
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.048 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: MySQL Community Server 9.7.1 rejects a valid SELECT with `ERROR 1525: Incorrect DATE value: '0000-00-00'` when an outer IS NULL predicate is applied to a UNION-derived DATE column. Both input columns are NOT NULL and contain only the valid date `'2023-01-01'`; no zero date is inserted or referenced by the query. The expected result is an empty set. Evaluating the predicate directly on either input, using a simple derived table, or disabling derived condition pushdown returns that empty set without error. UNION DISTINCT, INTERSECT, EXCEPT and a DATETIME variant exhibit the same failure pattern. These controls associate the error with derived condition pushdown; the internal source-code cause has not been established. ## Expected result An empty result set. UNION ALL contains two copies of a valid, non-null date. Neither row satisfies IS NULL, and no date conversion involving a zero-date literal is requested. ## Actual result ```text ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00' ``` How to repeat: SET SESSION optimizer_switch=DEFAULT; SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; CREATE TABLE da(d DATE NOT NULL); CREATE TABLE db(d DATE NOT NULL); INSERT INTO da VALUES('2023-01-01'); INSERT INTO db VALUES('2023-01-01'); SELECT * FROM ( SELECT d FROM da UNION ALL SELECT d FROM db ) AS s WHERE s.d IS NULL;