MySQL Coalesce date missing results, why?

0 votes

2 answers

3087 views

                          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?

Asked by Mr Redstoner (101 rep)

Dec 20, 2019, 02:01 PM
Last activity: Dec 22, 2019, 05:13 PM

MySQL Coalesce date missing results, why?

Related Questions