Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

arel replacing first three objects of array for zeros in association queries

#5732

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?

Reported by Josh Delsman · September 29th, 2010 @ 06:48 PM

State: resolved
Milestone: none
Assigned to: Aaron Patterson Aaron Patterson
Importance: Low

Activity

  1. Josh Delsman
    Josh Delsman

    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.

    September 29th, 2010 @ 07:07 PM

  2. David Trasbo
    David Trasbo
    • Importance changed from to Low

    So you're saying you're running your app using the 3-0-stable branch?

    September 29th, 2010 @ 07:50 PM

  3. Josh Delsman
    Josh Delsman

    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
    

    September 29th, 2010 @ 07:55 PM

  4. David Trasbo
    David Trasbo
    • Assigned user set to Aaron Patterson

    Assigning this to Aaron as he's working on the Arel stuff at the moment.

    September 29th, 2010 @ 07:59 PM

  5. Aaron Patterson
    Aaron Patterson
    • State changed from new to resolved

    Thanks for reporting this. I've fixed it in ARel here:

    http://github.com/rails/arel/commit/7a625bf74e4e197746b857ae23b1a0f...

    September 29th, 2010 @ 10:19 PM