with_scope & :having option bug with 2.3 RC1

oops

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

nobody use :having & with_scope together?

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]

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

here the diff:

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

-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.

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.

Great! Thank you!

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

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

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.

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