Support window functions like row_number()

[jrmuizel]

This would be nice to have.

[cswinter]

There actually used to be support for an OFFSET clause and it looks like the query engine still supports that functionality, it was only unsupported by the new parser but looks like making this work again it fairly easy: https://github.com/cswinter/LocustDB/pull/136 Once that has landed, queries with an OFFSET <rows> ROWS clause should work.

[cswinter]

Fixed in v0.3.4

[jrmuizel]

I was looking to number the rows with a query like

SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;

[cswinter]

I see. Adding just the row_number() should be relatively straightforward, but the OVER (ORDER BY y) clause is more involved since it requires two separate sort/query passes which the query engine does not currently support. If there was support for arbitrary subqueries it might be possible to implement this as a desugaring compiler pass, though that's an even larger undertaking. I don't expect I will work on this in the foreseeable future.