Unexpected PG::UndefinedColumn error on GROUP BY

Hi, when I call

User.select("coalesce(email, '00000@example.com') as user_email").group(:user_email)

It raises an PG::UndefinedColumn error

But if I do the same in plain SQL

SELECT coalesce(email, '00000@example.com) AS user_email GROUP BY user_email

It doesn’t Is it a correct behavior that I’ve got the error?

On edge Rails it seems to work for me. (v7.1)

Try using .group(1) like this:

User.select("COALESCE(email, '00000@example.com') AS user_email")

The ‘1’ means to group by the first column in the select list.

Incidentally if possible it’s best to group on a column instead of an expression, so you would likely want to consider doing .group(:email).

Also, try adding .to_sql to the end of your original AR chain, and see if Arel is actually arriving at the same exact query as your hand-written one. (To get rid of any spurious backslashing that might make that less of a copy-paste able query, prepend it with a puts.)

# in a rails console
puts User.select("coalesce(email, '00000@example.com') as user_email").group(:user_email).to_sql

Then try running that exact query in your database tool of choice.


pry(main)> puts User.select("coalesce(email, '00000@example.com') as user_email").group(:user_email).to_sql
SELECT coalesce(email, '00000@example.com') as user_email FROM "users" GROUP BY "user_email"

Nothing especial there, it produces a valid SQL

Try using .group(1)

Yeah, it works!

But I still don’t understand, is it an issue or not. For example, ordering works fine for me

User.select("coalesce(email, '00000@example.com') as user_email").order(:user_email)

Sorry, I didn’t notice that it works on Edge. I’ve tried it with v6