Description:
For a table containing DATE values with zero year/month/day components (inserted while sql_mode permitted them, e.g. under NO_ENGINE_SUBSTITUTION only), SELECT DISTINCT a FROM t1 WHERE ... correctly returns all matching rows, including these zero-component dates, with no warnings.
However, unioning the identical query with itself (SELECT a FROM t1 WHERE ... UNION SELECT a FROM t1 WHERE ...), which should be semantically equivalent to the DISTINCT query and return the same result set, instead:
Emits Warning 1292: Incorrect date value for each zero-component date value, once per UNION branch, even though these values are already validly stored in the table and were never re-entered by the user in this query.
Silently drops one of the matching rows ('1001-00-00') from the final result, while the other zero-component date ('0000-00-00') is retained.
How to repeat:
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE t1 (a DATE, INDEX (a))
PARTITION BY LIST (TO_SECONDS(a)) (
PARTITION p0001 VALUES IN (TO_SECONDS('0001-01-01')),
PARTITION p2001 VALUES IN (TO_SECONDS('2001-01-01')),
PARTITION pnull VALUES IN (NULL),
PARTITION p0000 VALUES IN (TO_SECONDS('0000-01-02')),
PARTITION p1001 VALUES IN (TO_SECONDS('1001-01-01'))
);
INSERT INTO t1 VALUES
('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
('1001-00-00'), ('1001-01-01'), ('1002-00-00'),
('2001-01-01');
ANALYZE TABLE t1;
SET SESSION sql_mode = DEFAULT;
-- Correct: 4 rows, no warnings
SELECT DISTINCT a FROM t1 WHERE a < '1001-01-01';
-- Incorrect: 3 rows (missing '1001-00-00'), 4 warnings
SELECT a FROM t1 WHERE a < '1001-01-01'
UNION
SELECT a FROM t1 WHERE a < '1001-01-01';
SHOW WARNINGS;
-- Correct: 4 rows, no warnings -- casting to CHAR avoids the issue
SELECT CAST(a AS CHAR(10)) FROM t1 WHERE a < '1001-01-01'
UNION
SELECT CAST(a AS CHAR(10)) FROM t1 WHERE a < '1001-01-01';
SHOW WARNINGS after the failing UNION query:
Warning | 1292 | Incorrect date value: '0000-00-00' for column 'a' at row 1
Warning | 1292 | Incorrect date value: '1001-00-00' for column 'a' at row 1
Warning | 1292 | Incorrect date value: '0000-00-00' for column 'a' at row 1
Warning | 1292 | Incorrect date value: '1001-00-00' for column 'a' at row 1
Expected result:
The UNION of a query with itself should return the same 4 rows as SELECT DISTINCT on the same query, with no warnings, since the values being processed are already validly stored DATE values, not new input being validated for the first time.
Description: For a table containing DATE values with zero year/month/day components (inserted while sql_mode permitted them, e.g. under NO_ENGINE_SUBSTITUTION only), SELECT DISTINCT a FROM t1 WHERE ... correctly returns all matching rows, including these zero-component dates, with no warnings. However, unioning the identical query with itself (SELECT a FROM t1 WHERE ... UNION SELECT a FROM t1 WHERE ...), which should be semantically equivalent to the DISTINCT query and return the same result set, instead: Emits Warning 1292: Incorrect date value for each zero-component date value, once per UNION branch, even though these values are already validly stored in the table and were never re-entered by the user in this query. Silently drops one of the matching rows ('1001-00-00') from the final result, while the other zero-component date ('0000-00-00') is retained. How to repeat: SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; CREATE TABLE t1 (a DATE, INDEX (a)) PARTITION BY LIST (TO_SECONDS(a)) ( PARTITION p0001 VALUES IN (TO_SECONDS('0001-01-01')), PARTITION p2001 VALUES IN (TO_SECONDS('2001-01-01')), PARTITION pnull VALUES IN (NULL), PARTITION p0000 VALUES IN (TO_SECONDS('0000-01-02')), PARTITION p1001 VALUES IN (TO_SECONDS('1001-01-01')) ); INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'), ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01'); ANALYZE TABLE t1; SET SESSION sql_mode = DEFAULT; -- Correct: 4 rows, no warnings SELECT DISTINCT a FROM t1 WHERE a < '1001-01-01'; -- Incorrect: 3 rows (missing '1001-00-00'), 4 warnings SELECT a FROM t1 WHERE a < '1001-01-01' UNION SELECT a FROM t1 WHERE a < '1001-01-01'; SHOW WARNINGS; -- Correct: 4 rows, no warnings -- casting to CHAR avoids the issue SELECT CAST(a AS CHAR(10)) FROM t1 WHERE a < '1001-01-01' UNION SELECT CAST(a AS CHAR(10)) FROM t1 WHERE a < '1001-01-01'; SHOW WARNINGS after the failing UNION query: Warning | 1292 | Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning | 1292 | Incorrect date value: '1001-00-00' for column 'a' at row 1 Warning | 1292 | Incorrect date value: '0000-00-00' for column 'a' at row 1 Warning | 1292 | Incorrect date value: '1001-00-00' for column 'a' at row 1 Expected result: The UNION of a query with itself should return the same 4 rows as SELECT DISTINCT on the same query, with no warnings, since the values being processed are already validly stored DATE values, not new input being validated for the first time.