{"action":"created","issue":{"url":"https://api.github.com/repos/slick/slick/issues/1340","repository_url":"https://api.github.com/repos/slick/slick","labels_url":"https://api.github.com/repos/slick/slick/issues/1340/labels{/name}","comments_url":"https://api.github.com/repos/slick/slick/issues/1340/comments","events_url":"https://api.github.com/repos/slick/slick/issues/1340/events","html_url":"https://github.com/slick/slick/issues/1340","id":113156926,"number":1340,"title":"DISTINCT and DISTINCTON generate wrong and complex queries","user":{"login":"nlhkh","id":3459416,"avatar_url":"https://avatars1.githubusercontent.com/u/3459416?v=3","gravatar_id":"","url":"https://api.github.com/users/nlhkh","html_url":"https://github.com/nlhkh","followers_url":"https://api.github.com/users/nlhkh/followers","following_url":"https://api.github.com/users/nlhkh/following{/other_user}","gists_url":"https://api.github.com/users/nlhkh/gists{/gist_id}","starred_url":"https://api.github.com/users/nlhkh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nlhkh/subscriptions","organizations_url":"https://api.github.com/users/nlhkh/orgs","repos_url":"https://api.github.com/users/nlhkh/repos","events_url":"https://api.github.com/users/nlhkh/events{/privacy}","received_events_url":"https://api.github.com/users/nlhkh/received_events","type":"User","site_admin":false},"labels":[{"id":66034912,"url":"https://api.github.com/repos/slick/slick/labels/1%20-%20Ready","name":"1 - Ready","color":"c7def8","default":false},{"id":67602757,"url":"https://api.github.com/repos/slick/slick/labels/improvement","name":"improvement","color":"009800","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2015-10-24T12:20:45Z","updated_at":"2017-03-28T08:17:44Z","closed_at":null,"body":"Suppose I have a simple query like this using `hello-slick-3.1.0`\n\n``` scala\nval query = for {\n    (c, s) <- coffees joinLeft suppliers distinctOn(_._1.name)\n} yield(c, s)\n\nprintln(query.result.statements)\ndb.run(query result)\n```\n\nOne would naturally think that the equivalent sql query is\n\n``` sql\nSELECT DISTINCT ON (c.name) *\nFROM coffees c\nLEFT JOIN suppliers s\nON c.sup_ID = s.id\n```\n\nThis query is simple and execute fast. However, the generated query by Slick is as follow\n\n``` sql\nselect distinct x2.\"STATE\", x2.\"SUP_NAME\", x2.\"CITY\", x2.\"STREET\", x2.\"ZIP\", x3.\"PRICE\", x3.\"SALES\", x3.\"COF_NAME\", x2.\"SUP_ID\", x3.\"TOTAL\", x3.\"SUP_ID\", (case when (x2.\"SUP_ID\" is null) then null else 1 end) from \"COFFEES\" x3 left outer join \"SUPPLIERS\" x2 on 1=1\n```\n\nThis query execute very slow because it misses the optimization of the database with this syntax. In my case when the tables have 10000 rows each, the query timeout after several minutes. On some rare cases the slow query completes in 3 minutes, as opposed to about 200ms with the hand written query.\n\nI think this is a bug since all it has to do is to create a DISTINCT ON statement with the given projection.\n"},"comment":{"url":"https://api.github.com/repos/slick/slick/issues/comments/289697261","html_url":"https://github.com/slick/slick/issues/1340#issuecomment-289697261","issue_url":"https://api.github.com/repos/slick/slick/issues/1340","id":289697261,"user":{"login":"lisinge","id":22270,"avatar_url":"https://avatars3.githubusercontent.com/u/22270?v=3","gravatar_id":"","url":"https://api.github.com/users/lisinge","html_url":"https://github.com/lisinge","followers_url":"https://api.github.com/users/lisinge/followers","following_url":"https://api.github.com/users/lisinge/following{/other_user}","gists_url":"https://api.github.com/users/lisinge/gists{/gist_id}","starred_url":"https://api.github.com/users/lisinge/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lisinge/subscriptions","organizations_url":"https://api.github.com/users/lisinge/orgs","repos_url":"https://api.github.com/users/lisinge/repos","events_url":"https://api.github.com/users/lisinge/events{/privacy}","received_events_url":"https://api.github.com/users/lisinge/received_events","type":"User","site_admin":false},"created_at":"2017-03-28T08:17:44Z","updated_at":"2017-03-28T08:17:44Z","body":"We are also seeing this on slick 3.2.0"}}
{"actor":{"display_login":"lisinge"}}