This project is archived and is in readonly mode.
arel replacing first three objects of array for zeros in association queries
Reported by Josh Delsman | September 29th, 2010 @ 06:48 PM
While upgrading one of my apps, I ran into some trouble with something which was recently fixed in the rails 3-0-stable branch. Upgrading, however, required using arel 2.0.0+. Once I upgraded to arel 2.0.0, I ran into all kinds of bugs. Here is one which I can't seem to figure out.
irb(main):053:0> Order.joins(:client => :country).where(:status => "completed").where(:completed_at => Time.now.beginning_of_day..Time.now.end_of_day).where(:clients => { :countries => { :iso => ["US"] }})
SQL (1.4ms) SHOW TABLES
Order Load (4.4ms) SELECT `orders`.* FROM `orders` INNER JOIN `clients` ON `clients`.`id` = `orders`.`client_id` INNER JOIN `countries` ON `countries`.`id` = `clients`.`country_id` WHERE (`orders`.`status` = 'completed') AND (`orders`.`completed_at` BETWEEN '2010-09-28 23:00:00' AND '2010-09-29 22:59:59') AND (`countries`.`iso` IN (0))
When running this query, it replaces anywhere from one to three objects with zeros, then starts with the rest of the array. Here is one with four:
irb(main):054:0> Order.joins(:client => :country).where(:status => "completed").where(:completed_at => Time.now.beginning_of_day..Time.now.end_of_day).where(:clients => { :countries => { :iso => ["US", "CA", "MX", "JP"] }})
SQL (1.9ms) SHOW TABLES
Order Load (5.7ms) SELECT `orders`.* FROM `orders` INNER JOIN `clients` ON `clients`.`id` = `orders`.`client_id` INNER JOIN `countries` ON `countries`.`id` = `clients`.`country_id` WHERE (`orders`.`status` = 'completed') AND (`orders`.`completed_at` BETWEEN '2010-09-28 23:00:00' AND '2010-09-29 22:59:59') AND (`countries`.`iso` IN (0, 0, 0, 'JP'))
I have narrowed it down to only associations, because calling
Country.where(:iso => "US")
or
Country.where(:iso => ["US"])
will work:
irb(main):055:0> Country.where(:iso => "US")
Country Load (1.7ms) SELECT `countries`.* FROM `countries` WHERE (`countries`.`iso` = 'US')
=> [#<Country id: 226, iso: "US", name: "United States", iso3: "USA", numcode: 840>]
irb(main):056:0> Country.where(:iso => ["US"])
Country Load (2.0ms) SELECT `countries`.* FROM `countries` WHERE (`countries`.`iso` IN ('US'))
=> [#<Country id: 226, iso: "US", name: "United States", iso3: "USA", numcode: 840>]
Any ideas?
Comments and changes to this ticket
-
Josh Delsman September 29th, 2010 @ 07:07 PM
Not sure if this helps to narrow it down, but one work around is to do the following:
Order.joins(:client => :country) .where(:status => "completed") .where(:completed_at => Time.now.beginning_of_day..Time.now.end_of_day) .where(["`countries`.iso IN (?)", ["US", "CA", "MX"])
This comes back with the correct, desired results, and uses the proper query structure.
-
David Trasbo September 29th, 2010 @ 07:50 PM
- Importance changed from to Low
So you're saying you're running your app using the
3-0-stable
branch? -
Josh Delsman September 29th, 2010 @ 07:55 PM
Yes, with arel 2.0.0 on master branch:
gem "rails", :git => "git://github.com/rails/rails.git", :branch => "3-0-stable" gem "arel", :git => "git://github.com/rails/arel" GIT remote: git://github.com/rails/arel revision: d68165501fc80bbce34c43eaaf48381bcca27c15 specs: arel (2.0.0.dev.20100924164835) GIT remote: git://github.com/rails/rails.git revision: 1a2b28c9d9deb7958124917195529f1b96b6bd82 branch: 3-0-stable
-
David Trasbo September 29th, 2010 @ 07:59 PM
- Assigned user set to Aaron Patterson
Assigning this to Aaron as he's working on the Arel stuff at the moment.
-
Aaron Patterson September 29th, 2010 @ 10:19 PM
- State changed from new to resolved
Thanks for reporting this. I've fixed it in ARel here:
http://github.com/rails/arel/commit/7a625bf74e4e197746b857ae23b1a0f...
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>