#1711 Has many through association with :order causes a SQL error with PostgreSQL - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

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

Comments and changes to this ticket

Winky January 7th, 2009 @ 10:39 PM
- no changes were found...
- has_many_through_with_order.diff 692 Bytes
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).
You flagged this item as spam.
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 March 8th, 2009 @ 04:45 PM
- State changed from “new” to “incomplete”
Patch needs tests.

Thanks.
You flagged this item as spam.
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.
You flagged this item as spam.
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).

Thanks
You flagged this item as spam.
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 May 4th, 2010 @ 06:48 PM
- Milestone changed from “2.x” to “3.x”
[bulk edit]
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 December 15th, 2010 @ 07:25 PM
- State changed from “incomplete” to “invalid”
I agree with Jon. Closing this ticket as invalid.
You flagged this item as spam.
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!
You flagged this item as spam.
Lance Carlson May 11th, 2011 @ 05:05 PM
Created a github issue:

https://github.com/rails/rails/issues/520