Bugfix: DATE vs DATETIME comparisons are now sargable again

We (me coding, Igor and Evgen reviewing) have fixed BUG#32198. According to Support/Bugs team, the bug was causing pain to a number of people, so I thought it deserved a post. The problem was as follows:

• Before MySQL 5.0.42, comparisons in form

    date_column CMP datetime_const

  were interpreted as comparisons of DATEs (CMP is one of =, <, > , <=, or >=). The time part of datetime_const was ignored.

• In 5.0.42 we’ve fixed it to perform in a way that is closer to the SQL standard: the comparisons are now resolved by comparing values as DATETIMEs. Our fault was that “date_column CMP datetime_const” stopped being sargable. It turned out there quite a few people who had queries like

    SELECT ... WHERE date_column < NOW() ... 

  and those queries became awfully slow.

• In 5.0.54, We’ve fixed BUG#32198 and made “date_column CMP datetime_const” sargable again. Apologies to everyone who was affected.

What keeps me concerned is that both old and new interpretation of DATE/DATETIME comparisons are not compatible with PostgreSQL. PostgreSQL does something strange: comparison of DATE column with DATETIME constant is interpreted as DATETIME comparison, while comparison with NOW() seems to be interpreted as comparison of DATEs:

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2007-12-16 18:24:12 |
+---------------------+
1 row in set (0.01 sec)

test=# select now();
              now
-------------------------------
 2007-12-16 18:23:47.197907+03
(1 row)

mysql> select a from tbl where date_col=now();
Empty set (0.02 sec)

test=# select a from tbl where date_col=now();
 a
---
(0 rows)

mysql> select a from tbl
    -> where date_col='2007-12-16 18:24:12';
Empty set (0.00 sec)

test=# select a from tbl
test-# where date_col='2007-12-16 18:24:12';
     a
------------
 2007-12-16
(1 row)

I don’t know what is the logic behind this (or is this a bug in PostgreSQL?) If you do, please let me know.

In my SQL code, I think I’m going to play it safe and always explicitly cast to either DATE or DATETIME.