Description:
The following is somewhat bizarre.
In the where clause if you compare date(date_field) with a string (e.g. '2007-07-20')
then the comparison will return "false" if hour(date_field) > 21.
How to repeat:
Here is a query that was run on mysql 5.0.37
mysql> select event_id, scheduled_date, date(scheduled_date), event_summary from fs_event
where date(scheduled_date) = '2007-07-20' order by scheduled_date;
+----------+---------------------+----------------------+---------------+
| event_id | scheduled_date | date(scheduled_date) | event_summary |
+----------+---------------------+----------------------+---------------+
| 11726 | 2007-07-20 14:20:00 | 2007-07-20 | F 2-6 |
| 13700 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-1 |
| 13062 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-10 |
| 12460 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-1 |
| 9068 | 2007-07-20 19:05:00 | 2007-07-20 | F 10-2 |
| 9993 | 2007-07-20 19:05:00 | 2007-07-20 | F 14-4 |
| 9833 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-10 |
| 10295 | 2007-07-20 19:07:00 | 2007-07-20 | F 4-2 |
| 12896 | 2007-07-20 19:35:00 | 2007-07-20 | F 4-2 |
| 12342 | 2007-07-20 20:05:00 | 2007-07-20 | F 8-4 |
| 9276 | 2007-07-20 20:10:00 | 2007-07-20 | F 5-7 |
| 11196 | 2007-07-20 20:35:00 | 2007-07-20 | F 3-2 |
| 10640 | 2007-07-20 22:05:00 | 2007-07-20 | F 6-1 |
| 14381 | 2007-07-20 22:05:00 | 2007-07-20 | F 7-3 |
| 14188 | 2007-07-20 22:40:00 | 2007-07-20 | F 4-1 |
+----------+---------------------+----------------------+---------------+
15 rows in set (0.02 sec)
And then we upgraded to mysql 5.0.45
mysql> select event_id, scheduled_date, date(scheduled_date), event_summary from fs_event
where date(scheduled_date) = '2007-07-20' order by scheduled_date;
+----------+---------------------+----------------------+---------------+
| event_id | scheduled_date | date(scheduled_date) | event_summary |
+----------+---------------------+----------------------+---------------+
| 11726 | 2007-07-20 14:20:00 | 2007-07-20 | F 2-6 |
| 13700 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-1 |
| 13062 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-10 |
| 12460 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-1 |
| 9068 | 2007-07-20 19:05:00 | 2007-07-20 | F 10-2 |
| 9993 | 2007-07-20 19:05:00 | 2007-07-20 | F 14-4 |
| 9833 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-10 |
| 10295 | 2007-07-20 19:07:00 | 2007-07-20 | F 4-2 |
| 12896 | 2007-07-20 19:35:00 | 2007-07-20 | F 4-2 |
| 12342 | 2007-07-20 20:05:00 | 2007-07-20 | F 8-4 |
| 9276 | 2007-07-20 20:10:00 | 2007-07-20 | F 5-7 |
| 11196 | 2007-07-20 20:35:00 | 2007-07-20 | F 3-2 |
+----------+---------------------+----------------------+---------------+
12 rows in set (0.02 sec)
NOTE: all of the event_id where scheduled_date > 22 were removed. We ran queries for
other days and found that the bridge point seems to be hour 21. Which makes us suspect
some kind of timezone logic in the "date()" function?
Here is how to do a workaround:
Wrapping the date(scheduled_date) in concat seems to convert it to a string and so the
compare works.
mysql> select event_id, scheduled_date, date(scheduled_date), event_summary from fs_event
where concat(date(scheduled_date)) = '2007-07-20' order by scheduled_date;
+----------+---------------------+----------------------+---------------+
| event_id | scheduled_date | date(scheduled_date) | event_summary |
+----------+---------------------+----------------------+---------------+
| 11726 | 2007-07-20 14:20:00 | 2007-07-20 | F 2-6 |
| 13700 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-1 |
| 13062 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-10 |
| 12460 | 2007-07-20 19:05:00 | 2007-07-20 | F 2-1 |
| 9068 | 2007-07-20 19:05:00 | 2007-07-20 | F 10-2 |
| 9993 | 2007-07-20 19:05:00 | 2007-07-20 | F 14-4 |
| 9833 | 2007-07-20 19:05:00 | 2007-07-20 | F 3-10 |
| 10295 | 2007-07-20 19:07:00 | 2007-07-20 | F 4-2 |
| 12896 | 2007-07-20 19:35:00 | 2007-07-20 | F 4-2 |
| 12342 | 2007-07-20 20:05:00 | 2007-07-20 | F 8-4 |
| 9276 | 2007-07-20 20:10:00 | 2007-07-20 | F 5-7 |
| 11196 | 2007-07-20 20:35:00 | 2007-07-20 | F 3-2 |
| 10640 | 2007-07-20 22:05:00 | 2007-07-20 | F 6-1 |
| 14381 | 2007-07-20 22:05:00 | 2007-07-20 | F 7-3 |
| 14188 | 2007-07-20 22:40:00 | 2007-07-20 | F 4-1 |
+----------+---------------------+----------------------+---------------+
15 rows in set (0.00 sec)
