DISTINCT gets added to generated SQL twice when using #uniq and joining another table #266

Closed
twinge opened this Issue Sep 13, 2012 · 4 comments

4 participants

@twinge

I'm using postgres (haven't tried this in mysql)

@contacts = @contacts.includes(:addresses).where('addresses.city' => params[:city])
@contacts = @contacts.uniq
@contacts = @contacts.page(params[:page])

<%= will_paginate @contacts %>

Generates the following invalid sql:

SELECT  DISTINCT DISTINCT "contacts".id, contacts.name AS alias_0, contacts.name AS alias_1 FROM "contacts" LEFT OUTER JOIN "contact_people" ON "contact_people"."contact_id" = "contacts"."id" LEFT OUTER JOIN "people" ON "people"."id" = "contact_people"."person_id" LEFT OUTER JOIN "taggings" ON "taggings"."taggable_id" = "contacts"."id" AND taggings.context = 'tags' AND "taggings"."taggable_type" = 'Contact' LEFT OUTER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" LEFT OUTER JOIN "addresses" ON "addresses"."addressable_id" = "contacts"."id" AND "addresses"."addressable_type" = 'Contact' WHERE "contacts"."account_list_id" = 471 AND "addresses"."state" IN ('AGS', 'ALB') ORDER BY contacts.name LIMIT 30 OFFSET 0
@schuetzm

This is actually a bug in ActiveRecord (see Empact/rails@2121059).
For now, you can workaround this by placing a file with the following contents in your config/initializers directory:

module ActiveRecord
  module FinderMethods
    def construct_limited_ids_condition(relation)
      orders = relation.order_values.map { |val| val.presence }.compact
      values = @klass.connection.distinct("#{@klass.connection.quote_table_name table_name}.#{primary_key}", orders)

      relation = relation.dup
      relation.uniq_value = false # avoids SELECT DISTINCT DISTINCT

      ids_array = relation.select(values).collect {|row| row[primary_key]}
      ids_array.empty? ? raise(ThrowResult) : table[primary_key].in(ids_array)
    end
  end
end
@pwalker

The link to the bug is no longer working. Any updates? This issue is still affecting me.

@schuetzm

The commit has been retracted by its author in favour of a different solution, which isn't merged yet:
rails/rails#6792

@mislav
Owner

Yup, Active Record bug. Glad to know it's being worked on! Closing this issue

@mislav mislav closed this Jan 13, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment