#5626 ActiveRecord generates invalid SQL when eager loading a hmt association with an order - 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.

#5626 ✓resolved

ActiveRecord generates invalid SQL when eager loading a hmt association with an order

Reported by Ryan Wallace | September 13th, 2010 @ 04:26 PM | in 2.3.10

Given:

class Author < ActiveRecord::Base
  has_many :posts
  has_many :comments_desc, :through => :posts, :source => :comments, :order => 'comments.id DESC'
end

Running:

Author.first(:include => :comments_desc)

Will generate the following invalid SQL:

SELECT "posts".* FROM "posts" WHERE ("posts".author_id = 1)  ORDER BY comments.id DESC

This issue was not present in 2.3.5 but occurs in 2.3.9.

I've attached a failing test case which demonstrates this issue.

eager-load-hmt-with-order.diff 1.3 KB

Comments and changes to this ticket

You flagged this item as spam.
Ryan Wallace September 13th, 2010 @ 04:43 PM
- no changes were found...
You flagged this item as spam.
Ryan Wallace September 13th, 2010 @ 05:05 PM
The issue seems to have been introduced in this commit:
http://github.com/rails/rails/commit/0963774c0a6a58ba13ac0ff4763527...

Though it looks like before this commit Rails would generate valid SQL, but the order option would not be respected (therefore this test still fails in Rails 2.3.5 though for a different reason).
You flagged this item as spam.
Ryan Wallace September 13th, 2010 @ 05:10 PM
I'm thinking it might make sense to fall back to a table join strategy (as mentioned in the ActiveRecord::AssociationPreload documentation) though I'm not sure how to do this yet. Let me know if I'm way off base on this.
You flagged this item as spam.
Ryan Wallace September 15th, 2010 @ 05:23 PM
- Tag changed from “2.3.9” to “2.3.9, activerecord, eager_loading, has_many_through”
You flagged this item as spam.
Marcelo Giorgi September 16th, 2010 @ 02:57 AM
Hi Ryan,

I've just implement a patch for this.

My work was based on your test, so I've added 2 patch files, 1 with your test (where you are the author), and the second one which is the fix I've implemented.

This patches applies cleanly on 2-3-stable

Any suggestions are welcome.
- 0001-add-test-to-demonstrate-failure-with-eager-loading-h.patch 1.3 KB
- 0002-preserving-include-options-for-hmt-association-with.patch 1.3 KB
You flagged this item as spam.
Marcelo Giorgi September 16th, 2010 @ 02:58 AM
- Assigned user set to “Santiago Pastorino”
- Tag changed from “2.3.9, activerecord, eager_loading, has_many_through” to “2.3.9, activerecord, eager_loading, has_many_through, patched”
You flagged this item as spam.
Ryan Wallace September 23rd, 2010 @ 10:31 PM
Hi Marcelo,

Thanks for drafting up a patch! I did a bit of experimenting and I've found one situation where invalid SQL will still be generated:
```
  class Author < ActiveRecord::Base
    has_many :posts
    has_many :comments_desc, :through => :posts, :source => :comments, :order => 'body DESC'
  end

  Author.first(:include => :comments_desc)
```
Basically, if you don't specify the tables name in the order clause, the SQL will be invalid.

Do you think we should cover this case too or just make a note somewhere? My opinion is that it would be okay to leave it like this. It is a good practice to provide the table name in the order clause anyhow.

Ryan
Santiago Pastorino September 24th, 2010 @ 12:06 AM
- Milestone set to “2.3.10”
- State changed from “new” to “open”
- Assigned user changed from “Santiago Pastorino” to “Aaron Patterson”
- Importance changed from “” to “Low”
You flagged this item as spam.
Marcelo Giorgi September 24th, 2010 @ 02:02 AM
- Assigned user changed from “Aaron Patterson” to “Santiago Pastorino”
Hi Ryan,

I think the bug that you found is a different problem, in fact I think it is a more general issue of Rails. As you mentioned it is arguable that the developer should include the table name explicitly in the order clause...

Also, if you look into the documentation (http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassM..., under the title 'Eager loading of associations', this behavior is already documented, where it says:

...You must disambiguate column references for this fallback to happen, for example :order => 'author.name DESC' will work but :order => "name DESC" will not...

Just to keep the commit aligned with the original intention of the problem at hand, perhaps, we can create a new ticket to discuss this other issue, do you agree ?
You flagged this item as spam.
Ryan Wallace September 24th, 2010 @ 03:13 PM
Oh perfect. Perhaps I should have read the documentation before asking for that case to be documented! +1 for this patch from me. Small, simple, and effective.

Thanks again Marcelo.

Ryan
You flagged this item as spam.
Patrick Campbell September 27th, 2010 @ 11:54 PM
Tested with environment ree-1.8.7-2010.01.

The test passed successfully and the implementation makes sense to me. Also, I agree that the order clause must have the attributes fully qualified when there is room for ambiguity.

+1
Santiago Pastorino September 28th, 2010 @ 05:44 PM
- Assigned user changed from “Santiago Pastorino” to “Aaron Patterson”
Aaron Patterson September 28th, 2010 @ 06:09 PM
- State changed from “open” to “resolved”
These patches are applied. Thanks!
You flagged this item as spam.
Andrea Campi October 11th, 2010 @ 07:22 AM
- Tag changed from “2.3.9, activerecord, eager_loading, has_many_through, patched” to “2.3.9, activerecord, eager_loading, has_many_through, patch”
bulk tags cleanup
You flagged this item as spam.
klkk May 23rd, 2011 @ 02:53 AM
louisvuittonwarehouse.com
You flagged this item as spam.
klkk May 23rd, 2011 @ 02:55 AM
www.louisvuittonwarehouse.com