SELECT DISTINCT ON expressions must match initial ORDER BY expressions

[gjaldon]

I encounter the error SELECT DISTINCT ON expressions must match initial ORDER BY expressions
for a query that looks like this:

from(f in query,
  distinct: fragment("?[?]", f.path, ^level),
  order_by: [asc: fragment("?[?]", f.path, ^level), desc: f.id])

Which outputs the following query in Postgres:

SELECT DISTINCT ON (f0."path"[$1]) f0."id", f0."path", f0."salt", f0."hash", f0."size", f0."mtime", f0."atime", f0."ctime", f0."content", f0."removed", f0."created_at", f0."updated_at", f0."user_id" FROM "files" AS f0 ORDER BY f0."path"[$2], f0."id" DESC [2, 2]

It works when I change the query to:

from(f in query,
  distinct: fragment("?[?]", f.path, ^level),
  order_by: [asc: fragment("?[$1]", f.path), desc: f.id])

I just hardcode $1 to the above query so the output query in Postgres is:

SELECT DISTINCT ON (f0."path"[$1]) f0."id", f0."path", f0."salt", f0."hash", f0."size", f0."mtime", f0."atime", f0."ctime", f0."content", f0."removed", f0."created_at", f0."updated_at", f0."user_id" FROM "files" AS f0 ORDER BY f0."path"[$1], f0."id" DESC

[josevalim]

@ericmj what if we automatically set the order_by when we set the distinct? Or we introduce something like distinct_order_by? It seems silly to force the user to repeat the expressions?

[ericmj]

@josevalim Yes, but it also seems like we should try to converge when the same expression is interpolated twice to a single query parameter.

[josevalim]

@ericmj how should we do it though? check for equality in the params as we merge them? Unfortunately it doesn't work for database like mysql. should it be a postgres connection optimization?

[ericmj]

I didn't consider mysql so lets table that. It's useful for optimization but not for anything else.

[josevalim]

@ericmj should we then change the postgres adapter to automatically add what is in distinct to order_by?

[ericmj]

I'm not sure, maybe it should be explicit? Should we do it by default even though Postgres doesn't?

[josevalim]

I am not sure. We will definitely have issues like above AND there is no work around. Hardcoding to $1 is brittle. :(

[josevalim]

I think we should give it a go at automatically copying. Let's just make it explicit in the docs. So this is now ready for tackling. /cc @briksoftware @whatyouhide

[gjaldon]

I'd like to take this one on. Expect a PR in the next 24 hours. :)