aggregates (size) "broken" w/ :limit

[Gary Yngve]

With dm-aggregates, size does not produce the expected answer with a query containing limit.

Simple example:

>> 3.times {Thing.gen}

=> 3

>> Thing.all(:limit=>2).size

=> 3

>> Thing.all(:limit=>2).map{1}.size   # my new fave idiom until this bug is fixed

=> 2

Queries w/ limit come up a lot when working with pagination, so it would be really nice to get this fixed.

(Someone writing MVC Merb code should not have to know about this weirdness.)

This issue is with the data_objects/mysql adapter.  The bug is that DM translates the query 

"Thing.all(:limit=>2).size" into:

"SELECT COUNT(*) FROM `things` LIMIT 2"

which MySql interprets as ( SELECT COUNT(*) FROM `things` ), return one row, then LIMIT 2,

and which Postgres complains as non-kosher SQL.

Note that ActiveRecord returns Thing.all(:limit=>2) as an Array, so .size() works just fine there (it's buggy though with its auto-conversion of find_by_sql to count_by_sql).

It seems like the fix would involve something like translating the query into 

"SELECT COUNT(*) FROM (SELECT 'things' LIMIT 2) tmp"

and translating something like

"SELECT SUM(mass) FROM `things` LIMIT 2"

into

"SELECT SUM(tmp.mass) FROM (SELECT `things` LIMIT 2) tmp"

I imagine it would get more complex with nested queries / associations.

Anyone else have any thoughts?

Thanks,

Gary

[Dan Kubb (dkubb)]

Hi Gary,

On Dec 9, 3:23 am, "Gary Yngve" <gary.yn...@gmail.com> wrote:
> With dm-aggregates, size does not produce the expected answer with a query
> containing limit.
>

> >> Thing.all(:limit=>2).map{1}.size # my new fave idiom until this bug is

In the meantime you should be able to use:

Thing.all(:limit=>2).to_a.size # my new fave idiom until this bug
is

Can you create a ticket with this issue in our bug tracker:

http://datamapper.lighthouseapp.com/

If you can include a stand-alone script that duplicates the problem.
Here's an example script:

http://gist.github.com/29902

Failing specs are also welcome, but a stand-alone script is usually
enough for us to track down the issue, create the specs ourselves, and
then verify the fixes. It would be nice to have a few of the failing
scenarios outlined.

I'm wondering if perhaps aliasing Collection#size to Collection#count
is a bit premature. Obviously we need alot more test cases to ensure
the results are correct. Worst case we'll remove it prior to the next
release, but I'll see what I can do about fixing it on edge first.

Dan
(dkubb)

[Dan Kubb (dkubb)]

Gary,

> With dm-aggregates, size does not produce the expected answer with a query
> containing limit.

I just updated dm-aggregates on edge to use Array#size underneath,
like before. Can you give it a try in sam/dm-more and see if it fixes
your problem?

Dan
(dkubb)

[Gary]

It's fixed for size when both Collection and Model use Array#size
underneath.

It also fixes size for a find_by_sql query (the find_by_sql query is
really a second-class citizen
because it does not have any support for chaining or aggregates).

-Gary