Rails Ruby on Rails

Comments and changes to this ticket

• You flagged this item as spam.
  

  ReggieB February 14th, 2011 @ 09:11 AM

  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

• 

  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. :)

• You flagged this item as spam.
  

  ReggieB February 15th, 2011 @ 11:26 AM

  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
  

• You flagged this item as spam.
  

  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

  • limit_join_bug.diff 1 KB