search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
29.87k stars 3.77k forks source link

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

Open RaduBerinde opened 4 years ago

RaduBerinde commented 4 years ago 
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

Jira issue: CRDB-5288

RaduBerinde commented 4 years ago

This TODO is relevant: https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/parser/sql.y#L8139

namibj commented 4 years ago

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 commented 4 years ago

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).

github-actions[bot] commented 3 years ago

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!

github-actions[bot] commented 12 months ago

We have marked this issue as stale because it has been inactive for 18 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to CockroachDB!