This project is archived and is in readonly mode.

#2493 ✓resolved
Richard Head

Eager Loading With :limit Returns Wrong Result Set (MySQL)

Reported by Richard Head | April 14th, 2009 @ 09:39 PM | in 2.3.6

The models:

class Package < ActiveRecord::Base
 has_many :deliveries,
          :select=>'id, user, delivered, package_id'

class Delivery < ActiveRecord::Base
  belongs_to :package

The find:

 Package.find :all, :include=>:deliveries, :order=>'deliveries.delivered desc', :limit=>10

The 1st query:

SELECT DISTINCT `packages`.id FROM `packages` LEFT OUTER JOIN `deliveries` ON deliveries.package_id = ORDER BY deliveries.delivered desc LIMIT 10

Because DISTINCT is being used with a non-selected order by column, records aren't returned in the desired order, so the 2nd AR query:

select deliveries.* where deliveries.package_id in ( ... )

returns the wrong rows. Wrong meaning X.delivered > Y.delivered yet X is not in the result set.

MySQL doesn't impose the "order by columns are required in select if DISTINCT is used". I wonder how this query would be generated on say MSSQL.

Comments and changes to this ticket

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>