MySQL Coalesce date missing results, why?

Question

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?

Answer 1

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.

Answer 2

Don't use BETWEEN for checking a date or datetime range; it is inclusive, so you get the second (usually midnight) at both ends.

For checking DATE or DATETIME against one "day", do this:

WHERE col >= '2019-12-19'
  AND col  < '2019-12-19' + INTERVAL 1 DAY

That will work 'correctly' whether col is a DATE, DATETIME, or DATETIME(6). And the Optimizer is generally happy with that expression.

Also, keep in mind that CURDATE() is midnight of this morning.

(Rebuttal to ypercube:)

Perhaps this demonstrates that the issue is with the datatype returned by COALESCE, not the existence of 00:00:00:

mysql> SELECT dt FROM dtts WHERE COALESCE(dt, '9999-1-1')
                        between '2016-07-31' and '2016-07-31';
Empty set (0.00 sec)

mysql> SELECT dt, just_date FROM dtts WHERE COALESCE(dt, DATE('9999-1-1'))
                        between '2016-07-31' and '2016-07-31';
+---------------------+------------+
| dt                  | just_date  |
+---------------------+------------+
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
+---------------------+------------+
5 rows in set (0.00 sec)


mysql> SELECT dt, just_date FROM dtts WHERE COALESCE(just_date, '9999-1-1')
                        between '2016-07-31' and '2016-07-31';
+---------------------+------------+
| dt                  | just_date  |
+---------------------+------------+
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
| 2016-07-31 00:00:00 | 2016-07-31 |
+---------------------+------------+

mysql> SELECT dt, just_date FROM dtts WHERE COALESCE(just_date, '9999-1-1')
                        between '2016-07-31 00:00:00' and '2016-07-31 00:00:00';
Empty set (0.00 sec)

Coalesce seems to return DATE or DATETIME:

mysql> SELECT dt, COALESCE(dt, DATE('9999-1-1')), just_date, COALESCE(just_date, DATE('9999-1-1')) from dtts;
+---------------------+--------------------------------+------------+---------------------------------------+
| dt                  | COALESCE(dt, DATE('9999-1-1')) | just_date  | COALESCE(just_date, DATE('9999-1-1')) |
+---------------------+--------------------------------+------------+---------------------------------------+
| 2011-06-08 20:45:55 | 2011-06-08 20:45:55            | 2011-06-08 | 2011-06-08                            |
| 2013-03-10 02:35:47 | 2013-03-10 02:35:47            | 2013-03-10 | 2013-03-10                            |
| 2014-02-08 09:36:48 | 2014-02-08 09:36:48            | 2014-02-08 | 2014-02-08                            |
| 2016-07-31 00:00:00 | 2016-07-31 00:00:00            | 2016-07-31 | 2016-07-31                            |
| NULL                | 9999-01-01 00:00:00            | 0000-00-00 | 0000-00-00                            |

Perhaps if the non-null arguments of COALESCE have different datatypes, then there is a pecking order, something like INT > string > datetime. I found this for including INT.