This project is archived and is in readonly mode.

#6450 ✓resolved
Szymon Nowak

Arel generates invalid SQL when using DISTINCT ON in PostgreSQL

Reported by Szymon Nowak | February 18th, 2011 @ 03:52 PM | in 3.x

For the following query

Country.select("DISTINCT ON(countries.id) countries.*").joins(:cities).order("countries.name ASC")

Arel generates the following SQL (in Visitors::PostgreSQL#visit_Arel_Nodes_SelectStatement):

SELECT * FROM (SELECT DISTINCT ON(countries.id) countries.* FROM "countries" INNER JOIN "cities" ON "cities"."country_id" = "countries"."id") AS id_list ORDER BY id_list.alias_0

However, it causes some problems:

ActiveRecord::StatementInvalid: PGError: ERROR:  column id_list.alias_0 does not exist
LINE 1: ...untry_id" = "countries"."id") AS id_list ORDER BY id_list.al...
                                                             ^
: SELECT * FROM (SELECT DISTINCT ON(countries.id) countries.* FROM "countries" INNER JOIN "cities" ON "cities"."country_id" = "countries"."id") AS id_list ORDER BY id_list.alias_0

I'm no SQL expert, but I'm not sure why Arel generates subquery if DISTINCT ON is present if the following SQL works just fine:

SELECT DISTINCT ON(countries.id) countries.* FROM countries INNER JOIN cities ON cities.country_id = countries.id ORDER BY countries.id;

Comments and changes to this ticket

  • 23inhouse

    23inhouse February 19th, 2011 @ 12:52 PM

    Not sure if this is appropriate, and i'm no doctor, but i concur.

    I need Arel to generate this SQL in postgres.

    Discount.select("DISTINCT ON(email) *").order("email, created_at DESC")
    => SELECT DISTINCT ON (email) * FROM discounts ORDER BY email, created_at DESC
    
  • Jon Atack

    Jon Atack February 19th, 2011 @ 09:57 PM

    I ran across the same issue using Postgresql under Rails 3.0.4 and Ruby 1.9.2p0 2 months ago.

    I wanted to do this:

    scope :last_concerts,

    select("DISTINCT (concerts.artist_id)").
    joins(:event).
    where("result <> ? and result <= ?", CONCERT_LOST, CONCERT_WON).
    order(["concerts.artist_id", "events.date DESC"])
    

    And ended up having to do it with find_by_sql:

    def self.last_concert

    find_by_sql("SELECT DISTINCT ON (artist_id) concerts.*
    FROM concerts INNER JOIN events ON events.id = concerts.event_id
    WHERE (result <> #{CONCERT_LOST} and result <= #{CONCERT_WON})
    ORDER BY artist_id, events.date DESC;")
    

    end

  • Jon Atack

    Jon Atack February 19th, 2011 @ 09:58 PM

    I meant to write under Rails 3.0.3 ...

  • Wojciech Wnętrzak

    Wojciech Wnętrzak February 21st, 2011 @ 10:51 AM

    We're getting good results after overwriting arel method: https://github.com/rails/arel/blob/master/lib/arel/visitors/postgre... to return just super in any case - not really sure why this subqueries are needed.
    So after that change it's possibly to do in activerecord:

    select("DISTINCT ON (posts.author_id) posts.*").order("posts.author_id")
    

    which can be chained with other order calls.

  • Szymon Nowak

    Szymon Nowak February 24th, 2011 @ 10:03 AM

    Just small update - it seems that the column in DISTINCT ON must match the first column in ORDER BY clause:

    This works fine:

    SELECT DISTINCT ON(countries.name) countries.* FROM countries ORDER BY countries.name;
    

    This causes error:

    SELECT DISTINCT ON(countries.id) countries.* FROM countries ORDER BY countries.name;
    ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
    LINE 1: SELECT DISTINCT ON(countries.id) countries.* FROM countries ...
    

    According to PostgreSQL docs: The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). So only if it's not the case, a subquery needs to be generated.

  • Aaron Patterson

    Aaron Patterson March 22nd, 2011 @ 11:30 PM

    • Milestone set to 3.x
    • Importance changed from “” to “Low”

    I'd like to remove the automatic subselect generation. It's a relic from ARel 1.0.

    However, removing the subselect generation breaks tests in AR. Specifically, it breaks queries like this: https://github.com/rails/rails/blob/3-0-stable/activerecord/test/ca...

    I think there are two ways to fix this:

    1. If a user doesn't add the correct order by clause, add it automatically here: https://github.com/rails/rails/blob/3-0-stable/activerecord/lib/act...

    2. Don't add anything automatically, let the database blow up, and force the user to specify the column in the order clause.

    Personally I lean towards #2 since there is less magic involved. But either way, I think this should be fixed in Rails 3.1.

    Thoughts?

  • Szymon Nowak

    Szymon Nowak April 11th, 2011 @ 02:10 PM

    I'm for #2 in a short term, because right now it doesn't work at all.

    However, having e.g. the following requirement: "Display the latest post posted by each user and sort the results by created_at", it's really not so easy to do.

    If one does:

    SELECT DISTINCT ON(user_id) * FROM posts ORDER BY user_id, created_at DESC;
    

    it will display the latest post by each user, but the results will be ordered by user_id. So, the results need to be used as a subquery, which will be ordered by created_at DESC once more:

    SELECT * FROM posts WHERE id IN (SELECT DISTINCT ON(user_id) id FROM posts ORDER BY user_id, created_at DESC) ORDER BY created_at DESC
    

    which is probably exactly what ARel tries to do now.

    So in a long term, it would be nice if ARel did all this stuff automatically, but it's probably really complicated to implement it properly, so the most important thing for now is just to make it work at all :)

  • laptopbatteries

    laptopbatteries April 16th, 2011 @ 03:10 AM

    rolex watches are very common in our Audemars Piguet Replicas lifes, there are quite several well known wrist watches brands, the majority Gucci Replicas of them are Swiss bands, Panerai Replicas ,and it is Omega Replicas unlikely, unless the replica watches 's owner is filthy rich and equally careless replica breitling with his, Even the highest quality, Some replica omega are believed to be to acquire luxury wrist that are Replica Concord founded of gold or platinum or other high priced materials. placing on these wrist fake rolex certainly will make us stand out from other Swiss Replica Watch people.Does everyone can afford these genuine, replica tag heuer Taking your or replica watch ? When should expensive replica watch uk , before you take your precious?

  • Repository

    Repository April 22nd, 2011 @ 04:49 AM

    • State changed from “new” to “resolved”

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="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Referenced by

Pages