Query with GROUP BY DATE_SUB(...) Fails

3 posts / 0 new
Last post
jon-eachscape
jon-eachscape's picture
Offline
Last seen: 1 year 8 months ago
Joined: Jul 1 2011
Junior Boarder

Posts: 4

Jon Botelho
Query with GROUP BY DATE_SUB(...) Fails

I just copied all my data from MySQL to InfiniDB, and after running some test queries, I'm noticing one query in particular that isn't working properly.

I have a query that is supposed to find how many unique clients had a session in a given week. To do this, I have constructed this query:
[code]
SELECT SUBDATE(DATE(report_time), WEEKDAY(report_time)) AS t, COUNT(DISTINCT client_id) AS n FROM sessions GROUP BY t;
[/code]

in MySQL, I get about 60 results, like this:
[code]
+------------+-------+
| t | n |
+------------+-------+
| 2011-01-10 | 8498 |
| 2011-01-17 | 10133 |
| 2011-01-24 | 11328 |
| 2011-01-31 | 11308 |
| 2011-02-07 | 9964 |
| 2011-02-14 | 9585 |
...
| 2011-06-20 | 15228 |
| 2011-06-27 | 14077 |
| 2011-07-04 | 7201 |
+------------+-------+
[/code]

In InfiniDB, the same query returns back over 100,000 results, with duplicate values for t. The only difference from the data on my MySQL server (which is on a separate machine) is that the storage engine is InfiniDB instead of InnoDB.

Any sort of fix or workaround for this would be greatly appreciated.

radams
radams's picture
Offline
Last seen: 4 days 12 hours ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: Query with GROUP BY DATE_SUB(...) Fails

Please make sure you are running on InfiniDB 2.1.1 or higher.

It looks like we have a bug in the WEEKDAY function. As a work around
you can use DAYOFWEEK

https://bugs.launchpad.net/infinidb/+bug/807580

SELECT SUBDATE(DATE(report_time), (DAYOFWEEK(report_time)+5)%7 ) AS t, COUNT(DISTINCT client_id) AS n FROM sessions GROUP BY t;

Thanks,

Robert
Calpont

jon-eachscape
jon-eachscape's picture
Offline
Last seen: 1 year 8 months ago
Joined: Jul 1 2011
Junior Boarder

Posts: 4

Jon Botelho
Re: Query with GROUP BY DATE_SUB(...) Fails

It turns out my original query works fine (I'm on InfiniDB 2.2 Community). Some of the tables were set to use varchar instead of datetime for the report_time column.