Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Has many through association with :order causes a SQL error with PostgreSQL

#1711

A has_many :through association with an :order option generates SQL which causes PostgreSQL (and possibly others) to report the error:

PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Take the following example model:


class Group < ActiveRecord::Base
  has_many :memberships
  has_many :people, :through => :memberships, :order => "memberships.renewal_date", :uniq => true
end

I then load a fixture for some People and the Group with id=1. Using the console, I can run:


Group.find(1).people

Which results in the following SQL being generated:


SELECT DISTINCT "people".* FROM "people" INNER JOIN memberships ON people.id = memberships.person_id WHERE (("memberships".group_id = 1)) ORDER BY memberships.renewal_date

The "memberships.renewal_date" column does not appear in the SELECT field list and thus causes the error.

The attached diff appears to fix the issue. Whether or not it does so correctly, I leave to the experts.

Reported by Winky · January 7th, 2009 @ 10:36 PM

State: invalid
Milestone: 3.x
Assigned to: Aaron Patterson Aaron Patterson
Importance: none

Activity

  1. Winky
    Winky

    January 7th, 2009 @ 10:39 PM

  2. Frederick Cheung
    Frederick Cheung

    Won't this cause the column use for ordering to appear (piggy backed) in the person objects thus loaded ? Probably relatively harmless usually but could cause grief if it ends up shadowing a column (at least for users of other databases).

    January 8th, 2009 @ 02:28 PM

  3. Winky
    Winky

    I'm not familiar with rails internals and attribute processing, but definitely a risk. Might consider skipping attribute processing for fields not from the primary table in this case, but then the SQL building will have to be altered to ensure that all ORDER BY fields on the association table are fully qualified with the table name.

    I also found that removing the :order option to the association fixes the problem, but I'm not sure that solution is any better if you're dealing with an order-sensitive association.

    I defer to you guys to determine the best and most elegant solution.

    January 8th, 2009 @ 04:15 PM

  4. Pratik
    Pratik
    • State changed from new to incomplete

    Patch needs tests.

    Thanks.

    March 8th, 2009 @ 04:45 PM

  5. Bjørn Arild Mæland
    Bjørn Arild Mæland

    Is anyone working on this patch? The error is very annoying as it also occurs if the associated model defines a default_scope with ordering.

    July 2nd, 2009 @ 04:49 PM

  6. Gravis
    Gravis

    +1 I fell into this problem today.
    I'm using a :select => "DISTINCT column_thing" and the generated SQL is failing because of the default order by not part of the selected rows (which I don't want of course).

    Thanks

    October 17th, 2009 @ 02:51 PM

  7. Sean
    Sean

    +1 experiencing the same problem. In my case, removing DISTINCT from the SQL statement (or :uniq from the AR command) solves the problem.

    November 11th, 2009 @ 05:56 PM

  8. Jeremy Kemper
    Jeremy Kemper
    • Milestone changed from 2.x to 3.x

    May 4th, 2010 @ 06:48 PM

  9. Jon Leighton
    Jon Leighton
    • Assigned user set to Aaron Patterson
    • Importance changed from to

    It's not clear what the expected result should be, which is why postgres throws an error.

    Due to the use of DISINCT, for any given Person in the output, there may be many different renewal_date values. Which is the correct one to use for the sort? The answer is ambiguous, hence why postgres gives an error.

    I'm not in favour of Active Record trying to magically resolve this ambiguity by making an assumption, so I'd say we should close the ticket.

    December 12th, 2010 @ 01:29 PM

  10. Aaron Patterson
    Aaron Patterson
    • State changed from incomplete to invalid

    I agree with Jon. Closing this ticket as invalid.

    December 15th, 2010 @ 07:25 PM

  11. Lance Carlson
    Lance Carlson

    I disagree with Jon. Since the assumption is that memberships should be unique (:uniq => true), the Person object should only have one membership and thus should only have one renewal_date to compare against. The intent is pretty explicit in this case and thus I feel AR can make the solid assumption that there won't be ambiguity amongst multiple membership objects.

    I think this ticket should be re-opened and solved because I'm pretty sure this behavior works (magically) in MySQL with this exact syntax, so Postgres should behave the same.

    The only work around I've found for this has been to eliminate the :uniq property and specify:

    :select => 'DISTINCT people.*, memberships.renewal_date', :order => "memberships.renewal_date ASC"

    which is not a full solution because with :uniq disabled, multiple memberships objects can get created.

    Since my app is heavily JSON based, I've resorted to crafting the JSON instead of trying to hack AR. The end result is more queries, but I had to solve it somehow. :)

    +1000 to testing this patch and pulling it in if it works!

    May 11th, 2011 @ 04:44 PM

  12. Lance Carlson
    Lance Carlson

    May 11th, 2011 @ 05:05 PM