#6058 update_all ignores conditions, when :orders and :limit options are supplied - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#6058 open

update_all ignores conditions, when :orders and :limit options are supplied

Reported by Valentine Bichkovsky | November 24th, 2010 @ 06:03 PM

Rails version: 3.0.2 and 3.0.3 (no such bug in Rails 3.0.1)
Ruby 1.8.7
Adapter: postgres

In code:

Reservation.update_all("status = 'ready'", 
  ["date = ? and crossing_point_id = ? and position > ?", 
   Time.zone.today, crossing_point.id, last_crossed],
  :order => :position, :limit => params[:count])

In log file:

AREL (15.0ms) UPDATE "reservations" SET status = 'ready' WHERE "reservations"."id" IN (SELECT "reservations"."id" FROM "reservations" ORDER BY position LIMIT 4)

After removing :order and :limit options

In code:

Reservation.update_all("status = 'ready'", 
  ["date = ? and crossing_point_id = ? and position > ?", 
   Time.zone.today, crossing_point.id, last_crossed])

In log file:

AREL (29.7ms)  UPDATE "reservations" SET status = 'ready' WHERE (date = '2010-11-24' and crossing_point_id = 2 and position > 182)

Comments and changes to this ticket

Neeraj Singh November 24th, 2010 @ 06:56 PM
- State changed from “new” to “open”
- Assigned user set to “Neeraj Singh”
- Importance changed from “” to “Low”
looking into it.
Neeraj Singh November 24th, 2010 @ 07:24 PM
Attached is a failing test.

Relation is being built right. Look like issue is with Arel. Studying the code. Patch might take a while :-)
- 6058-failing-test.diff 1.1 KB
rails February 25th, 2011 @ 12:00 AM
- Tag changed from “3.0.2, arel, update_all” to “302, arel, update_all”
This issue has been automatically marked as stale because it has not been commented on for at least three months.

The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.

Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.
rails February 25th, 2011 @ 12:00 AM
- State changed from “open” to “stale”
You flagged this item as spam.
Howard Yeh March 3rd, 2011 @ 10:26 AM
- State changed from “stale” to “open”
[state:open]

this problem persists for 3.0.5.

Ruby 1.9.2
Adapter: postgres
You flagged this item as spam.
Hugo Peixoto March 5th, 2011 @ 11:12 PM
Looks like the bug is in ARel indeed. When a subquery is generated, all other WHERE clauses are dropped.

I attached a patch that fixes this bug and adds a test case.
- propagate_where_on_updatediff.txt 1.7 KB
You flagged this item as spam.
Hugo Peixoto March 6th, 2011 @ 10:53 AM
The previous patch kept the where conditions on the UPDATE statement. That is not the correct behavior, as the order/limit clauses wouldn't be applied with the WHERE clauses in mind.

This one passes the WHERE clauses to the generated subquery.
- 6058-propagate_where_on_update.diff 1.8 KB
Santiago Pastorino March 10th, 2011 @ 09:52 PM
- Assigned user changed from “Neeraj Singh” to “Aaron Patterson”
Aaron Patterson March 21st, 2011 @ 09:56 PM
@Hugo I've applied this patch to ARel master which is the unreleased 2.1.0 version.

Would you mind backporting this to 2.0.x?
You flagged this item as spam.
Hugo Peixoto March 24th, 2011 @ 06:41 AM
2.0.x backported patch attached.
- 6058-propagate_where_on_update_.diff 1.8 KB