This project is archived and is in readonly mode.

#1711 ✓invalid

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

Reported by Winky | January 7th, 2009 @ 10:36 PM | in 3.x

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

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


Which results in the following SQL being generated:

SELECT DISTINCT "people".* FROM "people" INNER JOIN memberships ON = 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.

Comments and changes to this ticket

  • Winky
  • Frederick Cheung

    Frederick Cheung January 8th, 2009 @ 02:28 PM

    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).

  • Winky

    Winky January 8th, 2009 @ 04:15 PM

    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.

  • Pratik

    Pratik March 8th, 2009 @ 04:45 PM

    • State changed from “new” to “incomplete”

    Patch needs tests.


  • Bjørn Arild Mæland

    Bjørn Arild Mæland July 2nd, 2009 @ 04:49 PM

    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.

  • Gravis

    Gravis October 17th, 2009 @ 02:51 PM

    +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).


  • Sean

    Sean November 11th, 2009 @ 05:56 PM

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

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Jon Leighton

    Jon Leighton December 12th, 2010 @ 01:29 PM

    • 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.

  • Aaron Patterson

    Aaron Patterson December 15th, 2010 @ 07:25 PM

    • State changed from “incomplete” to “invalid”

    I agree with Jon. Closing this ticket as invalid.

  • Lance Carlson

    Lance Carlson May 11th, 2011 @ 04:44 PM

    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!

  • Lance Carlson

Create your profile

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

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>