Ecto's `distinct` adds an order by clause

When using distinct on you may encounter this error:

select distinct on (color) id, color from fruits order by id;
-- ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

This is because distinct on needs to enounter the rows in a specific order so that it can make the determination about which row to take. The expressions must match the initial ORDER BY expressions!

So the above query works when it looks like this:

select distinct on (color) id, color from fruits order by color, id;

OK, now it works.

Ecto's distinct function helps us avoid this common error by prepending an order by clause ahead of the order by clause you add explicitly.

This elixir statement:

Fruit |> distinct([f], f.color) |> order_by([f], f.id) |> Repo.all

Produces this sql (cleaned up for legibility):

select distinct on (color) id, color from fruits order by color, id;

Ecto added color to the order by!

Without any order by at all, distinct does not prepend the order by.

Read the docs!