goccy / go-zetasqlite

A database driver library that interprets ZetaSQL queries and runs them using SQLite3
MIT License
56 stars 28 forks source link

[Windowing] `ORDER BY .. NULLS FIRST / LAST` not implemented #152

Open ohaibbq opened 8 months ago

ohaibbq commented 8 months ago
WITH toks AS (
  SELECT DATETIME "2024-01-07 00:00:00" as dt
  UNION ALL SELECT DATETIME "2024-01-01 00:00:00"
  UNION ALL SELECT null
)
SELECT dt,
ROW_NUMBER() OVER (ORDER BY dt DESC NULLS FIRST),
ROW_NUMBER() OVER (ORDER BY dt DESC NULLS LAST)
FROM toks

Expected

dt rn1 rn2
2024-01-07 00:00:00 2 1
2024-01-01 00:00:00 3 2
null 1 3

Actual

dt rn1 rn2
2024-01-07 00:00:00 2 2
2024-01-01 00:00:00 3 3
null 1 1
ohaibbq commented 8 months ago

Null ordering can be added to the parser here https://github.com/goccy/go-zetasqlite/blob/main/internal/formatter.go#L1055-L1058 via item.NullOrder()