cockroachdb / cockroachdb/cockroach
ordinary functions accept WITHIN GROUP (ORDER BY ...) without error
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
## Describe the problem
CockroachDB allows `WITHIN GROUP (ORDER BY ...)` on non-ordered-set functions such as `concat_ws`. The clause is silently ignored and the function executes as if `WITHIN GROUP` were absent. PostgreSQL rejects this with `function ... is not an ordered set aggregate` (`SQLSTATE: 42809`).
This is a semantic / compatibility bug, not a crash.
**Example signature:**
```
SELECT concat_ws(',', 'a', 'b') WITHIN GROUP (ORDER BY 1);
-- CRDB: 'a,b'
-- PG: ERROR: function concat_ws(unknown, unknown, unknown) is not an ordered set aggregate
-- SQLSTATE: 42809
```
## To Reproduce
Start a single-node cluster for testing, e.g.
```bash
cockroach start-single-node --insecure --listen-addr=localhost
```
Run SQL (via `cockroach sql --insecure` or equivalent):
```sql
SELECT concat_ws(',', 'a', 'b') WITHIN GROUP (ORDER BY 1);
SELECT concat_ws(',', 'a', 'b') WITHIN GROUP (ORDER BY 'x');
```
## Control
Without `WITHIN GROUP`, behavior is correct and matches PostgreSQL:
```sql
SELECT concat_ws(',', 'a', 'b'); -- 'a,b'
```
Ordered-set aggregates that should accept the syntax still work:
```sql
SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY 1);
```
## Expected behavior
Reject `WITHIN GROUP` on functions that are not ordered-set aggregates with a clear user-facing error matching PostgreSQL semantics. Do not silently ignore the clause.
## Environment
- CockroachDB: v26.3.0-alpha.00000000-dev, CCL, linux amd64
- OS: Linux 4.18.0-553.el8_10.x86_64
- Client: `cockroach sql --insecure`
Jira issue: CRDB-64381
Contributor guide
Assessment
This issue has not been assessed yet.