{"action":"created","issue":{"url":"https://api.github.com/repos/h2database/h2database/issues/1009","repository_url":"https://api.github.com/repos/h2database/h2database","labels_url":"https://api.github.com/repos/h2database/h2database/issues/1009/labels{/name}","comments_url":"https://api.github.com/repos/h2database/h2database/issues/1009/comments","events_url":"https://api.github.com/repos/h2database/h2database/issues/1009/events","html_url":"https://github.com/h2database/h2database/pull/1009","id":307535923,"number":1009,"title":"Fix ARRAY_AGG with ORDER BY and refactor aggregates","user":{"login":"katzyn","id":23182944,"avatar_url":"https://avatars0.githubusercontent.com/u/23182944?v=4","gravatar_id":"","url":"https://api.github.com/users/katzyn","html_url":"https://github.com/katzyn","followers_url":"https://api.github.com/users/katzyn/followers","following_url":"https://api.github.com/users/katzyn/following{/other_user}","gists_url":"https://api.github.com/users/katzyn/gists{/gist_id}","starred_url":"https://api.github.com/users/katzyn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/katzyn/subscriptions","organizations_url":"https://api.github.com/users/katzyn/orgs","repos_url":"https://api.github.com/users/katzyn/repos","events_url":"https://api.github.com/users/katzyn/events{/privacy}","received_events_url":"https://api.github.com/users/katzyn/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-03-22T07:42:51Z","updated_at":"2018-03-22T08:15:06Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/h2database/h2database/pulls/1009","html_url":"https://github.com/h2database/h2database/pull/1009","diff_url":"https://github.com/h2database/h2database/pull/1009.diff","patch_url":"https://github.com/h2database/h2database/pull/1009.patch"},"body":"1. `ARRAY_AGG` with `ORDER BY` returned invalid values. For example, when `(1, 2, 3)` was expected this aggregate returned `((1, 1), (2, 2), (3, 3))`. Cases without `ORDER BY` were not affected. Test unit was malformed. It contained `------` before actual expected data, it's a SQL comment so following expected values were not checked. Test unit is fixed too. I did not notice that during review of #967 because such lines look like wrapped line with separator between table header and values.\r\n\r\n2. `ARRAY_AGG` and `GROUP_CONCAT` now sort values by default if `DISTINCT` is specified and `ORDER BY` is not specified. Before this change order of values was semi-random (they were ordered by hash map). I didn't check different databases, but it looks like PostgreSQL sorts values in such situation. Let's do the same for predictable result. Test cases are added to show that default sort order works as expected and that if explicit sort order is specified it used instead of default ascending ordering.\r\n\r\n3. Code duplication between `ARRAY_AGG`, `GROUP_CONCAT`, and `MEDIAN` is reduced."},"comment":{"url":"https://api.github.com/repos/h2database/h2database/issues/comments/375213251","html_url":"https://github.com/h2database/h2database/pull/1009#issuecomment-375213251","issue_url":"https://api.github.com/repos/h2database/h2database/issues/1009","id":375213251,"user":{"login":"katzyn","id":23182944,"avatar_url":"https://avatars0.githubusercontent.com/u/23182944?v=4","gravatar_id":"","url":"https://api.github.com/users/katzyn","html_url":"https://github.com/katzyn","followers_url":"https://api.github.com/users/katzyn/followers","following_url":"https://api.github.com/users/katzyn/following{/other_user}","gists_url":"https://api.github.com/users/katzyn/gists{/gist_id}","starred_url":"https://api.github.com/users/katzyn/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/katzyn/subscriptions","organizations_url":"https://api.github.com/users/katzyn/orgs","repos_url":"https://api.github.com/users/katzyn/repos","events_url":"https://api.github.com/users/katzyn/events{/privacy}","received_events_url":"https://api.github.com/users/katzyn/received_events","type":"User","site_admin":false},"created_at":"2018-03-22T08:15:06Z","updated_at":"2018-03-22T08:15:06Z","author_association":"CONTRIBUTOR","body":"At least this makes test case with `DISTINCT` and without `ORDER BY` stable. I can revert this change if this is not desired.\r\n\r\n`DISTINCT` with such aggregates without an `ORDER BY` should not be used in normal SQL queries."}}
{"actor":{"display_login":"katzyn"}}