Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sql: aggregate with both DISTINCT and ORDER BY not supported #43237

Open
RaduBerinde opened this issue Dec 17, 2019 · 3 comments
Open

sql: aggregate with both DISTINCT and ORDER BY not supported #43237

RaduBerinde opened this issue Dec 17, 2019 · 3 comments

Comments

@RaduBerinde
Copy link
Member

@RaduBerinde RaduBerinde commented Dec 17, 2019

root@127.180.249.72:38785/movr> explain (opt) select json_agg(distinct j order by j) from a;
invalid syntax: statement ignored: at or near "order": syntax error
@RaduBerinde RaduBerinde added this to Triage in SQL Planning via automation Dec 17, 2019
@RaduBerinde

This comment has been minimized.

Copy link
Member Author

@RaduBerinde RaduBerinde commented Dec 17, 2019

@namibj

This comment has been minimized.

Copy link

@namibj namibj commented Dec 20, 2019

This bug resulted in nasty relational subquery nesting the optimizer turned into 9 joins, of which 7 are just in a diagonal line in the explain output. And those all are encased in two layers of explicitly-sorting group-by aggregations.
Highly denormalized tables using the left join+coalesce attribute inheritance pattern cause enough query complexity on their own. Things like not having this bug are what we have to stay afloat among these confusing factors.

@RaduBerinde

This comment has been minimized.

Copy link
Member Author

@RaduBerinde RaduBerinde commented Dec 20, 2019

The workaround we suggested offline, along the lines of select json_agg(distinct j) from (select * from a order by j) doesn't require changing the input of the query (a).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
SQL Planning
  
Triage
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants
You can’t perform that action at this time.