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

Tested on 2-3-stable:

def test_eager_association_loading_with_belongs_to_and_limit_non_selected_order_by
  comments = Comment.find(:all, :include => :post, :limit => 5, :order => 'comments.post_id')
  puts comments.inspect
  assert_equal 5, comments.length
  assert_equal [1,2,3,5,6], comments.collect { |c| c.id }
end

got:

• SELECT * FROM "comments" ORDER BY comments.post_id LIMIT 5;
• SELECT * FROM "posts" WHERE ("posts"."id" IN (1,2,4));

Verified

I was able to test this using following scenario:

  class A < ActiveRecord::Base
    has_many :bs, :select => 'id, position, a_id'
  end

  class B < ActiveRecord::Base
    belongs_to :a
  end

Data in Table 'as':
+----+------+ | id | name | +----+------+ | 1 | A1 | | 2 | A2 | | 3 | A3 | +----+------+

Data in Table 'bs':
+----+------+----------+ | id | a_id | position | +----+------+----------+ | 1 | 1 | 1 | | 2 | 1 | 4 | | 3 | 2 | 6 | | 4 | 2 | 2 | | 5 | 2 | 5 | | 6 | 3 | 3 | +----+------+----------+

Finding without :limit option yields:

  >> A.find :all, :include => :bs, :order => 'bs.position desc'
  => [#<A id: 2 ... >, #<A id: 1 ... >, #<A id: 3 ... >]

(looks correct)

However, finding with :limit gives:

  >> A.find :all, :include => :bs, :order => 'bs.position desc', :limit => 2
  => [#<A id: 2 ... >, #<A id: 3 ... >]

Instead, it should give

  [#<A id: 2 ... >, #<A id: 1 ... >]

Sorry for the formatting mess, the tables were supposed to be:

Data in Table 'as':

+----+------+
| id | name |
+----+------+
|  1 | A1   | 
|  2 | A2   | 
|  3 | A3   | 
+----+------+

Data in Table 'bs':
+----+------+----------+
| id | a_id | position |
+----+------+----------+
|  1 |    1 |        1 | 
|  2 |    1 |        4 | 
|  3 |    2 |        6 | 
|  4 |    2 |        2 | 
|  5 |    2 |        5 | 
|  6 |    3 |        3 | 
+----+------+----------+

Ok, I had that backwards. I've attached a corrected patch that fails on Mysql & Sqlite3. It passes on PostgresSQL.

I've attached railsbob's example as a test

+1 verified.

The patches above apply & fail on latest master.

Verified, +1

The preivious patch does not apply anymore. The attached one applies cleanly on master and demonstrates the bug

-1

I don't believe that :limit or :select are really the problem here.

Unlike Postgres, MySQL orders null values first by default (ASC). So, in the Post has_many Comments example:

Post.find(:all, :include => :comments, :order => 'comments.post_id asc')

When limiting the Posts returned in the find() above to n, you get the first n records (as expected), nulls first in MySQL.

I've attached a patch to help illustrate.

Yes. I'd say close it Rizwan.

Looks applied already.