cockroachdb / cockroachdb/cockroach

ordinary functions accept WITHIN GROUP (ORDER BY ...) without error

Open
#171,236 1 comment 1 reaction 0 assignees View on GitHub
A-sql-pgcompat C-bug O-community T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.