This project is archived and is in readonly mode.

#6374 ✓invalid
James

Limit is ignored when joins is specified

Reported by James | February 4th, 2011 @ 04:45 PM

Limit is being ignored in the following case. When calling to_sql on the ARel object, it returns the correct SQL. Then, calling that query with find_by_sql returns the correct count.

ruby-1.9.2-p136 :005 > Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").count
=> 229

ruby-1.9.2-p136 :006 > Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").to_sql
=> "SELECT animals.* FROM animals INNER JOIN lots ON lots.id = animals.lot_id WHERE animals.location_id = 20 AND lots.species = 'Horse' LIMIT 1"

ruby-1.9.2-p136 :007 > Animal.find_by_sql("SELECT animals.* FROM animals INNER JOIN lots ON lots.id = animals.lot_id WHERE animals.location_id = 20 AND lots.species = 'Horse' LIMIT 1").count
=> 1

Comments and changes to this ticket

  • James

    James February 4th, 2011 @ 05:22 PM

    Turns out it has nothing to do with the joins.

    Animal.limit(1).count
    => 1297

    Is that expected behavior?

    Found this by trying to do (essentially) Animal.limit(1).update_all(...) -- not surprisingly based on the above, it's updating every record.

  • Jan Xie

    Jan Xie February 5th, 2011 @ 01:04 PM

    Model.limit(1).count generate correct sql (select count(*) from model limit 1), it's mysql's behavior:

    mysql> select count(*) from knowledges limit 1;
    +----------+
    | count(*) |
    +----------+
    |      842 |
    +----------+
    1 row in set (0.00 sec)
    
  • James

    James February 5th, 2011 @ 03:55 PM

    Yeah -- that makes perfect sense -- forgot that count and size actually modify the query and return a single row.

    So what about my specific case with update_all? It builds the update_all query including the "wheres" and discards the limit:
    Animal.limit(1).joins(:lot).where("animals.location_id" => 20).where("lots.species" => "Horse").update_all(:location_id => 20)

    AREL (2.7ms) UPDATE animals INNER JOIN lots ON lots.id = animals.lot_id SET location_id = 20 WHERE animals.location_id = 20 AND lots.species = 'Horse'

  • Aaron Patterson

    Aaron Patterson February 25th, 2011 @ 08:02 PM

    • State changed from “new” to “invalid”
    • Importance changed from “” to “Low”

    James,

    order must also be supplied if you want the limit to work on updates.

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="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Tags

Pages