#17 ✓resolved
Georg Ledermann

BUG: DISTINCT is used for SUM

Reported by Georg Ledermann | October 24th, 2008 @ 06:26 AM

There is something wrong with the automatic DISTINCT. Please have a look at the following example:

Order.build_search.sum(:amount)

This generates the following SQL:

SELECT sum(DISTINCT `orders`.amount) AS sum_amount
FROM `orders`

The DISTINCT is not correct here.

Testet with - Searchgasm 1.5.3 released 2008-10-21 - Rails 2.1.2 (released yesterday)

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson October 24th, 2008 @ 11:53 AM

    • State changed from “new” to “open”

    I added a condition to only enforce this if joins are present. The problem is when you start joining tables, you will get duplicate orders which will return a false sum. Also ActiveRecord created the :distinct option for calculations, so I would assume they would handle it properly.

    What do you think?

  • Georg Ledermann

    Georg Ledermann October 25th, 2008 @ 08:13 AM

    IMHO ActiveRecord uses DISTINCT only for COUNT (if :include is given), not for other calucations like SUM etc. So using DISTINCT for SUM/MAX/MIN etc. should be avoided in every cases.

    See this example using joins:

    LineItem.build_search(:conditions => { :order => { :user => { :name_starts_with => 'A' }}}).sum(:amount)
    

    (Sum up the amount of all line items which belong to User which name is starting with 'A')

    Searchgasm builds "SUM(DISTINTC amount) FROM ..." I think this is not correct. I think summing up only distinct values is always wrong.

    BTW, if you use the following syntax, no DISTINCT is generated:

    LineItem.scoped(:conditions => { :order => { :user => { :name_starts_with => 'A' }}}).sum(:amount)
    

    I think the automatic DISTINCT should be used for COUNT only.

  • Ben Johnson

    Ben Johnson October 25th, 2008 @ 11:35 AM

    • State changed from “open” to “resolved”

    Done, the automatic distinct is only used on count and when their are joins. Thanks for letting me know about this.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Provides common named scopes and object based searching.

People watching this ticket

Pages