This project is archived and is in readonly mode.

#2006 ✓resolved
Thibaud Guillaume-Gentil

with_scope & :having option bug with 2.3 RC1

Reported by Thibaud Guillaume-Gentil | February 18th, 2009 @ 09:59 AM

I have a bug when using find :having option (and :group option) with with_scope. with_scope doesn't support :having yet or it's another problem?

@@@ Ruby with_scope :find => { :group => '...', :having => '...', .... } do find(:all) end



NameError (undefined local variable or method scoped_having' for #<Class:0x689e634>): activerecord (2.3.0) lib/active_record/base.rb:1963:inmethod_missing_without_paginate' activerecord (2.3.0) lib/active_record/base.rb:1759:in add_group!' activerecord (2.3.0) lib/active_record/base.rb:1695:inconstruct_finder_sql' activerecord (2.3.0) lib/active_record/base.rb:1554:in find_every' activerecord (2.3.0) lib/active_record/base.rb:615:infind' ... activerecord (2.3.0) lib/active_record/base.rb:2141:in with_scope'


Comments and changes to this ticket

  • Thibaud Guillaume-Gentil

    Thibaud Guillaume-Gentil February 18th, 2009 @ 10:01 AM

    oops

    
    with_scope :find => { :group => '...', :having => '...', .... } do
      find(:all)
    end
    
  • Thibaud Guillaume-Gentil

    Thibaud Guillaume-Gentil February 25th, 2009 @ 03:52 PM

    nobody use :having & with_scope together?

  • Blane Dabney

    Blane Dabney February 25th, 2009 @ 06:40 PM

    I am, and have also noticed this bug. It's a bug with variable assignment within a conditional modifier and trying to use that variable in the statement.

    I've manually fixed this in mine by changing line 1759 in lib/active_record/base.rb from:

    sql << " HAVING #{scoped_having}" if (scoped_having = scope[:having])

    to:

    if(scoped_having = scope[:having]) sql << " HAVING #{scoped_having}" end

    I'm not sure what the purpose of doing those assignments is (scoped_group, scoped_having). If they're unnecessary, that line could simply be"

    sql << " HAVING #{scope[:having]}" if scope[:having]

  • Thibaud Guillaume-Gentil

    Thibaud Guillaume-Gentil February 26th, 2009 @ 07:25 AM

    scoped_having seems to be needed, your first proposition fix the bug for me, thanks Blane!

    here the diff:

  • DHH

    DHH February 27th, 2009 @ 01:48 PM

    • Milestone cleared.
  • Zach Holman

    Zach Holman February 27th, 2009 @ 07:42 PM

    Ran into the same issue; patch works for me (though might be worthwhile to add a quick test case for this).

  • CancelProfileIsBroken

    CancelProfileIsBroken February 28th, 2009 @ 03:10 PM

    -1 on this fix without some more work. Patch works on MySQL and on SQLite 3 but fails on PostgreSQL.

    Here's the patch with some tests attached, run the AR Postgres tests to see them fail. There's some deeper underlying problem here, in addition to the obvious one that this patch fixes.

    This should not be applied until someone fixes the PostgreSQL case.

  • Pratik

    Pratik February 28th, 2009 @ 03:49 PM

    • Assigned user set to “Pratik”
  • CancelProfileIsBroken

    CancelProfileIsBroken February 28th, 2009 @ 05:03 PM

    OK, we can't fix the world on one patch, my bad.

    Here's a more targeted test for just this patch. Passes under MySQL, PostgreSQL, SQLite3.

    Will open a new ticket for the simple group by case that's still failing under PG.

  • Repository

    Repository February 28th, 2009 @ 05:43 PM

    • State changed from “new” to “resolved”

    (from [c653f700d3afb1208b15fb7fec6250bf3a3f0321]) Fix that scoped find with :group and :having [#2006 state:resolved]

    Signed-off-by: Pratik Naik pratiknaik@gmail.com http://github.com/rails/rails/co...

  • Thibaud Guillaume-Gentil
  • Matthias Hennemeyer

    Matthias Hennemeyer March 1st, 2009 @ 01:04 PM

    This doesn't work with Ruby1.9.1: You will get: aggregate functions are not allowed in the GROUP BY clause (SQLite3::SQLException)

    I don't know if this is related to sqlite3, sqlite3-ruby gem or ruby and i don't even know if it is a bug or a feature or whatever, but I wanted to mention it.

    I have added a script that you can eval with ruby1.8 => works ruby1.9 => fails

    Matthias

  • Bjørn Arild Mæland

    Bjørn Arild Mæland March 30th, 2009 @ 11:55 AM

    Actually this fails for SQLite3 on my Ruby 1.8.7 system.

    sleep:~/src/rails/activerecord $ ruby -v ruby 1.8.7 (2008-08-11 patchlevel 72) [x86_64-linux] sleep:~/src/rails/activerecord $ sqlite3 --version 3.6.11

    Test output:

    1) Error: test_scoped_find_with_group_and_having(BasicsTest): ActiveRecord::StatementInvalid: SQLite3::SQLException: aggregate functions are not allowed in the GROUP BY clause: SELECT SUM(salary) as salary FROM "developers" GROUP BY salary HAVING SUM(salary) > 10000

  • CancelProfileIsBroken

    CancelProfileIsBroken March 30th, 2009 @ 01:29 PM

    • State changed from “resolved” to “open”

    Reopening for investigation. Version issues, perhaps?

    Working for me on:

    OS X, Ruby 1.8.6, sqlite3 3.6.6.2, sqlite3-ruby 1.2.4

    Ubuntu, ruby 1.8.6, sqlite3 3.4.2, sqlite3-ruby 1.2.4

    Ubuntu, ruby 1.9.1, sqlite3 3.4.2, sqlite3-ruby 1.2.4

  • Dmitry Ratnikov

    Dmitry Ratnikov March 30th, 2009 @ 11:43 PM

    I'd like to confirm Chrononaut's error.

    Configuration:

    OS: ubuntu 8.10 ruby 1.8.6 (2008-08-11 patchlevel 287) [i686-linux] sqlite3 --version => 3.5.9 gem list | grep sqlite3 => sqlite3-ruby (1.2.4)

    I attached the full error.

  • Hector E. Gomez Morales

    Hector E. Gomez Morales April 10th, 2009 @ 08:48 PM

    I think the problem is in SQLite3.

    If we look to the query it has some ambiguity because it is aliasing SUM(salary) to salary but the table has a salary column too. In MySQL it even outputs this message:

    1052 Column 'salary' in group statement is ambiguous

    Output doing the query from the sqlite console:

    
    sqlite> SELECT SUM(salary) AS salary FROM developers GROUP BY salary HAVING SUM(salary) > 10000;
    SQL error: aggregate functions are not allowed in the GROUP BY clause
    

    Output doing the query but qualifying the field in the GROUP BY clause:

    
    sqlite> SELECT SUM(salary) AS salary FROM developers GROUP BY developers.salary HAVING SUM(salary) > 10000;
    80000
    800000
    150000
    

    I think there was a change from 3.4.2 (used in ci.rubyonrails.org) to 3.6.12 (my version) where the default qualification now locks to SUM(salary) when before it locked to developers.salary

  • Pratik

    Pratik April 21st, 2009 @ 01:35 PM

    • State changed from “open” to “resolved”

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>

Attachments

Referenced by

Pages