Description:
I've been working on the daily sales reports, by day with the time added. I have run into something unexpected. All the tables are Innodb.
I run the query:
mysql> select sale_num, today_date, today_time from mainsale where addtime(today_date, today_time ) >= '2009-2-27 00:00:00' and addtime( today_date, today_time ) <= '2009-2-27 24:00:00' and clerk='webtix';
+----------+------------+------------+
| sale_num | today_date | today_time |
+----------+------------+------------+
| 108129 | 2009-02-27 | 19:52 |
| 108366 | 2009-02-28 | 10:49 |<--
| 108394 | 2009-02-28 | 00:36 |<--
| 108481 | 2009-04-16 | 23:54 |<--
| 109051 | 2009-02-27 | 19:37 |
| 109865 | 2009-02-27 | 10:48 |
| 109868 | 2009-02-27 | 10:45 |
| 109879 | 2009-02-27 | 11:40 |
| 109882 | 2009-02-27 | 10:37 |
| 109974 | 2009-02-27 | 16:26 |
| 109982 | 2009-02-27 | 16:25 |
| 109986 | 2009-02-27 | 16:24 |
| 110028 | 2009-02-27 | 19:44 |
+----------+------------+------------+
13 rows in set
This is wrong. Look at records 2, 2 & 4. They are for the wrong date. Now, I change the query slightly to:
mysql> select sale_num, today_date, today_time from mainsale where addtime(today_date, today_time ) >= '2009-02-27 00:00:00' and addtime( today_date, today_time ) <= '2009-02-27 24:00:00' and clerk='webtix';
+----------+------------+------------+
| sale_num | today_date | today_time |
+----------+------------+------------+
| 108129 | 2009-02-27 | 19:52 |
| 109051 | 2009-02-27 | 19:37 |
| 109865 | 2009-02-27 | 10:48 |
| 109868 | 2009-02-27 | 10:45 |
| 109879 | 2009-02-27 | 11:40 |
| 109882 | 2009-02-27 | 10:37 |
| 109974 | 2009-02-27 | 16:26 |
| 109982 | 2009-02-27 | 16:25 |
| 109986 | 2009-02-27 | 16:24 |
| 110028 | 2009-02-27 | 19:44 |
+----------+------------+------------+
10 rows in set
And, these query results are correct.
Have I found a bug in MySQL?
Oddly enough, it only seems to happen with the addtime() function. If I use this query, I get:
mysql> select sale_num, today_date, today_time from mainsale where today_date >= '2009-2-27' and today_date <= '2009-2-27' and clerk='Webtix';
+----------+------------+------------+
| sale_num | today_date | today_time |
+----------+------------+------------+
| 109882 | 2009-02-27 | 10:37 |
| 109868 | 2009-02-27 | 10:45 |
| 109865 | 2009-02-27 | 10:48 |
| 109879 | 2009-02-27 | 11:40 |
| 109986 | 2009-02-27 | 16:24 |
| 109982 | 2009-02-27 | 16:25 |
| 109974 | 2009-02-27 | 16:26 |
| 109051 | 2009-02-27 | 19:37 |
| 110028 | 2009-02-27 | 19:44 |
| 108129 | 2009-02-27 | 19:52 |
+----------+------------+------------+
10 rows in set
How to repeat:
Add leading zeros to the date in addtime() and then remove them
Suggested fix:
Have WySQL check for 2 digits in the month and day. Then, add the zero if necessary.
Description: I've been working on the daily sales reports, by day with the time added. I have run into something unexpected. All the tables are Innodb. I run the query: mysql> select sale_num, today_date, today_time from mainsale where addtime(today_date, today_time ) >= '2009-2-27 00:00:00' and addtime( today_date, today_time ) <= '2009-2-27 24:00:00' and clerk='webtix'; +----------+------------+------------+ | sale_num | today_date | today_time | +----------+------------+------------+ | 108129 | 2009-02-27 | 19:52 | | 108366 | 2009-02-28 | 10:49 |<-- | 108394 | 2009-02-28 | 00:36 |<-- | 108481 | 2009-04-16 | 23:54 |<-- | 109051 | 2009-02-27 | 19:37 | | 109865 | 2009-02-27 | 10:48 | | 109868 | 2009-02-27 | 10:45 | | 109879 | 2009-02-27 | 11:40 | | 109882 | 2009-02-27 | 10:37 | | 109974 | 2009-02-27 | 16:26 | | 109982 | 2009-02-27 | 16:25 | | 109986 | 2009-02-27 | 16:24 | | 110028 | 2009-02-27 | 19:44 | +----------+------------+------------+ 13 rows in set This is wrong. Look at records 2, 2 & 4. They are for the wrong date. Now, I change the query slightly to: mysql> select sale_num, today_date, today_time from mainsale where addtime(today_date, today_time ) >= '2009-02-27 00:00:00' and addtime( today_date, today_time ) <= '2009-02-27 24:00:00' and clerk='webtix'; +----------+------------+------------+ | sale_num | today_date | today_time | +----------+------------+------------+ | 108129 | 2009-02-27 | 19:52 | | 109051 | 2009-02-27 | 19:37 | | 109865 | 2009-02-27 | 10:48 | | 109868 | 2009-02-27 | 10:45 | | 109879 | 2009-02-27 | 11:40 | | 109882 | 2009-02-27 | 10:37 | | 109974 | 2009-02-27 | 16:26 | | 109982 | 2009-02-27 | 16:25 | | 109986 | 2009-02-27 | 16:24 | | 110028 | 2009-02-27 | 19:44 | +----------+------------+------------+ 10 rows in set And, these query results are correct. Have I found a bug in MySQL? Oddly enough, it only seems to happen with the addtime() function. If I use this query, I get: mysql> select sale_num, today_date, today_time from mainsale where today_date >= '2009-2-27' and today_date <= '2009-2-27' and clerk='Webtix'; +----------+------------+------------+ | sale_num | today_date | today_time | +----------+------------+------------+ | 109882 | 2009-02-27 | 10:37 | | 109868 | 2009-02-27 | 10:45 | | 109865 | 2009-02-27 | 10:48 | | 109879 | 2009-02-27 | 11:40 | | 109986 | 2009-02-27 | 16:24 | | 109982 | 2009-02-27 | 16:25 | | 109974 | 2009-02-27 | 16:26 | | 109051 | 2009-02-27 | 19:37 | | 110028 | 2009-02-27 | 19:44 | | 108129 | 2009-02-27 | 19:52 | +----------+------------+------------+ 10 rows in set How to repeat: Add leading zeros to the date in addtime() and then remove them Suggested fix: Have WySQL check for 2 digits in the month and day. Then, add the zero if necessary.