An index on a DATE column changes the query result: YEAR(d) = 2020 misses '2020-00-00' and '2020-01-00'

Likely cause: commit f0b665f880bb6a6864660818d926fde9db18fa3e (MDEV-8320), in Date_cmp_func_rewriter::create_start_bound(). CS 10.11.19, from before it, does not have the bug.

CREATE TABLE t1 (d DATE, KEY (d));

INSERT INTO t1 VALUES ('2019-12-31'),('2020-00-00'),('2020-01-00'),('2020-05-15'),('2021-01-01');

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE YEAR(d) = 2020, c, 1)

if ($c != 3)

{

  --die The dates 2020-00-00 and 2020-01-00 are not found by YEAR(d) = 2020

}

DROP TABLE t1;

Leads to:

mysqltest: At line 6: The dates 2020-00-00 and 2020-01-00 are not found by YEAR(d) = 2020

The index changes the query result. Without KEY (d) on the same data, the query finds 3 rows. In the SELECT list, YEAR(d) = 2020 is 1 for 3 rows. With IGNORE INDEX (d), the query still finds 1 row. YEAR(d) < 2020 finds 3 rows instead of 1. On a DATETIME and on a TIMESTAMP column, YEAR(d) = 0 does not find the zero dates. A prepared statement with YEAR(d) = ?, and sql_mode NO_ZERO_IN_DATE or TRADITIONAL, give the same. Optimized builds give the same results.

MySQL is not affected: with sql_mode='', MySQL 5.5.62 to 9.1.0 find the 3 rows, and give the right result for the other queries too.

From the code it seems that create_start_bound() in sql/opt_rewrite_date_cmp.h makes the start bound of a year with month 1 and day 1. EXPLAIN EXTENDED shows YEAR(d) = 2020 as d BETWEEN '2020-01-01' AND '2020-12-31'. A DATE with a zero month or a zero day, such as '2020-00-00' or '2020-01-00', has YEAR() 2020, but it is less than '2020-01-01'.