Imported from Lighthouse. Original ticket at: http://rails.lighthouseapp.com/projects/8994/tickets/6426
Created by Igor Alexandrov - 2011-02-13 16:16:59 UTC
Hello. This ticked is enhanced version of this one https://rails.lighthouseapp.com/projects/8994/tickets/1207-postgresqladapter-postgres-error-while-finding-when-using-a-function-with-two-parameters-in-order-clause
The problem is that PostgreSQL requires the ORDER BY columns in the select list for distinct queries, and also requires that the ORDER BY include the distinct column. All works pretty well if you just have plain parameters in ORDER BY clause. For example, something like
ORDER BY "pcs".min_cost DESC, "pcs".title
will be parsed well and added to DISTINCT PART as
SELECT DISTINCT ..., "pcs".min_cost AS alias_0, "pcs".title AS alias_1
But if you will use something complex in ORDER BY string – all will be bad! The problem is that now distinct() method of PostgreSQL adapter simple splits ORDER BY string by ','.
ORDER BY COALESCE("pcs".min_cost, "pcs".max_cost) DESC
will be parsed, splitted and transformed into incorrect DISTINCT clause:
SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost AS alias_0, pcs.max_cost) AS alias_1 FROM "pcs"
This query won't be executed because of syntax errors. I created patch that allows to use Array as :order param in find() method.
:order => ["COALESCE(pcs.min_cost, pcs.max_cost) DESC"]
will be translated into
SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost, pcs.max_cost) AS alias_0
:order => ["COALESCE(pcs.min_cost, pcs.max_cost) DESC", "pcs.title"]
SELECT DISTINCT ON ("pcs".id) "pcs".id, COALESCE(pcs.min_cost, pcs.max_cost) AS alias_0, pcs.title AS alias_1
All works good, and Array syntax is easy to understand. All test run well. This affects 2.3 and 3.0 branches.
Thanks. Hope it will be useful.
Imported from Lighthouse.
Comment by Igor Alexandrov - 2011-02-21 21:18:21 UTC
Guys, this is really annoying bug. Please review the patch.
Attachments saved to Gist: http://gist.github.com/971779