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.

Running from jws,  jre 5.0 was required and now it’s fixed to support 5.0 or greater.If you experienced proplem running shoka via jws with jre 6.0, please try again.

thanks to Thermopyle ( more )