An index on a DATETIME column changes the query result: DATE(dt) = '2020-01-01 10:00:00' finds the rows of 2020-01-01

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

CREATE TABLE t1 (dt DATETIME, KEY (dt));

INSERT INTO t1 VALUES ('2019-12-31 23:00:00'),('2020-01-01 00:00:00'),('2020-01-01 12:00:00'),('2020-01-02 00:00:00');

--let $c= query_get_value(SELECT COUNT(*) AS c FROM t1 WHERE DATE(dt) = '2020-01-01 10:00:00', c, 1)

if ($c != 0)

{

  --die Rows are found by DATE(dt) = '2020-01-01 10:00:00', which is false for every row as the comparison is DATETIME

}

DROP TABLE t1;

Leads to:

mysqltest: At line 6: Rows are found by DATE(dt) = '2020-01-01 10:00:00', which is false for every row as the comparison is DATETIME

The index changes the query result. Without KEY (dt) on the same data, the query finds 0 rows. In the SELECT list, DATE(dt) = '2020-01-01 10:00:00' is 0 for every row. With IGNORE INDEX (dt), the query still finds 2 rows. DATE(dt) < '2020-01-01 10:00:00' finds 1 row instead of 3, and DATE(ts) >= '2020-01-01 10:00:00' on a TIMESTAMP column finds 3 rows instead of 1. A prepared statement with DATE(dt) = ? gives the same. Optimized builds give the same results.

MySQL is not affected: MySQL 5.5.62 to 9.1.0 find 0, 3 and 1 rows for the three queries, the same as without the key.

From the code it seems that Date_cmp_func_rewriter::create_bound() in sql/opt_rewrite_date_cmp.cc, which create_start_bound() and create_end_bound() call, keeps only the day of the constant, and uses the start or the end of that day as the bound for a DATETIME or TIMESTAMP column. EXPLAIN EXTENDED shows DATE(dt) = '2020-01-01 10:00:00' as dt BETWEEN '2020-01-01 00:00:00' AND '2020-01-01 23:59:59'. The original comparison uses the full constant, 10:00:00 included.