Running MySQL 8.0.16, settings left at defaults as far as I know.
I have a table, let's call it the_table, with columns
id: bigint primary key auto_increment
the_time: datetime
I have a record with the_time of 2019-12-19 00:00:00.
When I run either of
SELECT id, the_time
FROM the_table
WHERE the_time BETWEEN '2019-12-19' AND '2019-12-19';
SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, DATE('9999-1-1')) BETWEEN '2019-12-19' AND '2019-12-19';
I get that record. However, with
SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-19' AND '2019-12-19';
I do not, though changing it to
SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-18' AND '2019-12-20';
I suddenly do again. Why does MySQL behave this way? Why does the extra DATE() make a difference? I would have thought converting between date and string formats would be bijective and shouldn't affect the computation, but it seems it does. And why does just the coalesce make a difference in the not-null record being found or not?
Best Answer
Because it converts the datetimes to strings and does the comparisons with the string values. And
'2019-12-19 00:00:00' <= '2019-12-19'is false in text comparison.