This project is archived and is in readonly mode.

#6429 new

limit is ignored with has_and_belongs_to_many and count

Reported by ReggieB | February 14th, 2011 @ 09:04 AM

It's easiest to use an example to explain the problem:

class Articles
  has_and_belongs_to_many categories

class Category
  has_and_belongs_to_many articles

c = Category.first

=> 8   # Correct

=> 8   # Incorrect

c.articles.find(:all, :limit => '3').count
=> 3   # Correct

The problem is that "LIMIT 3" is appended to the end of the SQL statement joining the two tables in the SQL generated by count:

SELECT COUNT(*) AS count_id FROM `articles` INNER JOIN
`articles_categories` ON `articles`.id =
`articles_categories`.article_id WHERE
(`articles_categories`.category_id = 9 ) LIMIT 3

As an example of a possible solution, this returns the correct count:

SELECT count() FROM (
  FROM articles
  INNER JOIN articles_categories ON =
  WHERE (articles_categories.category_id = 9 )
  LIMIT 6, 4) inner_table

My system is Windows 7 with a MySQL 5.1 database

Comments and changes to this ticket

  • ReggieB

    ReggieB February 14th, 2011 @ 09:11 AM

    The last SQL is:

    SELECT count(*) FROM (
      SELECT  *
      FROM `articles`
      INNER JOIN `articles_categories` ON `articles`.id =
      WHERE (`articles_categories`.category_id = 9 )
      LIMIT 6, 4) inner_table

    For some reason the two * in the first two lines aren't appearing above

  • Jeff Kreeftmeijer

    Jeff Kreeftmeijer February 14th, 2011 @ 06:18 PM

    • Tag set to activerecord
    • Importance changed from “” to “Low”

    This doesn't seem like a Rails problem, this is MySQL behavior:

    mysql> select count(*) from categories;
    | count(*) |
    |       13 | 
    1 row in set (0.00 sec)
    mysql> select count(*) from categories limit 3;
    | count(*) |
    |       13 | 
    1 row in set (0.00 sec)

    I don't think Rails should try to "fix" this by doing creepy sub query stuff like this, but I'm wondering what the rest thinks. Maybe we can find a nicer solution?

    Thanks for reporting, Reggie. :)

  • ReggieB

    ReggieB February 15th, 2011 @ 11:26 AM


    The problem does occur within a simpler SQL query. However, it only seems to become an ActiveRecord issue if there is a join involved. For example

    => 10 # Correct

    Article.limit(3).count => 3 # Also correct.

    The SQL generated by this statement is:

    SELECT COUNT(*) AS count_id FROM (SELECT 1 FROM `categories` LIMIT 3) AS subquery

    So Rails is already using the sub-query technique for simpler relationships.

    The LIMIT clause acts to limit the number of lines being output. As a simple count only has one output line, LIMIT appears to have no effect. However, compare the output from these three statements:

      SELECT count(*) FROM categories LIMIT 3 
      SELECT count(*) FROM categories GROUP BY id
      SELECT count(*) FROM categories GROUP BY id LIMIT 3
  • x37v

    x37v February 15th, 2011 @ 11:57 PM

    I've attached a diff which contains a test i added to the active record tests. This diff is from 3-0-stable

    but basically:

    X.join(:y).where(condition).limit(2).count != X.find_by_sql(X.join(:y).where(condition).limit(2).to_sql).count

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>