Postgresql vs mysql request with Rails 3

[Michel Pigassou]

Hello.

I've encountered a common problem that appears when switching from MySQL to PostgreSQL, related to the following error:

"column X must appear in the GROUP BY clause or be used in an aggregate function"

I've found some posts that explain why it does happen and how to correct it.

But I wonder why Rails does not handle it through its DB adapters. I'm using query methods like #group, #join etc.

Isn't it the reason why we use ORM?

Or maybe I'm doing something wrong?

Thanks.

[Chirag Singhal]

Can you post your code/query here?

[Michel Pigassou]

Entry.joins(:program => :user).group('programs.user_id') -> works in MySQL, not Postgresql

Entry.joins(:program => :user).select('distinct on (programs.user_id) entries.*') -> works in pg, not mysql

and an entry belongs to a program.

I'd like all entries with only one occurrence by user (program_id could also do the trick I guess, but that is not the point).

[Chirag Singhal]

Did some search on it and it's a revelation for me too.

Here's what I understand based on all the articles I've read:

It's not the problem with DB adapters, it's because of the difference in how MySQL implements "group by" clause

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Strictly speaking, both the adapters are implementing what's right as per each database.

And since MySQL chose to implement "group by" clause differently and most rails devs have been using mysql as default database, we are used to use "group by" the mysql way.

[elitwin]

On Jun 23, 8:39 am, Chirag Singhal <chirag.sing...@gmail.com> wrote:
> Strictly speaking, both the adapters are implementing what's right as per
> each database.
> And since MySQL chose to implement "group by" clause differently and most
> rails devs have been using mysql as default database, we are used to use
> "group by" the mysql way.

Actually, MySQL defaults to allowing you to do non standard group by
operations.
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_only_full_group_by

As the link points out, the following query is allowed when the
ONLY_FULL_GROUP_BY option is not enabled (which I think is the
default):
SELECT name, address, MAX(age) FROM t GROUP BY name;

I've always thought this was a really bad default for MySQL, and
PostgreSQL is rightfully complaining that the query should be:
SELECT name, address, MAX(age) FROM t GROUP BY name, address;

- Eric