UNION loses a stored DATE value with zero components after enabling NO_ZERO_IN_DATE and NO_ZERO_DATE

After DATE values with zero components have been inserted in permissive SQL mode, enabling stricter date validation changes the result of a self-UNION query.

SELECT DISTINCT returns four distinct stored dates, but the equivalent UNION query returns only three. The stored value '1001-00-00' is missing from the UNION result.

The issue reproduces using the native mariadb command-line client. A partitioned table is not required.

Reproducer:

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

CREATE TABLE t (

a DATE,

INDEX (a)

);

INSERT INTO t VALUES

('0000-00-00'),

('0000-01-02'),

('0001-01-01'),

('1001-00-00');

SET SESSION sql_mode =

'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';

SELECT DISTINCT a

FROM t

WHERE a < '1001-01-01';

SELECT a

FROM t

WHERE a < '1001-01-01'

UNION

SELECT a

FROM t

WHERE a < '1001-01-01';

Actual result on 11.8.9-MariaDB:

The SELECT DISTINCT query returns four rows:

a

0000-00-00

0000-01-02

0001-01-01

1001-00-00

The UNION query returns only three rows:

a

0000-00-00

0000-01-02

0001-01-01

Expected result:

Both queries should return the same four distinct dates, ignoring row order:

a

0000-00-00

0000-01-02

0001-01-01

1001-00-00

Related reports:

The corresponding MySQL report is:
https://bugs.mysql.com/bug.php?id=121398