dolthub / dolthub/doltgresql

Window functions can't be evaluated

Open
#1,796 6 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
2.1k
Forks
73
Avg merge
1d 11h
Merged PRs (30d)
152

Description

Discovered when adding tests to dolthub/go-mysql-server#3178. Tests fail in Doltgres but are valid queries in Postgres

```
Query: select row_number() over () as rn from o where c_id=1;
Error: a window function 'ROW_NUMBER' is in a context where it cannot be evaluated.

Query: select o_id, c_id, rank() over(order by o_id) as rnk from o where c_id=1;
Error: a window function 'RANK' is in a context where it cannot be evaluated.

Query: select ship, dense_rank() over (order by ship) as drnk from o where c_id in (1, 2) order by ship;
Error: a window function 'DENSE_RANK' is in a context where it cannot be evaluated.

Query: SELECT * FROM (SELECT c_id AS c_c_id, bill FROM c) sq1, LATERAL (SELECT row_number() OVER () AS rownum FROM o WHERE c_id = c_c_id) sq2 ORDER BY c_c_id, bill, rownum;
Error: a window function 'ROW_NUMBER' is in a context where it cannot be evaluated.
```

Postgres results
```
postgres=# select row_number() over () as rn from o where c_id=1;
rn
----
1
2
3
(3 rows)

postgres=# select o_id, c_id, rank() over(order by o_id) as rnk from o where c_id=1;
o_id | c_id | rnk
------+------+-----
10 | 1 | 1
20 | 1 | 2
30 | 1 | 3
(3 rows)

postgres=# select ship, dense_rank() over (order by ship) as drnk from o where c_id in (1, 2) order by ship;
ship | drnk
------+------
CA | 1
CA | 1
CA | 1
CA | 1
TX | 2
| 3
(6 rows)

postgres=# SELECT * FROM (SELECT c_id AS c_c_id, bill FROM c) sq1, LATERAL (SELECT row_number() OVER () AS rownum FROM o WHERE c_id = c_c_id) sq2 ORDER BY c_c_id, bill, rownum;
c_c_id | bill | rownum
--------+------+--------
1 | CA | 1
1 | CA | 2
1 | CA | 3
2 | TX | 1
2 | TX | 2
2 | TX | 3
4 | TX | 1
4 | TX | 2
6 | FL | 1
(9 rows)
```

Test currently tagged with Dialect: "mysql". Remove dialect tag once fixed.

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.