This project is archived and is in readonly mode.

#2460 ✓stale
Cássio Marques

Calling empty? on a named_scope with :select => "distinct table_name.*" throws error

Reported by Cássio Marques | April 8th, 2009 @ 09:01 PM | in 3.x

I have the following situation => where I use :select => "distinct some_table.*"

When I call the named scope it runs ok, but if I use something like MyModel.the_named_scope.empty? it executes a "select count(distinct some_table.*)..." query and I get an error from the database saying it cannot find an equality operator for the 'some_table' type.

Comments and changes to this ticket

  • Micah Winkelspecht

    Micah Winkelspecht April 21st, 2009 @ 10:57 PM

    This also seems to happen when using MyModel.the_named_scope.count.

  • Chris Nelson

    Chris Nelson May 20th, 2009 @ 07:52 PM

    Seems like the best way to fix this issue would be to allow a :distinct options for find. Right now this appears to only be a valid option on count.

  • Emili Parreño

    Emili Parreño May 21st, 2009 @ 09:18 AM

    @micah if you use MyModel.the_named_scope, the result is an Array, you have to use length method or size method MyModel.the_named_scope.length

  • Dominique Brezinski

    Dominique Brezinski June 19th, 2009 @ 06:25 PM

    Are you using the DISTINCT with select because you are also using :joins and it returns dup records?

    If so, use :include instead for the associations you are doing the :joins on and add :group with either id or an attribute on the model you want to distinguish the result set. :include does a LEFT OUTER JOIN on the association and the calculation methods do a DISTINCT in that case. count, length and size will all return the same result and what you expect. Here is an example where you want to do :include instead of :joins:

    class Group < ActiveRecord::Base
      has_many :group_invites
      named_scope :invited_to,
                  lambda {|person| { :include => :group_invites,
                                     :conditions => ["group_invites.person_id = ?",],
                                     :group => "" } }

    If you use :joins and :group, the returned association set will be what you expect (no dup objects), but calling size, length and count will all return a possibly larger number of rows that SELECT returned but include duplicates.

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Andrew C. Greenberg

    Andrew C. Greenberg May 16th, 2010 @ 05:20 AM

    This is something of a general problem for any aggregation operator on a relation including a hand-coded projection (select). #empty? appears to effectively operate as "" (or is it on a relation, which in turn sets up a query that REPLACES the custom projection with "COUNT(*)." This in turn will throw an error if the custom select contained matter necessary to make the rest of the query valid SQL.

    I recently chased a bug with a query that originally had something like (giving the sql to show how the difficulty occurs).

    SELECT primaries.*, AS most_recent_date
    FROM primaries JOIN associateds LEFT JOIN ...complex join goes here ...
    WHERE most_recent_date < '2001-1-1'

    This was accomplished with something like:

    primary_with_most_recent_date_before_2001 = Primary.joins(custom_string).select(custom_string)

    which worked fine for all purposes, until the code


    blew up with an invalid SQL error.

    This happens because the expression becomes invalid when the SELECT phrase was replaced with "COUNT(*)," as the definition of 'most_recent_date' is lost. The query works just fine without using .count or .empty?, but fails when rails tries to convert the relation sql generated.

    Of course, for now you can simply replace the complex




    and it will work fine, however inefficiently. Perhaps it would be better to translate relations having custom language with "COUNT(*) FROM" a subselection to assure valid SQL and hope the optimizer can sort the wheat from the chaff?

  • gamov

    gamov July 27th, 2010 @ 11:33 AM

    • Importance changed from “” to “”

    Emili, weirdly enough with a scope like this:

      named_scope :distinct_per_location, lambda {{
              :select => "distinct item_variant_id, location_id"

    StockItem.distinct_per_location.size returns 5 (wrong, all records, using select count(*) instead of my select)
    StockItem.distinct_per_location.length returns 4 (correct)

    I guess Rails 2.3.8 tries to optimize the .size method but in a buggy way...

    Edited by Rohit Arondekar for formating.

  • Santiago Pastorino

    Santiago Pastorino February 9th, 2011 @ 12:31 AM

    • State changed from “new” to “open”

    This issue has been automatically marked as stale because it has not been commented on for at least three months.

    The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

    Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.

  • Santiago Pastorino

    Santiago Pastorino February 9th, 2011 @ 12:31 AM

    • State changed from “open” to “stale”
  • bingbing

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=""></a>