ActiveRecord/Arel query resolves to something weird when attempt to order

I'm attempting a transition from MySQL to what seems to be the stricter and less Rails-friendly PostgreSQL.

I'm running into this funny conundrum:

irb(main):015:0> puts w.to_sql
SELECT DISTINCT ON (vendors.id) vendors.*
FROM "vendors" 
INNER JOIN "locations" ON "locations"."locatable_id" = "vendors"."id"
  AND "locations"."locatable_type" = 'Vendor'
WHERE (locations.latitude IS NOT NULL开发者_StackOverflow AND locations.longitude IS NOT NULL)

But...

irb(main):017:0> puts w.order('vendors.id').to_sql
SELECT * FROM (
  SELECT DISTINCT ON (vendors.id) vendors.*
  FROM "vendors"
  INNER JOIN "locations" ON "locations"."locatable_id" = "vendors"."id"
    AND "locations"."locatable_type" = 'Vendor'
  WHERE (locations.latitude IS NOT NULL AND locations.longitude IS NOT NULL)
) AS id_list ORDER BY id_list.alias_0 

This, despite the fact that just adding ORDER BY vendors.id works just fine as a valid PostgreSQL query. Instead of just adding that, it does something super funny and produces an invalid query at the end of the day:

ActiveRecord::StatementInvalid: PGError: ERROR:  column id_list.alias_0 does not exist
LINE 1: ...tions.longitude IS NOT NULL)) AS id_list ORDER BY id_list.al...

Any clue what I should look at?

I've run into the same problem. Looks like it was a bug in Arel causing DISTINCT ON not to play nice with ORDER when using PostgreSQL.

The original bug is no longer visible since Rails has moved from Lighthouse to github issues but you can take a look at the Google cache. There's a comment on a more recent pull request which indicates the issue was fixed after Rails 3.0.7 was released. Looks like it will be in 3.1 but I couldn't verify as some of the gems I'm using are not yet compatible.

The current work-around is to use find_by_sql.