This project is archived and is in readonly mode.

limit is ignored with has_and_belongs_to_many and count

#6429

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

class Articles
  has_and_belongs_to_many categories
end

class Category
  has_and_belongs_to_many articles
end

c = Category.first

c.articles.count
=> 8   # Correct

c.articles.limit(3).count
=> 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 (
  SELECT  
  FROM articles
  INNER JOIN articles_categories ON articles.id =
articles_categories.article_id
  WHERE (articles_categories.category_id = 9 )
  LIMIT 6, 4) inner_table

My system is Windows 7 with a MySQL 5.1 database

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

Activity

ReggieB

The last SQL is:


SELECT count(*) FROM (
  SELECT  *
  FROM `articles`
  INNER JOIN `articles_categories` ON `articles`.id =
`articles_categories`.article_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

February 14th, 2011 @ 09:11 AM

Jeff Kreeftmeijer
- 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. :)
February 14th, 2011 @ 06:18 PM
ReggieB
Jeff,

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
```
Article.count
=> 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
```
February 15th, 2011 @ 11:26 AM
x37v
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
- limit_join_bug.diff 1.01 KB
February 15th, 2011 @ 11:57 PM