{"action":"created","issue":{"url":"https://api.github.com/repos/elixir-lang/ecto/issues/495","labels_url":"https://api.github.com/repos/elixir-lang/ecto/issues/495/labels{/name}","comments_url":"https://api.github.com/repos/elixir-lang/ecto/issues/495/comments","events_url":"https://api.github.com/repos/elixir-lang/ecto/issues/495/events","html_url":"https://github.com/elixir-lang/ecto/issues/495","id":59617742,"number":495,"title":"SELECT DISTINCT ON expressions must match initial ORDER BY expressions","user":{"login":"gjaldon","id":1457730,"avatar_url":"https://avatars.githubusercontent.com/u/1457730?v=3","gravatar_id":"","url":"https://api.github.com/users/gjaldon","html_url":"https://github.com/gjaldon","followers_url":"https://api.github.com/users/gjaldon/followers","following_url":"https://api.github.com/users/gjaldon/following{/other_user}","gists_url":"https://api.github.com/users/gjaldon/gists{/gist_id}","starred_url":"https://api.github.com/users/gjaldon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gjaldon/subscriptions","organizations_url":"https://api.github.com/users/gjaldon/orgs","repos_url":"https://api.github.com/users/gjaldon/repos","events_url":"https://api.github.com/users/gjaldon/events{/privacy}","received_events_url":"https://api.github.com/users/gjaldon/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2015-03-03T09:46:53Z","updated_at":"2015-03-04T16:37:49Z","closed_at":null,"body":"I encounter the error `SELECT DISTINCT ON expressions must match initial ORDER BY expressions`\r\nfor a query that looks like this:\r\n```elixir\r\nfrom(f in query,\r\n  distinct: fragment(\"?[?]\", f.path, ^level),\r\n  order_by: [asc: fragment(\"?[?]\", f.path, ^level), desc: f.id])\r\n```\r\n\r\nWhich outputs the following query in Postgres:\r\n```\r\nSELECT 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]\r\n```\r\n\r\nIt works when I change the query to:\r\n```elixir\r\nfrom(f in query,\r\n  distinct: fragment(\"?[?]\", f.path, ^level),\r\n  order_by: [asc: fragment(\"?[$1]\", f.path), desc: f.id])\r\n```\r\n\r\nI just hardcode `$1` to the above query so the output query in Postgres is:\r\n```\r\nSELECT 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\r\n```\r\n"},"comment":{"url":"https://api.github.com/repos/elixir-lang/ecto/issues/comments/77193070","html_url":"https://github.com/elixir-lang/ecto/issues/495#issuecomment-77193070","issue_url":"https://api.github.com/repos/elixir-lang/ecto/issues/495","id":77193070,"user":{"login":"ericmj","id":316890,"avatar_url":"https://avatars.githubusercontent.com/u/316890?v=3","gravatar_id":"","url":"https://api.github.com/users/ericmj","html_url":"https://github.com/ericmj","followers_url":"https://api.github.com/users/ericmj/followers","following_url":"https://api.github.com/users/ericmj/following{/other_user}","gists_url":"https://api.github.com/users/ericmj/gists{/gist_id}","starred_url":"https://api.github.com/users/ericmj/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ericmj/subscriptions","organizations_url":"https://api.github.com/users/ericmj/orgs","repos_url":"https://api.github.com/users/ericmj/repos","events_url":"https://api.github.com/users/ericmj/events{/privacy}","received_events_url":"https://api.github.com/users/ericmj/received_events","type":"User","site_admin":false},"created_at":"2015-03-04T16:37:49Z","updated_at":"2015-03-04T16:37:49Z","body":"I didn't consider mysql so lets table that. It's useful for optimization but not for anything else."}}