Return to Answer

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.

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.

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 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-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';
Empty set (0.00 sec)

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)

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)

(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 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-07-31 |
| 2016-07-31 00:00:00 | 2006-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';
Empty set (0.00 sec)

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)

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.