This project is archived and is in readonly mode.

#5732 ✓resolved
Josh Delsman

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

    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

    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

    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

    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

    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>

Pages