cockroachdb / cockroachdb/cockroach

sql: window functions next to SRFs behave differently from Postgres

Open
#93,386 1 comment 0 reactions 0 assignees View on GitHub
C-bug O-pg-regress T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Consider the following example query, minimized from a more complex one in the `tsrf.sql` pg_regress file:

```
SELECT a, lag(a) OVER(), count(*) OVER(), generate_series(1,2) FROM (select 1) as x(a);
```

CockroachDB evaluates this differently from Postgres. According to the pg_regress test case, this is because SRFs should be computed after window functions: `-- SRFs are normally computed after window functions`

CRDB
```
root@localhost:26257/defaultdb> SELECT a, lag(a) OVER(), count(*) OVER(), generate_series(1,2) FROM (select 1) as x(a);

a | lag | count | generate_series
----+------+-------+------------------
1 | NULL | 2 | 1
1 | 1 | 2 | 2
(2 rows)
```

PG
```
jordan=# SELECT a, lag(a) OVER(), count(*) OVER(), generate_series(1,2) FROM (select 1) as x(a);
a | lag | count | generate_series
---+-----+-------+-----------------
1 | | 1 | 1
1 | | 1 | 2
(2 rows)
```

Jira issue: CRDB-22398

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.